Chapter 7: Structured Query Language (SQL) Class 12 Computer Science NCERT Solutions

Class 12 Computer Science (Python) – Chapter 7: Structured Query Language

Short Answer Type Questions-I

Question 1: Write queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.

Table: VEHICLE

VCODE	VEHICLETYPE	PERKM	KM	NOP
V01	Car	10	50	3
V02	Bus	15	120	50
V03	Car	10	80	2
V04	Bus	15	150	60
V05	Truck	20	200	2

SQL Queries:

1. SELECT CNO, CNAME, TRAVELDATE FROM TRAVEL ORDER BY CNO DESC;
2. SELECT CNAME FROM TRAVEL WHERE VCODE IN (‘V01’, ‘V02’);
3. SELECT CNO, CNAME FROM TRAVEL WHERE TRAVELDATE BETWEEN ‘2015-05-01’ AND ‘2015-12-31’;
4. SELECT * FROM TRAVEL WHERE KM > 120 ORDER BY NOP ASC;
5. SELECT COUNT(*), VCODE FROM TRAVEL GROUP BY VCODE HAVING COUNT(*) > 1;
6. SELECT DISTINCT VCODE FROM TRAVEL;
7. SELECT A.VCODE, CNAME, VEHICLETYPE FROM TRAVEL A, VEHICLE B WHERE A.VCODE = B.VCODE AND KM < 90;
8. SELECT CNAME, KM * PERKM FROM TRAVEL A, VEHICLE B WHERE A.VCODE = B.VCODE AND A.VCODE = ‘V05’;

Question 2: Consider the following tables SCHOOL and ADMIN and answer this question:

Table: SCHOOL

TEACHER	DESIGNATION	EXPERIENCE
Alice	Teacher	5
Bob	Principal	15
Charlie	Teacher	10

Table: ADMIN

TEACHER	DESIGNATION	GENDER
Alice	Teacher	Female
Bob	Principal	Male
Charlie	Teacher	Male

SQL Queries:

1. SELECT DESIGNATION, COUNT(*) FROM ADMIN GROUP BY DESIGNATION HAVING COUNT(*) < 2;
2. SELECT MAX(EXPERIENCE) FROM SCHOOL;
3. SELECT TEACHER FROM SCHOOL WHERE EXPERIENCE > 12 ORDER BY TEACHER;
4. SELECT COUNT(*), GENDER FROM ADMIN GROUP BY GENDER;

Question 3: Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables TRANSPORT and TRIP.

Table: TRANSPORT

TCODE	TTYPE	PERKM	KM
101	Truck	10	50
102	Bus	15	120
103	Car	12	100
104	Van	18	80
105	Truck	20	200

Table: TRIP

NO	NAME	TCODE	TDATE	NOP
1	John	101	2015-02-10	4
2	Mark	103	2015-03-15	2
3	Sam	102	2015-04-01	6
4	Alex	104	2015-05-05	5
5	Chris	105	2015-06-01	3

SQL Queries:

1. SELECT NO, NAME, TDATE FROM TRIP ORDER BY NO DESC;
2. SELECT NAME FROM TRIP WHERE TCODE IN (101, 103);
3. SELECT NO, NAME FROM TRIP WHERE TDATE BETWEEN ‘2015-02-10’ AND ‘2015-04-01’;
4. SELECT * FROM TRIP WHERE KM > 100 ORDER BY NOP ASC;
5. SELECT COUNT(*), TCODE FROM TRIP GROUP BY TCODE HAVING COUNT(*) > 1;
6. SELECT DISTINCT TCODE FROM TRIP;
7. SELECT A.TCODE, NAME, TTYPE FROM TRIP A, TRANSPORT B WHERE A.TCODE = B.TCODE AND KM < 90;
8. SELECT NAME, KM * PERKM FROM TRIP A, TRANSPORT B WHERE A.TCODE = B.TCODE AND A.TCODE = ‘105’;

Question 4: Write SQL query to add a column total price with datatype numeric and size 10, 2 in a table product.

ALTER TABLE PRODUCT ADD TOTAL_PRICE NUMBER(10, 2);

Question 5: Identify and correct the query to find names where the third character is 0.

Corrected Query: SELECT NAME FROM TEACHER WHERE NAME LIKE '__0%';

Question 6: Command to remove all rows but retain structure of table BANK.

DELETE FROM BANK;

Question 7: Command to add a column PRICE to the CUSTOMER table.

ALTER TABLE CUSTOMER ADD PRICE NUMBER(10, 2);

Question 8: What is the use of a wildcard?

Wildcard characters like % and _ are used with the LIKE operator for pattern matching in SQL queries.

Question 9: Differentiate between DELETE and DROP.

Command	DELETE	DROP
Purpose	Deletes data	Deletes table and data
Structure	Retains table structure	Removes structure
Command Type	DML	DDL

Long Answer Type Questions

Question 1: Write SQL commands for the queries (i) to (iv) and output for (v) & (viii) based on the tables COMPANY and CUSTOMER.

(i) SELECT NAME FROM COMPANY WHERE PRICE < 30000;
(ii) SELECT NAME FROM COMPANY ORDER BY NAME DESC;
(iii) UPDATE CUSTOMER SET PRICE = PRICE + 1000 WHERE NAME LIKE 'S%';
(iv) ALTER TABLE CUSTOMER ADD TOTALPRICE DECIMAL(10,2);
(v) SELECT COUNT(*), CITY FROM COMPANY GROUP BY CITY;
(vi) SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY > 10;
(vii) SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE '%r%';
(viii) SELECT PRODUCTNAME, CITY, PRICE FROM COMPANY, CUSTOMER WHERE COMPANY.CID = CUSTOMER.CID AND PRODUCTNAME = 'MOBILE';

Question 2: Consider the following tables SCHOOL and ADMIN and answer the following questions:

(i) SELECT TEACHERNAME, PERIODS FROM SCHOOL WHERE PERIODS > 25;
(ii) SELECT * FROM SCHOOL ORDER BY EXPERIENCE DESC;
(iii) SELECT DISTINCT DESIGNATION FROM ADMIN;
(iv) SELECT TEACHERNAME, CODE, DESIGNATION FROM SCHOOL, ADMIN WHERE SCHOOL.CODE = ADMIN.CODE AND GENDER = 'MALE';

Question 3: Write SQL commands for the queries (i) to (iv) and output for (v) to (viii) based on the tables WATCHES and SALE.

(i) SELECT * FROM WATCHES WHERE WATCH_NAME LIKE '%TIME';
(ii) SELECT WATCH_NAME, PRICE FROM WATCHES WHERE PRICE BETWEEN 5000 AND 15000;
(iii) SELECT SUM(QTY_STORE) FROM WATCHES WHERE TYPE LIKE 'UNISEX';
(iv) SELECT WATCH_NAME, QTY_SOLD FROM WATCHES W, SALE S WHERE W.WATCH_ID = S.WATCH_ID AND QUARTER = 1;
(v) SELECT MAX(PRICE), MIN(QTY_STORE) FROM WATCHES;
(vi) SELECT QUARTER, SUM(QTY_SOLD) FROM SALE GROUP BY QUARTER;
(vii) SELECT WATCH_NAME, PRICE, TYPE FROM WATCHES W WHERE WATCH_ID NOT IN (SELECT WATCH_ID FROM SALE);
(viii) SELECT WATCH_NAME, QTY_STORE, SUM(QTY_SOLD), (QTY_STORE - SUM(QTY_SOLD)) AS STOCK FROM WATCHES W, SALE S WHERE W.WATCH_ID = S.WATCH_ID GROUP BY S.WATCH_ID;

Question 4: Answer the questions (a) and (b) on the basis of the following tables SHOP and ACCESSORIES.

(a)
(i) SELECT NAME, PRICE FROM ACCESSORIES ORDER BY PRICE ASC;
(ii) SELECT ID, SNAME FROM SHOP WHERE AREA = 'Nehru Place';
(iii) SELECT NAME, MIN(PRICE), MAX(PRICE) FROM ACCESSORIES GROUP BY NAME;
(iv) SELECT NAME, PRICE, SNAME FROM ACCESSORIES, SHOP WHERE ACCESSORIES.ID = SHOP.ID;

(b)
(i) SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE >= 5000;
(ii) SELECT AREA, COUNT(*) FROM SHOP GROUP BY AREA;
(iii) SELECT COUNT(DISTINCT AREA) FROM SHOP;
(iv) SELECT NAME, PRICE * 0.05 AS DISCOUNT FROM ACCESSORIES WHERE SNO IN ('S02', 'S03');

Question 5: Write SQL queries for the following based on the table APPLICANTS and COURSES:

SELECT NAME, FEE, GENDER, JOINYEAR FROM APPLICANTS WHERE JOINYEAR < 2010;
SELECT NAME FROM APPLICANTS WHERE FEE > 30000;
SELECT NAME FROM APPLICANTS ORDER BY JOINYEAR ASC;
SELECT JOINYEAR, COUNT(*) FROM APPLICANTS GROUP BY JOINYEAR;
SELECT C_ID, COUNT(*) FROM APPLICANTS GROUP BY C_ID;
SELECT NAME, COURSE FROM APPLICANTS, COURSES WHERE APPLICANTS.C_ID = COURSES.C_ID;

-- Outputs:
SELECT Name, Joinyear FROM APPLICANTS WHERE GENDER='F' AND C_ID='A02'; -- Output: Avisha 2009
SELECT MIN(Joinyear) FROM APPLICANTS WHERE Gender='m'; -- Output: 2009
SELECT AVG(Fee) FROM APPLICANTS WHERE C_ID='A01' OR C_ID='A05'; -- Output: 67000
SELECT SUM(Fee), C_ID FROM APPLICANTS GROUP BY C_ID HAVING COUNT(*)=2; -- Output: 55000 A01

Question 6: Write SQL queries and output based on the tables ITEMS and TRADERS:

SELECT * FROM ITEMS ORDER BY INAME ASC;
SELECT INAME, PRICE FROM ITEMS WHERE PRICE BETWEEN 10000 AND 22000;
SELECT TCODE, COUNT(CODE) FROM ITEMS GROUP BY TCODE;
SELECT PRICE, INAME, QTY FROM ITEMS WHERE QTY > 150;
SELECT TNAME FROM TRADERS WHERE CITY = 'DELHI' OR CITY = 'MUMBAI';
SELECT COMPANY, INAME FROM ITEMS ORDER BY COMPANY DESC;

-- Outputs:
SELECT MAX(PRICE), MIN(PRICE) FROM ITEMS; -- Output: 38000, 1200
SELECT PRICE*QTY FROM ITEMS WHERE CODE=1004; -- Output: 1075000
SELECT DISTINCT TCODE FROM ITEMS; -- Output: T01, T02, T03
SELECT INAME, TNAME FROM ITEMS I, TRADERS T WHERE I.TCODE=T.TCODE AND QTY < 100;
-- Output:
-- LED SCREEN 40    DISP HOUSE INC
-- CAR GPS SYSTEM    ELECTRONICS SALES

Question 7: Write SQL queries and output based on the tables PRODUCTS and SUPPLIERS:

SELECT * FROM PRODUCTS ORDER BY PNAME ASC;
SELECT PNAME, PRICE FROM PRODUCTS WHERE PRICE BETWEEN 10000 AND 15000;
SELECT SUPCODE, COUNT(PID) FROM PRODUCTS GROUP BY SUPCODE;
SELECT PRICE, PNAME, QTY FROM PRODUCTS WHERE QTY > 100;
SELECT SNAME FROM SUPPLIERS WHERE CITY = 'DELHI' OR CITY = 'CHENNAI';
SELECT COMPANY, PNAME FROM PRODUCTS ORDER BY COMPANY DESC;

-- Outputs:
SELECT DISTINCT SUPCODE FROM PRODUCTS; -- Output: S01, S02, S03
SELECT MAX(PRICE), MIN(PRICE) FROM PRODUCTS; -- Output: 28000, 1100
SELECT PRICE*QTY FROM PRODUCTS WHERE PID = 104; -- Output: 550000
SELECT PNAME, SNAME FROM PRODUCTS P, SUPPLIERS S WHERE P.SUPCODE = S.SUPCODE AND QTY > 100;
-- Output:
-- DIGITAL CAMERA 14X    GETALL INC
-- PENDRIVE 16GB         GETALL INC

Question 8: Consider the tables CARDEN and CUSTOMER and answer the following:

(a) Primary and Alternate Keys:

• Primary Key of CARDEN: Ccode
• Alternate Key of CARDEN: CarName
• Primary Key of CUSTOMER: Code
• Alternate Key of CUSTOMER: Cname

(b) SQL Queries:

SELECT CarName FROM CARDEN WHERE Color = 'SILVER';
SELECT CarName, Make, Capacity FROM CARDEN ORDER BY Capacity DESC;
SELECT MAX(Charges) FROM CARDEN;
SELECT Cname, CarName FROM CARDEN, CUSTOMER WHERE CARDEN.Ccode = CUSTOMER.Ccode;

(c) Outputs:

SELECT COUNT(DISTINCT Make) FROM CARDEN; -- Output: 4
SELECT MAX(Charges), MIN(Charges) FROM CARDEN; -- Output: 35, 112
SELECT COUNT(Ccode), Make FROM CARDEN; -- Output: 5
SELECT Cname FROM CUSTOMER WHERE Ccode = CARDEN.Ccode AND CarName = 'SX4'; -- Output: SX4, C Class

Question 9: Consider the following tables CABHUB and CUSTOMER and answer the following:

(a) Table CABHUB:

Vcode	VehicleName	Make	Capacity	Colour	Charges
V101	SX4	Maruti	4	White	18
V102	Innova	Toyota	7	Black	25
V103	City	Honda	4	Silver	22
V104	C Class	Mercedes	4	White	35

• Primary Key: Vcode
• Candidate Key: VehicleName

(b) SQL Queries:

SELECT VehicleName FROM CABHUB WHERE Colour = 'WHITE';
SELECT VehicleName, Capacity FROM CABHUB ORDER BY Capacity ASC;
SELECT MAX(Charges) FROM CABHUB;
SELECT Cname, VehicleName FROM CABHUB, CUSTOMER WHERE CUSTOMER.Vcode = CABHUB.Vcode;

(c) Outputs:

SELECT COUNT(DISTINCT Make) FROM CABHUB; -- Output: 4
SELECT MAX(Charges), MIN(Charges) FROM CABHUB; -- Output: 35, 12
SELECT COUNT(*) FROM CABHUB; -- Output: 5
SELECT VehicleName FROM CABHUB WHERE Capacity=4; -- Output: SX4, C Class

Question 10: Consider the following tables EMPLOYEE and DEPARTMENT and answer the following:

(a) SQL Queries:

SELECT DepName, DEPARTMENT.DepCde FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.DepCde = DEPARTMENT.DepCde ORDER BY DEPARTMENT.DepCde DESC;
SELECT AVG(Age) FROM EMPLOYEE WHERE DepCde = '103';
SELECT DeptHead FROM DEPARTMENT WHERE DEPARTMENT.DepCde = (SELECT DepCde FROM EMPLOYEE WHERE TName = 'Sanjeev P');
SELECT * FROM EMPLOYEE WHERE JoinDate < '01-JAN-2007';

(b) Outputs:

SELECT COUNT(DISTINCT DepCde) FROM EMPLOYEE; -- Output: 3
SELECT MAX(JoinDate), MIN(JoinDate) FROM EMPLOYEE; -- Output: 22-OCT-2010, 01-MAY-2003
SELECT TName, DepHead FROM EMPLOYEE E, DEPARTMENT D WHERE E.DepCde = D.DepCde; -- Employee names and DeptHeads
SELECT COUNT(*) FROM EMPLOYEE WHERE Salary > 60000 AND Age > 30; -- Output: 2

Question 11: Consider the following tables WORKER and PAYLEVEL and answer the following:

(a) SQL Commands:

SELECT NAME FROM WORKER ORDER BY DOB DESC;

SELECT NAME, DESIGN FROM WORKER WHERE PLEVEL = 'P001' OR PLEVEL = 'P002';

SELECT * FROM WORKER WHERE DOB BETWEEN '19-JAN-1984' AND '18-JAN-1987';

INSERT INTO WORKER VALUES (19, 'DayaKishore', 'Operator', 'P003', '19-Sep-2008', '11-Jul-1984');

(b) Output of SQL Queries:

SELECT COUNT(PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;

Output: Group-wise count of workers per PLEVEL

SELECT MAX(DOB), MIN(DOJ) FROM WORKER;

Output: MAX(DOB): 23-DEC-1987, MIN(DOJ): 01-JAN-2002

SELECT Name, PAY FROM WORKER W, PAYLEVEL P WHERE W.PLEVEL = P.PLEVEL AND W.ECODE < 13;

Output: Names and pay details of workers with ECODE less than 13

SELECT * FROM PAYLEVEL WHERE PLEVEL = 'P003';

Output: Full row from PAYLEVEL table where PLEVEL = 'P003'

Question 12: Consider the following tables EMPLOYEE and SALGRADE and answer (b) and (c) parts of this question:

(a) Relational Algebra Concepts:

Projection (π): Selects specific columns from a relation.

Selection (σ): Selects specific rows that meet a condition.

(b) SQL Commands:

SELECT * FROM EMPLOYEE ORDER BY DOJ DESC;

SELECT NAME, DESIGN FROM EMPLOYEE WHERE SGRADE = 'S02' OR SGRADE = 'S03';

SELECT * FROM EMPLOYEE WHERE DOJ BETWEEN '09-FEB-2006' AND '08-AUG-2009';

INSERT INTO EMPLOYEE VALUES (109, 'Harish Roy', 'HEAD-IT', 'S02', '09-SEP-2007', '21-APR-1983');

(c) Output of SQL Queries:

SELECT COUNT(SGRADE), SGRADE FROM EMPLOYEE GROUP BY SGRADE;

SELECT MIN(DOB), MAX(DOJ) FROM EMPLOYEE;

SELECT NAME, SALARY FROM EMPLOYEE E, SALGRADE S WHERE E.SGRADE = S.SGRADE AND E.ECODE < 103;

SELECT SGRADE, SALARY + HRA FROM SALGRADE WHERE SGRADE = 'S02';

Question 13: Consider the following tables GAMES and PLAYER and answer (b) and (c) parts of this question:

(a) Relational Key Concepts:

Primary Key: Attribute(s) that uniquely identify a row.

Candidate Keys: Possible choices for a primary key.

(b) SQL Commands:

SELECT GameName, GCode FROM GAMES;

SELECT * FROM GAMES WHERE PrizeMoney > 7000;

SELECT * FROM GAMES ORDER BY ScheduleDate;

Question 14: Consider the following tables ACTIVITY and COACH and answer (a) and (b) parts of this question:

(a) SQL Commands:

SELECT ACode, ActivityName FROM ACTIVITY ORDER BY ACode DESC;

SELECT Stadium, SUM(PrizeMoney) FROM ACTIVITY GROUP BY Stadium;

SELECT Name, ACode FROM COACH ORDER BY ACode ASC;

SELECT * FROM ACTIVITY WHERE ScheduleDate < '01-JAN-2004' ORDER BY ParticipantsNum ASC;

(b) Output of SQL Queries:

SELECT COUNT(DISTINCT ParticipantsNum) FROM ACTIVITY;

Output: 3

SELECT MAX(ScheduleDate), MIN(ScheduleDate) FROM ACTIVITY;

Output: 19-Mar-2004, 12-Dec-2003

SELECT Name, ActivityName FROM ACTIVITY A, COACH C WHERE A.ACode = C.ACode AND A.ParticipantsNum = 10;

Output: Ravinder Discuss Throw

SELECT DISTINCT ACode FROM COACH;

Output: 1001, 1008, 1003

Question 15: Consider the following tables RESORT and OWNEDBY and answer (a) and (b) parts of this question:

(a) SQL Commands:

SELECT RCODE, PLACE FROM RESORT WHERE TYPE = '5 STAR' ORDER BY PLACE;

SELECT MAX(RENT), MIN(RENT) FROM RESORT GROUP BY TYPE;

SELECT * FROM RESORT WHERE STARTDATE > '31-DEC-05';

SELECT OWNER FROM RESORT A, OWNEDBY B WHERE A.TYPE = '5 STAR' AND A.PLACE = B.PLACE;

(b) Output of SQL Queries:

SELECT MIN(RENT) FROM RESORT WHERE PLACE = 'KERALA';

SELECT TYPE, STARTDATE FROM RESORT WHERE TYPE = '2 STAR' ORDER BY STARTDATE;

SELECT PLACE, OWNER FROM OWNEDBY WHERE PLACE LIKE '%A';

SELECT RCODE, RENT FROM RESORT, OWNEDBY WHERE RESORT.PLACE = OWNEDBY.PLACE AND TYPE = '3 STAR';

Question 16: Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this question:

(a) SQL Commands:

SELECT * FROM STORE ORDER BY LastBuy ASC;

SELECT ItemNo, Item FROM STORE WHERE Rate > 15;

SELECT * FROM STORE WHERE Scode = '22' OR Qty > 110;

SELECT Sname, MIN(Rate) FROM STORE, SUPPLIERS WHERE STORE.Scode = SUPPLIERS.Scode GROUP BY Sname;

(b) Output of SQL Queries:

SELECT COUNT(DISTINCT Scode) FROM STORE;

Output: 3

SELECT Rate * Qty FROM STORE WHERE ItemNo = 2004;

Output: 880

SELECT Item, Sname FROM STORE S, SUPPLIERS P WHERE S.Scode = P.Scode AND ItemNo = 2006;

Output: Gel Pen Classic, Premium Stationers

SELECT MAX(LastBuy) FROM STORE;

Output: 24-Feb-10

Question 17: Consider the following tables STOCK and DEALERS and answer (a) and (b) parts of this question:

(a) SQL Commands:

SELECT * FROM STOCK ORDER BY StockDate;

SELECT ItemNo, Item FROM STOCK WHERE UnitPrice > 10;

SELECT * FROM STOCK WHERE Dcode = '102' OR Qty > 100;

SELECT Dcode, MAX(UnitPrice) FROM STOCK GROUP BY Dcode;

(b) Output of SQL Queries:

SELECT COUNT(DISTINCT Dcode) FROM STOCK;

Output: 3

SELECT Qty * UnitPrice FROM STOCK WHERE ItemNo = 5006;

Output: 4400

SELECT Item, Dname FROM STOCK S, DEALERS D WHERE S.Dcode = D.Dcode AND ItemNo = 5004;

Output: Eraser Big, Clear Deals

SELECT MIN(StockDate) FROM STOCK;

Output: 01-Jan-09