Computer Science 4 Marks Each

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	VTYPE	PERKM
101	VOLVO BUS	160
102	AC DELUXE BUS	150
103	ORDINARY BUS	90
105	SUV	40
104	CAR	20

Note:

•PERKM is Freight Charges per kilometer

•VTYPE is Vehicle Type

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	AhmedKhan	2015-01-10	75	104	2
104	Raveena	2015-05-28	80	105	4
106	Kripal Anya	2016-02-06	200	101	25

Note:

•NO is Traveller Number

•KM is Kilometer travelled

•NOP is number of travellers travelled in vehicle

•TDATE is Travel Date

(i) To display NO, NAME, TDATE from the table TRAVEL in descending order of NO.

(ii) To display the NAME of all the travellers from the table TRAVEL who are travelling by vehicle with code 101 or 102.

(iii) To display the NO and NAME of those travellers from the table TRAVEL who travelled between ‘2015-12-31 ’and ‘2015-04-01 ’.

(iv) To display all the details from table TRAVEL for the travellers, who have travelled distance more than 100 KM in ascending order of NOP.

(v) SELECT COUNT (*), CODE FROM TRAVEL GROUP BY CODE HAVING COUNT(*)>1;

(vi) SELECT DISTINCT CODE FROM TRAVEL;

(vii) SELECT A.CODE,NAME,VTYPE FROM TRAVEL A,VEHICLE B WHERE A.CODE=B. CODE AND KM <90;

(viii) SELECT NAME,KM*PERKM FROM TRAVEL A, VEHICLE B WHERE A.CODE=B.CODE AND A.CODE=‘105 ’;

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

DCODE	DEPARTMENT	LOCATION
D01	INFRASTRUCTURE	DELHI
D02	MARKETING	DELHI
D03	MEDIA	MUMBAI
D05	FINANCE	KOLKATA
D04	HUMAN RESOURCE	MUMBAI

Table: EMPLOYEE

ENO	NAME	DOJ	DOB	GENDER	DCODE
1001	George K	2013-09-02	1991-09-01	MALE	D01
1002	Ryma Sen	2012-12-11	1990-12-15	FEMALE	D03
1003	Mohitesh	2013-02-03	1987-09-04	MALE	D05
1007	Anil Jha	2014-01-17	1984-10-19	MALE	D04
1004	Manila Sahai	2012-12-09	1986-11-14	FEMALE	D01
1005	R SAHAY	2013-11-18	1987-03-31	MALE	D02
1006	Jaya Priya	2014-06-09	1985-06-23	FEMALE	D05

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.

(ii) To display the Name of all the MALE employees from the table EMPLOYEE.

(iii) To display the Eno and Name of those employees from the table EMPLOYEE who a born between ‘1987‐01‐01 ’and ‘1991‐12‐01 ’.

(iv) To count and display FEMALE employees who have joined after ‘1986‐01‐01 ’.

(v) Select count(*),dcode from employee group by dcode having count(*)>1;

(vi) Select distinct department from dept;

(vii) Select name, department from employee e, dept d where e.dcode=d.dcode and en0 <1003;

(viii) select max(doj), min(dob) from employee;

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

DCODE	DEPARTMENT	CITY
D01	MEDIA	DELHI
D02	MARKETING	DELHI
D03	INFRASTRUCTURE	MUMBAI
D05	FINANCE	KOLKATA
D04	HUMAN RESOURCE	MUMBAI

Table: WORKER

WNO	NAME	DOJ	DOB	GENDER	DCODE
1001	George K	2013-09-02	1991-09-01	MALE	D01
1002	Ryma Sen	2012-12-11	1990-12-15	FEMALE	D03
1003	Mohitesh	2013-02-03	1987-09-04	MALE	D05
1007	Anil Jha	2014-01-17	1984-10-19	MALE	D04
1004	Manila Sahai	2012-12-09	1986-11-14	FEMALE	D01
1005	R SAHAY	2013-11-18	1987-03-31	MALE	D02
1006	Jaya Priya	2014-06-09	1985-06-23	FEMALE	D05

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.

(ii) To display the Name of all the FEMALE workers from the table WORKER.

(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 ’.

(iv) To count and display MALE workers who have joined after ‘1986-01-01 ’.

(v) SELECT COUNT(*), DCODE FROM WORKER GROUP BY DCODE HAVING COUNT(*)>1;

(vi) SELECT DISTINCT DEPARTMENT FROM DEPT;

(vii) SELECT NAME, DEPARTMENT, CITY FROM WORKER W,DEPT D WHERE W.DCODE=D.DCODE AND WNO <1003;

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

Answer the (a) and (b) on the basis of the following tables STORE and ITEM:

Table: STORE

SNo	SName	AREA
S01	ABC Computronics	GK II
S02	All Infotech Media	CP
S03	Tech Shoppe	Nehru Place
S05	Hitech Tech Store	CP

Table: ITEM

INo	IName	Price	SNo
T01	Mother Board	12000	S01
T02	Hard Disk	5000	S01
T03	Keyboard	500	S02
T04	Mouse	300	S01
T05	Mother Board	13000	S02
T06	Key Board	400	S03
T07	LCD	6000	S04
T08	LCD	5500	S05
T09	Mouse	350	S05
T10	Hard disk	4500	S03

(a) Write the SQL queries (1 to 4):

(i) To display IName and Price of all the items in the ascending order of their Price.

(ii) To display the SNo and SName or all stores located in CP.

(iii) To display the minimum and maximum price of each IName from the table Item.

(iv) To display the IName, price of all items and their respective SName where they are available.

(b) Write the output of the following SQL commands (i) to (iv):

(i) Select distinct iname from item where price >= 5000;

(ii) Select area, count(*) from store group by area;

(iii) Select count(distinct area) from store;

(iv) Select iname, price*0.05 discount from item where sno in (‘s02 ’, ‘s03 ’);

Write SQL queries for (a) to (f) and write the outputs for the SQL queries mentioned shown in (g1) to (g4) parts on the basis of tables ITEMS and TRADERS:

Table: ITEMS

CODE	INAME	QTY	PRICE	COMPANY	TCODE
1001	DIGITAL PAD 12i	120	11000	XENITA	T01
1006	LED SCREEN 40	70	38000	SANTORA	T02
1004	CAR GPS SYSTEM	50	21500	GEOKNOW	T01
1003	DIGITAL CAMERA 12X	160	8000	DIGICLICK	T02
1005	PEN DRIVE 32GB	600	1200	STOREHOME	T03

Table: TRADERS

TCode	TName	CITY
T01	ELECTRONIC SALES	MUMBAI
T03	BUSY STORE CORP	DELHI
T02	DISP HOUSE INC	CHENNAI

(a) To display the details of all the items in the ascending order of item names (i.e. INAME).

(b) To display item name and price of all those items, whose price is in 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

T02 2

T03 1

(d) To display the price, item name 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 names of the companies and the names of the items in descending order of company names.

(g1) Select max(price), min(price) from items;

(g2) Select price*qty amount from items where code=1004;

(g3) Select distinct tcode from items;

(g4) Select iname, tname from items i, traders t where i.tcode=t.tcode and qty <100;

Consider the following tables CARHUB and CUSTOMER and answer (a) and (b) parts of this question:

Table: CARHUB

Vcode	VehicleName	Make	Color	Capacity	Charges
100	Innova	Toyota	WHITE	7	15
102	SX4	Suzuki	BLUE	4	14
104	C Class	Mercedes	RED	4	35
105	A-Star	Suzuki	WHITE	3	14
108	Indigo	Tata	SILVER	3	12

Table: CUSTOMER

CCode	CName	VCode
1	Hemant Sahu	101
2	Raj Lal	108
3	Feroza Shah	105
4	Ketan Dhal	104

(a) Write SQL commands for the following statements:

(i) To display the names of all white colored vehicles

(ii) To display name of vehicle, make and capacity of vehicles in ascending order of their sitting capacity

(iii) To display the highest charges at which a vehicle can be hired from CARHUB.

(iv) To display the customer name and the corresponding name of the vehicle hired by them.

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

(i) Select count(distinct make) from cabhub;

(ii) Select max(charges), min(charges) from carhub;

(iii) Select count(*), make from carhub;

(Iv) Select vehiclename from carhub where capacity = 4;

Consider the following tables WORKER and PAYLEVEL and answer (a) and (b) parts of this question:

Relation: WORKER

ECODE	NAME	DESIG	PAYLEVEL	DOJ	DOB
11	Radhey Shyam	Supervisor	P001	13-Sep-2004	23-Aug-1981
12	Chander Nath	Operator	P003	22-Feb-2010	12-Jul-1987
13	Fizza	Operator	P003	14-June-2009	14-Oct-1983
15	Ameen Ahmed	Mechanic	P002	21-Aug-2006	13-Mar-1984
18	Sanya	Clerk	P002	19-Dec-2005	09-June-1983

Relation: PAYLEVEL

PAYLEVEL	PAY	ALLOWANCE
P001	26000	12000
P002	22000	10000
P003	12000	6000

(a) Write SQL commands for the following statements:

(i) To display the details of all WORKERs in descending order of DOB.

(ii) To display NAME and DESIG of those WORKERs whose PLEVEL is either P001 or P002.

(iii) To display the content of all the WORKERs table, whose DOB is in between ’19-JAN- 1984 ’and ’18-JAN-1987 ’.

(iv) To add a new row with the following: 19, ‘Daya kishore ’, ‘Operator ’, ‘P003 ’, ’19-Jun-2008 ’, ’11-Jul-1984 ’

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

(i) Select count(plevel), plevel from worker group by plevel;

(ii) Select max(dob), min(doj) from worker;

(iii) Select name, pay from worker w, paylevel p where w.plevel=p.plevel and w.ecode <13;

(iv) Select plevel, pay+allowance from paylevel where plevel=’p003 ’;

Consider the following tables GAMES and PLAYER. Write SQL commands for the statements (a) to (d) and give outputs for SQL queries (e1) to (e4)

Relation: GAMES

GCode	Game Name	Number	Prize Money	Schedule Date
101	Carom Board	2	5000	23-Jan-2004
102	Badminton	2	12000	12-Dec-2003
103	Table Tennis	4	8000	14-Feb-2004
105	Chess	2	9000	01-Jan-2004
108	Lawn Tennis	4	25000	19-Mar-2004

Relation: PLAYER

PCode	Name	Gcode
1	Nabi Ahmad	101
2	Ravi Sahai	108
3	Jatin	101
4	Nazneen	103

(a) To display the name of all Games with their Gcodes

(b) To display details of those games which are having PrizeMoney more than 7000.

(c) To display the content of the GAMES table in ascending order of ScheduleDate.

(d) To display sum of PrizeMoney for each of the Number of participation groupings (as shown in column Number)

(e1) Select COUNT(DISTINCT Number) FROM GAMES;

(e2) Select MAX(ScheduleDate),MIN(ScheduleDate) FROM GAMES;

(e3) Select SUM(PrizeMoney) FROM GAMES;

(e4) Select DISTINCT Gcode FROM PLAYER;

Write SQL commands for (a) to (f) and write the outputs for (g) on the basis of tables FURNITURE and ARRIVALS

Table: FURNITURE

NO	ITEMNAME	TYPE	DATEOFSTOCK	PRICE	DISCOUNT
1	White lotus	Double Bed	23/02/2002	30000	25
2	Pink feather	Baby cot	20/01/2002	7000	20
3	Dolphin	Baby cot	19/02/2002	9500	20
4	Decent	Office Table	01/01/2002	25000	30
5	Comfort zone	Double Bed	12/01/2002	25000	25
6	Donald	Baby cot	24/02/2002	6500	15
7	Royal Finish	Office Table	20/02/2002	18000	30
8	Royal tiger	Sofa	22/02/2002	31000	30
9	Econo sitting	Sofa	13/12/2001	9500	25
10	Eating Paradise	Dining Table	19/02/2002	11500	25

Table: ARRIVALS

NO	ITEMNAME	TYPE	DATEOFSTOCK	PRICE	DISCOUNT
1	Wood Comfort	Double Bed	23/03/2003	25000	25
2	Old Fox	Sofa	20/02/2003	17000	20
3	Micky	Baby cot	21/02/2003	7500	15

(a) To show all information about the Baby cots from the FURNITURE table.

(b) To list the ITEMNAME which are priced at more than 15000 from the FURNITURE table.

(c) To list ITEMNAME and TYPE of those items, in which date of stock is before 22/01/2002 from the FURNITURE table in descending order of ITEMNAME.

(d) To display ITEMNAME and DATEOFSTOCK of those items, in which the discount t percentage is more than 25 from FURNITURE table.

(e) To count the number of items, whose TYPE is “Sofa ”from FURNITURE table.

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

14,“Valvet touch ”, “Double bed ”, {25/03/03}, 25000,30

(g) Give the output of following SQL statement

Note: Outputs of the above mentioned queries should be based on original data given in both the tables i.e., without considering the insertion done in (f) part of this question.

(i) Select COUNT(distinct TYPE) from FURNITURE;

(ii) Select MAX(DISCOUNT) from FURNITURE, ARRIVALS;

(iii) Select AVG(DISCOUNT) from FURNITURE where TYPE=”Baby cot ”;

(iv) Select SUM(Price) from FURNITURE where DATEOFSTOCK <12/02/02;

Write SQL commands for the following queries on the basis of Club relation given below:

Relation: Club

Coach-ID	CoachName	Age	Sports	date_of_app	Pay	Sex
1	Kukreja	35	Karate	27/03/1996	1000	M
2	Ravina	34	Karate	20/01/1998	1200	F
3	Karan	34	Squash	19/02/1998	2000	M
4	Tarun	33	Basketball	01/01/1998	1500	M
5	Zubin	36	Swimming	12/01/1998	750	M
6	Ketaki	36	Swimming	24/02/1998	800	F
7	Ankita	39	Squash	20/02/1998	2200	F
8	Zareen	37	Karate	22/02/1998	1100	F
9	Kush	41	Swimming	13/01/1998	900	M
10	Shailya	37	Basketball	19/02/1998	1700	M

(a) To show all information about the swimming coaches in the club.

(b) To list the names of all coaches with their date of appointment (date_of_app) in descending order.

(c) To display a report showing coach name, pay, age, and bonus (15% of pay) for all coaches.

(d) To insert a new row in the Club table with ANY relevant data:

(e) Give the output of the following SQL statements:

(i) Select COUNT(Distinct Sports) from Club;

(ii) Select Min(Age) from Club where SEX = “F ”;

Write SQL commands for the following queries based on the relation Teacher given below:

Table: Teacher

No	Name	Age	Department	Date_of_join	Salary	Sex
1	Jugal	34	Computer	10/01/97	12000	M
2	Sharmila	31	History	24/03/98	20000	F
3	Sandeep	32	Maths	12/12/96	30000	M
4	Sangeeta	35	History	01/07/99	40000	F
5	Rakesh	42	Maths	05/09/97	25000	M
6	Shyam	50	History	27/06/98	30000	M
7	Shiv Om	44	Computer	25/02/97	21000	M
8	Shalakha	33	Maths	31/07/97	20000	F

(a) To show all information about the teacher of Computer department.

(b) To list the names of female teachers who are in Maths department.

(c) To list the names of all teachers with their date of joining in ascending order.

(d) To display teacher ’s name, salary, age for male teachers only.

(e) To count the number of teachers with Age >23.

Consider the following tables Product and Client. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii)

TABLE: PRODUCT

P_ID	Product Name	Manufacturer	Price
TP01	Talcom Powder	LAK	40
FW05	FaceWash	ABC	45
BS01	Bath Soap	ABC	55
SH06	Shampoo	XYZ	120
FW12	Face Wash	XYZ	95

TABLE: CLIENT

C_ID	ClientName	City	P_ID
01	Cosmetic Shop	Delhi	FW05
06	Total Health	Mumbai	BS01
12	Live Life	Delhi	SH06
15	Pretty Woman	Delhi	FW12
16	Dreams	Bangalore	TP01

(i) To display the details of those Clients whose City is Delhi

(ii) To display the details of Products whose Price is in the range of 50 to 100 (Both values included)

(iii) To display the ClientName, City from Table Client, and ProductName and Price from table Product, with their corresponding matching P_ID

(iv) To increase the Price of all Products by 10

(v) SELECT DISTINCT CITY FROM Client;

(vi) SELECT Manufacturer, MAX(Price), Min(Price), Count(*) FROM Product GROUP BY Manufacturer;

(vii) SELECT ClientName, ManufacturerName FROM Product, Client WHERE Client.Prod_Id = Product.P_Id;

Write the SQL query questions from (i) to (iv) and write the output of SQL command for questions from (v) to (vii) given below:

Table: EMPLOYEES

EMPID	FIRST NAME	LAST NAME	ADDRESS	CITY
010	George	Smith	83 First Street	Howard
105	Mary	Jones	842 Vine Ave.	Losantiville
152	Sam	Tones	33 Elm St.	Paris
215	Sarah	Ackerman	440 U.S. 110	Upton
244	Manila	Sengupta	24 Friends Street	New Delhi
300	Robert	Samuel	9 Fifth Cross	Washington
335	Henry	Williams	12 Moore Street	Boston
400	Rachel	Lee	121 Harrison St.	New York
441	Peter	Thompson	11 Red Road	Paris

Table: EMPSALARY

EMPID	SALARY	BENEFITS	DESIGNATION
010	75000	15000	Manager
105	65000	15000	Manager
152	80000	25000	Director
215	75000	12500	Manager
244	50000	12000	Clerk
300	45000	10000	Clerk
355	40000	10000	Clerk
4000	32000	7500	Salesman
441	28000	7500	Salesman

Write the SQL commands for the following :

(i) To show firstname, lastname, address and city of all employees living in paris.

(ii) To display the content of Employees table in ascending order of Firstname.

(iii) To display the firstname,lastname and total salary of all managers from the tables employee and empsalary, where total salary is calculated as salary+benefits.

(iv) To display the maximum salary among managers and clerks from the table Empsalary.

Give the Output of following SQL commands:

(v) Select firstname, salary from employees, empsalary where designation = ‘Salesman ’and Employees.

empid=Empsalary.empid;

(vi) Select count(distinct designation) from empsalary;

(vii) Select designation, sum(salary) from empsalary group by designation having count(*) >2;