Database Management System

Module 4

1
 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.

2
© 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.

3
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 4
© 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.

5
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 6
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 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
 Correlated Nested Queries

10
© 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.

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

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

18
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 19
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 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
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Aggregate Functions in SQL

24
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 25
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 26
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 27
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Grouping: The GROUP BY and HAVING Clauses

28
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 29
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 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
 SQL Constructs: WITH and CASE

33
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 34
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Recursive Queries in SQL

35
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 36
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Select SQL Statement

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

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

40
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 41
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 42
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 43
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 44
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Trigger in SQL

45
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 46
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 47
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 48
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 VIEWS (VIRTUAL TABLES) IN SQL
Concept of a View in SQL

49
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Specification of Views in SQL

50
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 51
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 52
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 View Implementation and View Update

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
 57
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 58
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 59
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 Views as Authorization Mechanisms

60
© Dr. Archana Bhat, Asst. Prof., Dept. of AI & ML, BMSIT
 SCHEMA CHANGE STATEMENTS IN SQL
The DROP Command

61
© 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.

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

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
 THANK YOU

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