24AD2103-DBMS-CO2-Syllabus
Introduction to SQL, Data Types, SQL BuildingBlocks, Referential integrity Constraints (Keys),
DDL/DML/DQL/DCL/TCL commands, Where Clause, Operations,Joins, Views, Indexes and Types of
Indexes, Relational Algebra: Relational Algebra Operators, Selection Operator,Projection Operator and
Set Theory Operators. Database Normalization: Key Attributes: Study of Super, Candidate,Closure of an
Attribute Set, Steps to find closure, Finding Candidate Keys of Given Relation. Database
Anomaly:Insertion, Deletion and Updation Anomalies with Examples. Guidelines for better Design of a
Relational Database:Concept of Functional Dependency, Types of Functional Dependencies.
Short Answer Questions (2 Marks) List
1) What is SQL?
2) Name any four data types in SQL.
3) What are the building blocks of SQL?
4) Define Primary Key and Foreign Key.
5) Differentiate between DDL and DML.
6) What is the use of the WHERE clause in SQL?
7) Define Referential Integrity.
8) Write the syntax of a simple SQL SELECT query.
9) What is a View in SQL?
10) List any two types of indexes in SQL.
11) What is a Join? List different types of joins.
12) Define the Selection operator in Relational Algebra.
13) What is Projection in Relational Algebra?
14) Define Functional Dependency.
15) Define a Super Key.
16) What is an insertion anomaly?
17) What is the closure of an attribute set?
18) Define Candidate Key.
19) What is the purpose of normalization?
20) Write two examples of set operators in Relational Algebra.
Page 1 of 4
Medium Answer Questions (4 Marks) List
1) Explain various types of SQL commands: DDL, DML, DQL, DCL, and TCL with examples.
2) Describe different types of SQL Joins with syntax and examples.
3) Explain Referential Integrity Constraints with examples.
4) Explain the concept of Views and their advantages.
5) Write a SQL query using WHERE, ORDER BY, and aggregate functions.
6) Differentiate between clustered and non-clustered indexes.
7) Explain Relational Algebra Selection and Projection operators with examples.
8) Discuss different types of Functional Dependencies with examples.
9) Define and illustrate Insertion, Deletion, and Update Anomalies with suitable examples.
10) Explain the steps to find the closure of an attribute set.
11) How do you find candidate keys in a given relation?
12) What are the guidelines for better relational database design?
Essay Questions (6 Marks) List
1) Explain the different types of SQL commands (DDL, DML, DQL, DCL, TCL) with suitable
examples for each.
2) Discuss all types of Joins in SQL in detail with diagrams and examples.
3) Explain Relational Algebra Operators in detail with examples.
4) Describe the types of Functional Dependencies and their role in normalization.
5) Explain the concept of normalization and discuss how it helps remove database anomalies.
6) Define Super Key, Candidate Key, and explain how to compute attribute closure and candidate
keys of a relation with example.
7) Discuss Database Anomalies and their impact on data integrity with appropriate examples.
8) Illustrate the process of finding attribute closure and candidate keys with a worked -out example.
9) Explain Indexing in DBMS. What are different types of indexes and their significance?
10) How can relational algebra and SQL be used together in database querying?
Page 2 of 4
Query-Based Questions List
Example Tables Used:
Table: employees
emp_id name department salary join_date
101 Alice HR 50000 15-01-2020
102 Bob IT 60000 10-06-2019
103 Charlie IT 70000 23-11-2018
104 David Sales 45000 01-03-2021
Table: departments
dept_id dept_name
1 HR
2 IT
3 Sales
Table: projects
project_id project_name dept_id
201 Alpha 2
202 Beta 3
Basic PostgreSQL Query Questions
1) Write a query to retrieve all records from the employees table.
2) Write a query to display names and salaries of all employees.
3) Retrieve names of employees who work in the IT department.
4) Write a query to fetch all employees with salary > 50000.
5) List employees who joined after January 1, 2020.
6) Use an alias to rename columns in your query.
7) Retrieve the number of employees in the HR department.
8) List employees sorted by salary in descending order.
9) Display the distinct departments from the employees table.
10) Use the LIKE operator to find employee names starting with 'A'.
Page 3 of 4
Intermediate PostgreSQL Query Questions
1) Write a query to find the average salary of employees in each department.
2) Find an employee with the highest salary.
3) Display employee names along with their department names (use JOIN).
4) List all employees and their project names (JOIN using departments and projects).
5) Count the number of employees in each department.
6) Show departments that do not have any employees (use LEFT JOIN).
7) Use GROUP BY and HAVING to find departments with more than one employee.
8) Write a query to update salary of employees in Sales department by 10%.
9) Write a query to delete employees who earn less than 45000.
10) Display top 2 highest paid employees using LIMIT.
Advanced PostgreSQL Query Questions
1) Find second highest salary from the employees table without using LIMIT.
2) Create a view to show employee name, department, and project name.
3) Use a subquery to list employees whose salary is above the average salary.
4) Create an index on the salary column for fast search.
5) Write a CTE (Common Table Expression) to find department-wise max salary.
6) Use COALESCE() to handle NULL values in a result set.
7) Write a query using CASE to categorize employees as 'High', 'Medium', or 'Low' earners.
8) Use IN and NOT IN to filter employees based on project assignment.
9) Write a query to find employees who do not belong to any department (assuming FK constraints are
optional).
10) Create a temporary table with employees earning more than 60000.
******************
Page 4 of 4