MySQL Queries I
Write SQL queries the following based on the tables.
TABLE: TRAINER
TID TNAME CITY HIREDATE SALARY
101 SUNAINA MUMBAI 1998-10-15 90000
102 ANAMIKA DELHI 1994-12-24 80000
103 DEEPTI CHANDIGARH 2001-12-21 82000
104 MEENAKSHI DELHI 2002-12-25 78000
105 RICHA MUMBAI 1996-01-12 95000
106 MANIPRABHA CHENNAI 2001-12-12 69000
TABLE: COURSE
CID CNAME FEES SARTDATE TID
C201 AGDCA 12000 2018-07-02 101
C202 ADCA 15000 2018-07-15 103
C203 DCA 10000 2018-10-01 102
C204 DDTP 9000 2018-09-15 104
C205 DHN 20000 2018-08-01 101
C206 O LEVEL 18000 2018-07-25 105
(I) Display the Trainer Name, City & Salary in descending order of their Hiredate. ANS:
SELECT TNAME, CITY, SALARY FROM TRAINER ORDER BY HIREDATE;
(II) To display the TNAME and CITY of Trainer who joined the Institute in the month of
December 2001.
ANS: SELECT TNAME, CITY FROM TRAINER WHERE HIREDATE BETWEEN
‘2001-12-01’ AND ‘2001-12-31’;
(III) To display TNAME, HIREDATE, CNAME, STARTDATE from tables TRAINER
and COURSE of all those courses whose FEES is less than or equal to 10000. ANS:
SELECT TNAME,HIREDATE,CNAME,STARTDATE FROM TRAINER, COURSE
WHERE [Link]=[Link] AND FEES<=10000;
(IV) To display number of Trainers from each city.
ANS: SELECT CITY, COUNT(*) FROM TRAINER GROUP BY CITY;
(V) Insert a new column named as “PHONE_NO” into the table TRAINER.
ANS: ALTER TABLE TRAINER ADD PHONE_NO VARCHAR(12);
(VI) Add a new record in to the table COURSE with some appropriate values. ANS:
INSERT INTO COURSE VALUES(“C207”, “PGDCA”, 35000, “2020-10-11”, 103);
MySQL Queries II
Write SQL queries the following based on the tables.
TABLE: WATCHES
Watch_Id Watch_Name Price Type Qty_Store
W001 High Time 10000 Unisex 100
W002 Life Time 15000 Ladies 150
W003 Wave 20000 Gents 200
W004 High Fashion 7000 Unisex 250
W005 Golden Time 25000 Gents 100
TABLE: SALE
Watch_Id Qty_Sold Quarter
W001 10 1
W003 5 1
W002 20 2
W003 10 2
W001 15 3
(I) To display all the details of those watches whose name ends with ‘Time’
ANS: SELECT * FROM WATCHES WHERE Watch_Name LIKE ‘%Time’;
(II) To display watch’s name and price of those watches which have price range in
between 5000-15000.
ANS: SELECT Watch_Name, Price FROM WATCHES WHERE Price BETWEEN 5000
AND 15000;
(III) To display total quantity in store of Unisex type watches.
ANS: SELECT SUM(Qty_Store) FROM WATCHES WHERE TYPE=’Unisex’;
(IV) To display watch name and their quantity sold in first quarter. ANS: SELECT
Watch_Name, Qty_Sold FROM WATCHES W, SALE S WHERE W.Watch_Id =
S.Watch_ID and Quarter=1;
(V) Delete all the watch details from the table WATCHES whose price is more than
15000.
ANS: DELETE FROM WATCHES WHERE Price BETWEEN 10000 AND 15000.
(VI) To display number of watches in each type from the table WATCHES.
ANS: SELECT TYPE, COUNT(*) FROM WATCHES GROUP BY TYPE;