Page 225 - Informatics_Practices_Fliipbook_Class12
P. 225
Ans. (i) SELECT CName, WName FROM Timepiece_Customers T, Watches W
WHERE T.WatchId=W.WatchId;
(ii) SELECT CName, Brand FROM Timepiece_Customers T, watches W
WHERE T.WatchId=W.WatchId
AND Brand="Apple";
(iii) SELECT Brand FROM Watches
ORDER BY Brand DESC;
(iv) SELECT MIN(Price),MAX(YOP) FROM Watches
GROUP BY Brand;
(v) UPDATE Watches
SET Price=Price+(10/100*Price);
(vi) DELETE FROM Timepiece_Customers
WHERE ContactNo IS NULL;
3. Refer to question no 2. If the following queries are given, what will be the output?
(i) SELECT WName, Price , CName FROM Timepiece_Customers T, watches W
WHERE T.WatchId=W.WatchId
AND price <50000;
(ii) SELECT CName, Price FROM Timepiece_Customers T, watches W
WHERE T.WatchId=W.WatchId
AND ContactNo IS NULL;
(iii) SELECT DISTINCT WName, CName FROM Timepiece_Customers T, watches W
WHERE T.WatchId=W.WatchId;
(iv) SELECT Type,MAX(Price) FROM watches
GROUP BY Type
HAVING TYPE!="Kids";
(v) SELECT COUNT(*) FROM Timepiece_Customers;
Ans. a. +------------+------------+ ------------+ b. +------------+------------+
| Wname | Price | CName | | Cname | Brand |
+------------+------------+ ------------+ +------------+------------+
| Titan Raga | 25416 | Sulekha | | Gurvinder | Apple |
+------------+------------+
| Max Pro | 15516 | Jonathan |
| G-Shock | 13753 | Pradipto |
+------------+------------+ ------------+
c. +---------------+------------+ d. +--------------+------------+
| Wname | CName | | Type | MAX(Price) |
+--------------+------------+
+---------------+------------+
| Smart Watch | 58757 |
| Titan Raga | Sulekha |
| Designer | 25416 |
| Max Pro | Jonathan |
| Sports Watch | 34559 |
| Apple iWatch | Gurvinder |
| G-Shock | Pradipto | +--------------+------------+
+---------------+------------+
e. +-----------+
| COUNT(*) |
+-----------+
| 4 |
+-----------+
SQL: Working with Two Tables 211

