Page 224 - Informatics_Practices_Fliipbook_Class12
P. 224
(iv) To display the Category, house name and house captain of athletes that are participating in 100m.
(v) To display gender wise count of athletes.
(vi) To display cartesian product of the two tables.
Ans. (i) SELECT Name, HouseName
FROM Athlete A, House H
WHERE A.HouseId=H.HouseId;
(ii) SELECT HouseName, HouseMaster
FROM Athlete A, House H
WHERE A.HouseId=H.HouseId
AND Category="Senior";
(iii) SELECT Category, COUNT(*)
FROM Athlete
GROUP BY Category;
(iv) SELECT Category, HouseName, HouseCaptain
FROM Athlete A, House H
WHERE A.HouseId=H.HouseId
AND (Event1="100m" OR Event2="100m");
(v) SELECT GENDER, COUNT(*)
FROM Athlete
GROUP BY GENDER;
(vi) SELECT * FROM Athlete, House;
2. Qala is a watch dealer and runs a store named 'Timepiece'. She wants to keep a record of all watches that are for sale in her
shop. At the same time, she also wants to keep a record of all her customers. As a database expert, you have created two
tables - Watches and Customers and have also inserted records as per the data provided by Qala.
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
T007 Zoop 8987 Kids Titan 2019-03-18
Table: Timepiece_Customers
CID CName ConatctNo WatchId
101 Sulekha 9090909090 T002
103 Jonathan 8888899999 T006
104 Gurvinder T001
105 Pradipto 7676767634 T005
Qala wants to retrieve specific data from the database. As per the given tables, write queries for each of the following:
(i) To display the Customer name and the watch name for all customers.
(ii) To display names of all customers who have purchased a watch of brand Apple.
(iii) To display the names of the brand in reverse alphabetical order.
(iv) To display the minimum price and latest purchase date of each brand.
(v) To increase the price of each watch by 10%.
(vi) To delete the record of customer whose contact no. is not known.
210 Touchpad Informatics Practices-XII

