SQL
SQL – Structured Query Language
Parts of SQL:
DDL
DML
Views
Security & Authorization
Transaction Control
CREATE SCHEMA
Authorization Identifier – Who owns the Schema
Tables are generally created within the SQL Schema
Attributes are considered
To be ordered
In this order
PRIMARY KEY = NOT NULL + UNIQUE
Character String
Bit String
Boolean
Date
TImestamp
NOT NULL
DEFAULT
Composite
Primary Key
Can potentially
Act as a
Secondary / Alternate Key
Foreign Key
Syntax for [Link] you know already.
What about actions when referential integrity is violated ?
The default action is to “REJECT” the update operation.
Referential Triggered Action
Referential Triggered Action
This line is added at the end of the CREATE Table Command
Called as Schema Evolution Commands
Queries
Multisets vs Sets
To constrain a table to be a Set, use Key constraint or DISTINCT Command
One Level
Recursive
Query
E Subordinates
S Supervisor Tuple Variables / Aliases / Copies
You can Rename/Alias without Explicitly using the AS keyword
Its equivalent to Project Operation in RA
* Operator, returns all the Attributes
Why SQL Allows Duplicates ?
This ALL is typically not used,
But it is implicit
What if I want to eliminate Duplicates ?
Pattern Matching in SQL
What if I want to search for ‘%’ or ‘_’ itself
Usage of Arithmetic operators within Queries
Between Operator
Order By
Order By
Structure of a Simple Query
Mandatory Clauses
Optional Clauses
Delete Command
Update Command
MultiSets in SQL
A TABLE in SQL may have duplicates, if it has no Primary Key defined.
NULL In SQL
Unknown
Not Available / Unavailable
Not Applicable
NULL In SQL
Three Valued Logic in SQL
Nested Queries
Nested Queries
Nested Queries
Nested Queries
Nested Queries
Comparing Single Value to a Set of Values
Comparison operators in Nested Queries
The Inner Query is evaluated once for each row of the outer query
The EXISTS function in SQL is used to check whether the result of a correlated nested query
is empty (contains no tuples) or not.
If at least one tuple EXISTS in the result of the nested query, then select that EMPLOYEE tuple
If and only if “NO” Tuple EXISTS in the result of the nested query, then select that EMPLOYEE tuple
Selects Employees
With Dependents
Selects Employees
Who are managers
SELECT DISTINCT [Link], [Link]
FROM EMPLOYEE E, DEPARTMENT D, DEPENDENT DEP
WHERE [Link] = D.Mgr_ssn
AND [Link] = [Link];
For each EMPLOYEE tuple:
Retrieve the Essn values for all DEPENDENT tuples with the same sex
and name as that EMPLOYEE tuple;
If the Ssn value of the EMPLOYEE tuple matches with the any of the
Essn Value, then return the [Link] and [Link]
Aggregate functions are used to summarize information from multiple tuples into a
single-tuple summary.
What if someone’s salary is NULL, will it be counted ?
In general, NULL values are discarded when aggregate functions are applied
to a particular column (attribute).
This correlated nested query counts the number of dependents that each employee has;
if this is greater than or equal to two, the employee tuple is selected
Consider queries like:
Find average salary of each department.
Find the number of employees in each project.
Find the number of employees managed by each manager.
Note that: Select clause includes only the Grouping Attribute and the
Aggregate functions to be applied on each
Group of tuples
What if there were some employees whose Dno = NULL
WHERE helps to choose tuples to which the aggregate functions are applied.
HAVING clause serves to choose whole groups.
Not Physically Stored
Virtual Table
Base Tables – Stored Physically
VIEWS are not generally subjected to Updation.
VIEWS can be queried just like any other table.
Querying the VIEWS
VIEW Materialization
Updating the VIEWS
Assertions – Specifying General Constraints
The salary of an employee must not be greater than the salary of the manager of the department that the employee
works for.
SQL triggers are a critical feature in database management systems (DBMS) that
provide automatic execution of a set of SQL statements when specific database
events, such as INSERT, UPDATE, or DELETE operations, occur.
Triggers are commonly used to maintain data integrity, track changes,
and enforce business rules automatically, without needing manual input.
For example, a trigger can be invoked when a row is inserted into a specified table or when
specific table columns are updated