Sr.

Questions(Unit 3)
No.
1 Consider the following database: Student (Roll_no, Name, Address), Subject
(Sub_code, Sub_name), Marks (Roll_no, Sub_code, Marks). Write SQL queries for
the following: (i) Find the average marks of each student along with their Roll_no
for subject code ‘CE2412’. (ii) Find how many students have failed in the subject
“DBMS”. (iii) Construct a suitable view on the given schema.
2 Explain the ON DELETE CASCADE command with a suitable example.
3 What are the different types of joins in SQL? Explain with suitable examples.
4 Explain SQL aggregate functions with suitable examples.
5 Write the syntax for the following SQL commands: (i) CREATE TABLE (ii)
ALTER TABLE (iii) DROP TABLE (iv) INSERT (v) DELETE (vi) UPDATE.
6 Write and explain SQL functions and procedures with a sample example.
7 Describe the circumstances under which you would choose to use Embedded SQL
rather than normal SQL or only a general-purpose programming language.
8 Differentiate between: (i) Trigger & Stored Procedure (ii) Embedded SQL &
Dynamic SQL.
9 Consider the following relations: Departments (Department_id, Department_name,
Manager_id, Location_id), Employees (Employee_id, Employee_name,
Mobile_number, Hire_Date, Job_Id, Salary, Commission_PCT, Manager_Id,
Department_Id). Write SQL queries for the following: (i) Show the name, job,
salary, and commission of employees who earn commission. Sort the data by salary
in descending order. (ii) Show the department names, locations, employee names,
job titles, and salaries of employees who work in location 1800. (iii) Find the
number of employees whose names end with an “n”. (iv) Show the department
names, locations, and the number of employees working in each department. Ensure
departments without employees are also included.
10 Consider the relational database: Supplier (sid, sname, address), Parts (pid, pname,
color), Catalog (sid, pid, cost). Write SQL queries for the following: (i) Find the
name of suppliers who supply red parts. (ii) Find the names of all parts whose cost
is more than Rs. 250. (iii) Find the names of all parts whose color is green. (iv) Find
the names of all parts supplied by each supplier.
11 Explain the need for the following: (i) View (ii) Null values.
12 Write a note on: (i) Set operations in SQL (ii) Aggregate Functions in SQL.
13 Explain in detail, with syntax, Stored Procedures and Triggers.
14 Write the syntax for the following SQL commands: (i) CREATE TABLE (ii)
ALTER TABLE (iii) DROP TABLE (iv) INSERT (v) UPDATE (vi) DELETE.
15 What is a View? List two major problems with processing update operations in
terms of views.
Sr. Questions(Unit No 4)
No.
1 What are the measures of query cost?
2 Explain 1st, 2nd, and 3rd normal forms with examples.
3 What do you mean by normalization? Explain different anomalies.
4 Compare BCNF and 3NF.
5 What are the different ways of evaluating expressions? Explain any one with an
example.
6 Define functional dependency. List various types of functional dependency.
Explain any one type.
7 Explain with an example Materialized Evaluation and Pipelining.
8 Consider the following relational table. Find nontrivial and trivial functional
dependencies: ​
ABC ​
a1 b1 c1 ​
a1 b1 c2 ​
a2 b1 c1 ​
a2 b1 c3
9 List the desirable properties of decomposition. Explain lossless join with an
example.
10 Consider the following Book relation: ​
Book (Book_id, Title, Author, Publisher, Year, Price) ​
Write relational algebra expressions for: ​
i) Display all book titles with authors and price. ​
ii) Display the titles of books having a price greater than 300. ​
iii) Display books published in the year 2000. ​
iv) Display all books published by ‘PHP’ with a price greater than 300.
11 What are the measures of query cost?
12 Define query processing. What are the steps involved in query processing?
13 Compute the closure of the following set F of functional dependencies for
relation schema R = (A, B, C, D, E): ​
A → BC, CD → E, B → D, E → A ​
List the candidate keys for R.
14 State and explain Armstrong’s axioms and their properties.
15 Explain the difference between 4NF & BCNF.
16 Describe the concept of transitive dependency. Explain how this concept is used
to define 3NF.
17 Explain with an example Materialized Evaluation and Pipelining.
18 Suppose that we decompose the schema R = (A, B, C, D, E) into: ​
(A, B, C) ​
(A, D, E) ​
Show that this decomposition is a lossless-join decomposition if the following
set F of functional dependencies holds: ​
A → BC, CD → E, B → D, E → A