DATABASE CONCEPTS - RDBMS Tools

Practical 01
Rakul has recently started working on RDBMS(Relational Data-base Management System).
He made a table “Item” given below. Write the queries given below:

Item_Id Item_code Name Quantity Price

4001 SP Soap 5000 71.23

4002 TB Tooth Brush 8000 34.50

4003 TP Tooth Paste 2000 138.90

4004 PL Pencil Drawing 10000 1.25

4005 NB Notebook 8000 34.67

4006 PL_B Pencil Black 7844 2.67

1.​ Display all the records in ascending order of name.

2.​ Display those items where quantity is more than 3000 and less than 9000.
3.​ Display distinct item name
4.​ Display those items whose price is between 20 to 100.
5.​ Display all the records in descending order of price.

Practical 02
Write the SQL statements for the table “Trainee” given below

No Name Monthly_fee Gender Branch_id DateOfJoin

1012 Amandeep 30000 M A01 2012-01-02

1102 Avisha 25000 F A02 2009-03-24

1103 Ekant 30000 M A02 2011-11-04

1049 Arun 40000 M A02 2009-05-06

1025 Amber 30000 M A02 NULL

1106 Eisha 40000 F A05 2010-11-12

1017 Nikita 35000 F A03 2012-12-04

1108 Arleena 30000 F A03 NULL

1101 Shakthi 25000 M A04 2011-03-23

1.​ Display the names of applicants, who are paying a fee of either 30000 or 40000.
 2.​ Display names and annual fee of all male applicants.
3.​ Display name, branch_id who joined in the year 2009.
4.​ Display the details of those trainees whose date of joining is not mentioned.
5.​ Display the trainee details in the descending order of name.
6.​ Change the monthly fees of Arleena to 40000.
7.​ Delete trainee details who have not paid monthly fee of Rs.30000.
8.​ Delete those records where name starts with the letter “A” and ends with letter “A”.

Practical 03
What will be the output of the following SQL statement with reference to the “Student” table
given below:

No Name Stipend Stream avg_marks Grade Class

1 Karan 400.00 Medical 78.5 B 12B

2 Divakar 450.00 Commerce 89.2 A 11C

3 Divya 300.00 Commerce 68.6 C 12C

4 Arun 350.00 Humanities 73.1 B 12C

5 Sabina 500.00 Non-medical 90.6 A 11A

6 John 400.00 Medical 75.4 B 12B

7 Robert 250.00 Humanities 64.4 C 11A

8 Rubina 450.00 Non-medical 88.5 A 12A

9 Vikas 500.00 Non-medical 92.0 A 12A

10 Mohan 300.00 Commerce 67.5 C 12C

1.​ SELECT Name FROM Student WHERE not avg_marks > 68;
2.​ SELECT Name, Stipend FROM Student WHERE Stipend between 400 and 500;
3.​ SELECT distinct Class FROM Student;
4.​ SELECT Stream, avg_marks FROM Student WHERE Stream = “non-medical”;

Practical 04
Given the table CARDEN having following data:
CCode CarName Company Color Capacity Charges

501 A-Star Suzuki Red 3 14

503 Indigo Tata Silver 3 12

502 Innova Toyota White 7 15

509 Qualis Toyota Silver 4 14

510 Wagon R Suzuki Red 4 35

 Write SQL Commands for the following
a)​ Display all the details where capacity is more than 3.
b)​ Display the capacity column.
c)​ Change the charges to 13 where the color is Red.
d)​ Display car details in ascending order of car name.

Practical 5
In a Fashion Store’s database, there is a table ‘Customer’ as given below

Acc_No Cust_Name Cust_City Cust_Phone Open_Bal

1001 Dharmesh Ambala 9710557614 10000

1002 Saniya Patna 8223545233 15000

1003 Joe New Delhi 9972136576 13000

1004 Mrinal New Delhi 9321305453 12000

1005 Ikshaan Agra 9809876798 19000

With reference to, the above given table, what will be the output of the following commands
a)​ SELECT Cust_Name FROM Customer WHERE Open_Bal < = 15000;
b)​ SELECT Cust_Name FROM Customer WHERE Cust_City like ‘%a%’;
c)​ SELECT Acc_No FROM Customer WHERE Cust_City=’New Delhi’;
d)​ SELECT Cust_Name, Acc_No FROM Customer WHERE Acc_No is NOT NULL;