UNIT 2: SQL and PL/SQL

🧠 MIND MAP: (Priority Based on Frequency in Q3/Q4)

🔴 HIGH PRIORITY (Appears in all 3 Exam Papers – Q3/Q4)
●​ Topic 1: Views (Definition, Creation, Updatability)​

i
●​ Topic 2: Triggers (Definition, Creation, Usage, Preserving old values)​

an
●​ Topic 3: SQL Queries (Complex Joins, Aggregate Functions, Subqueries, DML
operations)​

w
🟠 MEDIUM PRIORITY (Appears in 2 out of 3 Exam Papers – Q3/Q4)
●​ Topic 4: Constraints (Importance, Types with Examples: Primary Key, Foreign Key,
ul
Unique, Not Null, Check, Entity and Referential Integrity)​
M
●​ Topic 5: PL/SQL Procedures/Blocks (Creating, Calling, Input/Output Parameters,
Conditional Logic, Cursor usage)​

🟢 LOWER PRIORITY (Appears in 1 out of 3 Exam Papers – Q3/Q4)

sh

●​ Topic 6: Synonyms (Definition, Creation, Usage)​

●​ Topic 7: Indexes (Definition, Advantages, Disadvantages)​

ite

📘 DBMS Insem Questions – Sep 2024

H

Q3)

a) Consider following schema​

Student_fee_details (rollno, name, fee_deposited, date)​
Write a trigger to preserve old values of student fee details before updating in the table.​
[4 Marks]

b) What is view and how to create it? Can you update view? If yes, how? If not, why not?​
[6 Marks]
c) Consider following schema​
Hotels(hotel_no, hotel_name, city)​
Rooms(Room_no, hotel_no, price, type)​
Write a PL/SQL procedure to list the price & type of all rooms at the hotel ‘TAJ’​
[5 Marks]

OR

Q4)

a) Consider the following relation schema​

i
MOVIES(Mov_Id, Mov_Title, Mov_Year, Dir_Id)​

an
DIRECTOR(Dir_Id, Dir_Name)​
RATING(MOV_Id, Rev_Stars)​
Write the SQL queries for the following:​

w
i) List the title of all the movies directed by ‘RAJ KAPOOR’​
ii) Find the name of movies and number of stars for each movie. Sort the results on movie title
and from higher stars to least stars.​
ul
iii) Assign the rating of all movies directed by ‘Steven Spielberg’ to 9.​
[6 Marks]
M
b) What is the importance of creating constraints on the table? Explain with example any 4
constraints that can be specified when a database table is created.​
[5 Marks]

c) What is synonym? How to create and use synonym in SQL?​

sh

[4 Marks]

📘 DBMS Insem Questions – Sep 2023

ite

Q3)

a) Consider the following schemas​

Emp(Emp_no, Emp_name, Dept_no)​
H

Dept(Dept_no, Dept_name)​
Address(Dept_name, Dept_location)​
Write SQL queries for the following:​
i) Display the location of department where employee ‘Ram’ is working.​
ii) Create a view to store total no of employees working in each department in ascending order.​
iii) Find the name of the department in which no employee is working.​
[6 Marks]
b) What is view and how to create it? Can you update view? If yes, how? If not, why not?​
[5 Marks]

c) Consider following schema​

Student_fee_details (rollno, name, fee_deposited, date)​
Write a trigger to preserve old values of student fee details before updating in the table.​
[4 Marks]

OR

Q4)

i
a) Explain the concept of Referential and Entity Integrity constraint with example.​

an
[5 Marks]

b) Write a PL/SQL block of code which accepts the rollno from user. The attendance of rollno
entered by user will be checked in​

w
student_attendance(RollNo, Attendance) table and display on the screen.​
[5 Marks]

ul
c) What is the importance of creating constraints on the table? Explain with example any 4
constraints that can be specified when a database table is created.​
[5 Marks]
M
📘 DBMS Insem Questions – Oct 2022
Q3)
sh

a) What is view and how to create it? Can you update view? If yes, how? If not, why not?​
[5 Marks]

b) Define stored procedure. Explain creating and calling stored procedure with example.​
ite

[5 Marks]

c) Consider following schema​

Student_fee_details (rollno, name, fee_deposited, date)​
H

Write a trigger to preserve old values of student fee details before updating in the table.​
[5 Marks]

OR

Q4)

a) Consider the following schemas​

Supplier(SNO, Sname, Status, City)​
Parts (PNO, Pname, Color, Weight, City)​
Shipments(SNO, PNO, QTY)​
Write SQL queries for the following:​
i) Find shipment information (SNO, Sname, PNO, Pname, QTY) for those having quantity less
than 157.​
ii) List SNO, Sname, PNO, Pname for those suppliers who made shipments of parts whose
quantity is larger than the average quantity​
iii) Find aggregate quantity of PNO 1692 of color green for which shipments made by supplier
number who residing Mumbai.​
[6 Marks]

i
b) What is an index? What are the advantages and disadvantages of using index on a table?​

an
[4 Marks]

c) What is a trigger? How to create it? Discuss various types of triggers.​

[5 Marks]

w
TOPIC 1: VIEWS
ul
M
Question: What is view and how to create it? Can you update view? If yes, how? If not, why
not?

This question appeared in:

sh

●​ DBMS Insem Questions – Sep 2024: Q3 b) [6 Marks]

●​ DBMS Insem Questions – Sep 2023: Q3 b) [5 Marks]

●​ DBMS Insem Questions – Oct 2022: Q3 a) [5 Marks]

ite

1. What is a View?

A view in SQL is considered a

H

virtual table. It behaves like a table with rows and columns, but it does not store data itself.
Instead, a view is essentially a stored SQL query that retrieves data from one or more
underlying base tables when it is accessed. Views can be designed to include all rows and
columns from a base table or only specific rows and columns based on certain conditions.

IMPORTANT NOTE: The primary purpose of a view is to present a specific subset or

combination of data from one or more tables, often to simplify complex queries, enforce security,
or customize data presentation for different users.
2. How to Create a View?

Views are created using the

CREATE VIEW statement.

Syntax:

SQL
CREATE VIEW name_of_view AS

SELECT column1, column2, …

i
FROM table_name1, table_name2, …

an
WHERE condition;

(Reference: TE_SEMV_DBMS_technical.pdf, Page 2-44, Section 2.21, "Creating View")

w
Examples:

●​ i) Creating a View using a Single Table:​

ul
Consider a table Employee with attributes EmpID, EName, and Salary. To create a view
named EmployeeDetails that shows EmpID and EName for employees whose Salary is
M
greater than 10000:

●​ SQL
CREATE VIEW EmployeeDetails(EmpID, EName) AS
sh

SELECT E.EmpID, E.EName

FROM Employee E

WHERE E.Salary > 10000;

ite

●​

●​ (Reference: TE_SEMV_DBMS_technical.pdf, Page 2-44, Example i))

●​ ii) Creating a View from Multiple Tables:​

H

Consider two tables: Employee (EmpID, EName, Salary) and Department (EmpID,
DName). To create a view named Employee_dept_Details that displays the names and
salaries of employees belonging to the 'Sales' department:

●​ SQL
CREATE VIEW Employee_dept_Details(EName, Salary) AS

SELECT E.EName, E.Salary

FROM Employee E, Department D

WHERE E.EmpID = D.EmpID AND D.DName = 'Sales';

●​

●​ (Reference: TE_SEMV_DBMS_technical.pdf, Page 2-45, Example ii))

3. Can you update a View? If yes, how? If not, why not?

The updatability of a view depends on the complexity of the SQL query used to define it.

●​ When a View is Updatable:​

A view is generally updatable if the query defining it directly maps to a single base table
and does not involve any ambiguity in translating the operation back to the original data.

i
an
Key conditions for a view to be updatable include:

○​ It is defined on a single base table.

○​ It does not contain aggregate functions (e.g., SUM(), AVG(), COUNT(), MIN(),
MAX()).

w
○​ It does not include the DISTINCT keyword.

ul
○​ It does not use GROUP BY or HAVING clauses.

○​ It does not use set operators (UNION, INTERSECT, EXCEPT).

○​ It does not involve joins (most RDBMS generally restrict updates on views
M
created with joins due to ambiguity in mapping changes back to specific base
table rows).

○​ It does not contain subqueries in the SELECT list.

sh

●​ If these conditions are met, INSERT, UPDATE, and DELETE operations can be
performed directly on the view, and the database system will translate these operations
into corresponding changes on the underlying base table.
ite

●​ When a View is NOT Updatable (or restrictions apply):​

Views become non-updatable when the database system cannot clearly and
unambiguously determine how an update operation on the view should affect the data in
the underlying base tables. This occurs in the following common scenarios:
H

○​ Aggregate Functions: If a view includes an aggregate function (e.g.,

AVG(Salary)), updating a value in the view (like changing the average salary) has
no clear translation to the individual rows in the base table that contribute to that
aggregate.

○​ Joins: When a view is created by joining multiple tables, updating a field in the
view might require modifying rows in more than one base table, which can lead to
 ambiguity about which base table row corresponds to which view row, or how to
handle cases where a change affects relationships between tables.

○​ DISTINCT, GROUP BY, HAVING: These clauses modify the result set in a way
that makes it impossible to map updates back to individual, unique rows in the
original base table. For instance, if DISTINCT removes duplicate rows, an update
on the remaining single row in the view wouldn't know which of the original
duplicate rows in the base table to modify.

●​ In such cases, INSERT, UPDATE, or DELETE operations on the view will typically result
in an error or be disallowed by the database system.

i
an
w
TOPIC 2: TRIGGERS

Questions:
ul
●​ What is a trigger? How to create it? Discuss various types of triggers.

●​ Write a trigger to preserve old values of student fee details before updating in the table.
M
These questions appeared in:

●​ DBMS Insem Questions – Sep 2024: Q3 a) [4 Marks] (for preserving old values)

●​ DBMS Insem Questions – Sep 2023: Q3 c) [4 Marks] (for preserving old values)
sh

●​ DBMS Insem Questions – Oct 2022: Q4 c) [5 Marks] (for definition, creation, types)
and Q3 c) [5 Marks] (for preserving old values)
ite

1. What is a Trigger?

A trigger is a special type of stored procedure that is automatically executed (or "fired") when a
specific event occurs in the database. Triggers are associated with database events, which can
H

include:

●​ Data Manipulation Language (DML) statements: INSERT, UPDATE, or DELETE

operations on a table.

●​ Data Definition Language (DDL) statements: CREATE, DROP, or ALTER operations

on database objects.

●​ Database operations: Events such as server startup, shutdown, or user logon/logoff.

Triggers are stored in the database and are invoked repeatedly when the specific conditions
they are set for are met. They are commonly used for tasks such as:

●​ Enforcing complex business rules.

●​ Auditing data changes (e.g., logging old and new values).

●​ Maintaining referential integrity (beyond simple foreign key constraints).

●​ Propagating changes to other tables.

(Reference: TE_SEMV_DBMS_technical.pdf, Page 3-41, Section 3.13, "Triggers")

i
2. How to Create a Trigger?

an
Triggers are created using the

CREATE TRIGGER or CREATE OR REPLACE TRIGGER statement.

Syntax:

w
SQL
CREATE [OR REPLACE] TRIGGER Trigger_Name

{BEFORE | AFTER | INSTEAD OF}

{INSERT [OR] | UPDATE [OR] | DELETE}

ul
M
[OF Column_Name]

ON Table_Name

[REFERENCING OLD AS O NEW AS N]

sh

[FOR EACH ROW]

WHEN (Condition)

DECLARE
ite

-- Declaration Section (optional)

BEGIN

-- Execution Section (PL/SQL code)

H

END;

Explanation of Syntax Components:

●​ CREATE [OR REPLACE] TRIGGER trigger_name: Creates a new trigger or replaces an

existing one with the specified trigger_name.
 ●​ {BEFORE | AFTER | INSTEAD OF}: Specifies when the trigger will execute relative to
the triggering event.

○​ BEFORE: The trigger fires before the DML operation on the table.

○​ AFTER: The trigger fires after the DML operation on the table.

○​ INSTEAD OF: Used for triggers on views, firing instead of the DML operation on
the view, allowing updates to non-updatable views.

●​ {INSERT [OR] | UPDATE [OR] | DELETE}: Specifies the DML operation(s) that will fire
the trigger. You can combine multiple operations using​

i
OR.

an
●​ [OF Column_Name]: (Optional) Specifies the column name that, when updated, will fire
the trigger (only applicable for UPDATE events).

●​ ON Table_Name: Specifies the name of the table to which the trigger is associated.

w
●​ [REFERENCING OLD AS O NEW AS N]: (Optional) Allows you to refer to the old and
new values of rows for INSERT, UPDATE, and DELETE statements. OLD refers to the

O and N are commonly used.

ul
row's state before the change, and NEW refers to its state after the change. Aliases like​

●​ [FOR EACH ROW]: (Optional) Specifies a row-level trigger. If this clause is included,
M
the trigger body executes once for each row affected by the triggering SQL statement. If
omitted, it's a​
statement-level trigger, and the trigger body executes only once for the entire SQL
statement, regardless of the number of rows affected.
sh

●​ WHEN (Condition): (Optional) Provides a condition that must be true for the trigger to
fire.

●​ DECLARE section: (Optional) Used to declare variables, constants, and cursors.

ite

●​ BEGIN...END;: The PL/SQL block containing the logic to be executed when the trigger
fires.

(Reference: TE_SEMV_DBMS_technical.pdf, Page 3-41 to 3-42, Section 3.13, "Syntax")

H

3. Various Types of Triggers:

Triggers can be broadly classified based on:

●​ Timing (When they fire):

○​ BEFORE Triggers: Execute before the DML event occurs. Useful for data
validation, auditing before changes, or modifying data before it is written to the
table.
 ○​ AFTER Triggers: Execute after the DML event occurs. Useful for auditing
changes after they are committed, synchronizing data with other tables, or
performing actions based on the final state of the data.

○​ INSTEAD OF Triggers: Used specifically with views. They fire instead of the
DML operation on the view. This allows updates, inserts, or deletes on views that
are otherwise non-updatable (e.g., views involving joins or aggregate functions)
by providing custom logic to perform the underlying base table operations.

●​ Granularity (How many times they fire):

i
○​ Row-Level Triggers: Fire once for each row affected by the DML statement.

an
They are specified with the​
FOR EACH ROW clause. This type of trigger is useful when the trigger logic
depends on the specific data being changed in each row (e.g., auditing individual
row modifications). In row-level triggers, you can access both OLD and NEW

w
values of the columns.

○​ Statement-Level Triggers: Fire only once for the entire DML statement,

ul
regardless of how many rows are affected. They are the default if FOR EACH
ROW is omitted. This type is useful for actions that need to be performed once
per statement, such as logging the execution of a statement or performing
M
operations that don't depend on individual row data. You cannot access OLD or
NEW values in statement-level triggers.

●​ Event (What causes them to fire):

sh

○​ DML Triggers: Fire in response to INSERT, UPDATE, or DELETE statements.

○​ DDL Triggers: Fire in response to CREATE, ALTER, or DROP statements for

database objects.
ite

○​ System Event Triggers: Fire in response to database events like LOGON,

LOGOFF, STARTUP, SHUTDOWN.

(Reference: TE_SEMV_DBMS_technical.pdf, Page 3-41, Section 3.13 provides an overview of

trigger types including DML, DDL, and database operations, and implicitly covers the timing and
H

granularity in the syntax explanation.)

4. Writing a Trigger to Preserve Old Values of Student Fee Details Before Updating:

Scenario: Consider the schema Student_fee_details (rollno, name, fee_deposited, date). We

need to write a trigger to store the old fee_deposited and date values before an UPDATE
operation occurs on the Student_fee_details table. This is typically done for auditing purposes,
where a separate audit table stores historical data.

Assumption: We will create an audit table, Student_fee_details_audit, to store the old values.
Schema for Audit Table:

SQL
CREATE TABLE Student_fee_details_audit (

audit_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,

rollno NUMBER,

old_fee_deposited NUMBER,

old_date DATE,

i
changed_by VARCHAR2(100), -- User who made the change

an
change_timestamp TIMESTAMP -- When the change occurred

);

w
Trigger Implementation (Oracle PL/SQL Example):

SQL
ul
CREATE OR REPLACE TRIGGER trg_audit_student_fee

BEFORE UPDATE ON Student_fee_details

M
FOR EACH ROW

DECLARE

-- No specific declarations needed for this simple case

sh

BEGIN

-- Insert the OLD values into the audit table

INSERT INTO Student_fee_details_audit (

ite

rollno,

old_fee_deposited,

old_date,
H

changed_by,

change_timestamp

) VALUES (

:OLD.rollno, -- Accessing old value of rollno

:OLD.fee_deposited, -- Accessing old value of fee_deposited

:OLD.date, -- Accessing old value of date

 USER, -- Oracle built-in function for current user

SYSTIMESTAMP -- Oracle built-in for current timestamp

);

END;

Explanation:

i
1.​ CREATE OR REPLACE TRIGGER trg_audit_student_fee: Defines a trigger named

an
trg_audit_student_fee.

2.​ BEFORE UPDATE ON Student_fee_details: Specifies that this trigger will fire before an
UPDATE statement is executed on the Student_fee_details table.

w
3.​ FOR EACH ROW: This is a row-level trigger, meaning it will execute for every row that is
being updated. This is crucial because we need to capture the old values for each
individual row modified.
ul
4.​ :OLD.rollno, :OLD.fee_deposited, :OLD.date: Inside a row-level BEFORE UPDATE
trigger, the :OLD pseudorecord is used to access the values of the row before the
M
update takes effect. This allows us to capture the original values.

5.​ USER and SYSTIMESTAMP: These are Oracle built-in functions to record the current
database user and the exact timestamp of the change, providing a complete audit trail.
sh

6.​ INSERT INTO Student_fee_details_audit ... VALUES (...): This statement inserts the
captured old values, along with audit metadata, into our dedicated audit table.

This trigger ensures that whenever any update is made to the Student_fee_details table, the
previous state of the rollno, fee_deposited, and date for the affected row is automatically
ite

recorded in the Student_fee_details_audit table.

H

TOPIC 3: SQL QUERIES

Questions:

●​ DBMS Insem Questions – Sep 2024: Q4 a) [6 Marks]

○​ i) List the title of all the movies directed by ‘RAJ KAPOOR’

○​ ii) Find the name of movies and number of stars for each movie. Sort the results
on movie title and from higher stars to least stars.
 ○​ iii) Assign the rating of all movies directed by ‘Steven Spielberg’ to 9.

●​ DBMS Insem Questions – Sep 2023: Q3 a) [6 Marks]

○​ i) Display the location of department where employee ‘Ram’ is working.

○​ ii) Create a view to store total no of employees working in each department in

ascending order.

○​ iii) Find the name of the department in which no employee is working.

●​ DBMS Insem Questions – Oct 2022: Q4 a) [6 Marks]

i
○​ i) Find shipment information (SNO, Sname, PNO, Pname, QTY) for those having

an
quantity less than 157.

○​ ii) List SNO, Sname, PNO, Pname for those suppliers who made shipments of
parts whose quantity is larger than the average quantity

w
○​ iii) Find aggregate quantity of PNO 1692 of color green for which shipments
made by supplier number who residing Mumbai.

ul
SQL (Structured Query Language) is the standard language for managing and manipulating
relational databases. It consists of various types of commands, including Data Definition
M
Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and
Transaction Control Language (TCL). This topic focuses primarily on DML, specifically querying
and modifying data.
sh

1. SQL DML Queries: SELECT Statement and Clauses

The SELECT statement is the most fundamental DML command, used to retrieve data from one
or more database tables. The result of a

SELECT query is a new, virtual table called a result-set.

ite

Basic Syntax:

SQL
H

SELECT column1, column2, ...

FROM table_name

WHERE condition

ORDER BY column ASC|DESC;

(Reference: TE_SEMV_DBMS_technical.pdf, Page 2-9, Section 2.5.1, "SELECT Query")

Key Clauses and Concepts:

●​ SELECT Clause: Specifies the columns (attributes) to be retrieved.​

SELECT * retrieves all columns.

○​ DISTINCT keyword: Used to eliminate duplicate rows from the result set.

●​ FROM Clause: Indicates the table(s) from which the data is to be retrieved.

●​ WHERE Clause: Filters the rows based on a specified condition. It uses comparison
operators (=, !=, <, >, <=, >=), logical operators (AND, OR, NOT), and special operators
(LIKE, BETWEEN, IN, IS NULL). (Reference: TE_SEMV_DBMS_technical.pdf, Page

i
2-10, Section 2.5.2 "WHERE", and Page 2-14, Section 2.6 "Logical Operators")

an
●​ ORDER BY Clause: Sorts the result set based on one or more columns in ascending
(ASC, default) or descending (DESC) order.

●​ GROUP BY Clause: Groups rows that have the same values in specified columns into a

w
summary row. It is often used with aggregate functions.

●​ HAVING Clause: Filters groups created by the GROUP BY clause. It is similar to

ul
WHERE but applies conditions to groups rather than individual rows.

2. Aggregate Functions
M
Aggregate functions perform calculations on a set of values and return a single scalar value.
They are commonly used with the

GROUP BY clause.
sh

Common Aggregate Functions:

●​ AVG(): Calculates the average value of a numeric column.

●​ COUNT(): Counts the number of rows or non-null values in a column.​

COUNT(*) counts all rows, including duplicates and nulls.
ite

●​ SUM(): Calculates the total sum of a numeric column.

●​ MIN(): Returns the minimum value in a column.

H

●​ MAX(): Returns the maximum value in a column.

(Reference: TE_SEMV_DBMS_technical.pdf, Page 2-33, Section 2.17, "Aggregate Functions")

3. Joins

The

JOIN clause is used to combine rows from two or more tables based on a related column
between them.
Common Types of Joins:

●​ INNER JOIN (Equijoin): Returns only the rows that have matching values in both tables.
It is the most frequently used join.

○​ Syntax:

○​ SQL
SELECT columns

FROM Table1

i
INNER JOIN Table2

an
ON Table1.common_field = Table2.common_field;

○​

○​

w
○​ (Reference: TE_SEMV_DBMS_technical.pdf, Page 2-40, Section 2.20, "Inner
Join")

ul
●​ LEFT (OUTER) JOIN: Returns all rows from the left table, and the matching rows from
the right table. If there is no match, NULLs are returned for the right table's columns.
M
●​ RIGHT (OUTER) JOIN: Returns all rows from the right table, and the matching rows
from the left table. If there is no match, NULLs are returned for the left table's columns.

●​ FULL (OUTER) JOIN: Returns all rows when there is a match in one of the tables.

●​ CROSS JOIN: Returns the Cartesian product of the rows from the joined tables.
sh

●​ SELF JOIN: A regular join (usually INNER or LEFT) where a table is joined with itself.

(Reference: TE_SEMV_DBMS_technical.pdf, Page 2-39, Fig. 2.20.1 shows types of join

operations, and subsequent pages explain Inner Join).
ite

4. Nested Queries (Subqueries)

A nested query or subquery is a query embedded inside another SQL query. Subqueries can be
used in SELECT, INSERT, UPDATE, or DELETE statements. They are often used with
H

operators like IN, EXISTS, ANY, ALL.

●​ Non-correlated Subquery: The inner query executes independently of the outer query.

●​ Correlated Subquery: The inner query executes once for each row processed by the
outer query and depends on values from the outer query.

(Reference: TE_SEMV_DBMS_technical.pdf, Page 2-37, Section 2.19, "Nested Queries")

5. Database Modification (DML)

Besides SELECT, DML includes commands to modify data:

●​ INSERT: Adds new rows to a table.

○​ Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1,

value2, ...);

○​ (Reference: DBMS unit2.pdf, Page 10, snippet_25 "insert into instructor values
('10211', 'Smith', 'Biology', 66000);")

●​ UPDATE: Modifies existing data in a table.

i
○​ Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ...

an
WHERE condition;

●​ DELETE: Removes rows from a table.

○​ Syntax: DELETE FROM table_name WHERE condition;

w
○​ (Reference: DBMS unit2.pdf, Page 10, snippet_26 "delete from student")

ul
SOLUTIONS TO INSEM SQL QUERY QUESTIONS:

To provide solutions, we will assume standard SQL syntax compatible with most relational
M
database systems (e.g., Oracle, MySQL, PostgreSQL).

A. DBMS Insem Questions – Sep 2024: Q4 a)

Schema:
sh

●​ MOVIES(Mov_Id, Mov_Title, Mov_Year, Dir_Id)

●​ DIRECTOR(Dir_Id, Dir_Name)

●​ RATING(MOV_Id, Rev_Stars)
ite

●​ i) List the title of all the movies directed by ‘RAJ KAPOOR’

●​ SQL
SELECT M.Mov_Title
H

FROM MOVIES M

JOIN DIRECTOR D ON M.Dir_Id = D.Dir_Id

WHERE D.Dir_Name = 'RAJ KAPOOR';

●​

●​
 ●​ ii) Find the name of movies and number of stars for each movie. Sort the results
on movie title and from higher stars to least stars.

●​ SQL
SELECT M.Mov_Title, R.Rev_Stars

FROM MOVIES M

JOIN RATING R ON M.Mov_Id = R.Mov_Id

ORDER BY M.Mov_Title ASC, R.Rev_Stars DESC;

i
●​

an
●​

●​ iii) Assign the rating of all movies directed by ‘Steven Spielberg’ to 9.

●​ SQL

w
UPDATE RATING

SET Rev_Stars = 9

WHERE MOV_Id IN (SELECT M.Mov_Id

FROM MOVIES M
ul
M
JOIN DIRECTOR D ON M.Dir_Id = D.Dir_Id

WHERE D.Dir_Name = 'Steven Spielberg');

●​
sh

●​

B. DBMS Insem Questions – Sep 2023: Q3 a)

Schema:
ite

●​ Emp(Emp_no, Emp_name, Dept_no)

●​ Dept(Dept_no, Dept_name)
H

●​ Address(Dept_name, Dept_location)

●​ i) Display the location of department where employee ‘Ram’ is working.

●​ SQL
SELECT A.Dept_location

FROM Emp E

JOIN Dept D ON E.Dept_no = D.Dept_no

JOIN Address A ON D.Dept_name = A.Dept_name

WHERE E.Emp_name = 'Ram';

●​

●​

●​ ii) Create a view to store total no of employees working in each department in

ascending order.

●​ SQL
CREATE VIEW DepartmentEmployeeCount AS

i
SELECT D.Dept_name, COUNT(E.Emp_no) AS TotalEmployees

an
FROM Dept D

LEFT JOIN Emp E ON D.Dept_no = E.Dept_no

GROUP BY D.Dept_name

w
ORDER BY TotalEmployees ASC;

●​
ul
●​ Note: Using LEFT JOIN ensures departments with no employees are also listed with a
count of 0.
M
●​ iii) Find the name of the department in which no employee is working.

●​ SQL
SELECT D.Dept_name
sh

FROM Dept D

LEFT JOIN Emp E ON D.Dept_no = E.Dept_no

WHERE E.Emp_no IS NULL;

ite

●​

●​ Alternatively, using NOT EXISTS or NOT IN:

●​ SQL
H

SELECT D.Dept_name

FROM Dept D

WHERE NOT EXISTS (SELECT 1 FROM Emp E WHERE E.Dept_no = D.Dept_no);

●​

●​

C. DBMS Insem Questions – Oct 2022: Q4 a)

Schema:

●​ Supplier(SNO, Sname, Status, City)

●​ Parts (PNO, Pname, Color, Weight, City)

●​ Shipments(SNO, PNO, QTY)

●​ i) Find shipment information (SNO, Sname, PNO, Pname, QTY) for those having
quantity less than 157.

●​ SQL

i
SELECT S.SNO, S.Sname, P.PNO, P.Pname, SH.QTY

an
FROM Supplier S

JOIN Shipments SH ON S.SNO = SH.SNO

JOIN Parts P ON SH.PNO = P.PNO

w
WHERE SH.QTY < 157;

●​
ul
●​ (Reference: Adapted from TE_SEMV_DBMS_technical.pdf, Page 2-78, Example 2.22.35
i) for similar join logic)
M
●​ ii) List SNO, Sname, PNO, Pname for those suppliers who made shipments of
parts whose quantity is larger than the average quantity.

●​ SQL
sh

SELECT S.SNO, S.Sname, P.PNO, P.Pname

FROM Supplier S

JOIN Shipments SH ON S.SNO = SH.SNO

ite

JOIN Parts P ON SH.PNO = P.PNO

WHERE SH.QTY > (SELECT AVG(QTY) FROM Shipments);

●​
H

●​ (Reference: Adapted from TE_SEMV_DBMS_technical.pdf, Page 2-78, Example 2.22.35

ii) for similar subquery with average)

●​ iii) Find aggregate quantity of PNO 1692 of color green for which shipments made
by supplier number who residing Mumbai.

●​ SQL
SELECT SUM(SH.QTY) AS TotalQuantity

FROM Shipments SH
JOIN Parts P ON SH.PNO = P.PNO

JOIN Supplier S ON SH.SNO = S.SNO

WHERE P.PNO = 1692

AND P.Color = 'green'

AND S.City = 'Mumbai';

●​

●​ (Reference: Adapted from TE_SEMV_DBMS_technical.pdf, Page 2-78, Example 2.22.35

i
iii) for similar aggregate and join logic)

an
TOPIC 4: CONSTRAINTS

w
Questions:

●​ What is the importance of creating constraints on the table? Explain with example any 4
ul
constraints that can be specified when a database table is created.

●​ Explain the concept of Referential and Entity Integrity constraint with example.
M
These questions appeared in:

●​ DBMS Insem Questions – Sep 2024: Q4 b) [5 Marks] (Importance and 4 examples)

●​ DBMS Insem Questions – Sep 2023: Q4 a) [5 Marks] (Referential and Entity Integrity)
sh

and Q4 c) [5 Marks] (Importance and 4 examples)

1. What is the Importance of Creating Constraints on a Table?

ite

Creating constraints on a table is crucial for maintaining the integrity, accuracy, and reliability
of the data stored in a database. Constraints are rules enforced on data columns in a table,
designed to limit the type of data that can be entered into the table. This ensures that:
H

●​ Data Integrity: Constraints prevent invalid data from being inserted, updated, or deleted,
thus maintaining the quality and consistency of the data over time. Without constraints, it
would be easy to introduce errors, inconsistencies, or incomplete data into the database.

●​ Data Accuracy: They ensure that the data adheres to predefined business rules and
logical relationships, reflecting the real-world facts accurately. For example, a NOT NULL
constraint ensures that critical information is never missing.

●​ Data Reliability: By enforcing rules, constraints ensure that the data remains
dependable and trustworthy for applications and users.
 ●​ Data Consistency: Constraints help maintain consistency across related tables. For
instance, a Foreign Key constraint ensures that relationships between tables are valid,
preventing "orphaned" records.

●​ Reduced Application-Level Validation: By enforcing rules at the database level, the

need for complex data validation logic within applications is reduced, simplifying
application development and maintenance.

2. Explain with Example Any 4 Constraints that Can Be Specified When a Database Table
is Created.

i
When a database table is created, several types of constraints can be defined to enforce data

an
integrity. Here are four important ones:

●​ i) PRIMARY KEY Constraint:

○​ Purpose: The PRIMARY KEY constraint uniquely identifies each record in a

w
table. It must contain unique values for each row and cannot contain​
NULL values. A table can have only one primary key, which can consist of one
or more columns.
ul
○​ Importance: Ensures entity integrity (see section 3.i) by guaranteeing that each
record is uniquely identifiable.
M
○​ Example:

○​ SQL
CREATE TABLE Students (
sh

StudentID INT PRIMARY KEY, -- StudentID is the primary key

FirstName VARCHAR(50),

LastName VARCHAR(50),
ite

Age INT

);

○​
H

○​ In this example, StudentID will uniquely identify each student record.

●​ ii) FOREIGN KEY Constraint:

○​ Purpose: A FOREIGN KEY is a column (or a set of columns) in one table that
refers to the PRIMARY KEY of another table. It establishes a link between two
tables.
 ○​ Importance: Enforces referential integrity (see section 3.ii) by ensuring that
relationships between tables are valid. It prevents actions that would destroy links
between tables (e.g., deleting a parent record if child records still exist).

○​ Example:

○​ SQL
CREATE TABLE Courses (

CourseID INT PRIMARY KEY,

CourseName VARCHAR(100)

i
an
);

CREATE TABLE Enrollments (

w
EnrollmentID INT PRIMARY KEY,

StudentID INT,

CourseID INT,

EnrollmentDate DATE,
ul
M
FOREIGN KEY (StudentID) REFERENCES Students(StudentID), -- Links to Students table

FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) -- Links to Courses table

);
sh

○​

○​ Here, StudentID in Enrollments is a foreign key referencing StudentID in

Students, and CourseID in Enrollments is a foreign key referencing CourseID in
Courses. This ensures that an enrollment record can only exist for a student and
ite

a course that already exist in their respective tables.

●​ iii) NOT NULL Constraint:

○​ Purpose: The NOT NULL constraint ensures that a column cannot contain NULL
H

(unknown or missing) values.

○​ Importance: Guarantees that essential data is always present. For example, a

person's name cannot be left empty.

○​ Example:

○​ SQL
CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50) NOT NULL, -- FirstName cannot be NULL

LastName VARCHAR(50) NOT NULL, -- LastName cannot be NULL

Email VARCHAR(100)

);

○​

○​ In this table, FirstName and LastName must always have a value.

i
●​ iv) UNIQUE Constraint:

an
○​ Purpose: The UNIQUE constraint ensures that all values in a column (or a group
of columns) are different. While a​
PRIMARY KEY also enforces uniqueness, a table can have multiple UNIQUE

w
constraints but only one PRIMARY KEY. A UNIQUE column can contain NULL
values (unless NOT NULL is also specified).

○​ Importance: Prevents duplicate entries in columns where uniqueness is critical

ul
but not necessarily the primary identifier of the record.

○​ Example:
M
○​ SQL
CREATE TABLE Products (

ProductID INT PRIMARY KEY,

sh

ProductName VARCHAR(100) NOT NULL,

SKU VARCHAR(20) UNIQUE, -- SKU must be unique across all products

Price DECIMAL(10, 2)
ite

);

○​

○​ Here, the SKU (Stock Keeping Unit) for each product must be unique, even
H

though ProductID is the primary key.

●​ v) CHECK Constraint:

○​ Purpose: The CHECK constraint is used to limit the range of values that can be
placed in a column. It enforces a condition that must be true for each row.

○​ Importance: Ensures that data values fall within a specified range or meet a
specific condition, upholding domain integrity.
 ○​ Example:

○​ SQL
CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

ProductID INT,

Quantity INT CHECK (Quantity > 0), -- Quantity must be a positive number

OrderDate DATE

i
);

an
○​

○​ This ensures that the Quantity ordered for any product is always greater than
zero.

w
3. Explain the Concept of Referential and Entity Integrity Constraint with Example.

These two integrity constraints are fundamental to the relational model, ensuring the validity and
consistency of data across tables. ul
●​ i) Entity Integrity Constraint:
M
○​ Concept: The Entity Integrity constraint states that the primary key of a base
table cannot contain NULL values. This means that every row in a table must
have a unique and non-null value for its primary key.

○​ Purpose: It guarantees that each entity (row) in the table can be uniquely
sh

identified. If a primary key could be null, it would be impossible to identify specific

records reliably, undermining the core principle of relational databases.

○​ Example:​
ite

Consider a Students table with StudentID as its primary key:

Students Table:

+-----------+-----------+---------+
H

| StudentID | FirstName | Major |

+-----------+-----------+---------+

| 101 | Alice | Computer|

| 102 | Bob | Physics |

| NULL | Charlie | Chemistry| -- VIOLATION: StudentID (PK) cannot be NULL

+-----------+-----------+---------+
 ○​

○​ The row with Charlie violates the entity integrity constraint because its StudentID
(primary key) is NULL. Every student must have a unique identifier.

●​ ii) Referential Integrity Constraint:

○​ Concept: Referential integrity is maintained through the use of FOREIGN KEYs.

It states that if a foreign key in one table (the referencing table or child table)
refers to the primary key of another table (the referenced table or parent table),
then every value in that foreign key column must either:

i
1.​ Match a value in the primary key of the referenced table, or

an
2.​ Be NULL (if the foreign key is allowed to be null, meaning the relationship
is optional).

○​ Purpose: It ensures that relationships between tables remain consistent. It

w
prevents the creation of "orphan records"—records in the child table that
reference a non-existent record in the parent table.

○​ Example:​
ul
Consider a Departments table (parent) and an Employees table (child):

○​ SQL
M
CREATE TABLE Departments (

DeptID INT PRIMARY KEY,

DeptName VARCHAR(50)
sh

);
ite

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

EmpName VARCHAR(50),
H

DeptID INT,

FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)

);

○​

○​ Valid Data:
Departments: Employees:
+--------+----------+ +-------+---------+--------+

| DeptID | DeptName | | EmpID | EmpName | DeptID |

+--------+----------+ +-------+---------+--------+

|1 | HR | | 101 | John |1 |

|2 | IT | | 102 | Jane |2 |

+--------+----------+ | 103 | Peter | NULL | -- Allowed if DeptID is nullable

+-------+---------+--------+

i
○​

an
○​ Violation of Referential Integrity:​
If we try to insert an employee with DeptID = 3 into the Employees table when
DeptID = 3 does not exist in the Departments table, it would violate referential
integrity.

w
INSERT INTO Employees (EmpID, EmpName, DeptID) VALUES (104, 'Sarah', 3);

○​
ul
○​ This would fail because there is no department with DeptID 3 in the Departments
table, leading to an "orphan" employee record.
M
TOPIC 5: PL/SQL STORED PROCEDURES AND FUNCTIONS
sh

Questions:

●​ Define a stored procedure. Explain how to create and call a stored procedure with an
example.
ite

●​ Write a PL/SQL procedure to list the price & type of all rooms at the hotel ‘TAJ’.

●​ Write a PL/SQL block of code which accepts the roll number from the user. The
attendance of the roll number entered by the user will be checked in
H

student_attendance(RollNo, Attendance) table and display on the screen.

These questions appeared in:

●​ DBMS Insem Questions – Sep 2024: Q3 c) [5 Marks] (PL/SQL procedure for hotel
'TAJ' rooms)

●​ DBMS Insem Questions – Sep 2023: Q4 b) [5 Marks] (PL/SQL block for student
attendance)
 ●​ DBMS Insem Questions – Oct 2022: Q3 b) [5 Marks] (Definition, creation, and calling
of stored procedure)

1. Define Stored Procedure. Explain Creating and Calling Stored Procedure with Example.

A stored procedure is a named PL/SQL block of code that is compiled and stored in the
database. It is a subprogram that performs a specific action. Once created, a stored procedure
can be executed repeatedly by various applications or users, offering several advantages:

●​ Modularity and Reusability: Complex operations can be broken down into smaller,

i
manageable, and reusable procedures. This reduces code duplication and simplifies

an
maintenance.

●​ Performance: Stored procedures are compiled once and stored in an executable

format. This reduces parsing and compilation overhead for subsequent executions,

w
leading to faster performance compared to executing individual SQL statements.
Network traffic is also reduced as only the procedure call is sent.

●​ Security: Database administrators can grant users privileges to execute a procedure

ul
without giving them direct access to the underlying tables. This enhances security by
centralizing access control.
M
●​ Data Integrity: Stored procedures can encapsulate complex business rules and data
validation logic, ensuring that data is consistently manipulated and adheres to defined
integrity standards.

Syntax for Creating a Stored Procedure:

sh

SQL
CREATE [OR REPLACE] PROCEDURE procedure_name
ite

[(parameter_name [IN | OUT | IN OUT] datatype [, ...])]

IS | AS

-- Declaration section (optional: variables, cursors, etc.)

H

BEGIN

-- Execution section (PL/SQL statements)

EXCEPTION

-- Exception handling section (optional)

END;

/
(Reference: TE_SEMV_DBMS_technical.pdf, Page 3-27, Section 3.10 "Concept of Stored
Procedures" and Page 3-29, Section 3.10.2 "Procedures with Parameters")

Explanation of Syntax Components:

●​ CREATE [OR REPLACE] PROCEDURE procedure_name: This command creates a

new procedure or replaces an existing one with the same name.

●​ parameter_name [IN | OUT | IN OUT] datatype: Defines parameters for the procedure.

○​ IN (default): Used to pass values into the procedure. The procedure can read

i
an
these values but cannot modify them.

○​ OUT: Used to pass values out of the procedure back to the calling environment.
The procedure can assign values to OUT parameters.

○​ IN OUT: Used to pass initial values into the procedure, which can then be

w
modified within the procedure and passed out to the calling environment.

●​ IS | AS: Keywords that mark the beginning of the PL/SQL block.

ul
●​ DECLARE section (optional): Used to declare local variables, constants, and explicit
cursors within the procedure.
M
●​ BEGIN ... END;: The main executable block of the procedure, containing the SQL and
PL/SQL statements that define its logic.

●​ EXCEPTION section (optional): Used for handling runtime errors that occur within the
BEGIN...END block.
sh

Example for Creating and Calling a Stored Procedure (with and without parameters):

●​ Procedure without Parameters:​

This type of procedure performs a predefined action without requiring any external input.
ite

●​ SQL
-- Creating the procedure
H

CREATE OR REPLACE PROCEDURE Display_Welcome_Message

IS

BEGIN

-- This procedure simply displays a welcome message.

DBMS_OUTPUT.PUT_LINE('Welcome to the Database System!');

DBMS_OUTPUT.PUT_LINE('--- Procedure executed successfully ---');

END;
/

-- Calling the procedure

SET SERVEROUTPUT ON; -- Enable output display in SQL*Plus/SQL Developer

EXEC Display_Welcome_Message;

-- OR

-- CALL Display_Welcome_Message();

i
●​

an
●​

●​ Procedure with IN Parameters:​

This procedure accepts input values to perform its logic.

w
●​ SQL
-- Creating the procedure
ul
CREATE OR REPLACE PROCEDURE Greet_User (

p_user_name IN VARCHAR2,
M
p_greeting_text IN VARCHAR2 DEFAULT 'Hello' -- Default value if not provided

IS
sh

BEGIN

DBMS_OUTPUT.PUT_LINE(p_greeting_text || ', ' || p_user_name || '!');

END;
ite

-- Calling the procedure with one parameter

H

SET SERVEROUTPUT ON;

EXEC Greet_User('Alice');

-- Calling the procedure with two parameters

EXEC Greet_User('Bob', 'Good Morning');

●​
 ●​ (Reference: Adapted from TE_SEMV_DBMS_technical.pdf, Page 3-29, Section 3.10.2
"Procedures with Parameters" provides similar examples)

SOLUTIONS TO INSEM PL/SQL QUESTIONS:

A. DBMS Insem Questions – Sep 2024: Q3 c)

●​ Consider the following schema:​

Hotels(hotel_no, hotel_name, city)​
Rooms(Room_no, hotel_no, price, type)

i
an
●​ Write a PL/SQL procedure to list the price & type of all rooms at the hotel ‘TAJ’.

PL/SQL Procedure:

SQL

w
SET SERVEROUTPUT ON; -- Enable output to display messages

ul
CREATE OR REPLACE PROCEDURE List_Taj_Hotel_Rooms

IS
M
-- Declare variables to hold fetched room details

v_room_price Rooms.price%TYPE;

v_room_type Rooms.type%TYPE;
sh

-- Declare a cursor to select room price and type for 'TAJ' hotel

CURSOR c_taj_rooms IS
ite

SELECT R.price, R.type

FROM Rooms R

JOIN Hotels H ON R.hotel_no = H.hotel_no

H

WHERE H.hotel_name = 'TAJ';

BEGIN

DBMS_OUTPUT.PUT_LINE('--- Listing Rooms at TAJ Hotel ---');

DBMS_OUTPUT.PUT_LINE('---------------------------------');

-- Open the cursor

 OPEN c_taj_rooms;

-- Loop through the fetched rows

LOOP

FETCH c_taj_rooms INTO v_room_price, v_room_type;

EXIT WHEN c_taj_rooms%NOTFOUND; -- Exit loop when no more rows are found

i
-- Display the room details

an
DBMS_OUTPUT.PUT_LINE('Type: ' || RPAD(v_room_type, 15) || ' Price: ' || v_room_price);

END LOOP;

w
-- Check if any rooms were found

IF c_taj_rooms%ROWCOUNT = 0 THEN
ul
DBMS_OUTPUT.PUT_LINE('No rooms found for TAJ hotel.');

END IF;
M
-- Close the cursor

CLOSE c_taj_rooms;
sh

DBMS_OUTPUT.PUT_LINE('---------------------------------');
ite

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

H

END;

-- To call the procedure:

EXEC List_Taj_Hotel_Rooms;
Explanation:

1.​ SET SERVEROUTPUT ON;: Ensures that DBMS_OUTPUT.PUT_LINE statements

display messages in your SQL client (e.g., SQL*Plus, SQL Developer).

2.​ CREATE OR REPLACE PROCEDURE List_Taj_Hotel_Rooms: Defines the stored

procedure. It has no parameters as the hotel name ('TAJ') is fixed.

3.​ v_room_price Rooms.price%TYPE; v_room_type Rooms.type%TYPE;: Declares local

variables to temporarily store the price and type retrieved from the Rooms table. %TYPE
is a PL/SQL attribute that ensures the variable inherits the datatype of the specified

i
column.

an
4.​ CURSOR c_taj_rooms IS ...: An explicit cursor is declared. This cursor's SELECT
statement joins Rooms and Hotels tables to find rooms specifically associated with the
hotel named 'TAJ'.

w
5.​ OPEN c_taj_rooms;: Executes the SELECT statement defined in the cursor and
populates the result set (active set) in memory.

6.​ LOOP ... FETCH ... EXIT WHEN ... END LOOP;: This is the standard loop structure for
ul
processing each row retrieved by the cursor.

○​ FETCH c_taj_rooms INTO v_room_price, v_room_type;: Retrieves the values

M
from the current row of the cursor and assigns them to the v_room_price and
v_room_type variables.

○​ EXIT WHEN c_taj_rooms%NOTFOUND;: This condition checks a cursor attribute

(%NOTFOUND) which becomes true when the last fetch operation failed to
sh

retrieve a row, indicating no more data.

7.​ DBMS_OUTPUT.PUT_LINE(...);: Displays the room type and price for each fetched
room. RPAD is used for formatting the output.
ite

8.​ c_taj_rooms%ROWCOUNT: A cursor attribute that returns the number of rows

processed so far by the cursor. Used here to check if any rooms were found.

9.​ CLOSE c_taj_rooms;: Releases the resources held by the cursor after processing all
H

rows.

10.​EXCEPTION WHEN OTHERS: A generic exception handler to catch any unexpected

errors during procedure execution.

B. DBMS Insem Questions – Sep 2023: Q4 b)

●​ Write a PL/SQL block of code which accepts the roll number from the user. The
attendance of the roll number entered by the user will be checked in
student_attendance(RollNo, Attendance) table and display on the screen.
Assumed Schema:

●​ student_attendance (RollNo, Attendance) (where Attendance stores the attendance

value)

PL/SQL Block:

SQL
SET SERVEROUTPUT ON; -- Enable output to display messages

i
DECLARE

an
v_roll_no student_attendance.RollNo%TYPE; -- Variable for user-entered roll number

v_attendance student_attendance.Attendance%TYPE; -- Variable to store fetched attendance

BEGIN

w
-- Prompt the user to enter the roll number

DBMS_OUTPUT.PUT_LINE('Enter the student Roll Number:');

ul
-- Accept input from the user (for SQL*Plus/SQL Developer)

v_roll_no := '&input_roll_no';
M
-- Attempt to fetch attendance for the given roll number

SELECT Attendance
sh

INTO v_attendance

FROM student_attendance

WHERE RollNo = v_roll_no;

ite

-- If a record is found, display the attendance

DBMS_OUTPUT.PUT_LINE('--- Student Attendance Details ---');

H

DBMS_OUTPUT.PUT_LINE('Roll No: ' || v_roll_no);

DBMS_OUTPUT.PUT_LINE('Attendance: ' || v_attendance);

DBMS_OUTPUT.PUT_LINE('----------------------------------');

EXCEPTION

WHEN NO_DATA_FOUND THEN

 -- Handle the case where no student with the given roll number is found

DBMS_OUTPUT.PUT_LINE('Error: No student found with Roll Number ' || v_roll_no || '.');

WHEN OTHERS THEN

-- Handle any other unexpected errors

DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);

END;

i
an
Explanation:

1.​ SET SERVEROUTPUT ON;: Ensures DBMS_OUTPUT.PUT_LINE output is visible.

2.​ DECLARE: Variables v_roll_no and v_attendance are declared, using %TYPE to match

w
the column types in student_attendance.

3.​ v_roll_no := '&input_roll_no';: This line accepts input from the user. When this block is
ul
executed in SQL*Plus or SQL Developer, &input_roll_no will prompt the user to enter a
value, which is then assigned to v_roll_no.
M
4.​ SELECT Attendance INTO v_attendance FROM student_attendance WHERE RollNo =
v_roll_no;: This is a SELECT INTO statement. It attempts to retrieve the Attendance for
the RollNo provided by the user and stores it directly into v_attendance. This uses an
implicit cursor.
sh

5.​ DBMS_OUTPUT.PUT_LINE(...);: Displays the fetched RollNo and Attendance.

6.​ EXCEPTION WHEN NO_DATA_FOUND THEN ...: This is a specific exception handler
for the NO_DATA_FOUND exception. This exception is raised when the SELECT INTO
statement does not return any rows (i.e., no student with the entered roll number exists).
ite

It's crucial for handling cases where no matching record is found.

7.​ WHEN OTHERS THEN ...: This is a generic exception handler that catches any other
unexpected runtime errors that might occur.
H

TOPIC 6: SYNONYMS
Questions:

●​ What is a synonym? How to create and use a synonym in SQL?

This question appeared in:

 ●​ DBMS Insem Questions – September 2024: Q4 c) [4 Marks]

1. What is a Synonym?

In SQL, a

synonym is an alternative name or an alias for a database object, such as a table, view,
sequence, procedure, function, or package. Synonyms are primarily used to provide a level of
abstraction and simplify database object access.

i
Key purposes and advantages of using synonyms include:

an
●​ Data Independence: It hides the actual name and location of a database object. If the
underlying object is moved or renamed, only the synonym needs to be redefined, not all
the applications that refer to it.

●​ Security: It can provide a level of security by masking the name and owner of an object,

w
preventing unauthorized users from directly accessing the underlying objects. Users can
be granted privileges on the synonym rather than the base object.

ul
●​ Simplification of Queries: It simplifies long and complex object names, especially when
referencing objects in other schemas. Instead of using schema_name.object_name, you
can just use the synonym.
M
●​ Location Transparency: For distributed databases, synonyms allow users to access
remote objects as if they were local, without needing to know the actual location of the
object.
sh

2. How to Create a Synonym in SQL

Synonyms are created using the

CREATE SYNONYM statement. They can be public or private:

ite

●​ Public Synonyms: Are accessible to all users of the database. These are typically
created by a database administrator.

●​ Private Synonyms: Are accessible only to the user who created them or to other users
H

who have been granted specific privileges.

Syntax:

SQL
CREATE [PUBLIC] SYNONYM synonym_name

FOR [schema.]object_name[@dblink];
Explanation of Syntax Components:

●​ CREATE SYNONYM: The command to create a synonym.

●​ PUBLIC: (Optional) Specifies that the synonym is public and accessible to all users. If
omitted, the synonym is private.

●​ synonym_name: The name you want to give to the synonym.

●​ FOR: Specifies the object for which you are creating the synonym.

●​ [schema.]object_name: The actual name of the database object (e.g., table, view,

i
procedure) you are aliasing. If the object belongs to another schema, you must specify

an
the schema name.

●​ [@dblink]: (Optional) Used for creating synonyms for objects in a remote database,
accessed via a database link.

Example: Creating a Synonym

w
Let's assume we have a table named EMPLOYEE_RECORDS in the HR schema.

ul
●​ Creating a private synonym for EMPLOYEE_RECORDS:

●​ SQL
M
CREATE SYNONYM emp

FOR HR.EMPLOYEE_RECORDS;

●​
sh

●​ Now, a user can refer to HR.EMPLOYEE_RECORDS simply as emp.

●​ Creating a public synonym for EMPLOYEE_RECORDS (usually done by an

administrator):

●​ SQL
ite

CREATE PUBLIC SYNONYM all_employees

FOR HR.EMPLOYEE_RECORDS;
H

●​

●​ Any user can now refer to HR.EMPLOYEE_RECORDS as all_employees.

3. How to Use a Synonym in SQL

Once a synonym is created, you can use it in SQL statements just as you would use the actual
object name. The database transparently resolves the synonym to the underlying object.

Example: Using a Synonym

Continuing with the emp synonym created above for the HR.EMPLOYEE_RECORDS table:
 ●​ Selecting data using the synonym:​
Instead of:

●​ SQL
SELECT * FROM HR.EMPLOYEE_RECORDS WHERE department = 'Sales';

●​

●​ You can use:

●​ SQL

i
SELECT * FROM emp WHERE department = 'Sales';

an
●​

●​

●​ Inserting data using the synonym:

w
●​ SQL
INSERT INTO emp (employee_id, employee_name, department)

VALUES (101, 'John Doe', 'IT');

●​
ul
M
●​

●​ Updating data using the synonym:

●​ SQL
sh

UPDATE emp

SET salary = 60000

WHERE employee_id = 101;

ite

●​

●​
H

●​ Deleting data using the synonym:

●​ SQL
DELETE FROM emp

WHERE employee_id = 101;

●​

●​
To drop a synonym, you use the DROP SYNONYM statement:

SQL
DROP [PUBLIC] SYNONYM synonym_name;

Example:

SQL
DROP SYNONYM emp;

i
DROP PUBLIC SYNONYM all_employees;

an
w
TOPIC 7: INDEXES
Questions:
ul
●​ What is an index? What are the advantages and disadvantages of using indexes on a
table?
M
This question appeared in:

●​ DBMS Insem Questions – October 2022: Q4 b) [4 Marks]

sh

1. What is an Index?

An
ite

index in a database is a special lookup table that the database search engine can use to speed
up data retrieval operations. It is a pointer to the data in the database table. The concept of an
index is similar to the index at the back of a book, which contains keywords and page numbers,
allowing you to quickly locate desired information. With the help of indexing, data retrieval
H

becomes fast and efficient.

Indexes can be created on one or more columns of a table. When you create an index, the
database stores the data in the indexed columns in a sorted order, along with pointers to the
actual rows in the table.

Syntax for Creating an Index:

SQL
CREATE INDEX index_name ON table_name (column1, column2, ...);
Example:

To create an index named idx_isbn on the isbn field of a Book table:

SQL
CREATE INDEX idx_isbn ON Book(isbn);

You can also create a unique index, which ensures that all values in the indexed column(s) are

i
unique:

an
SQL
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

w
(Reference: TE_SEMV_DBMS_technical.pdf, Page 2-32, Section 2.16 "Indexes" )

2. Advantages of Using Indexes:

ul
●​ Faster Data Retrieval: The primary advantage of indexes is significantly improved query
performance, especially for SELECT statements with WHERE clauses, JOIN operations,
M
and ORDER BY clauses. This is because the database can quickly locate the required
data without scanning the entire table.

●​ Faster Sorting and Grouping: Indexes can speed up operations that involve sorting
(ORDER BY) and grouping (GROUP BY) because the data is already stored in a sorted
sh

order within the index.

●​ Ensures Uniqueness: Unique indexes enforce uniqueness on the indexed column(s),

preventing duplicate values and maintaining data integrity.
ite

●​ Primary Keys and Unique Constraints: When you define a PRIMARY KEY or
UNIQUE constraint on a table, the database typically creates an index automatically on
those columns to enforce the constraint efficiently.
H

3. Disadvantages of Using Indexes:

●​ Increased Storage Space: Indexes require additional disk space to store their structure.
For large tables with many indexes, this can consume significant storage.

●​ Slower Data Modification (DML Operations): While indexes speed up data retrieval,
they can slow down data modification operations (INSERT, UPDATE, DELETE). This is
because every time data in the indexed columns is changed, the index itself must also
be updated to reflect the changes, adding overhead.
 ●​ Performance Overhead During Creation and Maintenance: Creating indexes on large
tables can be a time-consuming process. Also, the database system needs to maintain
indexes whenever data is modified, which adds overhead and can impact performance,
especially during high-volume DML operations.

●​ Complexity: Over-indexing a table can lead to increased complexity in database

management and might not always result in performance gains, sometimes even leading
to performance degradation if the optimizer chooses to use an inappropriate index.

●​ Not Always Beneficial: Indexes are most effective on columns that are frequently used
in WHERE clauses, JOIN conditions, or ORDER BY clauses. Creating indexes on

i
an
columns that are rarely queried or have a high number of duplicate values might not
provide significant benefits and could even be detrimental.

w
ul
M
sh
ite
H