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
   183   184   185   186   187   188   189   190   191   192   193