DBMS Project for ipu

TO CREATE A NEW TABLE :

SYNTAX:

Create Table ( Datatype ( size ) , Datatype(size) ,…);

EXAMPLE:

SQL> Create Table Student (Stu_id int , Stu_name varchar(25) , DOB varchar(20) , Stu_marks int);

Table created.




STU_ID STU_NAME DOB STU_MARKS



























TO INSERT INTO A TABLE :

SYNTAX:

Insert into ( , , …) Values( Expression);

EXAMPLE:

SQL> Insert Into Student (Stu_id , Stu_name ,DOB , Stu_marks) Values (1 , ' Vineet gautam ' , '17 Aug 1988' , 70 ) ;

1 row created.

STU_ID STU_NAME DOB STU_MARKS
1 Vineet gautam 17 Aug 1988 70































VIEWING DATA STORED IN A TABLE :

1. ALL COLUMNS , ALL ROWS.

SYNTAX :

SQL> Select * From ;


EXAMPLE :


SQL> Select * From Student ;


STU_ID STU_NAME DOB STU_MARKS

1 Vineet gautam 17 Aug 1988 70
2 Kiran Nagar 13 Jan 1990 50
3 Reetika kaul 10 Jul 1989 73
4 Kishan 24 Dec 1988 65
5 Reetika kaul 10 Jul 1989 73
6 Kiran Nagar 13 Jan 1990 50
7 Vipin 15 Jul 1987 40
























2. SELECTED COLUMNS , ALL ROWS.

SYNTAX :

SQL> Select ( , , …) From ;

EXAMPLE :

SQL> Select (Stu_id , Stu_name ) From Student ;


STU_ID STU_NAME
1 Vineet gautam
2 Kiran Nagar
3 Reetika kaul
4 Kishan
5 Reetika kaul
6 Kiran Nagar
7 Vipin
























3. ALL COLUMNS , SELECTED ROWS.

SYNTAX :

SQL> Select * From Where (Search Condition) ;

EXAMPLE :

SQL> Select * From Student Where (Stu_marks > 50 ) ;



STU_ID STU_NAME DOB STU_MARKS
1 Vineet gautam 17 Aug 1988 70

3 Reetika kaul 10 Jul 1989 73
4 Kishan 24 Dec 1988 65
5 Reetika kaul 10 Jul 1989 73































4. SELECTED CULUMNS , SELECTED ROWS.

SYNTAX :

SQL> Select ( , , …) From Where (Search Condition) ;

EXAMPLE :

SQL> Select (Stu_id , Stu_name ) From Student Where ( Stu_marks > 50 ) ;





STU_ID STU_NAME

1 Vineet gautam
3 Reetika kaul
4 Kishan
5 Reetika kaul



























ELIMINATION OF DUPLIVINEET GAUTAMES USING SLECT STATEMENT :

1. ALL COLUMNS :

SYNTAX :

SQL> Select Distinct * From ;

EXAMPLE :

SQL> Select Distinct * From Student ;





STU_ID STU_NAME DOB STU_MARKS


1 Vineet gautam 17 Aug 1988 70
2 Kiran Nagar 13 Jan 1990 50
3 Reetika kaul 10 Jul 1989 73
4 Kishan 24 Dec 1988 65
7 Vipin 15 Jul 1987 40


















2. SELECTED COLUMNS

SYNTAX :

SQL>Select Distinct ( , ,…) From ;

EXAMPLE :

SQL> Select Distinct (Stu_id , Stu_name) From Student ;







STU_ID STU_NAME

1 Vineet gautam
2 Kiran Nagar
3 Reetika kaul
4 Kishan
7 Vipin






















SORTING DATA IN A TABLE :

SYNTAX:

SQL>Select * From order by ,,… <[Sort order]>;

SORTING IN ASCENDING ORDER
EXAMPLE:
SQL> Select * From Student order by Stu_name ;



STU_ID STU_NAME DOB STU_MARKS
2 Kiran Nagar 13 Jan 1990 50
4 Kishan 24 Dec 1988 65
1 Vineet gautam 17 Aug 1988 70
3 Reetika kaul 10 Jul 1989 73
7 Vipin 15 Jul 1987 40



























SORTING IN DESCENDING ORDER
EXAMPLE:
SQL> Select * From Student order by Stu_name Desc ;






STU_ID STU_NAME DOB STU_MARKS
7 Vipin 15 Jul 1987 40
3 Reetika kaul 10 Jul 1989 73
1 Vineet gautam 17 Aug 1988 70
4 Kishan 24 Dec 1988 65
2 Kiran Nagar 13 Jan 1990 50































CREATING A TABLE FROM ANOTHER TABLE :
SYNTAX:

SQL>Create Table (,,…)
As Select ,,… From ;







CREATING TARGET TABLE WITHOUT OLD RECORDS


EXAMPLE:

SQL>Create Table Student_info (S_no , Name , Birthday)
As Select Stu_id , Stu_name , DOB From Student
Where (Stu_id==0);


S_NO NAME BIRTHDAY






CREATING TARGET TABLE WITHOUT OLD RECORDS

EXAMPLE:
SQL>Create Table Stu_info (S_no , Name , Birthday)
As Select Stu_id , Stu_name , DOB From Student ;


S_NO NAME BIRTHDAY

1 Vineet gautam 17 Aug 1988
2 Kiran Nagar 13 Jan 1990
3 Reetika kaul 10 Jul 1989
4 Kishan 24 Dec 1988
7 Vipin 15 Jul 1987



INSERTING DATA INTO TABLE FROM ANOTHER TABLE:

SYNTAX:

SQL>Insert Into Select , ,… From ;

EXAMPLE:
SQL>Insert Into Student_info Select Stu_id , Stu_name , DOB From Student ;




S_NO NAME BIRTHDAY

1 Vineet gautam 17 Aug 1988
2 Kiran Nagar 13 Jan 1990
3 Reetika kaul 10 Jul 1989
4 Kishan 24 Dec 1988
7 Vipin 15 Jul 1987



























INSERT ING A DATASET INO A TABLE FROM ANOTHER TABLE
SYNTAX:

SQL>Insert Into Select , ,… From Where( Condition);

EXAMPLE:
SQL>Insert Into Student_info Select Stu_id , Stu_name , DOB From Student
Where ( Stu_marks > 50 ) ;






S_NO NAME BIRHDAY
1 Vineet gautam 17 Aug 1988
3 Reetika kaul 10 Jul 1989
4 Kishan 24 Dec 1988
5 Reetika kaul 10 Jul 1989

























DELETE OPERATIONS :

1. REMOVAL OF ALL ROWS.

SYNTAX:
Delete from ;

EXAMPLE:
Delete From Stu_info;


S_NO NAME BIRTHDAY




2. REMOVAL OF SPECIFIC ROWS.

SYNTAX:
Delete From Where (Condition) ;

EXAMPLE:
Delete From Student_info Where (S_no>=5);

S_NO NAME BIRHDAY
1 Vineet gautam 17 Aug 1988
3 Reetika kaul 10 Jul 1989
4 Kishan 24 Dec 1988






3.REMOVAL OF SPECIFIC ROWS BASED ON DATA HELD BY THE OTHER TABLE.

EXAMPLE:
Delete from student