Computer Science 5 Marks Each

Table: DEPT

Table: WORKER

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;

Table: STORE

Table: ITEM

(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):

Table: CARHUB

Table: CUSTOMER

(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;

[Delhi 2011]

Relation: WORKER

Relation: PAYLEVEL

(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:

Write SQL queries for (a) to (d) and find outputs for SQL queries (e) to (h), which are based on the tables. [CBSE Sample Paper 2017]

TABLE: WATCHES

TABLE: SALE

(a) To display all the details of those watches whose name ends with ‘time’

(b) To display watch’s name and price of those watches which have price range between 5000-15000

(c) To display total quantity in store of unisex type watches

(d) To display watch name and their quantity sold in first quarter

(e) Select max(price),min(qty_store) from watches;

(f) Select quarter, sum(qty_sold) from sale group by quarter;

(g) Select watch_name,price,type from watches w, sale s where w.watchid=s.watchid;

(h) Select watch_name, qty_store, sum(qty_sold),qty_store

Table: Trainer

Table: Course

(a) Display the Trainer Name, City and Salary in descending order of their hire date.

(b) To display the TNAME and CITY of Trainer of joined the institute in the month of December 2001.

(c) To display TNAME, HIREDATE,CNAME, STARTDATE from tables TRAINER and COURSE whose FEES is less than or equal to 10000

(d) To display number of trainer from each city (e) select tid, tname,from trainer where city not in(‘delhi’,’mumbai’);

(f) select distinct tid from course;

(g) select tid , count (*), min(fees) from course group by tid having count(*)>1;

(h) Select count(*),sum(fees) from course where startdate <’2018-09-15’;

TABLE: SALESPERSON

TABLE: ITEM

(a) To display the CODE and NAME of all salesperson having “I7” item Type Code from the table SALESPERSON.

(b) To display all details from table SALESPERSON in descending order of SALARY.

(c) To display the number of SALESPERSON dealing in each TYPE of ITEM.(Use ITCODE for the same)

(d) To display NAME of all the salesperson table along with their corresponding ITEMTYPE from the ITEM table.

(e) Select max(salary) from salesperson;

(f) Select distinct itcode from salesperson;

(g) select code,name,i.itcode from sales person s, item i where s.itcode=i.itcode and turnover>=7000000;

(h) Select sum (salary)from salesperson where itcode=”i2”;

TABLE : CUSTOMERS

TABLE : TRANSACTION

(a) To display details of all transactions of TYPE Credit from Table TRANSACTION.

(b) To display the CNO and AMOUNT of all transactions done in the month September 2017 from the table TRANSACTION.

(c) To display the last date of transaction (DOT) from the table TRANSACTION for the customer having CNO as 103.

(d) To display all CNO,CNAME and DOT (date of transaction) of those CUSTOMERS from tables CUSTOMERS and TRANSACTION who have done transactions more than or equal to 2000.

(e) Select count(*), avg(amount) from transaction where dot>= ‘2017-06-01’;

(f) Select cno, count(*), max (amount) from transaction group by cno having count(*)> 1;

(g) Select cno, cname from customer where address not in (‘Delhi, ‘Bangalore’);

(h) Select distinct cno from transaction;

Table: STUDENT

Write SQL commands for the following queries

(a) To show all information about the students of History department.

(b) To list the names of female students who are in Hindi department.

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

(d) To display student’s name, fee, age for male students only.

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

Table: Student

Table : Stream

Write SQL commands for the statements (a) to (h) on the table: Student and Stream

(a) Display class and total fee collected from each class.

(b) Display admission no, students name and stream name.

(c) Display all the student details who have taken Commerce Stream.

(d) Count number of students who have opted for HUMANITIES stream.

(e) Display information of commerce with ip students whose name start with ‘S’. Arrange the record by admission number.

(f) Display details of all students who are in the MEDICAL stream.

(g) Display total fee of ‘Non-Medical’ Student.

(h) Change the name of the Column Sname to Student_Name.

Table: Student

Table : Stream

Write SQL commands for the statements (a) to (h) on the above table: Student and Stream

(a) Identify Primary Keys and Foreign Key in the table student and primary key in Stream table given above.

(b) Display stream id and stream-wise total fee collected.

(c) Count no of students from each area.

(d) Display all the student details those who belongs to Madipur Area.

(e) Increase the fees of all students by10%.

(f) Display unique area from the student table.

(g) Display details of those students whose area contains ‘Puri’.

(h) Display the information of those students who are in class XII and section is either B or C.

Table: FURNITURE

Table: ARRIVALS

(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

(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”;

Table: Teacher

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

TABLE: PRODUCT

TABLE: CLIENT

(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;