3 MARKS (OR) 4 MARKS

1. Rashmi has forgotten the names of the databases, tables and the
structure of the tables that she had created in Relational Database
Management System (RDBMS) on her computer.
(a) Write the SQL statement to display the names of all the
databases present in RDBMS application on her
computer.
(b) Write the statement which she should execute to open
the database named "STOCK".
(c) Write the statement which she should execute to
display the structure of the table "ITEMS" existing in the
above opened database "STOCK".
2. Consider the table HOTEL given below and write any four SQL
commands :
Table : HOTEL

(i) Display the details of all the Hotels situated in London.

(ii) Display the details of all 'Deluxe' rooms with price
more than 6000 in ascending order of Price.
(iii) Display the Hotel names that end with ''e''.
(iv) Count different types of rooms available in the Hotels.
(v) Display the Hotel names in descending order.
3. Write SQL statements for the following queries (i) to (v) based
on the relations CUSTOMER and TRANSACTION given below :

APPAN RAJ D PGT- CS/IP 159

 (a) To display all information about the CUSTOMERs
whose NAME starts with 'A'.
(b) To display the NAME and BALANCE of Female
CUSTOMERs (with GENDER as 'F') whose
TRANSACTION Date (TDATE) is in the year 2019.
(c) To display the total number of CUSTOMERs for each
GENDER.
(d) (i) To display the CUSTOMER NAME and BALANCE in
ascending order of GENDER.
(ii) To display CUSTOMER NAME and their respective
INTEREST for all CUSTOMERs where INTEREST is
calculated as 8% of BALANCE.
4. (a) Ram wants to find the sum of commission earned by
each department. He has executed the following query :
SELECT dept,sum(comm) GROUP BY dept
FROM EMP;
But, he got an error. Rewrite the correct query after
identifying the error(s).
(b) Consider the following Table : ITEM

Find the output of the following SQL queries :

(a) SELECT 10+ QTY FROM ITEM WHERE ID = "P1003";
(b) SELECT PRICE*QTY FROM ITEM WHERE QTY < 2;
5. Consider the Table FURNITURE with the following data:

Write SQL queries for the following :

(a) To Display all the records in descending order of Item.
(b) To Display the Type and total number of items of each
Type.
(c) To Display the highest Price.
(d) To Display the Item with their price rounded to 1
decimal place.

APPAN RAJ D PGT- CS/IP 160

 6. Write the output of the queries (i) to (iv) based on the table,
TECH_COURSE given below:

(i) SELECT
DISTINCT TID FROM TECH_COURSE;
(ii) SELECT TID, COUNT(*), MIN(FEES) FROM
TECH_COURSE GROUP BY TID HAVING
COUNT(TID)>1;
(iii) SELECT CNAME FROM TECH_COURSE WHERE
FEES>15000 ORDER BY CNAME;
(iv) SELECT AVG(FEES) FROM TECH_COURSE WHERE
FEES BETWEEN 15000 AND 17000;
7. Write the outputs of the SQL queries (i) to (iv) based on the
relations Teacher and Placement given below:

(i) SELECT Department, avg(salary) FROM Teacher GROUP

BY Department;
(ii) SELECT MAX(Date_of_Join), MIN(Date_of_Join) FROM
TEACHER;
(iii) SELECT Name, Salary, T. Department, Place FROM
Teacher T, Placement P WHERE T.Department =
P.Department AND Salary>20000;
(iv) SELECT Name, Place FROM Teacher T, Placement P

APPAN RAJ D PGT- CS/IP 161

 WHERE Gender =’F’ AND T.Department=P.Department;
8. Write the output of the queries (a) to (d) based on the table,
Furniture given below:

(a) SELECT SUM(DISCOUNT) FROM FURNITURE WHERE

COST>15000;
(b) SELECT MAX(DATEOFPURCHASE) FROM FURNITURE;
(c) SELECT * FROM FURNITURE WHERE DISCOUNT>5
AND FID LIKE "T%";

(d) SELECT DATEOFPURCHASE FROM FURNITURE;

WHERE NAME IN ("Dining Table", "Console Table");
9. Write queries (a) to (d) based on the tables EMPLOYEE and
DEPARTMENT given below:

(a) To display the average

salary of all employees,
department wise.
(b) To display name and respective department name of
each employee whose salary is more than 50000.

APPAN RAJ D PGT- CS/IP 162

 (c) To display the names of employees whose salary is not
known, in alphabetical order.
(d) To display DEPTID from the table EMPLOYEE without
repetition.
10. Consider the following tables– EMPLOYEES AND DEPARTMENT:
What will be the output of the
following statement?

SELECT ENAME, DNAME

FROM EMPLOYEES E,
DEPARTMENT D WHERE
E.DNO=D.DNO;

11. Write the output of SQL queries (a) and (b) based on the follwing
two tables DOCTOR and PATIENT belonging to the same
database:

(a) SELECT DNAME,PNAME FROM DOCTOR NATURAL

JOIN PATIENT;
(b) SELECT PNAME, ADMDATE, FEES FROM PATIENT P,
DOCTOR D WHERE D.DNO=P.DNO AND FEES>1000;

APPAN RAJ D PGT- CS/IP 163

 12. Consider the following two tables:

What will be the degree and

cardinality of the Cartesian
product and the Natural join
of these tables?

13. Consider the following tables FACULTY and STUDENT. Write the
output for the MYSQL statement given below and state what type
of join is implemented.
SELECT * FROM FACULTY,STUDENT

14. HARSH AGARWAL has created a table named 'Actor' which

contains a field called Aname.
Write MySQL queries for the followings:
(i) To show all the names of the actors which contain the
string 'ch' in it.
(ii) To display all the names of the actors which contain exactly 5
characters and also the second characters is 'o' (such as
Gopal or Mohan).
15. Consider the following tables –
LOAN and BORROWER:

How many rows and columns

will be there in the natural
join of these two tables?

APPAN RAJ D PGT- CS/IP 164

 16. Consider the following tables MUSIC and DANCE:

Identify the degree and cardinality of:

a) Union operation on MUSIC and DANCE.
b) Intersection operation on MUSIC and DANCE.
17. Consider the SQL table "PRODUCT_SALES" with the following
data:

Predict the output of the following queries based on the table

"PRODUCT_SALES" given above:
1. SELECT (SalesQ2 - SalesQ1) / 2 AS Sale2 FROM
PRODUCT_SALES WHERE Segment = 'High';
2.SELECT SUM(SalesQ1) AS "TOTAL” FROM
PRODUCT_SALES WHERE Region = 'B';
18. (a) Consider the table, BOOK and MEMBER given below:

What will be the output of the following statement?

SELECT * FROM BOOK NATURAL JOIN MEMBER;

APPAN RAJ D PGT- CS/IP 165

 (b) Write the output of the queries (i) to (iv) based on
the table.
Table: Employee

i SELECT NAME, PROJECT FROM EMPLOYEE ORDER

BY NAME DESC;
ii SELECT NAME, SALARY FROM EMPLOYEE WHERE
NAME LIKE 'A%';

iii SELECT NAME, DOJ FROM EMPLOYEE WHERE

SALARY BETWEEN 100000 AND 200000;
iv SELECT * FROM EMPLOYEE WHERE PROJECT = 'P01';
19. Write SQL Queries for (a) to (d) based on the tables
PASSENGER and FLIGHT
given below:

(a) Write a query to change the fare to 6000 of the flight whose
FNO is F104.
(b) Write a query to display the total number of MALE and
FEMALE passengers.
(c) Write a query to display the NAME, corresponding FARE and
F_DATE of all passengers who have to START from DELHI.
(d) Write a query to delete the records of flights which end at end
MUMBAI.

APPAN RAJ D PGT- CS/IP 166

 20. Write the SQL Queries for the following tables:

(i) To display department name and number of employees

in each department where no of employees is greater
than one.
(ii) To display department name and sum of the salary
spent by the department, where the total amount spent
by the department as salary is more than 100000.
(iii) To display the name of the employee in descending
order of their seniority.
21. Write the outputs of the SQL queries

(i) SELECT DEPARTMENT, MAX(SALARY) FROM TEACHER

GROUP BY DEPARTMENT;
(ii) SELECT MAX(DATE_OF_JOIN),MIN(DATE_OF_JOIN)
FROM TEACHER;

APPAN RAJ D PGT- CS/IP 167

 (iii) SELECT NAME, SALARY, T.DEPARTMENT, PLACE FROM
TEACHER T, PLACEMENT P WHERE T.DEPARTMENT =
P.DEPARTMENT AND P.DEPARTMENT='HISTORY';
(iv) SELECT NAME, PLACE FROM TEACHER NATURAL JOIN
PLACEMENT WHERE GENDER='F';
22. (a) Consider the following table structure:

Write a SQL query to remove unique constraints from the table.

(b) Consider the following table:

(i) SELECT SNAME, STREAM FROM XII_A HAVING STREAM

LIKE '%B%';
(ii) SELECT STREAM, COUNT (*) FROM XII_A GROUP BY
STREAM HAVING COUNT(STREAM)<=1;
(iii) SELECT AGE, STREAM FROM XII_A WHERE AGE
BETWEEN 15 AND 15 ORDER BY SNAME;
(iv) SELECT ROLLNO, STREAM FROM XII_A WHERE STREAM
LIKE "P%B" AND STREAM <>"BS";

APPAN RAJ D PGT- CS/IP 168

 23. Write the output (i-iii) for the following SQL commands

(i) SELECT MAX(DOB) FROM PARTICIPANTS;

(ii) SELECT DISTINCT EVENT FROM PARTICIPANTS;
(iii) SELECT COUNT(DISTINCT(CLASS)) FROM PARTICIPANTS;
(iv) SELECT MAX(DOB), PNO FROM PARTICIPANTS GROUP BY
PNO HAVING COUNT(*)>1
24. Using the table of Q.No 25 Trainer and Course Write the output
for the following SQL Queries:
(i) SELECT TID, TNAME, FROM TRAINER WHERE CITY
NOT IN(‘DELHI’, ‘MUMBAI’);
(ii) SELECT DISTINCT TID FROM COURSE;
(iii) SELECT TID, COUNT(*), MIN(FEES) FROM COURSE
GROUP BY TID HAVING COUNT(*)>1;
(iv) SELECT COUNT(*), SUM(FEES) FROM COURSE
WHERE STARTDATE< ‘2018-09-15’;
25. Write the output of the queries (i) to (vi) based on the table given
below:

APPAN RAJ D PGT- CS/IP 169

 (i) SELECT BRAND_NAME, FLAVOUR FROM CHIPS WHERE
PRICE <> 10;
(ii) SELECT * FROM CHIPS WHERE FLAVOUR="TOMATO" AND
PRICE > 20;
(iii) SELECT BRAND_NAME FROM CHIPS WHERE PRICE > 15
AND QUANTITY < 15;
(iv) SELECT COUNT( DISTINCT (BRAND_NAME)) FROM CHIPS;
(v) SELECT PRICE , PRICE *1.5 FROM CHIPS WHERE
FLAVOUR = "PUDINA";
(vi) SELECT DISTINCT (BRAND_NAME) FROM CHIPS ORDER BY
BRAND_NAME DESC;
26. Write SQL statements for the q.no (i) to (iv) and output for (v)

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 tablesEmployee 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) To display the average salary of Clerk.

APPAN RAJ D PGT- CS/IP 170

 27. (i) Write a Query to insert House_Name=Tulip,
House_Captain= Rajesh andHouse_Point=278 into
table House(House_Name, House_Captain,
House_Points)
(ii) Write the output for SQL queries (i) to (iv), which are
based on the table: STUDENTgiven below:

(i) SELECT COUNT(*), City FROM STUDENT GROUP BY

CITY HAVING COUNT(*)>1;
(ii) SELECT MAX(DOB),MIN(DOB) FROM STUDENT;
(iii) SELECT NAME,GENDER FROM STUDENT WHERE
CITY="Delhi";
(iv) SELECT DISTINCT Class FROM STUDENT;
28. Write the outputs of the
SQL queries (i) to (iv)
based on the
relations student and
sports given below:

(i) SELECT ROLL_NO,AGE,GNAME FROM STUDENT ST,SPORTS

SP WHERE ST.ROLL_NO=SP.ROLL_NO AND GNAME
LIKE ‘_R%’;

APPAN RAJ D PGT- CS/IP 171

 (ii) SELECT AGE,GENDER FROM STUDENT WHERE DOB
IS NOT NULL AND AGE>15;
(iii) SELECT SNAME,GENDER FROM STUDENT WHERE AGE
NOT IN(12,22);
(iv) SELECT GENDER,AVG(TOTAL) FROM STUDENT WHERE
GENDER IN(‘M’,’F’) GROUP BY GENDER;
29.

(i) Display the SurNames, FirstNames and Cities of people

residing in Udhamwara city.
(ii) Display the Person Ids (PID), cities and Pincodes of
persons in descending order of Pincodes.
(iii) Display the First Names and cities of all the females getting
Basic salaries above 40000.
(iv) Display First Names and Basic Salaries of all the persons
whose firstnames starts with "G".
30. Write the output for the
queries (i) to (iv) based on the
table given below:

(i) SELECT MAX(FEES),MIN(FEES) FROM SPORTS;

(ii) SELECT COUNT(DISTINCT SNAME) FROM SPORTS;
(iii) SELECT SNAME, SUM(No_of_Players) FROM SPORTS
GROUP BY SNAME;
(iv) SELECT AVG(FEES*No_of_Players) FROM SPORTS WHERE
SNAME=”Basket Ball”;

APPAN RAJ D PGT- CS/IP 172

 31. Consider the following tables Write SQL commands for the
following statements.

(i) Display NAME of all doctors who are in "ORTHOPEDIC"

having more than 10 years experience from the table
DOCTOR.
(ii) Display the average salary of all doctors working in
"ENT" department using the DOCTOR and SALARY.
(Salary= Basic + Allowance)
(iii) Display the minimum ALLOWANCE of female doctors.
(iv) Display the highest consultation fee amount for all
male doctors.

**********************************************************

APPAN RAJ D PGT- CS/IP 173