SQL Practice – II

1. Deepika wants to remove all rows from the table BANK. But he needs to maintain the structure of the table.
Which command is used to implement the same?
Answer:
DELETE FROM BANK.

2. While creating table ‘customer’, Rahul forgot to add column ‘price’. Which command is used to add new
column in the table. Write the command to implement the same.
Answer:
ALTER TABLE CUSTOMER ADD PRICE NUMBER (10, 2).

3.What is the use of wildcard

Answer:
The wildcard operators are used with the LIKE operator to search a value similar to a specific pattern in a column.
There are 2 wildcard operators.
% – represents 0,1 or many characters – – represents a single number or character

4.Differentiate between DELETE and DROP table commands ?

Answer:
DELETE command is used to remove infor¬mation from a particular row or rows. If used without condition, it will
delete all row information but not the structure of the table. It is a DML command.
DROP table command is used to remove the entire structure of the table and information. It is a DDL command
5.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. 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);
6.
Answers
1. SELECT TEACHERNAME, PERIODS FROM SCHOOL WHERE PERIODS>25:
2. SELECT * FROM SCHOOL;
3. SELECT DISTINCT DESIGNATION FROM ADMIN;
4. SELECT TEACHERNAME.CODE DESIGNATION FROM SCHOOL.CODE = ADMIN.CODE WHERE
GENDER = MALE;

7.
1. TO DISPLAY ALL THE DETAILS OF THOSE WATCHES WHOSE NAME ENDS WITH ‘TIME’
2. TO DISPLAY WATCH’S NAME AND PRICE OF THOSE WATCHES WHICH HAVE PRICE
RANGE IN BE-TWEEN 5000-15000.
3. TO DISPLAY TOTAL QUANTITY IN STORE OF UNISEX TYPE WATCHES.
4. TO DISPLAY WATCH NAME AND THEIR QUANTITY SOLD IN FIRST QUARTER;
5. SELECT MAX (PRICE), MIN(QTY_STORE) FROM WATCHES;
6. SELECT QUARTER, SUM(QTY SOLD) FROM SALE GROUP BY QUARTER;
7. SELECT WATCH_NAME, PRICE, TYPE FROM WATCHES W, SALE S WHERE W.
WAT£H1D!=S.WATCHID; (viii) SELECT WATCH_NAME, QTYSTORE, SUM (QTY_SOLD),
QTY_STORESUM (QTYSOLD) “STOCK” FROM WATCHES W, SALE S WHERE W. WATCHID
= S.WATCHID GROUP BY S.WATCHID;
Answers
1. SELECT * FROM WATCHES WHERE WATCH_NAME LIKE ‘%TIME’(Vi mark for SELECT
query) (Vi mark for where clause)
2. SELECT WATCH_NAME, PRICE WATCH WHERE PRICE BETWEEN 5000 AND 15000;(Vi
mark for SELECT query) (Vz mark for where clause)
3. SELECT SUM (QTY STORE) FROM WATCHES WHERE TYPE LIKE ‘UNISEX’;(Vz mark for
SELECT query) (Vi mark for where clause)
4. SELECT WATCHNAME, QTY SOLD FROM WATCHES W,SALE S WHERE W. WATCHID
= S. WATCHID AND QUARTER = 1;

18.

a)Write SQL commands for the following statements:

1. To display the details of all Items in the STOCK table in ascending order of StockDate.
2. To display ItemNo and Item name of those items from STOCK table whose UnitPrice is
more than Rupees 10.
3. To display the details of those items whose dealer code (Dcode) is 102 or quantity in
STOCK (Qty) is more than 100 from the table Stock.
4. To display maximum UnitPrice of items for each dealer individually as per Dcode from the
table STOCK.
(b)Give the output of the following SQL queries:
1. SELECT COUNT(DISTINCT Dcode)
FROM STOCK;
2. SELECT Qty* UnitPrice FROM STOCK WHERE ItemNo=5006;
3. SELECT Item, Dname FROM STOCK S, Dealers D WHERE S.Dcode=D.Dcode AND
ItemNo = 5004;
4. SELECT MIN (StockDate) FROM STOCK;

Answer:
(a)

1. SELECT*FROM STOCK ORDER BY StockDate;

2. SELECT Item No, Item FROM STOCK WHERE UnitPrice >10;
3. SELECT *FROM DEALERS, STOCK
WHERE (DEALERS.Dcode=”102″OR STOCK.Qty >100 and DEALERS. DCODE
= STOCK.DCODE);
4. SELECT MAX (Unitprice) FROM DEALERS, STOCK ORDER BY STOCK. Dcode
WHERE DEALERS.Dcode = STOCK.Dcode;
(b)
 1. 3
2. 4400
3. Item Dname
Eraser Big Clear Deals
4. 01-Jan-09

19. A table FLIGHT has 4 rows and 2 columns and another table AIRHOSTESS has rows and 4 columns.
How many rows and columns will be there if we obtain the cartesian product of these two tables? (Delhi
2012)

Answer: Total number of rows will be 12 and total number of columns will be 6.

20. Shanya Khanna is using a table EMPLOYEE. It has the following columns:

Admno, Name, Agg, Stream [column Agg contains Aggregate

marks]

She wants to display highest Agg obtained in each Stream.

She wrote the following statement:

SELECT Stream, MAX(Agg) FROM EMPLOYEE;

But she did not get the desired result. Rewrite the above query with necessary
changes to help her get the desired output.
Answer:

SELECT Stream, MAX(Agg)

FROM EMPLOYEE

GROUP BY Stream;

21. State difference between date functions NOW() and SYSDATE() of MySql.
Answer:
Differences between Now() and SYSDATE() of MySql are as follows:

22. Write the output of the following SQL queries:

(i) SELECT RIGHT (‘software’, 2);
(ii) SELECT INSTR (‘twelve’, lV);
(iii) SELECT DAYOFMONTH (‘2014-03-01’);
(iv) SELECT (76.987,2); (All India 2014C)
Answer:
(i) re
(ii) 45
(iii) 01
(iv) 76.99