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
   220   221   222   223   224   225   226   227   228   229   230