IMPORTANT PRACTICAL QUESTIONS
OF
MYSQL
CS
Q1.
GARMENT
GCODE GNAME SIZE COLOUR PRICE
111 TShirt XL Red 1400.00
112 Jeans L Blue 1600.00
113 Skirt M Black 1100.00
114 Ladies Jacket XL Blue 4000.00
115 Trousers L Brown 1500.00
116 Ladies Top L Pink 1200.00
(i) To display names of those garments that is available in ‘XL’ size.
(ii) To display codes and names of those garments that has their names starting with ‘Ladies’.
(iii) To display garments names ,codes and prices of those garments that have price in the range 1000.00
to 1500.00 (both 1000.00 and 1500.00 included).
(iv) To change the colour of garment with code as 116 to ‘Orange’.
A1. (i) SELECT GNAME FROM GARMENT WHERE SIZE=’XL’;
(ii) SELECT GCODE,GNAME FROM GARMENT WHERE GNAME LIKE ‘Ladies%’;
(iii) SELECT GNAME,GCODE,PRICE FROM GARMENT WHERE PRICE BETWEEN 1000.00 AND 1500.00;
(iv) UPDATE garment SET COLOUR=’Orange’ WHERE GCODE=116;
Q2. STUDENTS
RollNo Name Class DOB Gender City Marks
1 Nanda X 6/6/95 M Agra 551
2 Saurabh XII 7/5/93 M Mumbai 462
3 Sanal XI 6/5/94 F Delhi 400
4 Trisla XII 8/8/95 F Mumbai 450
5 Store XII 8/10/95 M Delhi 369
6 Marisla XI 12/12/94 F Dubai 250
7 Neha X 8/12/95 F Moscow 377
8 Nishant X 12/6/95 M Moscow 489
(i) To display all information about class XII students.
(ii) To list the names of students of class X.
(iii) To list the names all class of all students in descending order of DOB.
(iv) To count the number of student in XII Class of Mumbai city.
(v) SELECT DISTINCT(Gender) FROM STUDENTS;
(vi) SELECT Gender, AVG (Marks) FROM STUDENTS GROUP BY Gender;
(vii) SELECT COUNT(*) FROM STUDENTS WHERE Class=‘XI’;
(viii) SELECT MAX(Marks) FROM STUDENTS;
A2. (i) SELECT * FROM STUDENTS WHERE Class=’XII’;
(ii) SELECT Name FROM STUDENTS WHERE Class=’X’;
(iii) SELECT Name FROM STUDENTS ORDER BY DOB DESC;
(iv) SELECT COUNT(*) FROM STUDENTS WHERE Class=’XII’ AND City=‘Mumbai’;
(v) MF
(vi) M=467.75
F=369.25
(vii) 2
(viii) 551
Q3. SUPPLIER
Scode Pname Supname Qty City Price
101 Coffee Nestle 200 Kolkata 55.00
102 Biscuit Hide & Seek 100 Delhi 10.00
103 Jam Kissan 110 Kolkata 25.00
104 Maggi Nestle 150 Mumbai 10.00
105 Chocolate Cadbury 170 Delhi 25.00
106 Sauce Maggi 56 Mumbai 55.00
107 Cake Britannia 72 Delhi 10.00
(i) To display names of the products, whose Pname starts with ‘B’ in ascending order of Price?
(ii) To display Supplier code, Product name and City of the products whose quantity is less than 150.
(iii) To count distinct product name in the table.
(iv) To insert a new row in the table Supplier.
110, ‘Bournvita’, ‘ABC’, 170, ‘Delhi’, 40.00;
(v) SELECT Pname from Supplier where Pname IN ("Bread", "Maggi");
(vi) SELECT count(distinct (City)) from Supplier;
(vii) SELECT max(Price) from Supplier where City= "Kolkata";
A3. (i) SELECT Pname from Supplier where Pname Like "B%" order by Price;
(ii) Select Scode , Pname , City from supplier where Qty<150;
(iii) Select Count(distinct Pname) from Supplier;
(iv) insert into Supplier values(110,"Bournvita", "ABC", 170, "Delhi", 40.00)
(v) Maggi
(vi) 3
(vii) 55.00
Q4. Write SQL commands for the statements (a) to (d) and give outputs for SQL queries (e) to (h).
BOOKS
BId BookName AuthorName Publisher Price Type Quantity
C01 Fast Cook Lata Kapoor EPB 355 Cookery 5
F01 The Tears William Hopkins First 650 Fiction 20
T01 My C++ Brain & Brooke EPB 350 Text 10
T02 C++ Brain [Link] TDH 350 Text 15
F02 Thuderbolts Anna Roberts First 750 Fiction 50
(i) To list the names from books of Text type.
(ii) To display the names and price from books in ascending order of their price.
(iii) To increase the price of all books of EPB publishers by 50.
(iv) To display the BookName, Quantity and Price for all C++ books.
(v) Select max(price) from books;
(vi) Select count (DISTINCT Publisher) from books where Price >=400;
(vii) Select BookName, AuthorName from books where Publisher = ‘First’;
(viii) Select min (Price) from books where type = ‘Text’;
A4. (i) SELECT BookName FROM BOOKS WHERE Type = ‘Text’;
(ii) SELECT Book_Name, Price FROM BOOKS ORDER BY Price;
(iii) UPDATE BOOKS SET Price = Price + 50 WHERE Publisher = ‘EPB’;
(iv) SELECT BookName, Quantity, Price FROM BOOKS WHERE BookName LIKE ‘%C++%’;
(v) 750
(vi) 1
(vii) The Tears William Hopkins
Thuderbolts Anna Roberts
(viii) 350
Q5.
HOSPITAL
No Name Age Department Dateofadm Charges Gender
1 Arpit 62 Surgery 21/01/98 300 M
2 Zarina 22 ENT 12/12/97 250 F
3 Kareem 32 Orthopedic 19/02/98 200 M
4 Arun 12 Surgery 11/01/98 300 M
5 Zubin 30 ENT 12/01/98 250 M
6 Ketaki 16 ENT 24/02/98 250 F
7 Ankita 29 Cardiology 20/02/98 800 F
8 Zareen 45 Gynecology 22/02/98 300 F
9 Kush 19 Cardiology 13/01/98 800 M
10 Shilpa 23 Nuclear Medicine 21/02/98 400 F
(i) To select all the information of patients of cardiology department.
(ii) To list the names of female patients who are in ENT department.
(iii) To list the names of all patients with their age in ascending order.
(iv) To count the number of patients with Age<30.
(v) To display Patients Name, Charges, Age for only female patients.
(vi) To list the names of all patients with their date of admission in ascending order.
(vii) To insert a new row in the HOSPITAL table with the following data:
11, ’Aftab’, 24, ’Surgery’, {25/02/98}, 300, ’M’
A5. (i) select * from hospital where department=’Cardiology’;
(ii) select name from hospital where gender=’F’ and department=’ENT’;
(iii) select name,age from hospital order by age ASC;
(iv) select count(*) from hospital where age<30;
(v) select Name,charges,age from hospital where Gender=’F’;
(vi) select name, dateofadm from hospital order by dateofadm;
(vii) insert into hospital values(11,’aftab’,24,’surgery’,{25/02/98},300,’M’)