Page 193 - Informatics_Practices_Fliipbook_Class12
P. 193
Case Based Questions
1. Sarfaraz runs a stationary shop. He wants to store the details of stationary so that he can retrieve the desired data as and
when required. The database designer has created the following table:
Table: Stationary
SCODE SNAME PRICE QTY BRAND
S001 Gel Pen 55 10 Reynolds
S002 Permanent Marker 60 5 Luxor
S003 White Board Marker 45 10 Luxor
S004 Paper Ream 310 4 Sando
S006 Pencil 15 Natraj
S009 Colouring Book 70 3 Artist
S101 Eraser 15 5 Natraj
S102 Sharpener 6 12 Natraj
Sarfaraz wants to retrieve some data from the table. Help him by writing the queries for the following:
(i) To display the structure of the table.
(ii) To display the records whose quantity is less than 5.
(iii) To display the name and price of those stationary items whose names end with 'Marker'.
(iv) To display the price of the most expensive item.
(v) To display the average price of all items.
(vi) To display the brand name and count of items of each brand.
(vii) To display the total number of records in a table.
(viii) To display the records in descending order of quantity.
(ix) To display the records whose price is not known.
(x) To display the name of each brand only once.
2. Tanmay is very fond of watches, just like his father. His father has a very good collection of watches. As a student of class
12, he learnt to create tables and perform queries in a MySQL database. He has created the table, Watches as given below:
Table: Watches
WatchId WName Price Type Brand YOP
T001 Apple iWatch 56000 Smart watch Apple 2020-12-12
T002 Titan Raga 25673 Designer Titan 2015-04-15
T003 NeoSplash 34908 Sports Watch Neo Sports 2018-11-09
T005 G-Shock 13892 Sports Watch Casio 2021-03-18
T006 Max Pro 15673 Designer Maxima 2014-06-03
Write the queries for the following:
(i) To display the name and price of each watch.
(ii) To change the price of Apple iWatch to 59350.
(iii) To display the records of all Sports watches.
(iv) To display the names of all brands, without repetition.
(v) To display the count of designer watches.
(vi) To display the count of watches of each brand.
(vii) To display the name and brand of watches that have been purchased in the year 2020.
Database Query using SQL 179

