Database (Queries)

• Database

     Database is collection of entity(table).

 

college- database

student- (roll,name,city)

faculty- (name,designation,experiance)

• Types of database

1.local database

2. global database (web database)

• Database terminology:

1. Field (Representing the records)

    roll  name    

     1    mohan

     2    soham

     

•  Types of field

1. Single value field(roll no)

2. Multi value field(first name,last name,middle name)

3. Null value field(No compulsory data field

• Database Tupple (imp):

Complete information in a table called tupple 

• Database Management:

  With the help of key manage complete database

• key

 key is a special type of field or managing complete information

types of key

1. Primary key

2. Unique key 

3. Foreign key

• Primary key

  There are two properties 

1. No blank

2. No repeat

 eg. Roll no

syntax: create table primary1 (id int PRIMARY KEY,name varchar(50));

• Unique Key 

   There are two properties

1. Blank 

2. No repeat

 eg. Mobile no 

• Foreign key

   it is called Refrencial key

In foreign key both table are used in commen field.

eg.

Mark           detail

Roll

semester

Subject

• Database create in dot net

database extention mdf(microsoft database file)

• how to create database?

select the root , website menu , add new item(c#) , Sql sever data base , change database name 

Query: set of instruction apply in database.

• Basic query:

1: Insert 

2: update

3: delete

4: select

5: create table query

Table name : Student

Column name (roll,name,city)

Column Data type

 1.Number- int

 2.String- varchar

; 3.Number+charcter- nvarchar

1.Create table query :   

 Create table Student( Roll int, name varchar(50), city varchar(50));

•   how to open query editor

   go to the server explorer-> Right click on database-> New query-> cancel

2.Insert query

    Note:All string value stored in single cotation 'Ramesh'

insert into Student values (102, 'Mohan', 'pune');

3.Select query (display record)

    select * from student;

4. delete query

    delete from testtb where id=101;

5.Update query

    update student set age=15 where id=101;

 

Note: column name does not use blank space

• Database clause

1.where clause

2.Between

3.Group by

4.Order by 

5.In

1.Where clause 

   Apply for comdition 

select start from student where roll=101;

Select* from student where city='nagpur';

2.Group by clause

    select city from student group by city;

3.order by

select* from student order by roll;

4.In (used in multiple string searching)

    select* from student where city in ( 'nagpur','mumbai');

5.Between

   select* from student where roll between 102 and 107;

15 queries generate using clause

where age is>18

Error

there is already object.

solution: if table table name is already exist

• conversion failed

solution: mismatched data value 

• How to create primary key

no blank 

no repeat

• Not Null

 this type of column cannot be blank

• how to set not null column

 create table Nulltb (id int, mobile varchar(20)not NULL);

• Database Alias

  change the heading of the column using alias

•  syntax

select name as 'student name' from mystu

• Alter query

  Alter query is basically used to update table structure 

1. add new column in data table

     alter table student ADD Age int;

• how to remove column

 alter table student drop column age;

• View(most imp)

  1. virtual table

  2. view stored query result

• view operation

  1. create view

  2. update view

  3. delete view

• view syntax: 

   create view myrecord AS select * from testdb1 

• how to display view record

  select * from myrecord

• how to update view

 for change using alter command 

 alter view myrecord2 AS select * from myrecord where city='pune';  

 

• parameter query

      Basic query fundamental('"+ +"') 

      does not accept special symbol(ramesh's)

solve this concept we have to use parameter query

 1.Parameter is used to parameter variable

 2.All parameter variable represent @ symbol

eg: @roll1 belongs to roll column 

    @name1 belongs to name column 

  

• Example of parameter query 

string k = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\college.mdf;Integrated Security=True;User Instance=True";

        cn = new SqlConnection(k);

        cn.Open();

        //how to use parameter query

        string k1 = "insert into student(roll,name,city) values (@roll1,@name1,@city1)";

        cm = new SqlCommand(k1, cn);

        //how to pass data value in a variable

        cm.Parameters.AddWithValue("roll1",TextBox1.Text);

        cm.Parameters.AddWithValue("name1", TextBox2.Text);

        cm.Parameters.AddWithValue("city1", TextBox3.Text);

        cm.ExecuteNonQuery();