■ DBMS UNIT-3
Complete Study Guide + Previous Year Questions
Subject Database Management Systems (21CSC205P)
Unit Unit 3 — SQL Commands, Joins, Views, Subqueries
College SRM Institute — 2nd Year, 4th Sem
Prepared By Claude AI — Detailed Notes for Exam
■ Study this guide thoroughly to score full marks in your DBMS exam!
■ TABLE OF CONTENTS
• 1. SQL Commands Overview
• 2. DDL – Data Definition Language (CREATE, DROP, ALTER, TRUNCATE)
• 3. DQL – Data Query Language (SELECT and its clauses)
• 4. DML – Data Manipulation Language (INSERT, UPDATE, DELETE)
• 5. DCL – Data Control Language (GRANT, REVOKE)
• 6. TCL – Transaction Control Language (COMMIT, ROLLBACK, SAVEPOINT)
• 7. SQL Constraints
• 8. SQL Set Operations (UNION, UNION ALL, INTERSECT, MINUS)
• 9. SQL JOINs (INNER, LEFT, RIGHT, FULL)
• 10. Aggregate Functions
• 11. SQL Views
• 12. SQL Subqueries
• 13. ★ PREVIOUS YEAR QUESTIONS WITH ANSWERS ★
1. SQL COMMANDS OVERVIEW
SQL (Structured Query Language) is the standard language to communicate with databases. It is used to
create, read, update, and delete data from a database. Think of SQL like giving instructions to a library —
you can add new books, find books, update info, or remove them.
SQL commands are divided into 5 categories:
Category Full Form Commands Purpose
DDL Data Definition Language CREATE, DROP, ALTER, TRUNCATE Define/modify table structure
DQL Data Query Language SELECT Retrieve/query data
DML Data Manipulation LanguageINSERT, UPDATE, DELETE Modify data in tables
DCL Data Control Language GRANT, REVOKE Control user permissions
TCL Transaction Control Language
COMMIT, ROLLBACK, SAVEPOINT Manage transactions
2. DDL – DATA DEFINITION LANGUAGE
DDL commands are used to DEFINE and MODIFY the structure (schema) of database objects like tables.
They auto-commit (changes are permanent immediately). Think of DDL as the BLUEPRINT of your
database.
A. CREATE
Used to create a new table or database object.
CREATE TABLE TABLE_NAME ( column1 datatype constraint, column2 datatype constraint,
... );
Example:
CREATE TABLE EMPLOYEE ( EmpID INT PRIMARY KEY, Name VARCHAR2(50) NOT NULL, Salary
NUMBER(10,2), DOB DATE );
■ Memory Tip: CREATE = Building a new table skeleton (no data yet).
B. DROP
Used to DELETE a table completely — both structure AND data are removed permanently.
DROP TABLE table_name; DROP DATABASE database_name;
Example:
DROP TABLE EMPLOYEE;
■■ WARNING: DROP cannot be undone (no rollback). The table is gone forever!
C. ALTER
Used to MODIFY an existing table structure — add/remove/modify columns.
-- Add a new column: ALTER TABLE table_name ADD (column_name datatype); -- Modify
existing column: ALTER TABLE table_name MODIFY (column_name new_datatype); -- Drop a
column: ALTER TABLE table_name DROP COLUMN column_name; -- Rename table: ALTER TABLE
table_name RENAME TO new_table_name;
Examples:
ALTER TABLE STU_DETAILS ADD (ADDRESS VARCHAR2(100)); ALTER TABLE STU_DETAILS MODIFY
(NAME VARCHAR2(50));
D. TRUNCATE
Used to REMOVE ALL DATA from a table but KEEPS the table structure (empty table remains). It is faster
than DELETE because it doesn't log each row deletion.
TRUNCATE TABLE table_name;
TRUNCATE TABLE EMPLOYEE;
DROP vs TRUNCATE vs DELETE — Key Differences:
Feature DROP TRUNCATE DELETE
Removes data Yes Yes Yes
Removes structure Yes No No
Can rollback No No (DDL) Yes (DML)
WHERE clause No No Yes
Speed Fast Fastest Slow
Type DDL DDL DML
3. DQL – DATA QUERY LANGUAGE
DQL has only ONE command — SELECT. It is the most frequently used SQL command. It retrieves data
from the database without changing anything.
Basic SELECT Syntax:
SELECT column1, column2, ... FROM table_name WHERE condition; -- Select everything:
SELECT * FROM employee; -- Select with condition: SELECT emp_name FROM employee WHERE
age > 20;
A. SELECT DISTINCT
Returns only UNIQUE (non-duplicate) values.
SELECT DISTINCT column_name FROM table_name; -- Example: Get unique mobile numbers
SELECT DISTINCT MobNo FROM Emp;
B. ORDER BY
Sorts results in ascending (ASC) or descending (DESC) order. Default is ASC.
SELECT * FROM Emp ORDER BY EmpNo; -- Ascending (default) SELECT * FROM Emp ORDER BY
EmpNo DESC; -- Descending SELECT * FROM Emp ORDER BY EmpNo ASC, Ename DESC; --
Multiple columns
C. GROUP BY
Groups rows with the same values together. Always used with aggregate functions like COUNT, SUM, AVG.
SELECT COUNT(EmpNo), City FROM Emp GROUP BY City; -- This shows: how many employees
are in each city
D. HAVING Clause
HAVING is like WHERE but for GROUP BY. WHERE filters rows BEFORE grouping; HAVING filters AFTER
grouping.
SELECT COUNT(EmpNo), City FROM Emp GROUP BY City HAVING COUNT(EmpNo) > 5 ORDER BY
COUNT(EmpNo) DESC; -- Shows cities with more than 5 employees, sorted high to low
■ WHERE vs HAVING — Super Important!
Feature WHERE HAVING
Works on Individual rows Groups (after GROUP BY)
Used with Normal columns Aggregate functions
Order of execution Filters before grouping Filters after grouping
Example WHERE salary > 5000 HAVING AVG(salary) > 5000
4. DML – DATA MANIPULATION LANGUAGE
DML commands are used to INSERT, UPDATE, and DELETE data in existing tables. Unlike DDL, DML
commands CAN be rolled back (undone). Think of DML as editing the content inside the table.
A. INSERT
Adds new rows (records) to a table.
-- Method 1: Without specifying column names (must provide all values in order) INSERT
INTO table_name VALUES (value1, value2, value3, ...); -- Method 2: With column names
(recommended, flexible) INSERT INTO table_name (col1, col2, col3) VALUES (val1, val2,
val3); -- Examples: INSERT INTO Employee VALUES (1, 'Ravi', 50000); INSERT INTO
Employee (EmpID, Name) VALUES (2, 'Priya');
B. UPDATE
Modifies existing data in a table. Always use WHERE or it will update ALL rows!
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; -- Examples:
UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = 3; UPDATE Student SET NAME
= 'PRATIK' WHERE Age = 20; -- ■■ Without WHERE — updates ALL rows: UPDATE Employee
SET Salary = 10000; -- This updates every employee!
■■ Always use WHERE clause with UPDATE to avoid updating all rows accidentally!
C. DELETE
Deletes specific rows from a table based on a condition.
DELETE FROM table_name WHERE condition; -- Examples: DELETE FROM Employee WHERE EmpID
= 3; DELETE FROM javatpoint WHERE Author = 'Sonoo'; -- ■■ Without WHERE — deletes ALL
rows (but table remains): DELETE FROM Employee;
5. DCL – DATA CONTROL LANGUAGE
DCL commands control who can access the database and what they can do. Think of it as setting
PERMISSIONS for users — like admin roles.
A. GRANT
Gives a user permission to perform operations on database objects.
GRANT privilege_name ON object_name TO user_name; -- Examples: -- Give SELECT
permission to user Amit on Users table: GRANT SELECT ON Users TO 'Amit'@'localhost';
-- Give multiple permissions: GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO
'Amit'@'localhost'; -- Give ALL permissions: GRANT ALL ON Users TO
'Amit'@'localhost'; -- Give SELECT to ALL users: GRANT SELECT ON Users TO
'*'@'localhost';
Privileges that can be granted:
Privilege What it allows
SELECT Read/query the table
INSERT Add new rows
UPDATE Modify existing rows
DELETE Remove rows
CREATE Create new tables
DROP Delete tables
ALTER Modify table structure
ALL All permissions except GRANT OPTION
INDEX Create indexes on the table
B. REVOKE
Removes permissions that were previously granted using GRANT.
REVOKE privilege_name ON object_name FROM user_name; -- Example: GRANT INSERT, SELECT
ON accounts TO Ram; -- First grant permissions REVOKE INSERT, SELECT ON accounts FROM
Ram; -- Then remove them -- When privilege is revoked from user U, privileges that U
gave to others -- are also revoked (cascades down).
6. TCL – TRANSACTION CONTROL LANGUAGE
TCL manages TRANSACTIONS — a transaction is a group of SQL operations that should be treated as
ONE unit. Either ALL operations succeed, or NONE do (like bank transfers). TCL ensures data integrity.
A. COMMIT
Permanently saves all changes made in the current transaction to the database.
COMMIT; -- After COMMIT, changes cannot be rolled back
B. ROLLBACK
Undoes all changes made since the last COMMIT or ROLLBACK. Used to recover from errors.
ROLLBACK; -- Reverts to the state at last COMMIT
C. SAVEPOINT
Creates a checkpoint within a transaction. You can rollback to a specific SAVEPOINT without undoing the
entire transaction.
SAVEPOINT savepoint_name; ROLLBACK TO savepoint_name; -- Example: INSERT INTO Student
VALUES (1, 'Ravi'); SAVEPOINT SP1; -- Save here DELETE FROM Student WHERE AGE = 20;
SAVEPOINT SP2; -- Another save point -- If we want to undo only the delete: ROLLBACK
TO SP1; -- Goes back to SP1, before the delete
■ Transaction Flow Diagram:
BEGIN → [SQL Operations] → SAVEPOINT → [More Operations] → COMMIT (save) or ROLLBACK (undo)
7. SQL CONSTRAINTS
Constraints are RULES applied to table columns to control what data can be stored. They maintain data
accuracy and integrity.
Constraint Purpose Example
NOT NULL Column cannot have NULL/empty value Name VARCHAR(50) NOT NULL
UNIQUE All values in column must be different Email VARCHAR(100) UNIQUE
PRIMARY KEY Uniquely identifies each row (NOT NULL + UNIQUE)
ID INT PRIMARY KEY
FOREIGN KEY Links to PRIMARY KEY of another table REFERENCES Customers(id)
CHECK Validates data against a condition amount INT CHECK (amount >= 100)
DEFAULT Sets default value if none provided country VARCHAR(20) DEFAULT 'IN'
CREATE INDEX Speeds up data retrieval CREATE INDEX idx ON emp(name)
Complete Example with all constraints:
CREATE TABLE Student ( student_id INT PRIMARY KEY, -- NOT NULL + UNIQUE student_name
VARCHAR(50) NOT NULL, -- Cannot be empty email VARCHAR(100) UNIQUE, -- Must be unique
age INT CHECK (age >= 18), -- Must be 18 or above city VARCHAR(50) DEFAULT 'Chennai'
-- Default city ); CREATE TABLE Enrollment ( enroll_id INT PRIMARY KEY, student_id
INT, course VARCHAR(50), FOREIGN KEY (student_id) REFERENCES Student(student_id) --
FK );
■ PRIMARY KEY = NOT NULL + UNIQUE (both combined). A table can have only ONE primary key.
8. SQL SET OPERATIONS
Set operations combine results of TWO or MORE SELECT statements. Rules: Both queries must have the
SAME number of columns with compatible data types.
Operation What it does Duplicates?
UNION Combines results of both queries Removes duplicates
UNION ALL Combines results of both queries Keeps all duplicates
INTERSECT Only rows common to BOTH queries No duplicates
MINUS / EXCEPT Rows in first query but NOT in second No duplicates
Example Tables:
Table A: {1-Jack, 2-Harry, 3-Jackson} | Table B: {3-Jackson, 4-Stephan, 5-David}
-- UNION (no duplicates): SELECT * FROM TableA UNION SELECT * FROM TableB; -- Result:
1-Jack, 2-Harry, 3-Jackson, 4-Stephan, 5-David (5 rows) -- UNION ALL (keeps
duplicates): SELECT * FROM TableA UNION ALL SELECT * FROM TableB; -- Result: 1-Jack,
2-Harry, 3-Jackson, 3-Jackson, 4-Stephan, 5-David (6 rows) -- INTERSECT (common
rows): SELECT * FROM TableA INTERSECT SELECT * FROM TableB; -- Result: 3-Jackson (only
1 row - common to both) -- MINUS (in A but not in B): SELECT * FROM TableA MINUS
SELECT * FROM TableB; -- Result: 1-Jack, 2-Harry (rows only in A)
9. SQL JOINS
JOIN combines rows from two or more tables based on a RELATED column between them. This is one of the
most important topics — expect questions in every exam!
JOIN Type What it Returns NULLs?
INNER JOIN Only rows that have matching values in BOTH tables No NULLs
LEFT JOIN ALL rows from LEFT table + matching rows from right (NULLRight
if no match)
side NULLs
RIGHT JOIN ALL rows from RIGHT table + matching rows from left (NULLLeft
if noside
match)
NULLs
FULL JOIN ALL rows from BOTH tables (NULL where no match) Both sides NULLs
NATURAL JOIN Automatically joins on columns with same name No NULLs
A. INNER JOIN
Returns rows that have MATCHING values in BOTH tables. Non-matching rows are excluded. This is the
most common join. JOIN and INNER JOIN are the same.
SELECT table1.col1, table2.col2 FROM table1 INNER JOIN table2 ON table1.common_col =
table2.common_col; -- Example: Get course details for enrolled students SELECT
StudentCourse.COURSE_ID, [Link], [Link] FROM Student INNER JOIN
StudentCourse ON Student.ROLL_NO = StudentCourse.ROLL_NO; -- Only shows students who
have a course (matching roll numbers)
B. LEFT JOIN (LEFT OUTER JOIN)
Returns ALL rows from the LEFT table and MATCHING rows from the right. If no match on right side, NULL
is returned for right table columns.
SELECT [Link], StudentCourse.COURSE_ID FROM Student LEFT JOIN StudentCourse ON
Student.ROLL_NO = StudentCourse.ROLL_NO; -- Shows ALL students. Students without a
course show NULL in COURSE_ID
C. RIGHT JOIN (RIGHT OUTER JOIN)
Returns ALL rows from the RIGHT table and MATCHING rows from the left. If no match on left side, NULL is
returned for left table columns.
SELECT [Link], StudentCourse.COURSE_ID FROM Student RIGHT JOIN StudentCourse ON
Student.ROLL_NO = StudentCourse.ROLL_NO; -- Shows ALL courses. Courses without
enrolled students show NULL in NAME
D. FULL JOIN (FULL OUTER JOIN)
Combines LEFT JOIN + RIGHT JOIN. Returns ALL rows from BOTH tables. NULL is placed wherever there
is no match.
SELECT [Link], StudentCourse.COURSE_ID FROM Student FULL JOIN StudentCourse ON
Student.ROLL_NO = StudentCourse.ROLL_NO; -- Shows ALL students AND ALL courses, NULLs
where no match
■ Memory Trick for JOINs:
INNER = Intersection (only matches) | LEFT = All LEFT + matches from right | RIGHT = All RIGHT + matches
from left | FULL = Everything from both sides
10. AGGREGATE FUNCTIONS
Aggregate functions operate on a SET of rows and return a SINGLE value. They are always used with
GROUP BY to get group-wise results.
Function What it does Example
COUNT() Counts number of rows SELECT COUNT(*) FROM Employee;
SUM() Adds up all values SELECT SUM(Salary) FROM Employee;
AVG() Calculates average SELECT AVG(Salary) FROM Employee;
MIN() Finds minimum value SELECT MIN(Salary) FROM Employee;
MAX() Finds maximum value SELECT MAX(Salary) FROM Employee;
Important: How NULL values are handled:
-- Sample Employee table: -- ID: 1,2,3,4,5,6 | Salary: 80,40,60,70,60,NULL SELECT
COUNT(*) FROM Employee; -- Result: 6 (counts all rows including NULL) SELECT
COUNT(Salary) FROM Employee; -- Result: 5 (ignores NULL) SELECT COUNT(DISTINCT
Salary) FROM Employee; -- Result: 4 (unique non-null: 40,60,70,80) SELECT SUM(Salary)
FROM Employee; -- Result: 310 (ignores NULL) SELECT AVG(Salary) FROM Employee; --
Result: 62 (=310/5, ignores NULL) SELECT MIN(Salary) FROM Employee; -- Result: 40
SELECT MAX(Salary) FROM Employee; -- Result: 80
■ Key Rule: All aggregate functions IGNORE NULL values EXCEPT COUNT(*)
11. SQL VIEWS
A VIEW is a VIRTUAL TABLE based on a SELECT query. It contains rows and columns like a real table, but
the data actually comes from the underlying tables. Think of it as a 'saved query' or a 'window' into your data.
Why use Views?
• Security: Hide sensitive columns from certain users
• Simplicity: Complex queries can be saved as a view and reused simply
• Data Independence: If table structure changes, view can be updated separately
Creating a View:
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
Example — View from single table:
CREATE VIEW DetailsView AS SELECT NAME, ADDRESS FROM Student_Details WHERE STU_ID < 4;
-- Query the view just like a normal table: SELECT * FROM DetailsView;
Example — View from multiple tables:
CREATE VIEW MarksView AS SELECT Student_Detail.NAME, Student_Detail.ADDRESS,
Student_Marks.MARKS FROM Student_Detail, Student_Marks WHERE Student_Detail.NAME =
Student_Marks.NAME; SELECT * FROM MarksView; -- Query the multi-table view
Deleting a View:
DROP VIEW view_name;
DROP VIEW DetailsView;
■ Views do NOT store data physically. They just store the query definition.
12. SQL SUBQUERIES
A SUBQUERY is a query INSIDE another query. The inner query runs first, and its result is used by the outer
query. Also called NESTED QUERY or INNER QUERY.
Rules for Subqueries:
• Subquery must be enclosed in PARENTHESES ( )
• Subquery returning multiple rows must use IN, ANY, ALL operators
• Subquery returning single value can use =, <, >, <=, >= operators
• SQL Server allows nesting up to 32 levels deep
• ORDER BY cannot be used in a subquery (only in outer query)
A. Subquery with SELECT:
-- Get all products with quantity > 45 SELECT * FROM products WHERE product_id IN (
SELECT product_id FROM products WHERE quantity_in_stock > 45 );
B. Subquery with FROM:
-- Get products with above-average quantity SELECT [Link], X.quantity_in_stock FROM
(SELECT AVG(quantity_in_stock) AS avg_qty FROM products) AS quantity_in_stock,
products_bkp AS X WHERE X.quantity_in_stock > quantity_in_stock.avg_qty;
C. Subquery with INSERT:
-- Copy data from products to products_backup INSERT INTO products_bkp SELECT * FROM
products WHERE product_id IN (SELECT product_id FROM products);
D. Subquery with UPDATE:
-- Update unit price where it's greater than minimum UPDATE products_bkp SET
unit_price = 5 WHERE unit_price > (SELECT MIN(unit_price) FROM products);
E. Subquery with DELETE:
-- Delete products with price less than maximum DELETE FROM products_bkp WHERE
unit_price < (SELECT MAX(unit_price) FROM products);
■ Tip: Think of subquery as 'first solve the inner problem, then use that answer for the outer problem'.
13. ★ PREVIOUS YEAR QUESTIONS WITH ANSWERS ★
Based on your exam paper (21CSC205P), here are detailed answers for all questions:
PART A — MCQ ANSWERS (1 Mark Each)
Q Question Answer Explanation
9 Aggregate function to find mean of salary
b) AVG(salary) AVG() calculates arithmetic mean. Mean(salary) doesn't
10 Which query replaces the given SELECT
b) with
Natural
JOIN?
join teaches The query joins instructor and teaches on instructor_id =
PART B — Short Answer Questions (4 Marks Each)
Q1. Relational database design — 'good' collection of schemas & issues of bad design
Answer:
A good relational database design requires schemas that minimize redundancy, support easy data retrieval,
and avoid anomalies. Issues with bad design include:
• Redundancy: Same data stored multiple times wastes space and causes inconsistency
• Update Anomaly: If data is duplicated, updating one copy but not others causes inconsistency
• Insertion Anomaly: You cannot add data about one entity without adding data about another
• Deletion Anomaly: Deleting one type of data unintentionally removes other important data
• Null values problem: Many NULL values in a table means poor schema design
Solution: Use Normalization (1NF, 2NF, 3NF, BCNF) to decompose tables and eliminate these problems.
Q2. Rules for converting ER-Diagram to Relational Database with examples
Answer:
Rule 1: Strong Entity Rule
Each strong entity becomes a TABLE. Attributes become COLUMNS. Primary key of entity becomes
PRIMARY KEY of table. Example: Entity 'Student' with attributes {SID, Name, Age} → CREATE TABLE
Student (SID INT PRIMARY KEY, Name VARCHAR(50), Age INT);
Rule 2: Weak Entity Rule
Weak entity becomes a TABLE with its partial key + primary key of the owner entity as a COMPOSITE
primary key (also foreign key).
Rule 3: 1:1 Relationship Rule
Add the primary key of one entity as a FOREIGN KEY in the other entity's table.
Rule 4: 1:N Relationship Rule
Add the primary key of the '1' side as a FOREIGN KEY in the 'N' side table. Example: Department (1) —
Employee (N): Add dept_id as FK in Employee table.
Rule 5: M:N Relationship Rule
Create a NEW TABLE for the relationship. Include primary keys of BOTH entities as foreign keys. Together
they form the primary key. Example: Student-Course (M:N) → Enrollment (student_id FK, course_id FK,
grade)
Rule 6: Multi-valued Attribute Rule
Create a separate table with the primary key of the entity + the multi-valued attribute. Example: Phone
numbers → Student_Phone (SID FK, Phone_No)
Q3. Relational Algebra Operators: Select, Set Difference, Project, Union
Answer:
i. SELECT (σ) — Selects ROWS satisfying a condition:
Notation: σ(Relation) Example: σ(age > 20)(Student) — Get all students older than 20
SQL equivalent: SELECT * FROM Student WHERE age > 20;
ii. PROJECT (π) — Selects specific COLUMNS:
Notation: π(Relation) Example: π(Name, Age)(Student) — Get only Name and Age columns
SQL equivalent: SELECT Name, Age FROM Student;
iii. SET DIFFERENCE (−) — Rows in R1 but NOT in R2:
Notation: R1 − R2 Example: Student − Graduate — Students who are not graduates SQL
equivalent: SELECT * FROM Student MINUS SELECT * FROM Graduate;
iv. UNION (∪) — Combines rows from both relations (removes duplicates):
Notation: R1 ∪ R2 Example: UG_Students ∪ PG_Students — All students SQL equivalent:
SELECT * FROM UG_Students UNION SELECT * FROM PG_Students;
PART C — Medium Answer Questions (4 Marks Each)
Q4. Elaborate various Set Operations in SQL
Answer:
SQL has 4 set operations to combine results of two SELECT statements. Both queries must have same
number of columns with compatible data types.
1. UNION
Combines results of two queries and REMOVES duplicates.
SELECT * FROM TableA UNION SELECT * FROM TableB;
2. UNION ALL
Combines results of two queries and KEEPS all duplicates (faster than UNION).
SELECT * FROM TableA UNION ALL SELECT * FROM TableB;
3. INTERSECT
Returns only rows COMMON to both queries.
SELECT * FROM TableA INTERSECT SELECT * FROM TableB;
4. MINUS / EXCEPT
Returns rows in FIRST query but NOT in second query.
SELECT * FROM TableA MINUS SELECT * FROM TableB;
Q5(i). Write short notes on VIEWS
Answer:
A VIEW is a virtual table derived from one or more base tables using a SELECT query. It does not store data
physically — it stores only the query definition. When queried, the view dynamically retrieves data from the
underlying tables.
-- Create view: CREATE VIEW EmpView AS SELECT Name, Department FROM Employee WHERE
Salary > 50000; -- Use view: SELECT * FROM EmpView; -- Delete view: DROP VIEW EmpView;
Advantages: Security, Simplicity, Data Independence, Reusability.
Q5(ii). Write short notes on SUB QUERIES
Answer:
A subquery is a SELECT query NESTED inside another SQL statement. The inner query executes first, and
its result is passed to the outer query. Subqueries can appear in SELECT, FROM, WHERE, or HAVING
clauses.
-- Example: Find employees earning above average salary SELECT Name, Salary FROM
Employee WHERE Salary > (SELECT AVG(Salary) FROM Employee); -- The inner query
calculates the average first. -- The outer query uses that average to filter
employees.
Q6. Categorize various JOIN operators and how JOINs are implemented in SQL
Answer:
A JOIN combines rows from two or more tables based on a related column. Types of JOINs:
INNER JOIN:
Returns only matching rows from both tables.
SELECT [Link], c.COURSE_ID FROM Student s INNER JOIN StudentCourse c ON s.ROLL_NO =
c.ROLL_NO;
LEFT JOIN:
Returns all rows from LEFT table; NULL for right where no match.
SELECT [Link], c.COURSE_ID FROM Student s LEFT JOIN StudentCourse c ON s.ROLL_NO =
c.ROLL_NO;
RIGHT JOIN:
Returns all rows from RIGHT table; NULL for left where no match.
SELECT [Link], c.COURSE_ID FROM Student s RIGHT JOIN StudentCourse c ON s.ROLL_NO =
c.ROLL_NO;
FULL JOIN:
Returns all rows from BOTH tables; NULL where no match on either side.
SELECT [Link], c.COURSE_ID FROM Student s FULL JOIN StudentCourse c ON s.ROLL_NO =
c.ROLL_NO;
NATURAL JOIN:
Automatically joins on column(s) with same name in both tables.
SELECT * FROM instructor NATURAL JOIN teaches;
PART D — Long Answer Questions (12 Marks Each)
Q7(A). Relational Calculus — Two types and differences
Answer:
Relational Calculus is a non-procedural query language — it describes WHAT data to retrieve without
specifying HOW to retrieve it (unlike relational algebra which specifies operations step by step). There are
two types:
1. Tuple Relational Calculus (TRC):
Queries are expressed in terms of TUPLES (rows). Notation: { t | P(t) } — Set of all tuples t such that
predicate P(t) is true.
-- Example: Find all students with age > 20 { t | t ∈ Student ∧ [Link] > 20 } -- Find
names of instructors in Physics dept: { [Link] | t ∈ instructor ∧ t.dept_name =
'Physics' }
2. Domain Relational Calculus (DRC):
Queries are expressed in terms of DOMAIN VARIABLES (values from attribute domains). Notation: { |
P(x1,x2,...) } — uses individual attribute values.
-- Example: Find names and ages of students older than 20 { | ∃i ( ∈ Student ∧ a > 20)
}
Differences between TRC and DRC:
Feature Tuple Relational Calculus Domain Relational Calculus
Variables represent Entire tuples (rows) Individual attribute values
Based on First-order predicate logic with tuple variablesFirst-order predicate logic with domain variables
Expression form { t | P(t) } { <x1,x2,...> | P(x1,x2,...) }
Query granularity Row-level Column-value level
Safety Can produce unsafe queries Can also produce unsafe queries
Q7(B). Relational Algebra Queries on Student-Course-Enrollment Schema
Schema: Student (student_id PK, student_name, student_age, student_major) Course
(course_id PK, course_name, course_instructor, course_credits) Enrollment
(enrollment_id PK, student_id FK, course_id FK, enrollment_grade)
i. Select all students majoring in Computer Science:
σ(student_major = 'Computer Science')(Student)
ii. Select all courses taught by Professor Smith:
σ(course_instructor = 'Professor Smith')(Course)
iii. Select all students enrolled in course_id = 101:
π(student_id, student_name)(Student ■ σ(course_id=101)(Enrollment))
iv. Select all students who received grade 'A':
π(student_name)(Student ■ σ(enrollment_grade='A')(Enrollment))
Q8(A). SQL Commands — All types with syntax and examples
Answer: (Full detailed answer — refer to Sections 2-6 of this guide)
SQL commands are categorized into 5 types. Here is a comprehensive illustration:
-- ===== DDL COMMANDS ===== CREATE TABLE Employee ( EmpID INT PRIMARY KEY, Name
VARCHAR(50) NOT NULL, Salary DECIMAL(10,2) ); ALTER TABLE Employee ADD (Department
VARCHAR(30)); TRUNCATE TABLE Employee; -- Removes all data, keeps structure DROP
TABLE Employee; -- Removes table completely -- ===== DQL COMMANDS ===== SELECT * FROM
Employee; SELECT Name, Salary FROM Employee WHERE Salary > 30000 ORDER BY Salary DESC;
SELECT Department, AVG(Salary) FROM Employee GROUP BY Department HAVING AVG(Salary) >
40000; -- ===== DML COMMANDS ===== INSERT INTO Employee (EmpID, Name, Salary) VALUES
(1, 'Ravi', 55000); UPDATE Employee SET Salary = 60000 WHERE EmpID = 1; DELETE FROM
Employee WHERE EmpID = 1; -- ===== DCL COMMANDS ===== GRANT SELECT, INSERT ON Employee
TO 'user1'@'localhost'; REVOKE INSERT ON Employee FROM 'user1'@'localhost'; -- =====
TCL COMMANDS ===== BEGIN; INSERT INTO Employee VALUES (2, 'Priya', 70000, 'IT');
SAVEPOINT sp1; UPDATE Employee SET Salary = 75000 WHERE EmpID = 2; ROLLBACK TO sp1; --
Undo only the update COMMIT; -- Save the insert permanently
Q8(B). SQL Queries on Employee-Department Schema
Schema: Employee (employee_id PK, employee_name, employee_salary, department_id FK)
Department (dep_id PK, dep_name, dep_location)
i. Select all employees and their salaries:
SELECT employee_name, employee_salary FROM Employee;
ii. Select names of employees in 'Sales' department:
SELECT e.employee_name FROM Employee e INNER JOIN Department d ON e.department_id =
d.dep_id WHERE d.dep_name = 'Sales';
iii. Select the average salary of all employees:
SELECT AVG(employee_salary) AS Average_Salary FROM Employee;
iv. Select department name and location for each employee:
SELECT e.employee_name, d.dep_name, d.dep_location FROM Employee e INNER JOIN
Department d ON e.department_id = d.dep_id;
★ QUICK REVISION CHEAT SHEET ★
Keywords to Remember:
Topic Key Points to Remember
DDL CREATE, ALTER, DROP, TRUNCATE — change STRUCTURE — auto-commit
DML INSERT, UPDATE, DELETE — change DATA — can rollback
DQL SELECT — only reads data
DCL GRANT, REVOKE — for USER PERMISSIONS
TCL COMMIT (save), ROLLBACK (undo), SAVEPOINT (checkpoint)
JOINS INNER=match only, LEFT=all left+match, RIGHT=all right+match, FULL=all both
Aggregate COUNT, SUM, AVG, MIN, MAX — ignore NULLs (except COUNT(*))
SET OPS UNION (no dup), UNION ALL (dup), INTERSECT (common), MINUS (difference)
VIEW Virtual table — stored query — no physical data storage
Subquery Query inside query — inner executes first — must be in parentheses
Constraints PK=NOT NULL+UNIQUE, FK=references other table, CHECK=condition, DEFAULT=value
WHERE vs HAVING WHERE filters rows BEFORE GROUP BY, HAVING filters AFTER GROUP BY
DROP vs DELETE DROP removes table+data, DELETE removes only selected rows
■ Good luck for your exam! Study this guide well and you will score full marks. Focus especially on: JOINs,
SQL Commands (DDL/DML/DCL), Constraints, Set Operations, Views, and Subqueries — all of which
appear directly in your PYQ paper.