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