Question

Consider the table SHOPPE given below. Write Command in MySQL for (i) to  (iv) and output for (v) to (vii).                         

                              Table : SHOPPE

Code	Item	Company	Qty	City	Qty
102	Biscuit	Hide & Seek	100	Delhi	10.00
103	Jam	Kishan	110	Kolkata	25.00
101	Coffee	Nestle	200	Kolkata	55.00
106	Sauce	Maggi	56	Mumbai	55.00
107	Cake	Britania	72	Delhi	10.00
104	Maggi	Nestle	150	Mumbai	10.00
105	Chocolate	Cadbury	170	Delhi	25.00

(i) To display names of the items whose name starts with 'C' in ascending order of Price.

(ii) To display code, Item name and City of the products whose quantity is less than 100.

(iii) To count distinct Company from the table.

(iv) To insert a new row in the table Shoppe '110'; Pizza', 'Papa Jones', 120, "Kolkata", 50.0

(v) SELECT Item FROM SHOPPE where Item IN (''Jam" ,"Coffee");

(vi) Select COUNT(distinct(City)) from SHOPPE;

(vii) Select MIN(Qty) from SHOPPE where City ="Mumba";

Answer 1

(i) SELECT Item FROM SHOPPE Where Item like 'c%' order by price;

(ii) SELECT Code, Item, City from Shoppe Where Qty < 100;

(iii) SELECT Count(Distinct(Company)) from SHOPPE;

(iv) Insert Into SHOPPE Values (110, 'pizza', 'papa Jones',120,'kolkata',50.0);

(v) Item jam Coffee

(vi) 3

(vii) MIN(QTY) 56