Annual Examination Revision Worksheet-6

DATABASE MANAGEMENT/STRUCTURED QUERY LANGUAGE (SQL) 15M

Fill in the blanks/True or False/Assertion and Reasoning/MCQ (1 Markers)

1. In SQL, which type of Join(s) produces output where cardinality of the final table is the multiplication of cardinalities of
both the tables (which are being joined)

2. Consider the given SQL Query:

SELECT CLASS, COUNT(*) FROM employees HAVING COUNT(*) > 5 GROUP BY CLASS WHERE CLASS IN (9,10,11,12);

Rabina is executing the query but not getting the correct output. Write the correction.

3. Which SQL command can change the Degree of an existing relation?

a) Alter b) Update c) Both a) & b) d) Degree Can’t be changed

4. In SQL, a relation consists of 6 columns and 6 rows. If 3 columns and 3 rows are added to the existing relation, what will
be the updated Cardinality of a relation?

a) 7 b) 8 c) 9 d) 6

5. Which SQL command will NEVER change the cardinality or Degree of a table?

a) UPDATE b) ALTER c) INSERT d) DELETE

6. In SQL, which operator is used to check if a value is within a range of values (inclusive)?

a) In b) Between c) Range d) Like

7. Consider the given SQL Query:

SELECT class, count(studentname) FROM student where AVG(age) > 10 GROUP BY class;

Hemant is executing the query but not getting the correct output. Write the correction.

8. Which SQL command DOES NOT affect the Cardinality of an existing relation?

a) Insert b) Delete c) Update d) Drop

9. In SQL, which of the following commands will delete a table permanently:

a) remove b) del c) drop d) delete

10. Which of the following is used along with Alter table statement to remove a column from a table?

a) Remove b) Delete c) Truncate d) Drop

11. Which SQL command can change the cardinality of a relation?

a) Alter table b) Insert c) drop d) update

12. Which command removes a table completely in SQL?

a) Truncate b) Delete c) Drop d) Alter

13. Which of the following is a DQL statement?

a) Insert b) Select c) Drop d) Delete

(A) Both A and R are true and R is the correct explanation of A

(B) Both A and R are true and R is not the correct explanation of A

(C) A is true but R is false

(D) A is false but R is true

1. Assertion (A): A foreign key can have duplicate values and can also be NULL.

Reason (R): A foreign key establishes a link between two tables, referring to the primary key of another table.

2. Assertion (A): A table can have multiple candidate keys, but only one primary key.

Reason (R): All candidate keys that are not chosen as the primary key are called alternate keys.

3. Assertion (A): Primary key is a candidate key

Reason (R): Candidate keys are the keys that uniquely identifies a row in the table.

4.

Subjective Questions (2, 3, 4, 5 Markers)

1. Write suitable commands to do the following in MySQL.

I. Select the Database(school_db) to work upon it.

II. Add a column ‘ID’ which will be the primary key of the table(students) Assume: The Table does not have a primary key.

2. Differentiate between Update and Alter query in SQL with a suitable example.

3. Consider the following table ORDERS:

Write SQL queries for the following:

I. To display the details of all orders placed from Delhi in descending order of amount.

II. To count the distinct product names from the ORDERS table.

III. To display the total quantity ordered for each product in Delhi

IV. To display product names and their total quantities ordered only for those products where total quantity exceeds 10

4. Consider the following table ORDERS:

I. Select customername, city from orders where customername like 'a%';

II. Select city, count(*) from orders group by city order by city;

III. Select product, sum(quantity) from orders group by product having sum(quantity) > 5;

IV. Select city, count(orderid) from orders group by city having count(orderid) >= 2

5. Afreen is managing a company and needs to access certain information from the table Departments and Employee for
an upcoming survey. Help her to extract the required information by writing the appropriate SQL queries as per the tasks
mentioned below:

I. To display the names of employees working in departments located in ‘Delhi’.

II. To display the department name, employee name and salary for employees working in ‘Mumbai’ or ‘Kolkata’.

III. To delete all employee records who joined before ‘2023-04-01’ with salary less than 65000

IV. A. To display the natural join for the two tables.

V. To display the cartesian product for the two tables.

6. Write suitable commands to do the following in MySQL.

I. Create a database named CBSE II. Show schema of table KVS

7. Differentiate between unique and distinct in SQL with a suitable example.

Write the following queries:

(i) Display NAME of all doctors whose name contains ‘A’ and have more than 10 years of experience.

(ii) Display the No. of Doctors in each department.

(iii) Display the name of doctors in ascending order.

(iv) Display all information of female doctors having experience less than 5 years.

9. Consider the table Doctor as given below:

Predict the output of the following:

I. SELECT * FROM Doctor where code> 105 ;

II. SELECT Name, Code FROM Doctor WHERE dept in (‘ent’,’caridology’,’skin’,’medicine’);

III. SELECT COUNT(*) FROM DOCTOR WHERE GENDER =’F’;

IV. SELECT AVG(Experience) FROM DOCTOR group by dept;

Write SQL queries for the following:

(i) Display customer names with the hotel names in which they stayed from tables HOTELS and BOOKINGS

(ii) Display the name of customers who availed hotel facility during the year 2023

(iii) Display the average rating of hotels

(iv) Display the hotel name, city, and rating in descending order of rating.

(v) Display the hotel names of Delhi, Mumbai or Kolkata.

11. Write difference between Char and Varchar datatypes in SQL.

12. Differentiate between Count(columnname) and count(distinct columname) with example.

13. Consider the following table “Courses”

Write SQL statements to:

i. Display the names of courses and their IDs which has fee under 15000.

ii. Display the details of courses which started after year 2020.

iii. List the courses (course names) starting with letter D

iv. List the names of courses along with Fees and applicable discount amount as 10% of the fees for the courses having
fees more than 12000.
14. Consider the following table “Courses”

Write result/output of the execution of following statements:

i. SELECT DISTINCT TID FROM COURSES;

ii. SELECT CID, CNAME FROM COURSES WHERE TID IS NULL OR FEES < 10000;

iii. SELECT CNAME, FEES FROM COURSES ORDER BY FEES DESC;

iv. Delete from courses; (Write what will this statement result)

15. Consider following tables Students and Teachers:

Write SQL statements for following (option d has internal choice):

a. Find out the number of students in each stream.

b. Find out the average stipend given to Commerce Stream.

c. Display the names of students taught by a teacher from Delhi

d. Display the number of students taught by each teacher

e. Find out the number of streams available using students table.