Question

Write SQL commands for the queries (i) to (iv) and output for (v) & (viii) based on a table COMPANY and CUSTOMER.

1. To display those company name which are having prize less than 30000.

2. To display the name of the companies in reverse alphabetical order.

3. To increase the prize by 1000 for those customer whose name starts with „S?

4. To add one more column totalprice with decimal] 10,2) to the table customer

5. SELECT COUNT(*) , CITY FROM COMPANY GROUP BY CITY;

6. SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY>10;

7. SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE “%r%;

8. SELECT PRODUCTNAME,CITY, PRICE 

FROM COMPANY, CUSTOMER WHERE 

COMPANY. CID=CUSTOMER.CID AND 

PRODUCTNAME=”MOBILE”;

Answer 1

1. SELECT NAME FROM COMPANY WHERE COMPANY.CID=CUSTOMER. CID AND 

PRICE < 30000;

2. SELECT NAME FROM COMPANY ORDER BY NAME DESC;

3. UPDATE CUSTOMER SET PRICE = PRICE + 1000 WHERE NAME LIKE ‘S%’;

4. ALTER TABLE CUSTOMER ADD TOTALPRICE DECIMAL(10,2);

5.

3	DELHI
2	MUMBAI
1	MADRAS

6. 50000,70000

7. 11

8.

MOBILE	MUMBAI	70000
MOBILE	MUMBAI	25000