Module-4 (DBMS-BCS403): Question Bank

NULL Values & Three-Valued Logic

Qn-1: Define NULL in SQL. Explain different interpretations of NULL and describe
three-valued logic (TRUE, FALSE, UNKNOWN) with examples.
ANS: NULL in SQL:
NULL is a special value in SQL used to represent missing, unknown,
unavailable, or not applicable data in a database. It does not mean zero, blank space,
or false. Each NULL value is treated as different from other NULL values.
Different Interpretations of NULL:
1. Unknown value:
Sometimes the value exists, but it is not known at present.
Example: A person’s date of birth is not known, so it is stored as NULL in the database.
2. Unavailable or withheld value:
The value exists, but it is not available or intentionally hidden.
Example: A person has a phone number, but does not want it to be displayed, so it is
stored as NULL.
3. Not applicable value:
The attribute does not apply to that particular record.
Example: A person who has no college degree will have CollegeDegree = NULL,
because that attribute is not applicable.

Three-Valued Logic in SQL:

In normal Boolean logic, only TRUE and FALSE values exist. But in SQL, when
NULL participates in comparison, the result may become UNKNOWN. Therefore SQL
uses three-valued logic:
 TRUE
 FALSE
 UNKNOWN
Example:
If Salary = NULL, then comparison like:
Salary = 50000 → Result is UNKNOWN
because actual value is not known.

Truth Table of Three-Valued Logic:

Condition Result

TRUE AND TRUE TRUE

TRUE AND FALSE FALSE
TRUE AND UNKNOWN UNKNOWN
FALSE OR UNKNOWN UNKNOWN
NOT TRUE FALSE
NOT FALSE TRUE
NOT UNKNOWN UNKNOWN
Nested Queries & Set Comparisons

Qn-2: Define nested queries. Explain their working with suitable examples.
ANS: Nested Query:
A nested query is a query written inside another SQL query. The inner query is
called the nested query (subquery), and the outer query is called the outer query. The
result of the inner query is used by the outer query to retrieve the required data from the
database. Nested queries are generally written inside the WHERE clause of another SQL
query.

Working of Nested Query:

In a nested query, first the inner query executes and produces a result. Then this
result is passed to the outer query, which uses it for comparison and retrieves the final
answer. Thus, nested queries help in solving complex retrieval problems in SQL.

Syntax:
SELECT column_name FROM table_name WHERE column_name operator
(SELECT column_name FROM table_name WHERE condition);

Example 1:
Retrieve project numbers of projects that have an employee with last name
‘manjunatha’ as manager.

SELECT DISTINCT Pnumber FROM PROJECT WHERE Pnumber IN

(SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE
Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'manjunatha');

Here, the inner query first finds project numbers managed by employee
‘manjunatha’. The outer query then retrieves those project numbers from PROJECT
table.

Example 2:
Retrieve names of employees whose salary is greater than all employees in
department 5.

SELECT Lname, Fname FROM EMPLOYEE WHERE Salary > ALL

(SELECT Salary FROM EMPLOYEE WHERE Dno = 5);
Here, the inner query finds salaries of employees in department 5, and the outer
query selects employees whose salary is greater than all those values.
Qn-3: Explain comparison operators used in nested queries (IN, ANY, ALL) with
examples.
ANS: Comparison Operators in Nested Queries:
In SQL, nested queries often return a set of values. To compare a single value
with this set, SQL uses comparison operators such as IN, ANY (or SOME), and ALL.
These operators help the outer query compare its value with the result produced by the
inner query.

1. IN Operator:
The IN operator is used to check whether a value is present in a set of values
returned by the nested query. If the value matches any one value in the set, the result
becomes TRUE.

Syntax:

SELECT column_name FROM table_name WHERE column_name

IN (SELECT column_name FROM table_name WHERE condition);

Example:
Retrieve project numbers of projects that have employee ‘PLH’ as manager.

SELECT DISTINCT Pnumber FROM PROJECT WHERE Pnumber

IN (SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'PLH');

Here, the outer query checks whether Pnumber is present in the set returned by the
inner query.

2. ANY Operator:
The ANY operator compares a value with a set of values returned by the nested
query. The condition becomes TRUE if comparison is satisfied with at least one value in
the set. The keyword SOME has the same meaning as ANY.

Syntax:
 SELECT column_name FROM table_name WHERE column_name operator
ANY (SELECT column_name FROM table_name WHERE condition);

Example:
If salary is greater than any one salary in department 5:

SELECT Lname, Fname FROM EMPLOYEE WHERE Salary > ANY

(SELECT Salary FROM EMPLOYEE WHERE Dno = 5);
This selects employees whose salary is greater than at least one employee’s
salary in department 5.

3. ALL Operator:
The ALL operator compares a value with all values returned by the nested query.
The condition becomes TRUE only if comparison satisfies every value in the set.

Syntax:

SELECT column_name FROM table_name WHERE column_name operator ALL

(SELECT column_name FROM table_name WHERE condition);

Example:
Retrieve employees whose salary is greater than all employees in department 5.

SELECT Lname, Fname FROM EMPLOYEE WHERE Salary > ALL

(SELECT Salary FROM EMPLOYEE WHERE Dno = 5);

This selects employees whose salary is greater than every salary in department 5.

Qn-4: Explain tuple comparison and use of nested queries in real-world scenarios.
ANS: Tuple Comparison in SQL:
Tuple comparison in SQL is used when more than one attribute value is compared
together in a nested query. The values are written inside parentheses as a tuple, and SQL
compares this group of values with the set of tuples returned by the inner query. This is
useful when comparison depends on multiple columns instead of a single column.
Syntax of Tuple Comparison:
SELECT column_name FROM table_name WHERE (column1, column2) IN
(SELECT column1, column2 FROM table_name WHERE condition);
Here, the outer query compares a pair (or group) of values with the tuple values
returned by the inner query.

Example of Tuple Comparison:

Retrieve the employee SSN values of employees who work on the same (project,
hours) combination as employee ‘John Smith’.

SELECT DISTINCT Essn FROM WORKS_ON WHERE (Pno, Hours) IN

(SELECT Pno, Hours FROM WORKS_ON, EMPLOYEE
WHERE Essn = Ssn
AND Fname = 'John'
AND Lname = 'Smith');
In this query, the inner query returns a set of tuples (Pno, Hours) for John Smith,
and the outer query compares its own tuple values with this result to find matching
employees.

Real-World Uses of Nested Queries:

1. Banking System:
Nested queries can be used to find customers whose loan amount is greater than the
average loan amount of a branch.

2. Employee Management:
Nested queries can retrieve employees whose salary is higher than all employees in a
particular department.
3. College Database:
Nested queries can find students who scored more than the average marks of their class.

4. Online Shopping:
Nested queries can identify customers who purchased products with the highest price in
a category.

These real-world applications show that nested queries are useful for solving
complex data retrieval problems.
Correlated Nested Queries

Qn-5: What are correlated nested queries? Explain their working with example.
EXISTS, NOT EXISTS, UNIQUE
ANS: Correlated Nested Queries:
A correlated nested query is a nested query in which the inner query references an
attribute from the outer query. In this type of query, the inner query is not executed
independently; instead, it is evaluated once for each tuple of the outer query. Therefore,
the inner query depends on the outer query for its execution.

Working of Correlated Nested Query:

In correlated nested queries, first one tuple from the outer query is selected. Then
the inner query executes using the value of that outer tuple. If the condition is satisfied,
that tuple is selected. This process continues for every tuple of the outer query. Thus, the
inner query is repeatedly executed for each record of the outer query.

Example of Correlated Nested Query:

Retrieve the names of employees who have a dependent with the same first name
and same sex.
SELECT [Link], [Link] FROM EMPLOYEE AS E WHERE [Link] IN
(SELECT Essn FROM DEPENDENT AS D WHERE [Link] = D.Dependent_name
AND [Link] = [Link]);
Here, the inner query uses [Link] and [Link] from the outer query, so it is a
correlated nested query.

EXISTS Function:
The EXISTS function is used to check whether the result of a correlated nested
query contains at least one tuple.
 Returns TRUE → if query result is not empty
 Returns FALSE → if query result is empty
Example:
 SELECT [Link], [Link] FROM EMPLOYEE AS E WHERE EXISTS
(SELECT * FROM DEPENDENT AS D WHERE [Link] = [Link]);
This selects employees who have at least one dependent.

NOT EXISTS Function:

The NOT EXISTS function checks whether the result of the nested query is
empty.
 Returns TRUE → if no tuple exists
 Returns FALSE → if tuples exist
Example:
SELECT Fname, Lname FROM EMPLOYEE WHERE NOT EXISTS
(SELECT * FROM DEPENDENT WHERE Ssn = Essn);
This selects employees who have no dependents.

UNIQUE Function:
The UNIQUE function checks whether the result of a nested query contains
duplicate tuples or not.
 Returns TRUE → if no duplicate tuples exist
 Returns FALSE → if duplicate tuples exist
It is used to test whether the result of a nested query is a set or multiset.

Qn-6: What is UNIQUE function in SQL? Explain its purpose.

ANS: UNIQUE Function in SQL:
The UNIQUE(Q) function in SQL is used to check whether the result of a nested
query Q contains duplicate tuples or not. It returns a Boolean value based on the query
result. This function helps to determine whether the output of a nested query is a set (no
duplicates) or a multiset (duplicates allowed).
Purpose of UNIQUE Function:
The main purpose of the UNIQUE function is to test whether the tuples returned
by a nested query are all distinct. It is used in SQL when we want to verify that the result
of a query does not contain repeated tuples. Thus, it helps in checking the uniqueness of
query results.

Working of UNIQUE Function:

When a nested query is written inside UNIQUE(Q), SQL checks the result of that
query:

 If the query result contains no duplicate tuples, UNIQUE returns TRUE.

 If the query result contains duplicate tuples, UNIQUE returns FALSE.

Syntax:
UNIQUE (subquery)

Example:
Suppose a nested query returns employee department numbers:

SELECT Dno FROM EMPLOYEE;

If we use:

UNIQUE (SELECT Dno FROM EMPLOYEE);

SQL checks whether all Dno values are unique.

 If no repeated department numbers exist → TRUE

 If duplicate department numbers exist → FALSE

Thus, the UNIQUE function in SQL is used to test whether a nested query result
contains duplicate tuples or not. Its main purpose is to check the uniqueness of query
results, and it returns TRUE when all tuples are distinct and FALSE when duplicates
exist.
 Explicit Sets & Renaming

Qn-7: Explain explicit sets in SQL and how they are used in queries with examples.
ANS: Explicit Sets in SQL:
In SQL, it is possible to use an explicit set of values directly in the WHERE
clause instead of using a nested query. Such a set of values is written inside
parentheses and contains a list of constant values separated by commas. SQL compares
the attribute value with this set and selects the matching tuples.

Purpose of Explicit Sets:

The main purpose of explicit sets is to simplify query writing when the values
to be compared are already known. Instead of writing a nested query to retrieve
values, the required values can be directly specified in the query. This makes SQL
queries easier and shorter.

Working of Explicit Sets:

In explicit sets, SQL checks whether the attribute value is present in the given set
of values. This is usually done using the IN operator.

 If the value matches one of the values in the set → TRUE

 If the value does not match any value → FALSE

Syntax:
SELECT column_name FROM table_name WHERE column_name IN
(value1, value2, value3);

Example:
Retrieve the Social Security numbers of all employees who work on project
numbers 1, 2, or 3.

SELECT DISTINCT Essn FROM WORKS_ON WHERE Pno IN (1, 2, 3);

In this query, (1, 2, 3) is an explicit set. SQL checks whether Pno matches any
value in this set, and if a match is found, the corresponding employee SSN is selected.

Advantages of Explicit Sets:

 Query becomes simple and easy to write.
 No need for a nested query when values are already known.
  Useful for comparing an attribute with a fixed set of values.

Thus, explicit sets in SQL are fixed sets of values written directly in the
WHERE clause, and they are used with the IN operator to compare attribute values
easily without using nested queries.

Qn-8: Explain attribute renaming using AS with suitable example.

ANS: Attribute Renaming in SQL:
In SQL, it is possible to rename any attribute that appears in the result of a
query by using the keyword AS followed by the new name. This new name is called an
alias. Attribute renaming changes only the display name in the query result and does
not change the original attribute name in the database table.

Purpose of Attribute Renaming:

The main purpose of attribute renaming is to make the query output more
meaningful, clear, and easy to understand. It is especially useful when the same table
is used more than once in a query or when attribute names are long or confusing.

Syntax:
SELECT attribute_name AS new_name FROM table_name;

Here, attribute_name is the original column name and new_name is the renamed
alias displayed in the result.

Example:
Retrieve the last name of each employee and his or her supervisor.
SELECT [Link] AS Employee_name, [Link] AS Supervisor_name
FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.Super_ssn = [Link];
In this query:
  [Link] AS Employee_name → renames employee last name as
Employee_name
 [Link] AS Supervisor_name → renames supervisor last name as
Supervisor_name
The original database column names remain unchanged, but the query result
shows the new names.

Advantages of Using AS:

 Makes output easy to read and understand.
 Gives meaningful names to columns in query result.
 Helps avoid confusion when same table or same attribute appears multiple
times in a query.
 Improves presentation of query results.

Thus, attribute renaming using AS is used to assign a temporary new name (alias)
to an attribute in SQL query results, making the output clear, meaningful, and easy to
understand without changing the original database table structure.

Joins in SQL
Q-9: Define JOIN. Explain different types of joins in SQL.
ANS:
Definition of JOIN:
A JOIN in SQL is used to combine records from two or more tables based on
a related attribute (common field) between them. It creates a result table by retrieving
matching rows from the joined tables. JOIN is used when data is stored in different
tables but needs to be retrieved together in a single query.
Types of JOIN in SQL:
SQL mainly supports the following types of joins:
1. INNER JOIN
2. OUTER JOIN
3. EQUIJOIN
4. NATURAL JOIN
1. INNER JOIN:
An INNER JOIN returns only those rows that satisfy the join condition in both
tables. It combines records based on matching values in the common attribute. It is the
most commonly used join in SQL.

Example:
SELECT * FROM EMPLOYEE INNER JOIN DEPARTMENT ON
[Link] = [Link];
This query returns only the employee records that have a matching department number.

2. OUTER JOIN:
An OUTER JOIN returns matching rows as well as non-matching rows from one
or both tables. If no match exists, SQL fills the missing values with NULL. Outer joins
are of three types:
 LEFT OUTER JOIN → returns all rows from left table and matching rows
from right table.
 RIGHT OUTER JOIN → returns all rows from right table and matching
rows from left table.
 FULL OUTER JOIN → returns all rows from both tables whether matching
or not.

3. EQUIJOIN:
An EQUIJOIN is a special type of join in which the join condition uses only the
equality (=) operator. If any other operator such as < or > is used, it is not an equijoin.
Example:
SELECT * FROM EMPLOYEE, DEPARTMENT WHERE [Link] =
[Link];
Here, equality operator = is used, so it is an equijoin.
 4. NATURAL JOIN:
A NATURAL JOIN is a type of equijoin in which SQL automatically joins tables
by comparing columns having the same name in both tables. The resulting table
contains only one copy of the common attribute.
Example:
SELECT Fname, Lname, Address FROM EMPLOYEE NATURAL JOIN
DEPARTMENT WHERE Dname = 'Research';

Here, SQL automatically joins the tables based on common column names.

Thus, JOIN is used to combine data from multiple tables using common
attributes. Different joins such as INNER JOIN, OUTER JOIN, EQUIJOIN, and
NATURAL JOIN are used depending on the requirement for matching and non-
matching data retrieval.

Qn-10: Explain INNER JOIN, EQUIJOIN and NATURAL JOIN with examples.
ANS:

Qn-11: Explain OUTER JOIN (LEFT, RIGHT, FULL) with examples.

ANS:

Multiway Join

Qn-12: What is multiway join? Explain with example.

ANS: Multiway Join:
A multiway join is a join in which three or more tables are joined together in a
single SQL query. In this type of join, one of the joined tables can itself participate in
another join. Thus, multiple join operations are combined to create a single joined table.
Multiway join is useful when data required for a query is stored in more than two related
tables.

Working of Multiway Join:

In a multiway join, SQL first joins two related tables based on a common
attribute, and then the result is joined with another table. This process can continue for
 more than three tables. As a result, information stored in different tables can be retrieved
together in one query.

Example:
Retrieve the project number, department number, manager’s last name, address,
and birth date.

SELECT Pnumber, Dnum, Lname, Address, Bdate

FROM ((PROJECT JOIN DEPARTMENT
ON Dnum = Dnumber)
JOIN EMPLOYEE
ON Mgr_ssn = Ssn);

Aggregate Functions
Qn-13: Explain aggregate functions (COUNT, SUM, AVG, MAX, MIN) with examples.
ANS:
Aggregate Functions in SQL:

Aggregate functions in SQL are used to summarize information from multiple

tuples into a single result value. They perform calculations on a set of values and return
one summary output. SQL provides built-in aggregate functions such as COUNT, SUM,
AVG, MAX, and MIN. These functions can be used in the SELECT clause and also
along with GROUP BY and HAVING clauses in SQL queries.
1. COUNT Function:
The COUNT function is used to count the number of tuples or values in a table. It
returns the total count of rows or distinct values specified in the query.
Example: Count the number of distinct salary values in the database
SELECT COUNT (DISTINCT Salary) FROM EMPLOYEE;
This query counts the number of different salary values present in the
EMPLOYEE table.

2. SUM Function:
The SUM function is used to calculate the total sum of numeric values in a
column.
Example: Find the sum of salaries of all employees
SELECT SUM (Salary) FROM EMPLOYEE;
This query returns the total salary paid to all employees in the EMPLOYEE table.

3. AVG Function:
The AVG function is used to calculate the average (mean) value of numeric data.
Example: Find the average salary of all employees
SELECT AVG (Salary) FROM EMPLOYEE;
This query returns the average salary of employees in the EMPLOYEE table.

4. MAX Function:
The MAX function is used to find the highest value in a column.
Example: Find the maximum salary of employees
SELECT MAX (Salary) FROM EMPLOYEE;
This query returns the highest salary among all employees.

5. MIN Function:
The MIN function is used to find the lowest value in a column.
Example: Find the minimum salary of employees
 SELECT MIN (Salary) FROM EMPLOYEE;
This query returns the lowest salary among all employees.

Combined Example:
SQL allows multiple aggregate functions in a single query.
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary) FROM
EMPLOYEE;
This query returns the total salary, highest salary, lowest salary, and average
salary of all employees in one result.

GROUP BY and HAVING

Qn-14: Explain GROUP BY clause and its working with examples.

ANS: GROUP BY Clause in SQL:
The GROUP BY clause in SQL is used to create subgroups of tuples before
summarization. It partitions a relation into groups of tuples that have the same value for
one or more attributes, called grouping attributes. After grouping, aggregate functions
such as COUNT, SUM, AVG, MAX, and MIN can be applied to each group separately.
Thus, GROUP BY helps in performing calculations on each subgroup rather than on the
whole table.

Working of GROUP BY Clause:

When SQL executes a query with GROUP BY, it first divides the table into
non-overlapping groups based on the specified grouping attribute. Then aggregate
functions are applied to each group, and a separate result is produced for every group.
The grouping attribute must usually appear in the SELECT clause along with the
aggregate function. If NULL values exist in the grouping attribute, SQL creates a
separate group for NULL values.
 Syntax:
SELECT grouping_attribute, aggregate_function(attribute)
FROM table_name GROUP BY grouping_attribute;

Example 1:
For each department, retrieve the department number, number of employees, and
average salary.
SELECT Dno, COUNT (*), AVG (Salary) FROM EMPLOYEE
GROUP BY Dno;
In this query, SQL groups employees according to department number (Dno).
Then for each department, SQL calculates:
 COUNT(*) → Number of employees in that department
 AVG(Salary) → Average salary of employees in that department
A separate result is displayed for each department.

Qn-15: Explain HAVING clause and differentiate it from WHERE with examples.
ANS: HAVING Clause in SQL:
The HAVING clause in SQL is used to specify a condition on the summary
information of groups created by the GROUP BY clause. It filters groups after aggregate
functions such as COUNT, SUM, AVG, MAX, and MIN have been applied. Only those
groups that satisfy the HAVING condition are included in the final result. Thus,
HAVING works on groups of tuples, whereas WHERE works on individual tuples.

Working of HAVING Clause:

When SQL executes a query containing HAVING, it first applies the WHERE
clause (if present) to select rows, then groups the rows using GROUP BY, applies
aggregate functions to each group, and finally checks the HAVING condition. Only the
groups satisfying the HAVING condition are displayed in the output.
 Syntax:
SELECT grouping_attribute, aggregate_function(attribute)
FROM table_name GROUP BY grouping_attribute HAVING condition;

Example of HAVING Clause:

For each project on which more than two employees work, retrieve the project
number, project name, and number of employees working on the project.
SELECT Pnumber, Pname, COUNT (*) FROM PROJECT, WORKS_ON
WHERE Pnumber = Pno GROUP BY Pnumber, Pname HAVING COUNT (*) > 2;

In this query:
 SQL first joins PROJECT and WORKS_ON tables.
 Records are grouped by Pnumber and Pname.
 COUNT(*) calculates number of employees in each project.
 HAVING COUNT(*) > 2 selects only those project groups where more than two
employees are working.

Difference between WHERE and HAVING:

WHERE Clause HAVING Clause

Used to filter individual rows before grouping Used to filter groups after grouping

Applied before GROUP BY Applied after GROUP BY

Cannot use aggregate functions directly Can use aggregate functions

Works on tuples Works on grouped data

Example
Using WHERE:
SELECT Dno, AVG(Salary) FROM EMPLOYEE WHERE Salary > 40000
GROUP BY Dno;
Here, WHERE Salary > 40000 filters employee rows first, and then grouping is done.
 Using HAVING:
SELECT Dno, AVG(Salary) FROM EMPLOYEE GROUP BY Dno
HAVING AVG(Salary) > 40000;

Here, grouping is done first, average salary is calculated for each department, and
then HAVING selects only those departments whose average salary is greater than
40000.

SQL Query Structure

Qn-17: Explain structure of SQL query and order of execution of clauses.

ANS: Structure of SQL Query:
A retrieval query in SQL can contain up to six clauses. Among them, only the
SELECT and FROM clauses are mandatory, while the remaining clauses are optional
depending on the requirement of the query. SQL queries may span multiple lines and are
terminated using a semicolon (;). The clauses must appear in a fixed order.

General Structure of SQL Query:

SELECT attribute_list
FROM table_list
WHERE condition
GROUP BY grouping_attributes
HAVING group_condition
ORDER BY attribute_list;

1. SELECT Clause:
The SELECT clause specifies the attributes or functions to be retrieved from the
database. It determines the columns displayed in the result.
2. FROM Clause:
The FROM clause specifies the relations (tables) required for the query. It may
also include joined tables.

3. WHERE Clause:
The WHERE clause specifies conditions for selecting tuples from the tables. It
may also contain join conditions. Only tuples satisfying the condition are selected.

4. GROUP BY Clause:
The GROUP BY clause divides tuples into groups based on common attribute
values. Aggregate functions are applied separately to each group.

5. HAVING Clause:
The HAVING clause specifies conditions on groups created by GROUP BY. It
filters grouped data after aggregate functions are applied.

6. ORDER BY Clause:
The ORDER BY clause arranges the final result in ascending or descending order
based on specified attributes.

Order of Execution of SQL Clauses:

Although clauses are written in one order, SQL conceptually evaluates them in
another order.
Execution Order:
1. FROM → identifies tables and joined tables
2. WHERE → selects required tuples
3. GROUP BY → creates groups
4. HAVING → filters groups
5. SELECT → retrieves required attributes/functions
6. ORDER BY → sorts the final result
Diagram:

Thus, an SQL query consists of clauses such as SELECT, FROM, WHERE,

GROUP BY, HAVING, and ORDER BY. These clauses have a fixed structure and are
executed conceptually in a specific order to retrieve the required result efficiently.
Assertions in SQL
Qn-18: What are assertions? Explain their syntax and usage with example.
ANS: Assertions in SQL:
Assertions in SQL are used to specify general constraints that cannot be expressed
using built-in relational model constraints. They are declarative constraints used to
maintain database consistency. Assertions are created using the CREATE ASSERTION
statement of SQL Data Definition Language (DDL). Each assertion is given a constraint
name and a condition that must always remain TRUE for every valid database state.

Syntax of Assertion:
The general syntax of assertion is:
CREATE ASSERTION <Name_of_assertion> CHECK (<condition>);
Here:
 CREATE ASSERTION → creates a new assertion constraint
 Name_of_assertion → name given to the assertion
 CHECK(condition) → specifies the condition that must always be TRUE
If the condition becomes FALSE, the assertion is violated.

Working of Assertions:
When an insert, update, or delete operation is performed on the database, SQL
checks whether the assertion condition is satisfied. If the condition remains TRUE, the
operation is allowed. If the condition becomes FALSE, the database rejects the operation
to maintain consistency. Assertions are generally written using conditions similar to the
WHERE clause and often use EXISTS or NOT EXISTS functions.

Example:
Constraint: Salary of an employee should not be greater than the salary of the manager
of that department.
CREATE ASSERTION SALARY_CONSTRAINT
CHECK (
NOT EXISTS (
 SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE [Link] > [Link]
AND [Link] = [Link]
AND D.Mgr_ssn = [Link]
)
);
In this assertion:
 The assertion name is SALARY_CONSTRAINT.
 The nested query checks employees whose salary is greater than their manager’s
salary.
 NOT EXISTS ensures that such tuples should not exist in the database.
 If any employee salary becomes greater than the manager’s salary, the assertion
condition becomes FALSE and the operation is rejected.

Uses of Assertions:
 Used to enforce complex database constraints.
 Helps maintain database consistency and correctness.
 Useful for conditions involving multiple tables.
 Can express constraints that cannot be handled by primary key, foreign key, or
CHECK constraints alone.

Thus, assertions in SQL are declarative constraints used to specify complex

conditions on the database. They are defined using CREATE ASSERTION and help
maintain database consistency by ensuring that specified conditions always remain
TRUE.
Triggers in SQL

Qn-19: Define trigger. Explain its components (Event, Condition, Action).

ANS: Trigger in SQL:
A trigger is a procedure that runs automatically whenever a specified event
occurs in the database management system. Triggers are used to perform automatic
actions when certain conditions are satisfied. SQL provides the CREATE TRIGGER
statement to define triggers. Triggers are useful for maintaining database consistency,
monitoring updates, and enforcing business rules automatically.

General Syntax of Trigger:

CREATE TRIGGER <name> BEFORE | AFTER <events>
FOR EACH ROW | FOR EACH STATEMENT WHEN (<condition>) <action>;

Components of Trigger:
A trigger mainly consists of three components:
1. Event:
An event specifies when the trigger should be activated. The trigger executes
automatically whenever the specified database event occurs. SQL supports three main
event types:
 INSERT → activated when new records are inserted
 UPDATE → activated when existing records are modified
 DELETE → activated when records are deleted
Triggers can execute either:
 BEFORE the event occurs
 AFTER the event occurs

Example:
CREATE TRIGGER ABC BEFORE INSERT ON STUDENTS
This trigger activates before a new record is inserted into the STUDENTS table.
2. Condition:
A condition is an optional part of a trigger. The trigger action executes only if the
specified condition becomes TRUE. If the condition is FALSE, the trigger is skipped.
Conditions are usually written using the WHEN clause.

Example:
WHEN ([Link] > 150000)
This condition checks whether the new salary value is greater than 150000.

3. Action:
The action specifies the operations performed when the event occurs and the
condition becomes TRUE. Actions are written inside the trigger body using SQL
statements.
Example:
BEGIN
[Link] = [Link] * 0.35;
END;
This action automatically calculates and stores the employee bonus as 35% of salary.

Example of Complete Trigger:

CREATE TRIGGER EmpBonus BEFORE INSERT OR UPDATE ON Employee
FOR EACH ROW
BEGIN
[Link] = [Link] * 0.35;
END;

 Event: INSERT or UPDATE on Employee table

 Condition: No explicit condition used
 Action: Automatically calculates bonus as 3% of salary before storing the record.
 Thus, a trigger is an automatic procedure executed when specific database events
occur. Its main components are Event, Condition, and Action, which together help in
maintaining database consistency and automating database operations.

Qn-20: Explain triggers with syntax and examples for INSERT, UPDATE and DELETE
operations.
ANS: Triggers in SQL:
A trigger is a procedure that executes automatically whenever a specified event
occurs in the database system. Triggers are activated by database operations such as
INSERT, UPDATE, and DELETE. They are mainly used for maintaining database
consistency, enforcing constraints, and performing automatic actions whenever data
changes occur. SQL provides the CREATE TRIGGER statement to define triggers.

General Syntax of Trigger:

CREATE TRIGGER <trigger_name> BEFORE | AFTER INSERT | UPDATE
DELETE ON <table_name> FOR EACH ROW
BEGIN
<trigger_action>
END;

1. INSERT Trigger:
An INSERT trigger executes automatically whenever a new tuple is inserted into a
table. It can execute either before or after insertion.
Example:
Automatically calculate bonus while inserting employee record.
CREATE TRIGGER Emp_Insert BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
BEGIN
[Link] = [Link] * 0.35;
END;
  Trigger activates before INSERT operation.
 Whenever a new employee record is inserted, the trigger automatically calculates
bonus as 35% of salary.

2. UPDATE Trigger:
An UPDATE trigger executes automatically whenever an existing tuple is
modified in a table. It is useful for checking changes and maintaining consistency.

Example:
Display old and new salary values whenever salary is updated.
CREATE TRIGGER Emp_Update BEFORE UPDATE OF Salary ON EMPLOYEE
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE ('Old Salary: ' || [Link]);
DBMS_OUTPUT.PUT_LINE ('New Salary: ' || [Link]);
END;
 Trigger activates before updating Salary attribute.
 [Link] stores previous salary value.
 [Link] stores updated salary value.
 Trigger displays both old and new salary values.

3. DELETE Trigger:
A DELETE trigger executes automatically whenever tuples are deleted from a
table. It can be used for maintaining backup or recording deleted information.
Example:
Store deleted employee details into backup table.
CREATE TRIGGER Emp_Delete BEFORE DELETE ON EMPLOYEE
FOR EACH ROW
BEGIN
INSERT INTO EMP_BACKUP
 VALUES (:[Link], :[Link], :[Link]);
END;

 Trigger activates before deleting employee record.

 The deleted employee information is stored in EMP_BACKUP table using old
values.
 This helps in maintaining backup records.

Qn-21: Differentiate between assertions and triggers.

ANS: Difference between Assertions and Triggers:
Assertions Triggers

Assertions specify conditions that must always Triggers automatically execute actions when
remain TRUE events occur

Defined using CREATE ASSERTION Defined using CREATE TRIGGER

Mainly used for automatic database

Mainly used for enforcing database constraints
operations

Do not perform actions automatically Execute actions automatically

Activated by INSERT, UPDATE, or

Checked whenever database is modified
DELETE events

Views in SQL
Qn-22: Define views. Explain concept, advantages and CREATE VIEW with example.
ANS: Views in SQL:
A view in SQL is a single virtual table derived from one or more base tables. The
view does not store data permanently; instead, it stores the query definition used to
retrieve data from the base tables. Therefore, a view behaves like a virtual table whose
contents are generated dynamically whenever the view is accessed. Views are created
using the CREATE VIEW statement.
Concept of View:
The main concept of a view is to provide users with a customized representation
of database data. A view may contain selected rows and columns from one or more
tables. Since views are virtual tables, changes made in the base tables are automatically
reflected in the view results. Views help simplify complex queries and provide security
by hiding unnecessary data from users.

Advantages of Views:
1. Security:
Views restrict users from accessing all columns and rows of a table. Users can see
only the required data through the view.
2. Simplicity:
Complex SQL queries can be simplified by creating views. Users can access data
using simple queries on views.
3. Data Independence:
Changes in base tables do not directly affect users working on views, thereby
providing logical data independence.
4. Customized Data:
Different users can be provided with different views according to their
requirements.

CREATE VIEW Statement:

Views are created using the CREATE VIEW command.
Syntax:
CREATE VIEW view_name AS SELECT attribute_list FROM table_name
WHERE condition;

Example:
Create a view containing employees working in Research department.
 CREATE VIEW RESEARCH_DEPT AS SELECT Fname, Lname, Salary
FROM EMPLOYEE, DEPARTMENT WHERE Dno = Dnumber
AND Dname = 'Research';

 The view name is RESEARCH_DEPT.

 It retrieves employee first name, last name, and salary.
 Only employees belonging to the Research department are included in the view.
 Whenever the base tables are updated, the view automatically reflects updated
data.

Thus, a view is a virtual table derived from one or more base tables, used for
security, simplicity, and customized data access. Views are created using the CREATE
VIEW statement and help users retrieve required information easily.

View Implementation & Updates

Qn-23: Explain view implementation techniques and issues in updating views.

ANS: View Implementation and View Updating:
A view is a virtual table derived from one or more base tables. Since views do not
normally store data physically, the DBMS must use special techniques to implement
views efficiently and keep them up-to-date. The implementation of views and updating
of views are important issues in SQL because a view may be defined on multiple tables,
joins, or aggregate functions.

View Implementation Techniques

The DBMS uses two main techniques for implementing views:
1. Query Modification Technique
In this approach, the query specified on the view is automatically transformed into
an equivalent query on the underlying base tables. The view itself is not stored
physically.
For example, if the user writes:
SELECT Fname, Lname FROM WORKS_ON1 WHERE Pname='ProductX';

The DBMS converts it into a query on the base tables:

SELECT Fname, Lname FROM EMPLOYEE, PROJECT, WORKS_ON

WHERE Ssn=Essn AND Pno=Pnumber AND Pname='ProductX';

Thus, the view definition is expanded and executed on the original tables
whenever the view is referenced.

Advantage:
 No extra storage is required for the view.
Disadvantage:
 Inefficient for complex views because the view query must be recomputed
every time it is accessed.

2. View Materialization Technique

In this approach, the DBMS physically creates and stores a temporary view table
when the view is first queried. Future queries can directly access this stored view instead
of repeatedly executing the underlying complex query.

To keep the materialized view up-to-date, the DBMS uses incremental update
techniques, which determine what tuples must be inserted, deleted, or modified
whenever the base tables change.
Advantage:
 Faster query processing for frequently used views.
Disadvantage:
 Additional storage is required.
 The view must be updated whenever base tables change.
Issues in Updating Views
Updating views is often difficult and ambiguous because the view may be derived
from multiple base tables.

1. Ambiguity in Multiple Table Views

For views created using joins of multiple tables, a single view update may
correspond to several possible updates on the base tables. The DBMS may not be able to
determine which update the user actually intends.

2. Aggregate Function Views

Views defined using aggregate functions such as SUM, COUNT, AVG, MAX,
and MIN are generally not updatable because it is not clear how changes should be
propagated to the underlying base tables.

3. Join Views
Views defined using joins of multiple tables are generally not updatable because a
modification in the view may require updates in more than one base table.

4. Single Table Views

A view based on a single table can be updated if it contains:
 Primary key of the base relation.
 All NOT NULL attributes that do not have default values.

5. WITH CHECK OPTION

SQL provides the WITH CHECK OPTION clause while defining a view. This
allows the DBMS to verify whether updates through the view are valid and maintain
consistency.
Schema Change Statements
Qn-24: Explain schema change statements (DROP, ALTER) with syntax and examples.
ANS: Schema Change Statements in SQL:
SQL provides schema evolution commands that allow modifications to the
database schema while the database is operational. These commands are used to add,
remove, or modify tables, attributes, constraints, and other schema elements without
recreating the entire database. The two important schema change statements are DROP
and ALTER.

1. DROP Command
The DROP command is used to remove named schema elements such as tables,
schemas, domains, views, and constraints from the database. When a table is dropped,
both its data and definition are removed from the database catalog.
Syntax:
DROP TABLE table_name CASCADE;
or
DROP TABLE table_name RESTRICT;

DROP Options:
 CASCADE: Removes the specified object along with all dependent objects such
as views and constraints.
 RESTRICT: Removes the object only if no other schema element depends on it.

Example 1: Drop a Table

DROP TABLE DEPENDENT CASCADE;
This command removes the DEPENDENT table and all dependent objects
associated with it.

Example 2: Drop a Schema

DROP SCHEMA COMPANY CASCADE;
 This command deletes the COMPANY schema along with all tables, domains,
and constraints contained in it.

2. ALTER Command
The ALTER command is used to modify the structure of existing tables or
schema elements. It supports operations such as adding columns, dropping columns,
changing column definitions, and modifying constraints.

A) Adding a Column
Syntax:
ALTER TABLE table_name ADD COLUMN column_name datatype;
Example:
ALTER TABLE [Link] ADD COLUMN Job VARCHAR(12);

This command adds a new attribute Job to the EMPLOYEE table.

B) Dropping a Column
Syntax:
ALTER TABLE table_name DROP COLUMN column_name CASCADE;
Example:
ALTER TABLE [Link] DROP COLUMN Address CASCADE;

This command removes the Address attribute from the EMPLOYEE table.

C) Modifying a Column Data Type

Syntax:
ALTER TABLE table_name MODIFY column_name datatype;
Example:
ALTER TABLE Persons ALTER COLUMN DateOfBirth YEAR;
 This command changes the data type of DateOfBirth column to YEAR.

Comparison of DROP and ALTER

DROP Command ALTER Command

Removes schema objects completely Modifies existing schema objects

Deletes table definition and data Retains table and data

Used when object is no longer required Used when structure needs modification

Example: ALTER TABLE ADD

Example: DROP TABLE
COLUMN

---------------------------------------------------------------END OF MODULE-4--------------------------------------------------------------