sql commands

 SQL Exercises- 2


Exercises:

1. List all the tables that you have created.

2. Using select statement create a new table called Buspath which has the same structure and rows of Busroute table.

3. Delete all the contents of “transport” table.

4. Create a table Busclass similar to the table Busdiv using select statement.

5. Insert multiple records in the table Busclass using ‘&’.

6. Create another table Busorder with the following structure and default values.

Table name: Busorder
Field 1: S.No. (6) default value = 01
Field 2: Code number (3) default value=’NonStop’.

7. Insert the values according to the following conditions:

 
S.No
Code
Route
(default)
130
(default)
02
(default)
PP
03
131
(default)

 

8. Add a new column (route number (2)) in the Busorder table.

9. Change default value for code as 231 in the Busorder table.

10. Change default value for S.No. as 05 in the Busorder table.

11. Insert the following values in the Busorder table.

 
S.No.
Code
Class
Route
(default)
(default)
FP
Null
05
(default)
Normal
Null
06
(default)
(default)
Null
Null
(default)
Null
Null

 

12. Display only distinct code and class from Busorder table.

13. Update the Bus order table as per the given values.

 
S.No.
Code
Class
Route
01
131
Non Stop
201
02
131
PP
202
03
131
Non Stop
203
04
231
FP
204
05
231
Normal
205
06
231
FP
206

14. Display fare for a normal bus originating from Chennai and terminating at Cochin.

15. Display only distinct buscode and distance with highest distance as first row and lowest distance as last row (use order by clause).

16. Display all buscodes and distances with lowest distance as first row and highest distance as last row.

17. Display origin and destination of only superfast buses.

18. Delete all rows from the busclass table using delete command.

19. Drop the busclass table.


Solutions:
1. Select * from tab;

2. Create table buspath as select * from busroute;

3. Truncate table transport;

4. Create table busclass as select * from busdiv;

5. Insert into busclass values (&bcode, ‘&bdescription’);

6. Create table busorder (Sno number (6) default 01, Code number (3) default 131, Clas chart (15) default ‘nonstop’);

7. Insert into busorder (code) values (130);
Insert into busorder (Sno, class) values (02, ‘PP’);
Insert into busorder (Sno, Code) values (03, 131);

8. Alter table busorder add (route number (2));

9. Alter table busorder modify (code number (3) default 231);

10. Alter table busorder modify (Sno number (6) default 04);

11. Insert into busorder (class) values (‘FP’);
Insert into busorder (Sno, class) values (05, normal);
Insert into busorder (Sno) values (06);
Insert into busorder (Sno, Class) values (null, null);

12. Select distinct (code) from busorder;

13. Update busorder set code=131 where Sno=01;
Update busorder set route=201 where Sno=01;
( Follow the above procedure and update your table accordingly).

14. Select fare from busroute where buscode=01 and origin=’Chennai’ and dest=’Cochin’;

15. Select Distinct (buscode), dist from busroute order by dist;

16. Select buscode, dist from busroute order by dist desc;

17. Select origin, dest from busroute where buscode=03;

18. Delete from busclass.

19. Drop table busclass.

 

Comments