Database Management System

Module 4

1
 SQL &
SQL: Advanced Queries

2
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 MORE COMPLEX SQL QUERIES
Comparisons Involving NULL and Three-Valued Logic:

● NULL is used to represent a missing value that usually has one of the 3
different interpretations.
- value unknown (exists but is not known or it is not known whether a value
exists or not),
- value not available (exists but is purposely withheld), or
- attribute not applicable (undefined for this tuple).
Examples:
1. Unknown value: A particular person has a date of birth but it is not known, so it
is represented by NULL in the database.
2. Unavailable or withheld value: A person has a home phone but does not want it
to be listed, so it is withheld and represented as NULL in the database.
3. Not applicable attribute: An attribute LastCollegeDegree would be NULL for a
person who has no college degrees, because it does not apply to that person.

3
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 When a record with NULL is involved in a comparison operation, the result is considered
to be UNKNOWN (it may be TRUE or it may be FALSE). Hence, SQL uses a 3-valued
logic with values TRUE, FALSE and UNKNOWN.

4
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
5
 6
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
Nested Queries, Tuples, and Set / Multiset Comparisons

Some queries require that existing values in the database be fetched and then
used in a comparison condition.

Nested queries are complete select-from-where blocks within another SQL query.
That other query is called the outer query. The nested queries can also appear in
the WHERE clause of the FROM clause or other SQL clauses as needed.

The comparison operator IN compares a value v with a set (or multiset) of values
V and evaluates to TRUE if v is one of the elements in V.

7
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 8
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 9
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 10
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 11
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Correlated Nested Queries

12
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 The EXISTS and UNIQUE Functions in SQL

EXISTS and UNIQUE are Boolean functions that return TRUE or FALSE.

● They can be used in WHERE clause condition.

● The EXISTS function in SQL is used to check whether the result of a correlated
nested query is empty (contains no tuples) or not.
● The result of EXISTS is a Boolean value TRUE if the nested query result contains
atleast one tuple or FALSE if the nested query result contains no tuples.
● EXISTS and NOT EXISTS are typically used in conjunction with a correlated
nested query.
● EXISTS (Q) returns TRUE if there is atleast one tuple in the result of the nested
query Q and returns FALSE otherwise.
● NOT EXIST S (Q) returns TRUE if there are no tuples in the result of the nested
query Q and returns FALSE otherwise.

13
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 14
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 15
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 16
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Explicit sets and Renaming of Attributes in SQL

17
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 18
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 19
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Joined Tables in SQL

A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
20
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 21
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 22
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
23
24
25
26
27
 28
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 29
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Aggregate Functions in SQL

30
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 31
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 32
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 33
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Grouping: The GROUP BY and HAVING Clauses

34
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 35
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 36
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 37
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 38
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 SQL Constructs: WITH and CASE

39
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 40
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Recursive Queries in SQL
In DBMS (Database Management Systems), a recursive function—usually in the context of
recursive queries—is used to repeatedly execute a set of instructions or retrieve data that has a
hierarchical or self-referencing relationship.

41
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Employees
---------
ID | Name | ManagerID
---|----------|----------
1 | Alice | NULL
2 | Bob |1
3 | Charlie | 2
4 | Dave | 2

You can write a recursive query to find all employees under Alice:
WITH RECURSIVE EmployeeHierarchy AS (
-- Anchor member: start with the top-level manager
(Alice)
SELECT ID, Name, ManagerID
FROM Employees
WHERE ManagerID IS NULL

UNION ALL

-- Recursive member: get employees under current

level
SELECT [Link], [Link], [Link]
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON
[Link] = [Link]
)
42
SELECT * FROM EmployeeHierarchy;
 43
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Select SQL Statement

44
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 45
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 SPECIFYING CONSTRAINTS
AS ASSERTIONS AND
ACTIONS AS TRIGGERS

46
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Specifying General Constraints as Assertions in SQL

47
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 CREATE ASSERTION valid_salary
CHECK (
NOT EXISTS (
SELECT *
FROM Employees
WHERE Salary < 0
)
);

This would prevent any employee from having a negative salary.

48
 49
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 50
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
MySQL does NOT support CREATE ASSERTION.
Instead, MySQL relies on other methods to enforce data integrity, like:

CREATE TABLE Employees (

ID INT,
Salary DECIMAL(10,2),
CHECK (Salary >= 0)
);

51
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 52
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Trigger in SQL

A trigger in MySQL is a set of instructions (SQL code) that automatically runs (or "fires") in
response to a specific event on a table, like:

•Inserting a row (BEFORE INSERT / AFTER INSERT)

•Updating a row (BEFORE UPDATE / AFTER UPDATE)
•Deleting a row (BEFORE DELETE / AFTER DELETE)

53
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 54
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 55
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 56
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 VIEWS (VIRTUAL TABLES) IN SQL
Concept of a View in SQL

A VIEW in SQL is like a virtual table.

It’s not real data — it's a saved SQL query that looks and behaves like a table. You can
query it like a normal table, but it doesn't store data itself — it pulls data from real tables
dynamically.

57
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
Suppose you have a Customers table and an Orders table.
View to show customers with their total order
amount

CREATE VIEW CustomerOrderSummary AS c

SELECT
[Link],
[Link],
SUM([Link]) AS TotalSpent
FROM
Customers c
JOIN
Orders o ON [Link] = [Link]
GROUP BY
[Link], [Link];

Now, instead of writing the whole SQL again, you can just do:
sql

SELECT * FROM CustomerOrderSummary

WHERE TotalSpent > 1000;

58
 Specification of Views in SQL

59
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 60
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 61
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 View Implementation and View Update

62
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 63
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 64
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 65
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 66
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 67
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 68
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Views as Authorization Mechanisms

69
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 SCHEMA CHANGE STATEMENTS IN SQL
The DROP Command
Automatically propagates changes from parent to child.

70
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 ● If the RESTRICT option is chosen instead of CASCADE, a table is
dropped only if it is not referenced in any constraints (for example, by
foreign key definitions in another relation) or views. With the
CASCADE option, all such constraints and views that reference the
table are dropped automatically from the schema, along with the table
itself.

● The DROP command can also be used to drop other types of named
schema elements, such as constraints or domains.

● The DROP TABLE command not only deletes all the records in the
table if successful, but also removes the table definition from the
catalog.

71
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 The ALTER Command

72
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 73
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 74
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 THANK YOU

75
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT