More Complex SQL Retrieval Queries
https://www.brainkart.com/article/More-Complex-SQL-Retrieval-Queri
es_11415/
http://masud.lecture.ub.ac.id/files/2018/05/SQL2.pdf
1. Comparisons Involving NULL and Three-Valued Logic
• SQL has various rules for dealing with NULL values.
• NULL is used to represent a missing value, but that it usually has one of three different interpretations
• value unknown (exists but is not known)
• Unknown value. A person’s date of birth is not known, so it is represented by NULL in the database.
• value not available (exists but is purposely withheld)
• 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.
• value not applicable (the attribute is undefined for this tuple).
• 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
• When a 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 three-valued logic with values TRUE, FALSE, and UNKNOWN instead of the standard
two-valued (Boolean) logic with values TRUE or FALSE
• SQL allows queries that check whether an attribute value is NULL
• Rather than using = or <> to compare an attribute value to NULL, SQL uses the comparison operators IS or IS
NOT
• Retrieve the names of all employees who do not have supervisors.
• SELECT Fname, Lname FROM EMPLOYEE WHERE Super_ssn IS NULL;
2. 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
• which are complete select-from-where blocks within the WHERE clause of another query.
• That other query is called the outer query.
• Introduces the comparison operator IN, which compares a value v with a set (or multiset) of values V
evaluates to TRUE if v is one of the elements in V.
Queries
• Retrieve the name and address of all employees who work for the
'Research' department.
• Retrieve the name of each employee who has a dependent with the
same first name as the employee.
• The EXISTS function in SQL is used to check whether a subquery returns any rows.
• It returns a Boolean value, either TRUE if the subquery returns one or more rows,
or FALSE if the subquery does not return any rows.
• Suppose you have a database with two tables: Customers and Orders.
• Query -You want to find all customers who have placed at least one order
SELECT CustomerID, CustomerName
FROM Customers
WHERE EXISTS (
SELECT 1
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID );
SQL JOIN
• A JOIN clause is used to combine rows from two or more
tables, based on a related column between them.
Inner JOIN
CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, EmployeeName VARCHAR(50),
DepartmentID INT );
CREATE TABLE Department ( DepartmentID INT PRIMARY KEY, DepartmentName VARCHAR(50) );
Query QJ1: Retrieve the employee names with the names of the department they work for
LEFT OUTER JOIN
• Query QJ2: Retrieve the employee names with the names of the
department they work for; every department must appear in the
result even if it has no employees
RIGHT OUTER JOIN
Query QJ3: Retrieve the employee names with the names of the
department they work for; every employee must appear in the result
even they are not currently assigned to a department
FULL OUTER JOIN
• Query QJ4: Retrieve the employee names with the names of the
department they work for; every employee and every department
must appear in the result
Aggregate Functions
• Include COUNT, SUM, MAX, MIN, and AVG
• These can summarize information from multiple tuples into a single
tuple
• Query:Find the maximum salary, the minimum salary, and the
average salary among all employees.
Aggregate Functions
• Query: Find the maximum salary, the minimum salary, and the
average salary among employees who work for the 'Research'
department.
• Find the maximum salary, the minimum salary, and the average salary
among employees who work for the 'Research' department
Aggregate Functions
• Query: Retrieve the total number of employees in the company
• Query: the number of employees in the 'Research' department
Grouping (Partitioning Records into Subgroups)
• In many cases, we want to apply the aggregate functions to
subgroups of tuples in a relation
• Each subgroup of tuples consists of the set of tuples that have the
same value for the grouping attribute(s) –
• for example, employees who work in the same department (have the
same DNO)
• SQL has a GROUP BY-clause for specifying the grouping attributes,
which must also appear in the SELECT-clause
Grouping (Partitioning Records into Subgroups)
• For each department, retrieve the department number, the number
of employees in the department, and their average salary.
• In above query the EMPLOYEE tuples are divided into groups-
• Each group has same value for the grouping attribute DNO
Grouping (Partitioning Records into Subgroups)
Query : For each project, retrieve the project number, project name,
and the number of employees who work on that project
The HAVING-clause
• Sometimes we want to retrieve the values of these aggregate
functions for only those groups that satisfy certain conditions
• The HAVING-clause is used for specifying a selection condition on
groups (rather than on individual tuples)
• Query : For each project on which more than two employees work,
retrieve the project number, project name, and the number of
employees who work on that project
• The original SQL as specified for SYSTEM R also had a CONTAINS
comparison operator, which is used in conjunction with nested
correlated queries
• The CONTAINS operator compares two sets of values, and returns
TRUE if one set contains all values in the other set
• The first nested query selects the project numbers of projects that have
an employee with last name ‘Smith’ involved as manager,
