Page 188 - Informatics_Practices_Fliipbook_Class12
P. 188
SHOWDATE DATE,
TICKETPRICE INT
);
(ii) Queries:
a. SELECT TITLE FROM THEATRE WHERE AUDI=1;
b. SELECT * FROM THEATRE WHERE LANGUAGE="Hindi";
c. SELECT TITLE FROM THEATRE WHERE TICKETPRICE>200;
d. SELECT LANGUAGE, COUNT(*) FROM THEATRE
GROUP BY LANGUAGE
HAVING LANGUAGE IN ("HINDI","ENGLISH");
e. SELECT TITLE FROM THEATRE
ORDER BY TITLE;
f. ALTER TABLE THEATRE
ADD TROUPE CHAR(40) NOT NULL;
g. DELETE FROM THEATRE WHERE AUDI=1;
2. Ganesh is a whole sale trader of stationeries. He has many dealers working under him. As his business is expanding, it is
becoming difficult for him to maintain the records of each dealer. Therefore, he requests you to create a table in MySQL
that stores the details of dealers. You have created the table as given below:
Table: Dealer
DealerCode DName City ContactNo TurnOver
D001 Verma Stationers Amritsar 9898989898 9834.80
D002 Sindhi Brothers Surat 1111199999 123.45
D003 Perfect Stationers Delhi 1234567890 4092.00
D004 Roy and Sons Kolkata 7164
D005 India Stationers Delhi 9876987600 567.81
Write the following queries to help Ganesh retrieve the desired records from the table:
(i) To display the records in alphabetical order of names of dealers.
(ii) To display the name of the dealers along with their turnover.
(iii) To display the name and contact number of dealers who are not from Delhi.
(iv) To display the records of dealers whose name begins with an 'R'.
(v) To display the average turnover of all dealers.
(vi) To display the names of dealers whose contact number is not known.
Ans. (i) SELECT * FROM Dealer
ORDER BY DName;
(ii) SELECT Dname, TurnOver FROM Dealer;
(iii) SELECT DName, ContactNo FROM Dealer
WHERE City!="Delhi";
(iv) SELECT * FROM Dealer
WHERE DName LIKE "R%";
(v) SELECT AVG(TurnOver) FROM Dealer;
(vi) SELECT DName FROM Dealer
WHERE ContactNo IS NULL;
174 Touchpad Informatics Practices-XII

