Question -
| CID | NAME | CITY | PRODUCTNAME |
| 111 | SONY | DELHI | TV |
| 222 | NOKIA | MUMBAI | MOBILE |
| 333 | ONIDA | DELHI | TV |
| 444 | SONY | MUMBAI | MOBILE |
| 555 | BLACKBERRY | MADRAS | MOBILE |
| 666 | DELL | DELHI | LAPTOP |
| CUSTID | NAME | PRICE | QTY | CID |
| 101 | ROHAN SHARMA | 70,000 | 20 | 222 |
| 102 | DEEPAK KUMAR | 50,000 | 10 | 666 |
| 103 | MOHAN KUMAR | 30,000 | 5 | 111 |
| 104 | SAHIL BANSAL | 35,000 | 3 | 333 |
| 105 | NEHA SONI | 25,000 | 7 | 444 |
| 106 | SONAL AGGARWAL | 20,000 | 5 | 333 |
| 107 | ARUN SINGH | 50,000 | 15 | 666 |
1. To display thosecompany name which are having prize less than 30000.
2. To display the name ofthe companies in reverse alphabetical order.
3. To increase the prizeby 1000 for those customer whose name starts with’S?
4. To add one more columntotalprice with decimal(10,2) to the table customer
5. SELECT COUNTO ,CITYFROM COMPANY GROUP BY CITY;
6. SELECT MIN(PRICE),MAX(PRICE) FROM CUSTOMER WHERE QTY>10;
7. SELECT AVG(QTY) FROMCUSTOMER WHERE NAME LIKE “%r%;
8. SELECTPRODUCTNAME,CITY, PRICE FROM COMPANY, CUSTOMER WHERE COMPANY.CID=CUSTOMER.CIDAND PRODU CTN AME=”MOBILE”;
Answer -
1. To displaythose company name which are having prize less than 30000.
SELECT NAME FROM COMPANY WHERE COMPANY.CID=CUSTOMER. CID AND PRICE< 30000
2.To display the name of the companies in reverse alphabetical order.
SELECT NAME FROM COMPANY
ORDER BY NAME DESC?;
3.To increase the prize by 1000 for those customer whose name starts with “S”
UPDATE CUSTOMER
SET PRICE = PRICE + 1000;
WHERE NAME LIKE ‘S%’;
4.To add one more column totalprice with decimal(10,2) to the table customer
ALTER TABLE CUSTOMER
ADD TOTALPRICE DECIMAL(10,2);
5.SELECT COUNT(*) ,CITY FROM COMPANY GROUP BY CITY;
| 3 | DELHI |
| 2 | MUMBAI |
| 1 | MADRAS |
6.SELECT MIN(PRICE),MAX(PRICE) FROM
CUSTOMER WHERE QTY> 10;
50000,70000
7.SELECT AVG(QTY) FROM CUSTOMER
WHERE NAME LIKE“%r%; [
8.SELECT PRODUCTNAME, CITY, PRICE FROM COMPANY, CUSTOMER WHERECOMPANY.CID=CUSTOMER.CID AND PRODUCTNAME=”MOBILE”;
| MOBILE | MUMBAI | 70000 |
| MOBILE | MUMBAI | 25000 |