[go: up one dir, main page]
Scribd
Upload
222 views24 pages

More Complex SQL Retrieval Queries

Uploaded by

SHRISHANTHA S
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
222 views24 pages

More Complex SQL Retrieval Queries

Uploaded by

SHRISHANTHA S
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 24

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,

You might also like