Unit - II
Structured Query
Language
UNIQUE
Other SQL Constructs: WITH and CASE
Recursive Queries in SQL
Specifying General Constraints as Assertions
in SQL
GeeksforGeeks
W3Schools
Stackoverflow
UNIQUE
in
SQL
UNIQUE in SQL
Unique constraint in SQL is used to check whether the
sub query has duplicate tuples in it’s result.
It returns a boolean value indicating the presence /
absence of duplicate tuples.
Unique construct returns true only if the sub query has
no duplicate tuples, else it return false.
Evaluates to true on an empty sub query.
Returns true only if there are unique tuples present as the output
of the sub query (two tuples are unique if the value of any attribute
of the two tuples differ).
Returns true if the sub query has two duplicate rows with at least
one attribute as NULL.
UNIQUE in SQL - Syntax
SELECT table.ID
FROM table
WHERE UNIQUE (SELECT table2.ID
FROM table2
WHERE table.ID = table2.ID);
UNIQUE in SQL - Example
Instructor
UNIQUE in SQL - Example
Find all the instructors who taught at most one course in the year 2017.
SELECT I.EMPLOYEEID, I.NAME
FROM Instructor as I
WHERE UNIQUE
(SELECT Inst.EMPLOYEEID
FROM Instructor as Inst
WHERE
I.EMPLOYEEID = Inst.EMPLOYEEID and Inst.YEAR = 2017);
Other SQL Constructs: WITH
and CASE
Other SQL Constructs: CASE
Control statements - most languages - they
control the execution of other sets of statements.
These are found in SQL too
Query filtering and query optimization
The Case-Switch statement in SQL.
Other SQL Constructs: CASE
1. The first takes a variable called case_value and
matches it with some statement_list.
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Other SQL Constructs: CASE
2. The second considers a search_condition
instead of variable equality and executes the
statement_list accordingly.
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN
statement_list] ...
[ELSE statement_list]
END CASE
CASE - Example
Modify this table such that if the department name is ‘CS’, it gets
modified to ‘Computer Science’, if it is ‘EC’ it gets modified to
‘Electronics and Communication’, and if it is ‘HSS’ it gets modified
to ‘Humanities and Social Sciences’.
CASE - Example
CASE department_name
WHEN 'CS‘ THEN
UPDATE Faculty SET department='Computer
Science';
WHEN 'EC‘ THEN
UPDATE Faculty SET department='Electronics
and Communication';
ELSE
UPDATE Faculty SET department='Humanities
and Social Sciences';
END CASE
CASE - Example
SELECT FacultyID, Name, Department,
CASE Gender
WHEN'M' THEN 'Male‘
WHEN'F' THEN 'Female'
END
FROM Faculty;
Other SQL Constructs: WITH
Other SQL Constructs: WITH
The SQL WITH clause was introduced by Oracle in the
Oracle 9i .
The SQL WITH clause allows you to give a sub-query
block a name (also called sub-query refactoring), which
can be referenced within the main SQL query.
The clause is used for defining a temporary relation such
that the output of this temporary relation is available and
is used by the query that is associated with the WITH
clause.
WITH - Syntax
WITH temporaryTable (averageValue) as
(SELECT avg(Attr1) FROM Table),
SELECT Attr1 FROM Table
WHERE
Table.Attr1 > temporaryTable.averageValue;
WITH clause is used to define a temporary relation temporaryTable
That has only 1 attribute averageValue.
averageValue holds the average value of column Attr1 described in
Table.
The SELECT statement that follows the WITH clause will produce only
those tuples where the value of Attr1 in relation Table is greater than
the average value obtained from the WITH clause statement.
Example - Employee
Find all the employee whose salary is more
than the average salary of all employees.
Example
WITH temporaryTable(averageValue) as
(SELECT avg(Salary) from Employee)
SELECT EmployeeID,Name, Salary FROM
Employee, temporaryTable
WHERE
Employee.Salary >
temporaryTable.averageValue;
The average salary of all employees is 70591
Recursive Queries in SQL
Recursive Queries in SQL
This syntax was added in SQL:99 to allow users the
capability to specify a recursive query in a declarative
manner.
An example of a recursive relationship between
tuples of the same type is the relationship between an
employee and a supervisor.
This relationship is described by the foreign key
Super_ssn of the EMPLOYEE
It relates each employee tuple (in the role of supervisee)
to another employee tuple (in the role of supervisor).
Recursive Queries in SQL
An example of a recursive operation is to retrieve all
supervisees of a supervisory employee e at all levels—
that is,
all employees e′ directly supervised by e,
all employees e′’ directly supervised by each employee e
′,
all employees e″′ directly supervised by each employee e
″, and so on.
Recursive Queries in SQL
An example of a recursive operation is to retrieve all
supervisees of a supervisory employee at all levels
WITH RECURSIVE SUP_EMP (SupSsn, EmpSsn) AS
(SELECT SupervisorSsn, Ssn
FROM EMPLOYEE
UNION
SELECT E.Ssn, S.SupSsn
FROM EMPLOYEE AS E, SUP_EMP AS S
WHERE E.SupervisorSsn = S.EmpSsn)
SELECT * FROM SUP_EMP;
Recursive Queries in SQL
Define a view SUP_EMP that will hold the result of the recursive query.
The view is initially empty.
It is first loaded with the first level (supervisor, supervisee) Ssn combinations
via the first part (SELECT SupervisorSss, Ssn FROM EMPLOYEE), which
is called the base query.
This will be combined via UNION with each successive level of
supervisees through the second part, where the view contents are joined
again with the base values to get the second level combinations, which are
UNIONed with the first level.
This is repeated with successive levels until a fixed point is reached,
where no more tuples are added to the view.
At this point, the result of the recursive query is in the view SUP_EMP.
Recursive Queries in SQL
Reference:
http://www.dbta.com/Columns/DBA-Corner/An-Introduction-to-
Recursive-SQL-96878.aspx
Specifying General Constraints as
Assertions in SQL
Specifying General Constraints as
Assertions in SQL
CREATE ASSERTION, which can be used to specify
additional types of constraints that are outside the scope
of the built-in relational model constraints
Built-in constraints can be specified within the CREATE
TABLE statement of SQL.
Specifying General Constraints as
Assertions in SQL
In SQL, users can specify general constraints— via
declarative assertions, using the CREATE
ASSERTION statement.
Each assertion is given a constraint name and is
specified via a condition similar to the WHERE clause of
an SQL query.
For example,
to specify the constraint that
the salary of an employee must not be greater than
the salary of the manager of the department that the
employee works for
Specifying General Constraints as
Assertions in SQL
CREATE ASSERTION
SALARY_CONSTRAINT
CHECK ( NOT EXISTS ( SELECT *
FROM EMPLOYEE E, EMPLOYEE M,
DEPARTMENT D
WHERE E.Salary>M.Salary
AND E.Dno = D.Dnumber
AND D.Mgr_ssn = M.Ssn ) );
Other SQL Constructs: WITH and CASE
Recursive Queries in SQL
Specifying General Constraints as
Assertions in SQL
