Chapterwise Question Bank CBSE Class 12 Computer Science (C++) – Structured Query Language

Chapterwise Question Bank CBSE Class 12 Computer Science (C++) – Structured Query Language

Exam Practice

Very Short Answer Type Questions [1 Mark]

Question 1:
What are DDL and DML?
Or
Differentiate between Data Definition Language and Data Manipulation Language.
Answer:
DDL (Data Definition Language) It is a part of SQL, which provides commands for creating, altering and deleting the tables. Different DDL commands are CREATE, ALTER and DROP.
DML (Data Manipulation Language) It is a part of SQL, which provides commands for inserting, deleting and updating the information in a database. Different DML commands are SELECT, UPDATE, DELETE, INSERT.

Question 2:
Differentiate between SQL commands DROP TABLE and DROP VIEW.
Answer:
Difference between DROP TABLE and DROP VIEW
DROP TABLE command deletes the definition of the table as well as the data of table. If the table is dropped, you cannot access it. While DROP VIEW command only deletes the definition of view. Dropping a view does not affect the base tables, i.e. no loss of data is there in DROP VIEW.
Syntax of DROP View is-DROP VIEW viewname;

Question 3:
Give the SQL statement to create a table STUDENT with Roll Number, Name, Age and Marks.
Answer:

CREATE TABLE STUDENT (Roll Number NUMBER(5) Primary Key, 
Name CHAR(20), Age NUMBER(2), Marks NUMBER(4));

Question 4:
Write a query on the SALESPEOPLE table, whose output will exclude all salespeople with a rating >=100, unless they are located in Delhi.
Answer:

SELECT * FROM SALESPEOPLE
WHERE rating<100 OR city='Delhi'; 
or
SELECT * FROM SALESPEOPLE WHERE NOT rating>=100 OR city ='Delhi’; 
or
SELECT * FROM SALESPEOPLE WHERE NOT(rating>=100 AND city<>'Delhi');

Question 5:
Create a table named PROGRAMMERS with the following structure:

(i) Display the name of the programmer, which has the highest salary.
(ii) UPDATE the salary of all programmer by 2000, whose name start with letter ‘R’.
Answer:

(i) SELECT P_Name MAX(SAL) FROM PROGRAMMERS;
(ii) UPDATE PROGRAMMERS SET SAL = SAL+2000 WHERE P_Name LIKE ' R%' ;

Long Answer Type Questions [6/8 Marks]

Question 6:
Answer the questions (a) and (b) on the basis of the following tables SHOPPE and
ACCESSORIES. All India 2014


(a) Write the SQL queries:

(i) To display Name and Price of all the Accessories in ascending order of their Price.
(ii) To display Id and SName of all Shoppe located in Nehru Place.
(iii) To display Minimum and Maximum Price of each Name of Accessories.
(iv) To display Name, Price of all Accessories and their respective SName, where they are available.

(b) Write the output of the following SQL commands:

(i) SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE>=5000;
(ii) SELECT AREA, COUNT(*) FROM SHOPPE GROUP BY AREA;
(iii) SELECT COUNT (DI ST INCT AREA) FROM SHOPPE:
(iv) SELECT NAME, PRICE*0.05 DISCOUNT FROM ACCESSORIES WHERE SNO IN ('S02'.'S03');

Answer:

(a) (i) SELECT Name, Price 
FROM ACCESSORIES 
ORDER BY Price:
(ii) SELECT Id, SName 
FROM SHOPPE
WHERE Area ='Nehru Place';
(iii) SELECT MIN(Price) "Minimum Price",
MAX(Price)"Maximum Price", Name 
FROM ACCESSORIES
GROUP BY Name:
(iv) SELECT Name, Price, SName 
FROM ACCESSORIES A, SHOPPE S
WHERE A.Id = S.Id;

but this query enable to show the result because A.Id and S.Id are not identical.

(iv) The given query will result in an error as there is no column named SNo in ACCESSORIES table.

Question 7:
Write SQL queries for (a) to (f) and write the output for the SQL queries mentioned in
(g) parts (i) to (iv) on the basis of tables ITEMS and TRADERS. Delhi 2013

(a) To display the details of all the items in ascending order of item names (i.e. I NAME).
(b) To display item name and price of all those items, whose price is in the range of 10000 and 22000 (both values inclusive).
(c) To display the number of items, which are traded by each trader. The expected output of this query should be:

T01 2
T03 1
T02 2

(d) To display the Price, item name (i.e. IName) and quantity (i.e. Qty) of those items,
which have quantity more than 150.
(e) To display the names of those traders, who are either from DELHI or from MUMBAI.
(f) To display the name of the companies and the name of the items in descending order of company names.
(g) Obtain the outputs of the following SQL queries based on the data given in tables
ITEMS and TRADERS above.

(i) SELECT MAX(Price), MIN(Price) FROM ITEMS;
(ii) SELECT Price * Qty AMOUNT .
FROM ITEMS WHERE Code=1004;
(iii) SELECT DISTINCT TCode FROM ITEMS;
(iv) SELECT IName, TName 
FROM ITEMS I, TRADERS T 
WHERE I.TCode = T.TCode AND Qty < 100;

Answer:

(a) SELECT*FR0M ITEMS ORDER BY IName;
(b) SELECT IName, Price FROM ITEMS 
WHERE Price BETWEEN 10000 AND 22000;
(c) SELECT TCode, COUNT (*) FROM ITEMS 
GROUP BY TCode;
(d) SELECT Price, IName, Qty 
FROM ITEMS
WHERE Qty >150;
(e) SELECT TName 
FROM TRADERS
WHERE City = 'MUMBAI' OR City = 'DELHI';
(f) SELECT Company, I Name
FROM ITEMS
ORDER BY Company DESC;

Question 8:
Write SQL queries for (a) to (f) and write the outputs for (g) parts (i) to (iv) on the basis of tables
APPLICANTS and COURSES. Delhi 2013C

(a) To display NAME, FEE, GENDER, JOINYEAR about the APPLICANTS, who have joined before 2010.
(b) To display the names of applicants, who are paying FEE more than 30000.
(c) To display the names of all applicants in ascending order of their joinyear.
(d) To display the year and the total number of applicants joined in each year from the table APPLICANTS.
(e) To display the CJD and the number of applicants registered in the course from the APPLICANTS table.
(f) To display the applicant’s name with their respective course’s name from the tables APPLICANTS and COURSES.
(g) Give the output of the following SQL statements:

(i) SELECT NAME, JOINYEAR FROM APPLICANTS WHERE GENDER =' F' AND C_ID= ’ A02 ’ ;
(ii) SELECT MIN (JOI NYEAR) FROM APPLICANTS WHERE GENDER = ' M' ;
(iii) SELECT AVG(FEE) FROM APPLICANTS WHERE C_ID='A01 ’ OR C_ID='A05’;
(iv) SELECT SUM( FEE), C_ID FROM APPLICANTS GROUP BY C_ID HAVING C0UNT(*)=2;

Answer:

(a) SELECT NAME, FEE, GENDER, JOINYEAR FROM APPLICANTS WHERE JOINYEAR<2010;
(b) SELECT NAME FROM APPLICANTS WHERE FEE>30000;
(c) SELECT NAME FROM APPLICANTS ORDER BY JOINYEAR;
(d) SELECT JOINYEAR, COUNT!*) FROM APPLICANTS GROUP BY JOINYEAR;
(e) SELECT C_ID, COUNT(*) FROM APPLICANTS GROUP BY C_ID;
(f) SELECT NAME, COURSE FROM APPLICANTS, COURSES WHERE APPLICANTS.C_ID =COURSES.C_ID;

Question 9:
Consider the following tables CUSTOMER and ONLINESHOP. Write SQL commands for the statements
(a) to (d) and give outputs for SQL queries (e) to (h). Delhi 2012C


(a) To display CNAME, AREA of all female customers from CUSTOMER table.
(b) To display the details of all the CUSTOMERS in ascending order of CNAME within SID.
(c) To display the total number of customers for each AREA from CUSTOMER table.
(d) To display CNAME and corresponding SHOP from CUSTOMER table and ONLINESHOP table.

(e) SELECT COUNT(*), GENDER FROM CUSTOMER GROUP BY GENDER;
(f) SELECT COUNT(*) FROM ONLINESHOP;
(g) SELECT CNAME FROM CUSTOMER WHERE CNAME LIKE 'L%';
(h) SELECT DISTINCT AREA FROM CUSTOMER;

Answer:

(a) SELECT CNAME, AREA FROM CUSTOMER 
WHERE GENDER = 1 FEMALE'; .
(b) SELECT * FROM CUSTOMER ORDER BY SID, CNAME;
(c) SELECT AREA COUNT(*) FROM CUSTOMER GROUP BY AREA;
(d) SELECT CUSTOMER.CNAME, ON LINESHOP.SHOP
FROM CUSTOMER, ONLINESHOP WHERE CUSTOMER.SID=0NLINESHOP.SID;

Question 10:
Consider the following tables CARDEN and CUSTOMER and answer (a) and (b) parts of this questions:
All India 2012


(a) Write SQL commands for the following statements:

(i) To display the name of all the SILVER colored cars.
(ii) To display name of Car, Make and sitting Capacity of cars in descending order of their sitting Capacity.
(iii) To display the highest Charges at which a vehicle can be hired from CARDEN.
(iv) To display the Customer name and the corresponding name of the Cars hired by them.

(b) Give the output of the following SQL queries:

(i) SELECT COUNT(DISTINCT Make) FROM CARDEN;
(ii) SELECT MAX(Charges), MIN (Charges) FROM CARDEN;
(iii) SELECT COUNT(*) Make FROM CARDEN;
(iv) SELECT CarName FROM CARDEN WHERE Capacity = 4;

Answer:

(a) (i) SELECT CarName FROM CARDEN WHERE Col or=' SILVER' ;
(ii) SELECT CarName, Make, Capacity FROM CARDEN ORDER BY Capacity DESC;
(iii) SELECT MAX(Charges) FROM CARDEN;
(iv) SELECT CUSTOMER.CName, CARDEN.CarName FROM CARDEN, 
CUSTOMER WHERE CARDEN.Ccode=CUSTOMER.Ccode;

Question 11:
Consider the following tables EMPLOYEE and SALGRADE and answer (a) and (b) parts of this question: All India 2012

(a) Write SQL commands for the folio wing statements:
(i) To display the details of all the EMPLOYEE in descending order of DOJ.
(ii) To display NAME and DESIGN of those EMPLOYEES, whose SGRADE is either S02 or S03.
(iii) To display the content of all the EMPLOYEES, whose DOJ is in between ’09-FEB-2006′ and ’08-AUG-2009′.
(iv) To add a new row in the EMPLOYEE table with the following:
109, ‘Harish Roy’, ‘HEAD-IT’, ‘S02′, ’09-SEP-2007′, ’21 -APR-1983’.
(b) Give the output of the following SQL queries:

(i) SELECT COUNT (SGRADE) , SGRADE FROM EMPLOYEE GROUP BY SGRADE;
(ii) SELECT MIN( DOB), MAX(DOJ) FROM EMPLOYEE;
(iii) SELECT NAME, SALARY FROM EMPLOYEE E, SALGRADE S 
WHERE E.SGRADE=S.SGRADE AND E.EC0DE<103;
(iv) SELECT SGRADE, SALARY+HRA FROM SALGRADE 
WHERE SGRADE='S02';

Answer:

(a) (i) SELECT * FROM EMPLOYEE 
ORDER BY DOJ DESC;
(ii) SELECT NAME, DESIGN FROM EMPLOYEE 
WHERE (SGRADE= ' S02' OR SGRADE='S03');
(iii) SELECT * FROM EMPLOYEE WHERE DOJ BETWEEN'09-FEB-2006'AND'08-AUG-2009';
(iv) INSERT INTO EMPLOYEE VALUES(109,'Hari sh Roy','HEAD-IT,'S02',
'09-SEP-2007',’21-APR-1983');

(b) The output is given after excluding the row given in part (a(iv)).

Question 12:
Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this questions: Delhi 2010


(a) Write SQL commands for the following statements:
(i) To display details of all the items in the STORE table in ascending order of LastBuy.
(ii) To display ItemNo and Item name of those items from STORE table, whose Rate is more than Rs. 15.
(Hi) To display the details of those items whose Supplier code (Scode) is 22 or Quantity in Store (Qty) is more than 110 from the table STORE.
(iv) To display minimum rate of items for each Supplier individually as per Scode from the table STORE.
(b) Give the output of the following SQL queries:

(i) SELECT COUNT(DISTINCT Scode) FROM STORE;
(ii) SELECT Rate * Qty FROM STORE WHERE ItemNo = 2004;
(iii) SELECT Item, Sname FROM STORE S, SUPPLIERS P 
WHERE S.Scode=P.Scode AND ItemNo=2006;
(iv) SELECT MAX( LastBuy) FROM STORE;

Answer:

(a) (i) SELECT * FROM STORE ORDER BY LastBuy;
(ii) SELECT ItemNo, Item FROM STORE WHERE Rate>15;
(iii) SELECT * FROM STORE WHERE Scode = 22 OR Qty>110;
(iv) SELECT MIN(Rate) FROM STORE GROUP BY Scode;

Question 13:
Consider the following tables STUDENT and STREAM. Write SQL commands for the statements (a) to (d) and give outputs for SQL queries (e) to (h). Delhi 2009C


(a) To display the name of streams in alphabetical order from table STREAM.
(b) To display the number of students whose POINTS are more than 5.
(c) To update GRADE to A’for all those students, who are getting more than 8 as POINTS.
(d) ARTS+MATHS stream is no more available. Make necessary change in table STREAM.

(e) SELECT SUM(POINTS) FROM STUDENT WHERE AGE > 14;
(f) SELECT STRODE, MAX(POINTS) FROM STUDENT GROUP BY STRODE 
HAVING SCODE BETWEEN 105 AND 130;
(g) SELECT AVG(AGE) FROM STUDENT WHERE SCODE IN (102, 105, 110, 120);
(h) SELECT COUNT(STRNAME) FROM STREAM WHERE STRNAME LIKE "SCI%";

Answer:

(a) SELECT STRNAME FROM STREAM ORDER BY STRNAME;
(b) SELECT COUNT(*) FROM STUDENT WHERE POINTS > 5;
(c) UPDATE STUDENT SET GRADE = 'A' WHERE POINTS > 8;
(d) DELETE FROM STREAM WHERE STRODE = 6;

Question 14:
Consider the following tables GARMENT and FABRIC. Write SQL commands for the statements (a) to (d) and give outputs for SQL queries (e) to (h). Delhi 2009


(a) To display GCODE and DESCRIPTION of each GARMENT in descending order ofGCODE.
(b) To display the details of all the GARMENTS, which have READYDATE in between 08-DEC-07 and 16-JUN-08 (inclusive of both the dates).
(c) To display the average PRICE of all the GARMENTS. Which are made up of FABRIC with FCODE as F03.
(d) To display FABRIC wise highest and lowest price of GARMENTS from GARMENT table. (Display FCODE of each GARMENT alongwith highest and lowest price.)

(e) SELECT SUM(PRICE) FROM GARMENT WHERE FCODE = 'F01’;
(f) SELECT DESCRIPTION, TYPE FROM GARMENT, FABRIC
WHERE GARMENT.FCODE = FABRIC.FCODE AND GARMENT.PRICE >=1260:
(g) SELECT MAX( FCODE) FROM FABRIC;
(h) SELECT COUNT(DISTINCT PRICE) FROM GARMENT;

Answer:

(a) SELECT GCODE, DESCRIPTION FROM GARMENT ORDER BY GCODE DESC;
(b) SELECT*FROM GARMENT
WHERE READYDATE BETWEEN '08-DEC-07' AND '16-JUN-08';
(c) SELECT AVG(PRICE) FROM GARMENT WHERE FCODE = ’E03' ;
(d) SELECT FCODE, MAX(PRICE), MIN(PRICE) FROM GARMENT GROUP BY FCODE;

Question 15:
Consider the following tables. Write SQL commands for the statements
(a) to (d) and give outputs for SQL queries (e) to (h).


(a) To display the names of all Senders from Mumbai.
(b) To display the RecID, SenderName, SenderAddress, RecName, RecAddressfor every Recipient.
(c) To display Recipient details in ascending order of RecName.
(d) To display number of Recipients from each City.

(e) SELECT DISTINCT SenderCity FROM SENDER;
(f) SELECT A.SenderName, B.RecName FROM SENDER A, RECIPIENT B 
WHERE A.Sender ID=B.Sender ID AND B.RecCity = 'Mumbai';
(g) SELECT RecName, RecAddress FROM RECIPIENT WHERE RecCity 
NOT IN ('Mumbai', 'Kolkata');
(h) SELECT RecID, RecNam FROM RECIPIENT
WHERE SenderlD = 'MU02' OR SenderlD = ' ND50 ’ ;

Answer:

(a) SELECT SenderName FROM SENDER WHERE SenderCity = 'Mumbai';
(b) SELECT RecID, SenderName, SenderAddress, RecName, RecAddress 
FROM RECIPIENT, SENDER WHERE RECIPI ENT.Sender ID = SENDER.Sender ID;
(c) SELECT * FROM RECIPIENT ORDER BY RecName;
(d) SELECT COUNT(*) As "No. of Recipients", RecCity FROM RECIPIENT 
GROUP BY RecCity;

Question 16:
Write the SQL commands for (a) to (f) and write the outputs for SQL queries (g) parts (i) to (iv) on the basis of the table HOSPITAL

(a) To show all information about the patients of Cardiology Department.
(b) To list the name of female patients, who are in Orthopaedic Department.
(c) To list names of all patients with their date of admission in ascending order.
(d) To display Patient’s Name, Charges, Age for male patients only.
(e) To count the number of patients with age>20.

(f) To insert a new row in the HOSPITAL table with the following data:

{11,'Mustafa',37,'ENT','25/02/98',250,'M'}

(g) Give the output of following SQL statements

(i) SELECT COUNT (DISTINCT Charges) FROM HOSPITAL;
(ii) SELECT MIN(Age) FROM HOSPITAL WHERE Sex = 'M' ;
(iii) SELECT SUM(Charges) FROM HOSPITAL WHERE Sex = ' F' ;
(iv) SELECT AVG(Charges) FROM HOSPITAL WHERE Dateofadm < '12/02/98' ;

Answer:

(a) SELECT * FROM HOSPITAL WHERE Department = 'Cardiology';
(b) SELECT Name FROM HOSPITAL WHERE Department = 'Orthopaedic' AND Sex = ' F' ;
(c) SELECT Name FROM HOSPITAL ORDER BY Dateofadm;
(d) SELECT Name, Charges, Age FROM HOSPITAL WHERE Sex = 'M' ;
(e) SELECT COUNT!*) FROM HOSPITAL WHERE Age > 20;
(f) INSERT INTO HOSPITAL VALUES (11,'Mustafa',37,'ENT','25/02/98',250, 'M');

Question 17:
Write SQL commands for (a) to (f) and write the outputs for (g) parts (i) to (iii) on the basis of table INTERIORS.

(a) To show all information about the Sofa from the INTERIORS table.
(b) To list the ITEM NAME, which are priced at more than 10000 from the INTERIORS table.
(c) To list ITEM NAME and TYPE of those items, in which DATEOFSTOCK is before 22/01/02 from the INTERIORS table in descending order of ITEM NAME.
(d) To display ITEM NAME and DATEOFSTOCK of those items, in which the discount percentage is more than 15 from INTERIORS table.
(e) To count the number of items, whose type is Double Bed from INTERIORS table.

(f) To insert a new row in the INTERIORS table with the following data

{114,'Truelndian','Office Table',' 28/03/0315000,20}

(g) Give the output of following SQL statements:

(i) SELECT COUNT ( DISTINCT TYPE) FROM INTERIORS; .
(ii) SELECT AVG (DISCOUNT) FROM INTERIORS WHERE TYPE = 'Baby cot’ ;
(iii) SELECT SUM( PRICE) FROM INTERIORS WHERE DATE0FST0CK<'12/02/02 ' ;

Outputs of the below mentioned queries should be based on original data given in the tables i.e. without considering the insertion done in (vi) part of this question.
Answer:

(a) SELECT * FROM INTERIORS WHERE TYPE = 'Sofa';
(b) SELECT ITEMNAME FROM INTERIORS WHERE PRICE > 10000;
(c) SELECT ITEMNAME, TYPE FROM INTERIORS WHERE DATEOFSTOCK 
< '22/01/02' ORDER BY ITEMNAME DESC;
(d) SELECT ITEMNAME , DATEOFSTOCK FROM INTERIORS WHERE DISCOUNT > 15;
(e) SELECT COUNT(*) FROM INTERIORS WHERE TYPE = ’Double Bed';
(f) INSERT INTO INTERIORS VALUES 
(14, ' True Indian', 'Office Table', '28/03/03',15000,20);

(g) The outputs is given after excluding the row given in part (f).

Question 18:
Given the following tables for a database LIBRARY

Write SQL queries for (a) to (f) and output of(g):
(a) To show Book name, Author name and Price of books of EPB Publishers.
(b) To list the names from books of Fiction type.
(c) To display the names and price of the books in descending order of their price.
(d) To increase the price of all books of First Publ Publishers by 50.
(e) To display the Book_ld, Book_Name and Quantityjssued for all books which have been issued.
(The query will require contents from both the tables.)
(f) To insert a new row in the table ISSUED having the following data

'F0002’, 4

(g) Give the output of the following queries based on the above tables:

(i) SELECT COUNT (DISTINCT Publishers) FROM BOOKS;
(ii) SELECT SUM( Price) FROM BOOKS WHERE Qty > 5;
(iii) SELECT Book_Name, Author_Name FROM BOOKS WHERE Price < 500;
(iv) SELECT COUNT(*) FROM BOOKS;

Answer:

(a) SELECT Book_Name, Author_Name, Price FROM BOOKS WHERE Publishers = 'EPB' ;
(b) SELECT Book_Name FROM BOOKS WHERE Type = 'Fiction';
(c) SELECT Book_Name, Price FROM BOOKS ORDER BY Price DESC;
(d) UPDATE BOOKS SET Price = Price + 50 
WHERE Publishers = 'First Publ ’ ;
(e) SELECT a.Book_Id, a.Book_Name, b.Quantity_Issued FROM BOOKS a, 
Issued b  WHERE a.Book_Id = b.Book_Id;
(f) INSERT INTO ISSUED VALUES(' F0002 ’ , 4);

(g) The output is given after excluding the row given in part(f).

Question 19:
Write SQL commands for (a) to (f) and write the outputs for (g) on the basis of table STUDENT

(a) List the name of all the students, who have taken stream as COMPUTER.
(b) To count the number of female students.
(c) To display the number of students stream wise.
(d) To insert a new row in the STUDENT table

9, 'KARISHMA', 'ECONOMICS', 300, 18, 'F'

(e) To display a report, listing NAME, STREAM, SEX and stipend, where stipend is 20% of fees.
(f) To display all the records in sorted order of name.
(g) Give the output of the following SQL statements based on STUDENT table:

(i) SELECT AVG ( FEES) FROM STUDENT WHERE STREAM = 'COMPUTER';
(ii) SELECT MAX(AGE) FROM STUDENT;
(iii) SELECT COUNT (DISTINCT STREAM) FROM STUDENT;
(iv) SELECT SUM( FEES) FROM STUDENT GROUP BY STREAM;

Answer:

(a) SELECT NAME FROM STUDENT WHERE STREAM ='COMPUTER';
(b) SELECT COUNT(*) FROM STUDENT WHERE SEX = 'F';
(c) SELECT STREAM, COUNT!*) FROM STUDENT GROUP BY STREAM;
(d) INSERT INTO STUDENT VALUES (9,'KARISHMA','ECONOMICS',300, 18,'F');
(e) SELECT NAME, STREAM, SEX, FEES*20/100 'STIPEND' FROM STUDENT;
(f) SELECT * FROM STUDENT ORDER BY NAME;

(g) The output is given after excluding the row given in part (f).

Question 20:
Given the following family relation. Write SQL commands for questions (a) to (f) and output of
(g) based on the table FAMILY

(a) To select all the information of family, whose Occupation is Service.
(b) To list the name of family, where female members are more than 3.
(c) To list all names of family with income in ascending order.
(d) To display family’s name, malemembers and occupation of business family.
(e) To count the number of family, whose income is less than 10,000.
(f) To insert a new record in the FAMILY table with the following data

9, "D'souza", 2, 1, 15000, "Service"

(g) Give the output of the following SQL commands .

(i) SELECT MIN(DISTINCT Income) FROM FAMILY;
(ii) SELECT MINI Femal eMembers) FROM FAMILY WHERE Occupation = 'Mixed';
(iii) SELECT SUM(Income) FROM FAMILY WHERE Occupation = ’Service’;
(iv) SELECT AVG( Income) FROM FAMILY;

Answer:

(a) SELECT * FROM FAMILY WHERE Occupation = 'Service';
(b) SELECT Name FROM FAMILY WHERE FemaleMembers > 3;
(c) SELECT Name, Income FROM FAMILY ORDER BY Income;
(d) SELECT Name, Mai eMembers , Occupation FROM FAMILY WHERE Occupati on = ' Business ' ;
(e) SELECT COUNT(*) FROM FAMILY WHERE Income < 10000;
(f) INSERT INTO FAMILY VALUES(9,'D'souza',2,1,15000, 'Service');

(g) The output is given after excluding the row given in part (f)

Question 21:
Consider the following tables PRODUCT and CLIENT. Write SQL commands for the statement (a) to (d) and give outputs for SQL queries (e) to (h). Delhi 2008

(a) To display the details of those Clients, whose City is Delhi.
(b) To display the details of products, whose Price is in the range of 50 to 100 (both values included).
(c) To display the ClientName, City from table CLIENT and ProductName and Price from table PRODUCT, with their corresponding matching PJD.
(d) To increase the Price of all products by 10.

(e) SELECT DISTINCT City FROM CLIENT;
(f) SELECT Manufacturer, MAX(Price), MIN(Price), Count(*) 
FROM PRODUCT GROUP BY Manufacturer;
(g) SELECT ClientName, Manufacturer FROM PRODUCT, 
CLIENT "" WHERE CLIENT. P_ID = PRODUCT . P_I D ;
(h) SELECT ProductName, Price * 4 FROM PRODUCT;

Answer:

(a) SELECT * FROM CLIENT WHERE Ci ty=' Delhi ' ;
(b) SELECT * FROM PRODUCT WHERE Price BETWEEN 50 AND 100;
(c) SELECT ClieritName, City, ProductName, Price FROM CLIENT, 
PRODUCT WHERE CLIENT.P_ID = PRODUCT.P_ID;
(d) UPDATE PRODUCT SET Price = Price + 10;

Question 22:
Study the following tables DOCTOR and SALARY and write SQL commands for the questions (a) to (d) and give outputs for SQL queries (e) and (f).


(a) Display NAME of all doctors who are in MEDICINE department having more than
lOyrs experience from the table DOCTOR.
(b) Display the average salary of all doctors working in ENT department using the tables DOCTOR and SALARY SALARY = BASIC + ALLOWANCE.
(c) Display the minimum ALLOWANCE of female doctors.
(d) Display the highest consultation fee among all male doctors.

(e) SELECT C0UNT(*) FROM DOCTOR WHERE SEX = 'F';
(f) SELECT NAME, DEPT, BASIC FROM DOCTOR, SALARY 
WHERE DEPT = 'ENT' AND DOCTOR.ID = SALARY.ID;

Answer:

(a) SELECT NAME FROM DOCTOR WHERE DEPT = ’MEDICINE’ AND EXPERIENCE > 10;
(b) SELECT AVG(BASIC + ALLOWANCE) FROM SALARY WHERE SALARY.ID IN(SELECT ID FROM
DOCTOR WHERE DEPT = 'ENT');
(c) SELECT MIN(ALLOWANCE) FROM SALARY WHERE SALARY.ID 
IN(SELECT ID FROM DOCTOR WHERE SEX = 'F’);
(d) SELECT MAX(CONSULTATION) FROM SALARY WHERE SALARY.ID 
IN(SELECT ID FROM DOCTOR WHERE SEX = 'M');

Question 23:
Study the following tables FLIGHTS and FARES and write SQL commands for the questions
(a) to (d) and give outputs for SQL queries (e) and (f).


(a) Display FL_NO and NO_FLIGHT from KANPUR to BENGALURU from the table FLIGHTS.
(b) Arrange the contents of the table FLIGHTS in the ascending order of FL_NO.
(c) Display the FL_NO and fare to be paid for the flights from DELHI to MUMBAI using the tables FLIGHTS and FARES, where the fare to be paid = FARE + FARE * TAX % 100.
(d) Display the minimum fare INDIAN AIRLINES is offering from the table FARES.

(e) SELECT FL_NO, NO_FLIGHT, AIRLINES FROM FLIGHTS, FARES 
WHERE, STARTING = 'DELHI' AND FLIGHTS,FL_N0 = FARES.FL_N0;
(f) SELECT COUNT (DISTINCT ENDING) FROM FLIGHTS;

Answer:

(a) SELECT FL_N0, N0_FLIGHT FROM FLIGHTS
WHERE STARTING = 'KANPUR' AND ENDING = 'BENGALURU';
(b) SELECT * FROM FLIGHTS ORDER BY FL NO;
(c) SELECT FL_N0, FARE + FARE * TAX%100 FROM FARES WHERE FL NO 
= (SELECT FL_No FROM FLIGHTS WHERE STARTING = 'DELHI' AND ENDING = 'MUMBAI');
(d) SELECT MIN(FARE) FROM FARES GROUP BY AIRLINES 
HAVING AIRLINES = 'INDIAN AIRLINES';

Question 24:
Write SQL commands for (a) to (g) on the basis of the table SPORTS

(a) Display the names of the students, who have grade ‘C in either GAM El or GAME2 or both.
(b) Display the number of students getting grade Win Cricket.
(c) Display the names of the students who have same game for both GAME1 and GAME2.
(d) Display the games taken up by the students, whose name starts with W.
(e) Add a new column named MARKS.
(f) Assign a value 200 for Marks for all those, who are getting grade ‘S’ or grade W in both GAME1 and GAME2.
(g) Arrange the whole table in the alphabetical order of Name.

Answer:

(a) SELECT NAME FROM SPORTS WHERE GRADE ='C’ OR GRADE1='C';
(b) SELECT COUNT(*) FROM SPORTS
WHERE (GAMEl='Cricket' AND GRADE='A' ) OR 
(GAME2 ='Cricket' AND GRADE1 = 'A');
(c) SELECT NAME FROM SPORTS WHERE GAME1=GAME2;
(d) SELECT GAME1, GAME2 FROM SPORTS WHERE NAME LIKE ’ A%' ;
(e) ALTER TABLE SPORTS ADD(MARKS NUMBER(3));
(f) UPDATE SPORTS SET MARKS = 200
WHERE GRADE = 'A' OR GRADE = 'B’ OR GRADE1 = 'A' OR GRADE1 = 'B';
(g) SELECT * FROM SPORTS ORDER BY NAME;

Question 25:
Write SQL commands for (a) to (d) and write the outputs for (e) and (f) on the basis of table EMPLOYEE

(a) List the names of the employees, who are more than 34 years old sorted by NAME.
(b) Display a report, listing NAME, BASIC, DEPARTMENT and annual salary. Annual salary equals to BASIC * 12.
(c) To count the number of employees, who are either working in PERSONNEL or COMPUTER department.
(d) To inset a new row in the EMPLOYEE table

11. 'VIJAY', 9300, 'FINANCE', ’13/7/98', 35, "M"

(e) Give the output of the following SQL statements based on table EMPLOYEE:

(i) SELECT SUM! BASIC) FROM EMPLOYEE WHERE DEPARTMENT = 'PERSONNEL';
(ii) SELECT AVG(BASIC) FROM EMPLOYEE WHERE SEX = ' F' ;
(Hi) SELECT MAX (BAS IC) FROM EMPLOYEE WHERE DATOFAPP > '22/02/97';
(iv) SELECT COUNT (DISTINCT DEPARTMENT) FROM EMPLOYEE;

(f) Assume that there is one more table INCHARGE in the database as shown below:

What will be the output of the following query:

SELECT NAME, HEAD
FROM EMPLOYEE E. INCHARGE.I
WHERE E.DEPARTMENT = I.DEPT;

Answer:

(a) SELECT NAME FROM EMPLOYEE WHERE AGE > 34 ORDER BY NAME;
(b) SELECT NAME, BASIC, DEPARTMENT, BASIC*12 "Annual Salary" FROM EMPLOYEE;
(c) SELECT COUNT(*) FROM EMPLOYEE
WHERE DEPARTMENT = 'PERSONNEL' OR DEPARTMENT = 'COMPUTER';
(d) INSERT INTO EMPLOYEE VALUES(11, ' VI JAY'. 9300, 'FINANCE',
'13/7/98', 35, 'M' );

Question 26:
Write SQL commands for (a) to (g) and write the outputs for (h) on the basis of table COLLEGE

(a) To change the Basic salary to 10500 of all those teachers from COLLEGE, who joined the COLLEGE after 01/02/89 and are above the age of 50.
(b) To display -Name, Age and Basic of all those from COLLEGE, who belong to
Physics and Chemistry department only.
(c) To display all the department names from COLLEGE, with no duplication.
(d) To list names of all teachers from COLLEGE with their date of joining in ascending order within their Basic salaries in ascending order.
(e) To display maximum salary amongst the female teachers and also amongst the male teachers from COLLEGE. (Give a single command)
(f) To insert a new row in the table COLLEGE with the following data:

15, "ATIN", 27, "Physics", '15/05/02', 8500, "M"

(g) To delete a row from table COLLEGE, in which NAME is VIREN.
(h) Give the output of the following SQL statements:

(i) SELECT COUNT (Name) FROM COLLEGE;
(ii) SELECT MIN(Basic) FROM COLLEGE WHERE Age>40;

Answer:

(a) UPDATE COLLEGE SET Basic = 10500
WHERE DateofJoin>’01/02/89 ' AND Age>50;
(b) SELECT Name, Age, Basic FROM COLLEGE *.
WHERE Department=' Physi cs ' OR Department =' Chemi stry-' ;
(c) SELECT DISTINCT Department FROM COLLEGE;
SELECT Name, DateofJoin,Basic FROM COLLEGE ORDER BY Basic, DateofJoin; 
(d) SELECT MAX(Basic) FROM COLLEGE GROUP BY Sex;
(f) INSERT INTO COLLEGE VALUES(15, 'AT IN', 27 'Physics', '15/05/02', 8500, 'M'); 
(g) DELETE FROM COLLEGE WHERE Name = ‘VIREN';

The output is given after excluding the insertion and deletion of rows performed in above queries.

Computer ScienceChapterwise Question Bank for Computer ScienceNCERT Solutions