Chapter 12 Structured Query Language Solutions
Question - 1 : - Differentiate between delete and drop table command ?
Answer - 1 : -
DELETE command is used to remove information from a particular row or rows. If used without any 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.
Question - 2 : - What is the use of wildcard ?
Answer - 2 : -
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
Question - 3 : - Write SQL query to add a column total price with datatype numeric and size 10, 2 in a table product.
Answer - 3 : -
ALTER TABLE product ADD total price number
Question - 4 : - While creating table ‘customer’, Rahula forgot to add column ‘price’. Which command is used to add new column in the table. Write the command to implement the same.
Answer - 4 : -
ALTER TABLE customer ADD price number(10,2)
Question - 5 : - 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 - 5 : -
DELETE FROM BANK
Question - 6 : - Sonal needs to display name of teachers, who have “0” as the third character in their name. She wrote the following query.
Select name from teacher where name = “$$0?”; But the query isn’t producing the result. Identify the problem.
Answer - 6 : -
The wildcards are incorrect. The corrected query is SELECT name FROM teacher WHERE name
LIKE’ _ _ 0%’
Question - 7 : - Write SQL queries for(i) to (iv) and find outputs for SQL queries (v) to (viii), which are based onthe tables.
| Table: VEHICLE |
| CODE | VTYPE | PERKM |
| 101 | VOLVO BUS | 160 |
| 102 | AC DELUXE BUS | 150 |
| 103 | ORDINARY BUS | 90 |
| 105 | SUV | 40 |
| 104 | CAR | 20 |
Note :
1. PERKM is FreightCharges per Kilometer.
2. VTYPE is VehicleType.
| Table: TRAVEL |
| No. | NAME | TDATE | KM | CODE | NOP |
| 101 | Janish Kin | 2015-11-13 | 200 | 101 | 32 |
| 103 | Vedika Sahai | 2016-04-21 | 100 | 103 | 45 |
| 105 | Tarun Ram | 2016-03-23 | 350 | 102 | 42 |
| 102 | John Fen | 2016-02-13 | 90 | 102 | 40 |
| 107 | Ahmed Khan | 2015-01-10 | 75 | 104 | 2 |
| 104 | Raveena | 2016-05-28 | 80 | 105 | 4 |
| 106 | Kripal Anya | 2016-02-06 | 200 | 101 | 25 |
Note:
· NO is Traveller Number
· KM is KilometerTravelled
· NOP is number oftravellers travelled in vehicle.
· TDATE is Travel Date
1. To display NO, NAME,TDATE from the table TRAVEL in descending order of NO.
2. To display the NAME ofall the travellers from the table TRAVEL who are travelling by vehicle withcode 101 or 102.
3. To display the NO andNAME of those travellers from the table TRAVEL who travelled between ‘2015-1231’and ‘2015-04-01’.
4. To display all thedetails from table TRAVEL for the travellers, who have travelled distance morethan 100 KM in ascending order of NOP .
5. SELECT COUNT (*), CODEFROM TRAVEL GROUP BY CODE HAVING COUNT(*)>1;
6. SELECT DISTINCT CODEFROM TRAVEL;
7. SELECT A. CODE,NAME,VTYPE
FROM TRAVEL A,VEHICLEB
WHERE A.CODE=B.CODE AND ‘KM<90;
8.SELECT NAME, KM*PERKM
FROM TRAVEL A,VEHICLE B
WHERE A.CODE=B. CODE AND A.CODE=’105’;
Answer - 7 : -
1. Select NO,Name, TDATE from TRAVEL order by NO desc
2. Select NAME fromTRAVEL, where CODE in (101, 102)
3. Select NO, NAME fromTRAVEL where TDATE between ’2015-12-31′ and ‘2015-04-01’.
4. Select * from TRAVELwhere KM > 100 order by NOP.
5.
| COUNT (*) | CODE |
| 2 | 101 |
| 2 | 102 |
6.
| DISTANCE (CODE) |
| 101 |
| 103 |
| 102 |
| 104 |
| 105 |
7.
| CODE | NAME | VTYPE |
| 104 | Ahmed khan | CAR |
| 105 | Raveena | SUV |
8.
NAME KM*PERKM
Tarun Ram 14000
Question - 8 : - Consider the followingtables FACULTY and COURSES. Write SQL commands for the statements (i) to (v)and give outputs for SQL queries (vi) to (vii)
FACULTY
| F_ID | Fname | Lname | Hire_date | Salary |
| 102 | Amit | Mishra | 12-10-1998 | 12000 |
| 103 | Nitin | Vyas | 24-12-1994 | 8000 |
| 104 | Rakshit | Soni | 18-5-2001 | 14000 |
| 105 | Rashmi | Malhotra | 11-9-2004 | 11000 |
| 106 | Sulekha | Srivastava | 5-6-2006 | 10000 |
COURSES
| C_ID | FJD | Cname | |
| C21 | 102 | Grid Computing | 40000 |
| C22 | 106 | System Design | 16000 |
| C23 | 104 | Computer Security | 8000 |
| C24 | 106 | Human Biology | 15000 |
| C25 | 102 | Computer Network | 20000 |
| C26 | 105 | Visual Basic | 6000 |
(i) To display detailsof those Faculties whose salary is greater than 12000.
Answer - 8 : -
Select * from faculty
where salry > 12000;
(ii) To display the details of courses whose fees is in th range of 15000 to 50000(both values included).
Аnswer:
Select * from Courses
where fees between 15000 and 50000;
(iii)To increase the fees of all courses by 500 of “System Design” Course.
Update courses set fees = fees + 500
where Cname = “System Design”;
(iv)To display details of those courses which are taught by ‘Sulekha’ in descending order of courses.
Аnswer:
Select * from faculty fac, courses cour
where fac.f_id = cour.f_id and fac.fname = ‘Sulekha’ order by cname desc;
(v)Select COUNT (DISTINCT F_ID) from COURSES;
Аnswer:
4
(vi)Select MIN (Salary) from FACULTY, COURSES where COURSES.F_ID = FACULTY.FJD;
Аnswer:
6000
Write SQL commands forthe queries (i) to (iv) and output for (v) & (viii) based on a tableCOMPANY and CUSTOMER
| 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 - 9 : -
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 |
Question - 10 : - Consider the followingtables SCHOOL and ADMIN and answer this question :
Table : SCHOOL
| CODE | TEACHERNAME | SUBJECT | DOJ | PERIODS | EXPERIENCE |
| 1001 | Ravi Shankar | English | 12/3/2000 | 24 | 10 |
| 1009 | Priya Rai | Physics | 03/09/1998 | 26 , | 12 |
| 1203 | Lisa Anand | English | 09/04/2000 | 27 | 5 |
| 1045 | Yashraj | Maths | 24/08/2000 | 24 | 15 |
| 1123 | Ganan | Physics | 16/07/1999 | 28 | 3 |
| 1167 | Harish B | Chemistry | 19/10/1999 | 27 | 5 |
| 1215 | Umesh | Physics | 11/05/1998 | 22 | 16 |
Table : Admin
| Code | Gender | Designation |
| 1001 | Male | Vice Principal |
| 1009 | Female | Coordinator |
| 1203 | Female | Coordinator |
| 1045 | Male | HOD |
| 1123 | Male | Senior Teacher |
| 1167 | Male | Senior Teacher |
| 1215 | Male | HOD |
Write SQL statementsfor the following :
1. To displayTEACHERNAME, PERIODS of all teachers whose periods are more than 25.
2. To display all theinformation from the table SCHOOL in descending order of experience.
3. To display DESIGNATIONwithout duplicate entries from the table ADMIN.
4. To displayTEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and ADMIN ofMale teachers.
Answer - 10 : -
1. To display TEACHERNAME, PERIODS of all teachers whose periods are more than 25.
SELECT TEACHERNAME, PERIODS
FROM SCHOOL WHERE PERIODS >25.
2.To display all the information from the table SCHOOL in descending order of experience.
SELECT * FROM SCHOOL;
3.To display DESIGNATION without duplicate entries from the table ADMIN.
SELECT DISTINCT DESIGNATION FROM ADMIN;
4.To display TEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and ADMIN of Male teachers.
SELECT TEACHERNAME.CODE
DESIGNATION FROM SCHOOL.CODE = ADMIN.CODE
WHERE GENDER = MALE;