NCERT Solutions Class 12 Computer Science (C++) Chapter -12 (Structured Query Language)

NCERT Solutions Class 12 Computer Science (C++) Chapter -12 (Structured Query Language)

NCERT Solutions Class 12 Computer Science (C++) from class 12th Students will get the answers of Chapter-12 (Structured Query Language) This chapter will help you to learn the basics and you should expect at least one question in your exam from this chapter.
We have given the answers of all the questions of NCERT Board Computer Science (C++) Textbook in very easy language, which will be very easy for the students to understand and remember so that you can pass with good marks in your examination.
Solutions Class 12 Computer Science (C++) Chapter -12 (Structured Query Language)
NCERT Question-Answer

Class 12 Computer Science (C++)

Chapter-12 (Structured Query Language)

Questions and answers given in practice

Chapter-12 (Structured Query Language)

Short Answer Type Questions-I [2 mark each]

Question 1:
Differentiate between delete and drop table command ?

Answer:
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:
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:
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:
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:
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:
The wildcards are incorrect. The corrected query is SELECT name FROM teacher WHERE name
LIKE’ _ _  0%’

Question 7:
Consider the following tables School and Admin and answer this question :
Give the output the following SQL queries :

  1. Select Designation Count (*) From Admin Group By Designation Having Count (*) <2;
  2. SELECT max (EXPERIENCE) FROM SCHOOL;
  3. SELECT TEACHERNAME FROM SCHOOL WHERE EXPERIENCE >12 ORDER BY TEACHER NAME;
  4. SELECT COUNT (*), GENDER FROM ADMIN GROUP BY GENDER;

Table : SCHOOL

CODETEACHERSUBJECTDOJPERIODSEXPERIENCE
1001RAVI SHANKARENGLISH12/3/20002410
1009PRIYARAIPHYSICS03/09/19982612
1203LIS ANANDENGLISH09/04/2000275
1045YASHRAJMATHS24/8/20002415
1123GANANPHYSICS16/7/1999283
1167HARISHBCHEMISTRY19/10/1999275
1215UMESHPHYSICS11/05/19982216

TABLE : ADMIN

CODEGENDERDESIGNATION
1001MALEVICE PRINCIPAL
1009FEMALECOORDINATOR
1203FEMALECOORDINATOR
1045MALEHOD
1123MALESENIOR TEACHER
1167MALESENIOR TEACHER
1215MALEHOD

Answer:
(i)

VICE PRINCIPAL01

(ii)

16

(iii)

UMESH
YASH RAJ

Short Answer Type Questions-II [3 mark each]

Question 1:

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

                                     Table: VEHICLE
CODE   VTYPEPERKM
101VOLVO BUS160
102AC DELUXE BUS150
103ORDINARY BUS90
105SUV40
104CAR20

Note :

  1.  PERKM is Freight Charges per Kilometer.
  2.  VTYPE is Vehicle Type.
                                                   Table: TRAVEL
No.NAMETDATEKMCODENOP
101Janish Kin2015-11-1320010132
103Vedika Sahai2016-04-2110010345
105Tarun Ram2016-03-2335010242
 102John Fen      2016-02-13     90     102      40
107     Ahmed Khan      2015-01-10     75     104       2
104Raveena       2016-05-28     80     105       4
106Kripal Anya2016-02-0620010125

Note:

  • NO is Traveller Number
  • KM is Kilometer Travelled
  • NOP is number of travellers 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 of all the travellers from the table TRAVEL who are travelling by vehicle with code 101 or 102.
  3. To display the NO and NAME of those travellers from the table TRAVEL who travelled between ‘2015-12­31’ and ‘2015-04-01’.
  4. To display all the details from table TRAVEL for the travellers, who have travelled distance more than 100 KM in ascending order of NOP  .
  5. SELECT COUNT (*), CODE FROM TRAVEL GROUP BY CODE HAVING COUNT(*)>1;
  6. SELECT DISTINCT CODE FROM TRAVEL;
  7. SELECT A. CODE,NAME, VTYPE

FROM TRAVEL A,VEHICLE B
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:

  1.   Select NO, Name, TDATE from TRAVEL order by NO desc
  2. Select NAME from TRAVEL, where CODE in (101, 102)
  3. Select NO, NAME from TRAVEL where TDATE between ’2015-12-31′ and ‘2015-04-01’.
  4. Select * from TRAVEL where KM > 100 order by NOP.
COUNT (*)CODE
2101
2102

6.

DISTANCE (CODE)
101
103
102
104
105

7.

CODENAMEVTYPE
104Ahmed khanCAR
105RaveenaSUV

8.

NAME KM*PERKM
Tarun Ram 14000

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

VCODEVEHICLETYPEPERKM
VOlVOLVO BUS150
V02AC DELUXE BUS125
V03ORDINARY BUS80
V0’5SUV30
V04CAR18

Note:
PERKM is Freight Charges per kilometer.
Table : TRAVEL

CNoCNAMETRAVELDATEKMVCODENOP
101K.Niwal2015-12-13200VOl32
103Fredrick Sym2016-03-21120V0345
105Hitesh Jain2016-04-23450V0242
102Ravi Anish2016-01-1380V0240
107John Malina2015-02-1065V042
104Sahanubhuti2016-01-2890V0 54
106Ramesh Jaya2016-04-06100VOl25

Note:

  • Km is Kilometers Travelled
  • NOP is number of plassangers travelled in vehicle.

1.To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending order of CNO.
2.To display the CNAME of all the customers from the table TRAVEL who are travelling by vehicle with code V01 or V02.
3.To display the CNO and CNAME of those customers from the table TRAVEL who travelled between ‘2015-12-31’ and ‘2015-05-01’.
4.To display all the details from table TRAVEL for the customers, who have travel distance more than 120 KM in ascending order of NOP.
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  ;

Answer:
(i) Select CNO, CNAME, TRAVELDATE from TRAVEL order by CNO desc
(ii) Select CNAME from TRAVEL, where VCODE in (‘VOl’, ‘ V02 )
(iii)Select CNO, CNAME from TRAVEL where TRAVELDATE between ‘2015-12-31’ and ‘2015­-05-01 ‘
(iv) Select * from TRAVEL where KM > 120 order by NOP.
(v)

COUNT (*)VCODE
2V01
2V02

(vi)

DISTANCE (CODE)
V01
V03
V02
V04
V05

(vii)

VCODECNAMEVEHICLETYPE
V04JOHN MALINICAR

(viii)
CNAME KM*PERKM
Sahanubhuti 30
Note: PERKM is neither given in query nor in TABLE so no output is also acceptable.

Long Answer Type Questions [ 4 marks each]

Question 1:
Consider the following tables FACULTY and COURSES. Write SQL commands for the statements (i) to (v) and give outputs for SQL queries (vi) to (vii)
FACULTY

F_IDFnameLnameHire_dateSalary
102AmitMishra12-10-199812000
103NitinVyas24-12-19948000
104RakshitSoni18-5-200114000
105RashmiMalhotra11-9-200411000
106SulekhaSrivastava5-6-200610000

COURSES

C_IDFJDCname
C21102Grid Computing40000
C22106System Design16000
C23104Computer Security8000
C24106Human Biology15000
C25102Computer Network20000
C26105Visual Basic6000

(i) To display details of those Faculties whose salary is greater than 12000.
Answer:
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).
Answer:
Select * from Courses
where fees between 15000 and 50000;
(iii)To increase the fees of all courses by 500 of “System Design” Course.
Answer:
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.
Answer:
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;
Answer:
4
(vi)Select MIN (Salary) from FACULTY, COURSES where COURSES.F_ID = FACULTY.FJD;
Answer:
6000

Question 2:
Consider the following DEPT and WORKER tables. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii):
TABLE : DEPT

DCODEDEPARTMENTCITY
D01MEDIADELHI
D02MARKETINGDELHI
D03INFRASTRUCTUREMUMBAI
D05FINANCEKOLKATA
D04HUMAN RESOURCEMUMBAI

TABLE : WORKER

WNONAME . Y;DOJDOBGENDERDCODE
1001George K2013-09-021991-09-01MALED01
1002Ryma Sen2012-12-111990-12-15FEMALED03
1003Mohitesh2013-02-031987-09-04MALED05
1007Anil Jha2014-01-171984-10-19MALED04
1004Manila Sahai2012-12-091986-11-14FEMALEDOl
1005RSAHAY2013-11-181987-03-31MALED02
1006Jaya Priya2014-06-091985-06-23FEMALEDQ5

Note : DOJ refers to date of joining and DOB refers to date of birth of workers.
(i)To display Wno. Name, Gender from the table WORKER in descending order of Wno. Ans.

Answer: 
SELECT WNO, Name, Gender FROM Worker
ORDER BY Wno DESC;
(ii)To display the Name of all the FEMALE workers from the table WORKER.
Answer:
SELECT Name FROM Worker
WHERE gender = ‘FEMALE’;
(iii)To display the Wno and Name of those workers from the table WORKER who are born between
‘1987-01-01’ and ‘1991-12-01’.

Answer:
SELECT Wno, Name FROM Worker
WHERE DOB BETWEEN ‘1987-01-01’ AND ‘1991-12-01’;

OR

SELECT Wno, Name FROM worker
WHERE DOB > = 1987-01-01′ AND DOB < = ‘1991-12-01’;
WHERE DOB BETWEEN ‘1987-01-01’ AND ‘1991-12-01’;

OR

WHERE DOB > = ‘1987-01-01’ AND DOB < = ‘1991-12-01’;
(iv)To count and display MALE workers who have joined after ‘1986-01-01’.
Answer:
SELECT COUNT (*) FROM Worker
WHERE GENDER = ‘MALE’ AND DOJ > ‘1986­01-01’;

OR

SELECT * FROM Worker
WHERE GENDER = ‘MALE’ AND DOJ > ‘1986­01-01’;
(Any valid query for counting and/or displaying for male workers will be awarded 1 mark)
(v) SELECT COUNT (*), DCODE FROM WORKER GROUP BY DCODE HAVING COUNT (*) > 1;
Answer:
COUNT (*) DCODE

2      D01

2     D05
(vi)SELECT DISTINCT DEPARTMENT FROM DEPT;
Answer:
Department
MEDIA
MARKETING
INFRASTRUCTURE
FINANCE
HUMAN RESOURCE
(viii)SELECT NAME, DEPARTMENT, CITY FROM WORKER W, DEPT D WHERE W DCODE = D. DCODE AND WNO < 1003;
Answer:

NAME  DEPARTMENT CITY
George KMEDIADELHI
Ryma SeninfrastructureMUMBAI

(viii) SELECT MAX (DOJ), MIN (DOB) FROM WORKER;
Answer: MAX (DOJ) MIN (DOB)
2014-06-09 1984-10-19
Note : In the output queries, please ignore the order of rows

Question 3:
Consider the following DEPT and EMPLOYEE tables. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii).
TABLE : DEPT

DCODEDEPARTMENTLOCATION
D01INFRASTRUCTUREDELHI
D02MARKETINGDELHI
D03MEDIAMUMBAI
DOSFINANCEKOLKATA
D04HUMAN RESOURCEMUMBAI

TABLE : EMPLOYE

   ENONAMEDOJDOBGENDERDCODE
1001GEORGE K2013-09-021991-09-01MALED01
1002Ryma Sen2012-12-111990-12-15FEMALED03
1003Mohitesh2013-02-031987-09-04MALED05
1007Anil Jha2014-01-17198410-19MALED04
1004Manila Sahai2012-12-091986-11-14FEMALED01
1005RSAHAY2013-11-181987-03-31MALED02
1006JAYA Priya2014-06-091985-06-23FEMALED05

Note :DOJ refers to date of joining and DOB refers to date of Birth of employees.
(i)To display Eno, Name, Gender from the table EMPLOYEE in ascending order of Eno.

Answer: SELECT Eno, Name, Gender FROM Employee ORDER BY Eno;
(ii)To display the Name of all the MALE employees from the table EMPLOYEE.
Answer: SELECT Name FROM EMPLOYEE WHERE
Gender = ‘MALE’;
(iii)To display the Eno and Name of those employees from the table EMPLOYEE who are born between ‘1987-01-01’ and ‘1991-12-01’.
Answer:
SELECT Eno, Name FROM Employee
WHERE DOB BETWEEN ‘1987-01-01’ AND ‘1991-12-01’;

OR

SELECT Eno, Name FROM Employee
WHERE DOB > = ‘1987-01-01′ AND DOB < =’1991-12-01’;

OR

SELECT Eno, Name FROM Employee WHERE DOB > ‘1987-01-01’ AND DOB < ‘1991­12-01’;
WHERE DOB BETWEEN ‘1987-01-01’ AND ‘1991-12-01’;

OR

WHERE DOB > = ‘1987-01-01’ AND DOB < = ‘1991-12-01’;

OR

WHERE DOB > ‘1987-01-01’ AND DOB < ‘1991­12-01’);
(iv)To count and display FEMALE employees who have joined after ‘1986-01-01’;
Answer: SELECT count (*) FROM Employee
WHERE GENDER = ‘FEMALE’ AND DOJ > ‘1986-01-01’;

OR

SELECT * FROM Employee
WHERE GENDER = ‘FEMALE’ AND DOJ > ‘1986-01-01’;
(Any valid query for counting and/or displaying for female employees will be awarded 1 mark)
(v)SELECT COUNT (*), DCODE FROM EMPLOYEE
GROUP BY DCODE HAVING COUNT (*) > 1;

Answer:

COUNTDCODE
2D01
2D05


(½Mark for correct output)

(vi)SELECT DISTINCT DEPARTMENT FROM DEPT

Answer:  Department
INFRASTRUCTURE
MARKETING
MEDIA
FINANCE
HUMAN RESOURCE
(vii) SELECT NAME, DEPARTMENT FROM EMPLOYEE E, DEPT D WHERE E. DCODE = D.DCODE AND ENO <1003;

NAME  DEPARTMENT
George KMEDIA
Ryma Seninfrastructure

(viii) SELECT MAX (DOJ), MIN (DOB) FROM EMPLOYEE;
Answer:
MAX (DOJ)           MIN (DOB)
2014-06-09                  1984-10-19
Note : In the output queries, please ignore the order of rows.
Question 4:
Write SQL commands for the queries (i) to (iv) and output for (v) & (viii) based on a table COMPANY and CUSTOMER

CIDNAMECITYPRODUCTNAME
111SONYDELHITV
222NOKIAMUMBAIMOBILE
333ONIDADELHITV
444SONYMUMBAIMOBILE
555BLACKBERRYMADRASMOBILE
666DELLDELHILAPTOP

 

CUSTIDNAMEPRICEQTYCID
101ROHAN SHARMA70,00020222
102DEEPAK KUMAR50,00010666
103MOHAN KUMAR30,0005111
104SAHIL BANSAL35,0003333
105NEHA SONI25,0007444
106SONAL AGGARWAL20,0005333
107ARUN SINGH50,00015666
  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 COUNTO ,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 PRODU CTN AME=”MOBILE”;

Answer:
1. To display those 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
2MUMBAI
1MADRAS

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 WHERE COMPANY.CID=CUSTOMER.CID AND PRODUCTNAME=”MOBILE”;

MOBILEMUMBAI70000
MOBILEMUMBAI25000

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

CODETEACHERNAMESUBJECTDOJPERIODSEXPERIENCE
1001Ravi ShankarEnglish12/3/20002410
1009Priya RaiPhysics03/09/199826 ,12
1203Lisa AnandEnglish09/04/2000275
1045YashrajMaths24/08/20002415
1123GananPhysics16/07/1999283
1167Harish BChemistry19/10/1999275
1215UmeshPhysics11/05/19982216

Table : Admin

CodeGenderDesignation
1001MaleVice Principal
1009FemaleCoordinator
1203FemaleCoordinator
1045MaleHOD
1123MaleSenior Teacher
1167MaleSenior Teacher
1215MaleHOD

Write SQL statements for the following :

  1. To display TEACHERNAME, PERIODS of all teachers whose periods are more than 25.
  2. To display all the information from the table SCHOOL in descending order of experience.
  3. To display DESIGNATION without duplicate entries from the table ADMIN.
  4. To display TEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and ADMIN of Male teachers.

Answer:
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;

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

Answer:

IdSNameArea
S001ABC computronicsCP
S002All Infotech MediaGKII
S003Tech ShoppeCP
S004Geeks Tecno SoftNehru Place
S005Hitech Tech StoreNehru Place

 

NoNamePriceId
A01Mother Board12000SOI
A02Hard Disk5000SOI
A03Keyboard500S02
A04Mouse300SOI
A05Mother Board13000S02
A06Keyboard400S03
A07LCD6000S04
T08LCD5500S05
T09Mouse350S05
T10Hard Disk4500S03
  1. To display Name and Price of all the Accessories in ascending order of their Price.
  2. To display Id and SName of all Shoppe located in Nehru Place.
  3. To display Minimum and Maximum Price of each Name of Accessories.
  4. To display Name, Price of all Accessories and their respective SName where they are available.

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

  1. SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE > =5000;
  2. SELECT AREA, COUNT(*) FROM SHOPPE GROUP BY AREA;
  3. SELECT COUNT (DISTINCT AREA) FROM SHOPPE;
  4. SELECT NAME, PRICE*0.05 DISCOUNT FROM ACCESSORIES

Answer: (a) (i) SELECT Name, Price FROM ACCESSORIES ORDER BY Prices;
(ii)SELECT ID, SName FROM SHOPPE WHERE Area=”Nehru Place”;
(iii)SELECT Name, max (Price); min(Price) FROM ACCESSORIES, Group By Name;
(iv)SELECT Name, price, Sname FROM ACCESSORIES, SHOPPE WHERE SHOPPE.ID=ACCESSORIES.ID
(b)(i) Name
Mother Board
Hard Disk
LCD
(ii)

AreaCount
CP2
GK II1
Nehru Place2

(iii) count(Distinct Area)
3
(iv) Name

NameDISCOUNT
600600
Hard Disk250
Key Board20
Hard Disk225

Question 7:
Answer: the questions (a) to (g) on the basics of the following tables APPLICANTS and COURSB.

  1. To display name, fee, gender, joinyear about the applicants, who have joined before 2010.
  2. To display names of applicants, who are paying fee more than 30000.
  3. To display names of all applicants in ascending order of their joinyear.
  4. To display the year and the total number of applicants joined in each YEAR from the table APPLICANTS.
NoNameFeeGenderC_IDJoin

Year

1012Amandeep30000MA012012
1102Avisha25000FA022009
1103Ekant30000MA022011
1049Arun30000 MA032009
1025Amber40000MA022011
1106Ela40000FA052010
1017Nikita35000FA032012
1108Arleena30000FA032012
2109Shakti35000MA042011
1101Kirat25000MA012012


Table Courses 

C_IDCourse
A01Fashion Design
A02Networking
A03Hotel Management
A04Event Manangement
A05Office Management

5.To display the C_ID (i.e., CourselD) and the number of applicants registered in the course from the APPLICANTS and table.
6.To display the applicant’s name with their respective course’s name from the tables APPLICANTS and COURSES.
7.Give the output statements:of following SQL statements :
(i)SELECT Name, Joinyear FROM APPLICANTS
WHERE            GENDER=’F’ and C_ID=’A02′;
(ii) SELECT MIN (Joinyear) FROM APPLICANTS
(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 COUNT(*)=2;

Answer: (a) SELECT NAME,FEE,GENDERJOINYEAR FROM APPLICANTS
WHERE JOINYEAR<2000;
(b)SELECT NAME FROM APPLICANTS
WHERE FEE>30000;
(c)SELECT NAME FROM APPLICANTS
ORDERBY JOINYEAR ASC;
(d)SELECT YEAR, COUNTf)    FROM APPLICANTS;

(e)SELECT C_ID, COUNT(*) FROM APPLICANTS, COURSES

WHERE APPLICANTS.C_ID=COURSES; C_ID;
(f)SELECT NAME,COURSE FROM
APPLICANTS, COURSES
WHERE    APPLICANTS.C_ID=COURSES. C_ID;
(g)(i) Avisha 2009
(ii)2009
(iii)67
(iv)55000 A01

Question 8:
Write SQL queries for (a) to (g) and write the output for the SQL queries mentioned shown in (hi) to (h4) parts on the basis of table ITEMS and TRADERS :
Table : ITEMS

CODEINAMEQTYPRICECOMPANYTCODE
1001DIGITAL PAD 12i12011000XENITAT01
1006LED SCREEN 407038000SANTORAT02
1004CAR GPS SYSTEM5021500GEOKNOWT01
1003DIGITAL CAMERA 12X1608000DIGICLICKT02
1005PEN DRIVE 32 GB6001200STOREHOMET03

Table : TRADERS

TCodeTNameCity
T01
T03
T02
ELECTRONIC SALES BUSY STORE CORP DISP HOUSE INCMUMBAI
DELHI
CHENNAI
  • To display the details of all the items in ascending order of item names (i.e., INAME).
  • To display item name and price of all those items, whose price is in the range of 10000 and 22000 (both values inclusive).
  • To display the number of items, which are traded by each trader. The expected output of this query should be:
  • To display the price, item name and quantity (i.e., qty) of those items which have quantity more than 150.
  • To display the names of those traders, who are either from DELHI or from MUMBAI.
  • To display the names of the companies and the names of the items in descending order of company names.
  • Obtain the outputs, of the following SQL queries based on the data given in tables ITEMS and TRADERS above.

(h1)SELECT MAX (PRICE), MIN (PRICE) FROM ITEMS;
(h2) SELECT PRICE*QTY
FROM ITEMS WHERE CODE-1004;
(h3) SELECT DISTINCT TCODE FROM ITEMS;
(h4) SELECT INAME, TNAME
FROM ITEMS I, TRADERS T WHERE I.TCODE=T.TCODE AND
QTY<100;

Answer:
(a) SELECT INAME FROM ITEMS ORDER BY INAME ASC;
(b)SELECT INAME, PRICE FROM ITEMS WHERE PRICE => 10000 AND PRICE =< 22000;
(c)SELECT TCODE, COUNT (CODE) FROM
ITEMS GROUP BY TCODE;
(d)SELECT PRICE, INAME, QTY FROM ITEMS
WHERE (QTY> 150);
(e)SELECT TNAME FROM TRADERS
WHERE (CITY = “DELHI”) OR (CITY = “MUMBAI”)
ORDER BY COMPANY DESC;
(g)   (h1) 38000
1200
(h2) 1075000
(h3) T01
T02
T03
(h4) LED SCREEN 40 DISPHOUSE INC
CAR GPS SYSTEM ELECTRONICS
SALES

Question 9:
Write SQL queries for (a) to (f) and write the outputs for the SQL queries mentioned shown in (hi) to (h4) parts on the basis of tables PRODUCTS and SUPPLIERS
Table : PRODUCTS

PIDPNAMEQTYPRICECOMPANYSUPCODE
101DIGITAL CAMERA 14X12012000RENBIXSOI
102DIGITAL PAD lli10022000DIGI POPS02
104PEN DRIVE 16 GB5001100STOREKINGSOI
106LED SCREEN 327028000DISPEXPERTSS02
105CAR GPS SYSTEM6012000MOVEONS03

Table : SUPPLIERS

SUPCODESNAMECITY
SOIGET ALL INCKOLKATA
S03EASY        MARKETDELHI
CORP
S02DIGI BUSY GROUPCHENNAI

(a)To display the details of all the products in ascending order of product names (e., PNAME).
(b)To display product name and price of all those products, whose price is in the range of 10000 and 15000 (both values inclusive).
(c)To display the number of products, which are supplied by each supplier, i.e., the expected output should be;
2
2
1
(d)To display the price, product name and quantity (i.e., qty) of those products which have quantity more than 100.
(e)To display the names of those suppliers, who are either from DELHI or from CHENNAI.
(f)To display the name of the companies and the name of the products in descending order of company names.
(g)Obtain the outputs of the following SQL queries based on the data given in tables PRODUCTS and SUPPLIERS above.
(gl) SELECT DISTINCT SUPCODE FROM PRODUCTS;
(g2) SELEC MAX (PRICE), MIN (PRICE) FROM PRODUCTS
(g3) SELECT PRICE*QTY
FROM PRODUCTS WHERE PID = 104;
(g4) SELECT PNAME, SNAME
FROM PRODUCTS P, SUPPLIERS S WHERE P SUPCODE = S. SUPCODE AND QTY>100;

Answer:
(a) SELECT * FROM PRODUCTS ORDER. BY PNAME ASC;
(b)SELECT PNAME, PRICE FROM PRODUCTS WHERE ((PRICE => 10000) AND (PRICE = < 15000));
(c)SELECT SUPCODE, COUNT (PID) [Yz] FROM PRODUCTS GROUP BY SUPCODE;
(d)SELECT PRICE, PNAME, QTY FROM PRODUCTS WHERE (QTY > 100);
(e)SELECT SNAME FROM SUPPLIERS WHERE ((CITY = “DELHI”) OR (CITY = “CHENNAI”));
(f)SELECT COMPANY, PNAME FROM PRODUCTS ORDER BY COMPANY DESC;
(g) SOI
(g1) S02
S03
(g2) 28000
1100
(g3) 550000
(g4) PNAME       SNAME
DIGITAL CAMERA 14 X GET ALL INC
PENDRIVE 16 GB GET ALL INC
Question 10:
Give a suitable example of a table with sample data and illustrate Primary and Alternate Keys in it. Consider the following tables CARDEN and CUSTOMER and answer (b) and (c) parts of this question :
Table : CARDEN

CeodeCarNameMakeColourCapacityCharges
501A-StarSuzukiRED3Q14
503IndigoTataSILVER312
502InnovaToyotaWHITE715
509SX4SuzukiSILVER414
510C ClassMercedesRED435

Table : CUSTOMER

CCodeCnameCeode
1001Hemant Sahu501
1002Raj Lai509
1003Feroza Shah503
1004Ketan Dhal502

(b)Write SQL commands for the following statements:

  1. To display the names of all the silver coloured cars.
  2. Tp display names of car, make and capacity of cars in descending order of their sitting capacity.
  3. To display the highest charges at which a vehicle can be hired from CARDEN.
  4. To display the customer name and the corresponding name of the cars hired by them.

(c)Give the output of the following SQL queries:
(i)SELECT COUNT(DlST!NCT Make) FROM CARDEN;
(ii)SELECT MAX(Charges), MIN (Charges) FROM CARDEN;
SELECT COUNTO, Make FROM CARDEN;
(iv) SELECT CarName FROM CARDEN WHERE Capacity=4;

Answer:
(a) Primary Key of CARDEN = Ceode of CARDEN
Alternate Key = CarName:
Primary key of Customer = Ceode
Alternate Key of Customer = Cname
(b) (i)   SELECT CarName    From   CARDEN
WHERE Color = “SILVER”;
(ii)SELECT CarName, Make,   Capacity
From CARDEN ORDER BY Capacity DESC;
(iii)SELECT MAX(Charges) From CARDEN;
(iv)SELECT Cname, CarName From
CUSTOMER, CARDEN WHERE CARDEN. Ccode = CUSTOMER. Ccode;
(c) (i) 4
(ii) MAX(Charges)                      MIN(Charges)
35                                            12
(iii)5
(iv)SX4
C Class
Question 11:
(a) Give a suitable example of a table with sample data and illustrate Primary agd Candidate Keys in it. Consider the following tables CABHUB and CUSTOMER and answer (b) and (c) parts of this question :
Table : CABHUB

VcodeVehicleNameMakeColourCapacityCharges
100InnovaToyotaWHITE715
102SX4SuzukiBLUE414
104C ClassMercedesRED435
105A-StarSuzukiWHITE314
108IndigoTataSILVER            –312

Table : CUSTOMER

CeodeCnameVcode
1Hemant Sahu101
2Raj Lai108
3Feroza Shah105
4Ketan Dhal104

(b) Write SQL commands for the following statements:

  1. To display the names of all the white coloured vehicles.
  2. To display name of vehicle name and capacity of vehicles in ascending order of their sitting capacity.
  3. To display the highest charges at which a vehicle can be hired from CABHUB.
  4. To display the customer name and the corresponding name of the vehicle hired by them.

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

  1. SELECT COUNT (DISTINCT Make) FROMCABHUB;
  2. SELECT MAX(Charges), MIN(Charges) FROM CABHUB;
  3. SELECT COUNT (*) Make FROM CABHUB;
  4. SELECT Vehicle FROM CABHUB WHERE Capacity=4;

Answer:
(a) Primary Key of CABHUB = Vcode
Alternate key of CABHUB = Vehicle Name.
Primary Key of Customer = Ccode
Alternate Key of CUSTOMER = Cname.
(b) (i) SELECT VehicleName FROM CABHUB WHERE Colour =”WHITE”;
(ii)SELECT VehicleName, Capacity From CABHUB ORDER BY Capacity ASC;
(iii)SELECT MAX(Charges) FROM CABHUB;
(iv)SELECT Cname,VehicleName FROM CABHUB, CUSTOMER WHERE CUSTOMER. Vcode=CABHUB. Vcode;
(c)4
(ii)Max(Charges)                Min(Charges)
35                              12
(iii)5
(iv)SX4
C Class

Long Answer Type Question – II

Question 1:

WatchidWatch_NamePriceTypeQty_Store
‘woolHigh Time10000Unisex100
W002Life Time15000Ladies150
W003Wave20000Gents200
W004HlghFashion7000Unisex250
W005GoldenTime25000Gents100

 

WatchidQly_SoldQuarter
wool101
W00351
W002202
W003102
W001153
W002203
W005103
W003154

 

  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 between 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.watchid!=s.watchid;
  8. select watch_name, qty_store, sum(qty_sold), qty_store-sum(qty_sold) “Stock” from watches w, sales where w. watchid=s. watchid group by s.watchid;

Answer:
(i) Select*from watches where watch_name like’Time’
(ii)select watchjname, 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.watchid=s.watchid and quarter=l;
(v)

max(price)min(qty_store)
25000100

(vi)

quartersuxn(qty_sold)
115
230
345
415

(vii)

Watch_namepricetype
HighFashion7000Unisex

(viii)

Watch_nameqty_storeqty_soldStock
HighTime1002575
LifTime15040110
Wave20030170
GoldenTime1001090

UNIT – 1 : OBJECT ORIENTED PROGRAMMING IN C++

UNIT – II : DATA STRUCTURES

UNIT – III : DATABASE MANAGEMENT SYSTEM AND SQL

UNIT – IV : BOOLEAN ALGEBRA

UNIT – V : NETWORKING & OPEN SOURCE SOFTWARE