Page 228 - Informatics_Practices_Fliipbook_Class12
P. 228

b.  SELECT P.TNO, TNAME, COUNT(*) AS "NoOfBookings"

                     FROM TRAINS T, PASSENGERS P
                     WHERE (T.TNO = P.TNO)
                     GROUP BY P.TNO, TNAME
                     HAVING COUNT(*)>1;
           4.  Consider  a  database  having  three  tables:  SUPPLIER,  PRODUCT, and  SHIPMENT.  A  supplier  has  the  following
              attributes: supplier number (SNo), supplier name  (Sname), and the location (SCity) of the supplier. Each product
              has a product name (Pname). Products of different brands are distinguished from each other and are assigned different
              product numbers (PNo). It is assumed that a product of a particular brand (Brand) is manufactured only in one city
              (Pcity). An order is  identified by (OrderNo) and includes a certain quantity (Quantity) of only one product
              supplied by a particular supplier at a specific price (Price).

         SUPPLIER                      PRODUCT                                SHIPMENT

         SNo Sname          SCity      PNo Pname          Brand    City        OrderNo SNo PNO Price    Quantity
         101 Aradhaya Pvt. L  Mumbai   201 Refrigerator   Whirlpool Chennai    1000   101 201   50000.00   50
         102 XYZ Enterprises  Bangalore  202 Washing Machine Samsung  Kolkata  1001   101 202   70000.00   30

         103 Komal Enterprise Delhi    203 Television     Sony     Mumbai      1002   102 202   90000.00   75
         104 Cloudtail      Patna      104 Television     Samsung  Kolkata     1003   104 205   25000.00   20
         105 PQR Enterprises Uttrakhand  205 Washing Machine Whirlpool Chennai  1004  105 205   55000.00   30
         106 Tech Solutions  Bangalore  206 Refrigerator  Whirlpool Delhi      1005   102 207   48000.00   50

         107 XYZ Enterprises  Mumbai   207 Microwave Oven  IFB     Mumbai      1006   105 210   23000.00   30
                                       208 Microwave      Samsung  Mumbai      1007   104 209   60000.00   75
                                       209 Air Fryer      IFB      Chandigarh  1008   102 208   65000.00   20
                                       210 Air Fryer      Philips  Lucknow     1009   103 207   81000.00   30
                                                                               1010   106 204   56000.00   30

                                                                               1011   107 210   38000.00   50
                                                                               1012   101 201   72000.00   50

              Write a SELECT statement for the following queries:

              (i)  Queries involving tables PRODUCT  and SHIPMENT
                  a.  Retrieve the order number and name of the product supplied in each shipment.

                  b.  Retrieve the order number of the products of the Whirlpool brand.
                  c.  Retrieve the product names sold at a price greater than 70000.
                  d.  For each product, retrieve the minimum price and the supplier number of the supplier supplying that product.
                  e.  For each city, find the total quantity shipped from that city.

              (ii)  Queries involving tables SUPPLIER and SHIPMENT
                  a.  Retrieve the supplier names for suppliers who supply the product with PNo  201.
                  b.  Retrieve the names of suppliers who have supplied more than two orders.
                  c.  Retrieve the number of orders supplied by each supplier.
                  d.  Retrieve the supplier names for suppliers who supply from Mumbai, and the order exceeds 50000.
                  e.  Retrieve the supplier names for suppliers who supply from Mumbai and supply quantity exceeding 40.
                  f.  Retrieve the details of shipments supplied by suppliers from Karnataka.




          214  Touchpad Informatics Practices-XII
   223   224   225   226   227   228   229   230   231   232   233