CBSE Class 12 Computer Science HOTs Sql 6 Marks Questions

1. Write SQL commands for (a) to ( j) and write output for (h) on the basis of Teacher relation given below.

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

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

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

d) To display students name, fee,age for male teacher only

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

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

9,”Raja”,26,”Computer”,13/05/95,2300,”M”.

g) To show all information about the teachers in this table

h) Add a new column named “Address”.

i) Arrange the whole table in the alphabetical order to name

j) Display the age of the teachers whose name starts with ‘S,.

k) Give the output of following statement.

(j) Select COUNT(distinct department) from TEACHER.

(ii) Select MAX(Age)from Teacher where sex=”F”

(iii) Select AVG(Salary) from Teacher where Dateofjoin<12/07/96

(iv) Select SUM(Salary) from teacher where Dateofjoin<12/07/96

(a) Display names of employees whose names include either of the substring “TH” or “LL”.

(b) Display data of all employees sorted by their department, seniority and salary.

(c) Find all the employees who have no manager.

(d) To display all employees who were hired during 1995.

(e) Show the average salary for all departments with more than 3 people for a job.

(f) Find out number of employees having ‘MANAGER’ as job.

(g) Create view DEPT20 with name and the salary of employees for dept 20.

(h) Display department no. and number of employees in each department.

(i) Find the output of the following :

     1. SELECT SYSDATE FROM DUAL;

     2. SELECT ENAME,SAL FROM EMPLOYEE WHERE DEPTNO=20;

     3. SELECT COUNT(*) FROM EMP;

     4. SELECT AVG(SAL) FROM EMP;

3. Consider the following WORKERS and DESIG. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii)

WORKERS

(i) To display W_ID, FIRSTNAME, ADDRESS and CITY of all employees living in NEW YORK from the table WORKERS.

(ii) To display the content of workers table in ascending order of LASTNAME.

(iii) To display the FIRSTNAME, LASTNAME and total salary of all alerks from the

tables WORKERS and DESIGN, where total salary is calculated as SALARY + BENEFITS.

(iv) To display the minimum salary among Managers and Clerks from the table DESIG.

(v) Give the output of following: 

a. SELECT FIRSTNAME, SALARY FROM WORKERS, DESIG WHERE

DESIGNATION = ‘Manager’ AND WORKERS.W_ID = DESIG.W_ID

b. SELECT COUNT(DISTINCT DESIGNATION) FROM DESIG.

c. SELECT DESIGNATION, SUM(SALARY FROM DESIGNATION GROUP BY

DESIGNATION HAVING COUNT(*)<3;

SELECT SUM(BENEFITS) FROM WORKERS WHERE DESIGNATION = ‘Salesman’;

4. Write SQL commands for the statements (i) to (vi) on the basis of the table EMPLOYEE:

i. Show sum and average salary for marketing deptt.

ii. Check all employees have unique names.

iii. Find al employess whose deptt is same as of ‘Amit’.

iv. Increase the salary of all employees by 10%,

v. Find the deptt that is paying max salaries to its employees.

vi. Display the details of all the employees having salary less than 10000.

5. Write the SQL commands for (a) to (d) and write the output for (e) on the basis of table Hospital :

(a) To show all information about the patients of cardiology department.

(b) To list the name of female patients who are in ENT department.

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

(d) To count the no of patients with age > 20.

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

(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/05/06};

6. Write the SQL commands for the following on the basis of tables INTERIORS and NEWONES

(i) To list the ITEMNAME which are priced at more than 1000 from the INTERIORS table

(ii) To list ITEMNAME and TYPE of those items, in which DATEOFSTOCK is before

22/01/02 from the INTERIORS table in descending order of ITEMNAME

(iii) To show all information about the sofas from the INTERIORS table

(iv) To display ITEMNAME and DATEOF STOCK of those items in which the discount percentage is more than 15 from INTERIORS table

(v) To count the number of items, whose type is “Double Bed” from INTERIORS table

(vi) To insert a new row in the NEWONES table with the following data

14,”True Indian”, “Office Table”, 28/03/03,15000,20

c) Get the Output (Use the above table without inserting the record)

Select COUNT(distinct TYPE) from INTERIORS

Select AVE(DISCOUNT) from INTERIORS where TYPE=”Baby Cot”

Select SUM(Price) from INTERIORS where DATEOF STOCK<{12/02/02}

Select MAX(Price) from INTERIORS , NEWONES;

7. Given the following tables for a database FURNITURE : Note: Write SQL command for (a ) to (d) and write the outputs for (e) on the basis of tables FURNITURE and ARRIVALS.

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

(b) To list ITEMNAME and TYPE of those items, in which DATEOFSTOCK is before

22/01/02 from the FURNITURE table in descending order of ITEMNAME.

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

(d) To count the number of items, whose TYPE is ‘Sofa’ from FURNITURE table.

(e) Give the output of fol1owing SQL statement:

Note: Outputs of the below mentioned queries should be based on original data given in both the tables .

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

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

(iil) Select AVG(DISCOUNT) from FURNITURE where TYPE = ‘Baby cot’ ;

(iv) Select SUM(PRICE) from FURNITURE where DATEOFSTOCK < {12/02/02}

8. Given the following LAB table, write SQL command for the questions (i) to (iii) and give the output of (iv).

(i) To select the ItemName,which are within the Warranty period till present date.

(ii) To display all the itemName whose name starts with “C”.

(iii) To list the ItemName in ascending order of the date of purchase Where quantity is more than 3.

(iv) Give the output of the following SQL commands:

     (a) select min(DISTINCT Quantity) from LAB;

     (b) select max(Warranty) from LAB; 

    (C) select sum(CostPerItem) from Lab;

(a) To show all information of students where capacity is more than the no of student in order of rtno.

(b) To show area_covered for buses covering more than 20 km., but charges less then 80000.

(c) To show transporter wise total no. of students traveling.

(d) To show rtno, area_covered and average cost per student for all routes where average cost per student is - charges/noofstudents.

(e) Add a new record with following data:

(11, “ Moti bagh”,35,32,10,” kisan tours “, 35000)

(f) Give the output considering the original relation as given:

     (i) select sum(distance) from schoolbus where transporter= “ Yadav travels”;

     (ii) select min(noofstudents) from schoolbus;

     (iii) select avg(charges) from schoolbus where transporter= “ Anand travels”;

     (v) select distinct transporter from schoolbus;

(a) List the names of those students who have obtained DIV 1 sorted by NAME.

(b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year assuming that the STIPEND is paid every month.

(c) To count the number of students who are either PHYSICS or COMPUTER SC graduates.

(d) To insert a new row in the GRADUATE table:

11,”KAJOL”, 300, “computer sc”, 75, 1

(e) Give the output of following sql statement based on table GRADUATE:

i. Select MIN(AVERAGE) from GRADUATE where SUBJECT=”PHYSICS”;

ii. Select SUM(STIPEND) from GRADUATE WHERE div=2;

iii. Select AVG(STIPEND) from GRADUATE where AVERAGE>=65;

iv. Select COUNT(distinct SUBDJECT) from GRADUATE;

Assume that there is one more table GUIDE in the database as shown below:

g) What will be the output of the following query:

SELECT NAME, ADVISOR FROM GRADUATE,GUIDE WHERE SUBJECT= MAINAREA;

11. Write SQL command for (a) to (g) on the basis of the table SPORTS

(a) Display the names of the students who have grade ‘C’ in either Game1 or Game2 or both.

(b) Display the number of students getting grade ‘A’ in 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 ‘A’.

(e) Assign a value 200 for Marks for all those who are getting grade ‘B’ or grade ‘A’ n both Game1 and Game2.

(f) Arrange the whole table in the alphabetical order of Name.

(g) Add a new column named ‘Marks’.

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

(v) Give the Output of following SQL commands:

(i) Select firstname,salary from employees ,empsalary where designation = ‘Salesman’

and Employees.empid=Empsalary.empid;

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

(iii) Select designation, sum(salary) from empsalary group by designation having count(*)

>2;

(iv) Select sum(benefits) from empsalary where designation =’Clerk’;

13 Write SQL commands for the following taking in view the following table as SCHOOL:

No.      Name       Age       Department     Sex

1         Ankit        45         Comp.Sc.        M

2         Sumit      32          History           M

3         Amit        22          Geog             M

4        Suchitra    23          Maths            F

5       Ankita        22           Hindi            F

6        Shruti       21          Comp.Sc.      F

7        Raksha     22            Hindi           F

8        Priya        33            Maths           F

b. To show all information about the members of the Hindi Department.

c. To list the names of female members who are in Hindi department.

d. To list names of all members with the ascending order of their ages.

e. To display member”s name, age and department name of males.

f. To count the number of members with age >21.

g. To insert a new row in the SCHOOL table with the following data :

9, “Pinto”,31,”Maths”,”M”

h. Give the output of the following, considering the above table :

    a. SELECT COUNT (DISTINCT AGE) FROM SCHOOL;

    b. SELECT MAX(AGE) FROM SCHOOL WHERE SEX = “M”;

    c. SELECT AVG(AGE) FROM SCHOOL WHERE SEX =”M”;

    d. SELECT SUM(AGE) FROM SCHOOL WHERE SEX=”M”;

14. Write SQL command for (a) to (g) on the basis of the table GAMES

(a) To Display the details of all games with their codes.

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

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

(d) TO Display sum of PrizeMoney for each of the number of participation grouping (as shown in column number 3.

(e) SELECT COUNT (DISTINCT Number) FROM GAMES;

(f) SELECT MAX (ScheduleDate), MIN (ScheduleDate) FROM GAMES;

(g) SELEC SUM (PrizeMoney) FROM GAMES;

(h) SELECT DISTINCE Gcode FROM PLAYER;