Structured%20Query%20Language

1
Structured Query Language
2
Basic Structure

• SQL is based on set and relational operations
  with certain modifications and enhancements.
• A typical SQL query has the form SELECT A1,
  A2, ..., An FROM r1, r2, ..., rm WHERE P
• Ais represent attributes
• ris represent relations
• P is a predicate.
• This query is equivalent to the relational
  algebra expression.
• ?A1, A2, ..., An(?P (r1 x r2 x ... x
  rm))
• The result of an SQL query is a relation.

3
The SELECT Clause

• The SELECT clause corresponds to the projection
  operation of the relational algebra. It is used
  to list the attributes desired in the result of a
  query.
• Query Retrieve the SSN values of the employees.
• SELECT SSN
• FROM EMPLOYEE
• In the pure relational algebra syntax, the
  query would be
• ?SSN(EMPLOYEE)
• An asterisk in the select clause denotes all
  attributes
• SELECT
• FROM EMPLOYEE

4
The SELECT Clause (Cont.)

• SQL allows duplicates in relations as well as in
  query results.
• To force the elimination of duplicates, insert
  the keyword DISTINCT after SELECT.
• Query Retrieve all distinct salary of employees.
• SELECT DISTINCT SALARY FROM EMPLOYEE
• The keyword ALL specifies that duplicates not be
  removed.
• Query Retrieve the salary of every employee.
• SELECT ALL SALARY FROM EMPLOYEE

5
The SELECT Clause (Cont.)

• The SELECT clause can contain arithmetic
  expressions involving the operation, , , ?, and
  /, and operating on constants or attributes of
  tuples.
• Query Show the resulting salaries if every
  employee is given a 10 percent raise.
• SELECT FNAME, LNAME, 1.1SALARY FROM
  EMPLOYEE
• would return the names of all employees and the
  attribute SALARY is multiplied by 110.

6
The WHERE Clause

• The WHERE clause corresponds to the selection
  predicate of the relational algebra. If consists
  of a predicate involving attributes of the
  relations that appear in the FROM clause.
• Query Retrieve the birth date and address of the
  employee(s) whose name is John B.
  Smith. SELECT BDATE, ADDRESS FROM
  EMPLOYEE WHERE FNAME John AND MINIT B
  AND LNAME Smith
• Comparison results can be combined using the
  logical connectives AND, OR, and NOT.
• Comparisons can be applied to results of
  arithmetic expressions.

7
The WHERE Clause (Cont.)

• SQL includes a BETWEEN comparison operator in
  order to simplify WHERE clauses that specify that
  a value be less than or equal to some value and
  greater than or equal to some other value.
• Query Retrieve all employees in department 5
  whose salary is between 30000 and 40000.
• SELECT FROM EMPLOYEE WHERE (SALARY
  BETWEEN 30000 AND 40000) AND DNO 5
• The condition (SALARY BETWEEN 30000 AND 40000) in
  the above query is equivalent to the condition
  (SALARY gt 30000 AND SALARY lt 40000).

8
The FROM Clause

• The FROM clause corresponds to the Cartesian
  product operation of the relational algebra. It
  lists the relations to be scanned in the
  evaluation of the expression.
• Query Find the Cartesian product EMPLOYEE X
  DEPARTMENT. SELECT ? FROM EMPLOYEE,
  DEPARTMENT
• Query Retrieve the name and address of all
  employees who work for the Research
  department. SELECT FNAME, LNAME, ADDRESS FROM
  EMPLOYEE, DEPARTMENT WHERE DNAMEResearch
  AND DNUMBERDNO
• The above query is similar to a
  SELECT-PROJECT-JOIN sequence of relational
  algebra where the condition DNAMEResearch is a
  selection condition and DNUMBERDNO is a join
  condition.

9
The RENAME Operation

• The SQL allows renaming relations and attributes
  using the AS clause
• old-name AS new-name
• Query Show the resulting salaries if every
  employee is given a 10 percent raise.
• SELECT FNAME, LNAME,
• 1.1SALARY AS INCREASED_SALARY
• FROM EMPLOYEE

10
Tuple Variables

• Tuple variables are defined in the FROM clause
  via the use of the AS clause.
• Query For each employee, retrieve the employees
  first and last name and the first and last name
  of his or her immediate supervisor.
• SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
• FROM EMPLOYEE AS E, EMPLOYEE AS
  S WHERE E.SUPERSSNS.SSN
• We can think of E and S as two different copies
  of the EMPLOYEE relation. It is possible to
  rename the relation attributes
• EMPLOYEE AS E(FN, MI, LN, SSN, BD, ADDR, SEX,
  SAL, SSN, DNO)

11
String Operations

• SQL includes a string-matching operator for
  comparisons on character strings. Patterns are
  described using two special characters
• percent (). The character matches any
  substring.
• underscore (_). The _ character matches any
  character.
• Query Retrieve all employees whose address is in
  Houston, Texas.
• SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDR
  ESS LIKE Houston, TX
• Query Find all employees who were born during
  the 1950s.
• SELECT FNAME, LNAME FROM EMPLOYEE WHERE BDAT
  E LIKE 195_ _ _ _ _ _ _

12
String Operations

• Match the name AB_CDEF
• LIKE AB\_CD\EF ESCAPE \
• SQL supports a variety of string operations such
  as
• concatenation (using )
• converting from upper to lower case (and vice
  versa)
• finding string length, extracting substrings, etc.

13
Ordering the Display of Tuples

• Query Retrieve a list of employees and the
  projects they are working on, ordered by
  department and within each department, ordered
  alphabetically by last name, first name.
• SELECT DISTINCT DNAME, LNAME, FNAME, PNAMEFROM
  DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECTWHERE
  DNUMBERDNO AND SSNESSN AND PNOPNUMBER
• ORDER BY DNAME, LNAME, FNAME
• We may specify DESC for descending order or ASC
  for ascending order, for each attribute
  ascending order is the default.
• E.g. ORDER BY DNAME DESC, LNAME ASC, FNAME ASC

14
Set Operations

• The set operations UNION, INTERSECT, and EXCEPT
  operate on relations and correspond to the
  relational algebra operations ????????
• Each of the above operations automatically
  eliminates duplicates to retain all duplicates
  use the corresponding multiset versions UNION
  ALL, INTERSECT ALL and EXCEPT ALL.Suppose a
  tuple occurs m times in r and n times in s, then,
  it occurs
• m n times in r UNION ALL s
• min(m,n) times in r INTERSECT ALL s
• max(0, m n) times in r EXCEPT ALL s

15
Set Operations

• Query Make a list of all project numbers for
  projects that involve an employee whose last name
  is Smith, either as a worker or as a manager of
  the department that controls the project.
• (SELECT DISTINCT PNUMBER
• FROM PROJECT, DEPARTMENT, EMPLOYEE
• WHERE DNUMDNUMBER AND MGRSSNSSN AND
  LNAMESmith)UNION
• (SELECT DISTINCT PNUMBER
• FROM PROJECT, WORKS_ON, EMPLOYEE
• WHERE PNUMBERPNO AND ESSNSSN AND
  LNAMESmith)

16
Aggregate Functions

• These functions operate on the multiset of values
  of a column of a relation, and return a value.
• AVG average value MIN minimum value MAX
  maximum value SUM sum of values COUNT
  number of values

17
Aggregate Functions (Cont.)

• Query Find the sum of the salaries of all
  employees, the maximum salary, the minimum
  salary, and the average.
• SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY),
  AVG(SALARY), FROM EMPLOYEE
• Query Retrieve the total number of employees in
  the research department SELECT COUNT ()FROM
  EMPLOYEE, DEPARTMENT
• WHERE DNODNUMBER AND DNAMEResearch
• Query Count the number of distinct salary values
  in the database.
• SELECT COUNT (DISTINCT SALARY)FROM EMPLOYEE

18
Aggregate Functions GROUP BY

• Query For each department, retrieve the
  department number, the number of employees in the
  department and their salary.
• SELECT DNO, COUNT(), AVG(SALARY) FROM
  EMPLOYEE GROUP BY DNO
• Note Attributes in SELECT clause outside of
  aggregate functions must appear in GROUP BY list.
• Query For each project retrieve the project
  number, the project name, and the number of
  employees who work on that project.
• SELECT PNUMBER, PNAME, COUNT() FROM
  PROJECT, WORKS_ON
• WHERE PNUMBERPNO GROUP BY PNUMBER, PNAME

19
Aggregate Functions HAVING Clause

• Query For each project on which more than two
  employees work, retrieve the project number, the
  project name, and the number of employees who
  work on the project.
• SELECT PNUMBER, PNAME, COUNT() FROM
  PROJECT, WORKS_ON
• WHERE PNUMBERPNO
• GROUP BY PNUMBER, PNAME
• HAVING COUNT() gt 2
• Note predicates in the HAVING clause are
  applied after the formation of groups whereas
  predicates in the WHERE clause are applied before
  forming groups.

20
NULL Values

• It is possible for tuples to have a null value,
  denoted by NULL, for some of their attributes.
• NULL signifies an unknown value or that a value
  does not exist.
• The predicate is NULL can be used to check for
  null values.
• Query Retrieve the names of all employees who do
  not have supervisors.
• SELECT FNAME, LNAME FROM EMPLOYEE WHERE
  SUPERSSN is NULL
• The result of any arithmetic expression involving
  NULL is NULL
• E.g. 5 NULL returns NULL.
• All aggregate operations except COUNT() ignore
  tuples with null values on the aggregated
  attributes.

21
NULL Values and Three Valued Logic

• Any comparison with NULL returns UNKNOWN
• E.g. 5 lt NULL or NULL ltgt NULL or NULL
  NULL
• Three-valued logic using the truth value UNKNOWN
• OR (UNKNOWN OR TRUE) TRUE,
• (UNKNOWN OR FALSE) UNKNOWN (UNKNOWN
  OR UNKNOWN) UNKNOWN
• AND (TRUE AND UNKNOWN) UNKNOWN,
• (FALSE AND UNKNOWN) FALSE,
  (UNKNOWN AND UNKNOWN) UNKNOWN
• NOT (NOT UNKNOWN) UNKNOWN
• P is UNKNOWN evaluates to true if predicate P
  evaluates to UNKNOWN
• Result of WHERE clause predicate is treated as
  FALSE if it evaluates to UNKNOWN.

22
Nested Subqueries

• SQL provides a mechanism for the nesting of
  subqueries.
• A subquery is a SELECT-FROM-WHERE expression that
  is nested within another query.
• A common use of subqueries is to perform tests
  for set membership, set comparisons, and set
  cardinality.

23
Example Query

• Query 1 Retrieve the name and address of all
  employees who work for the 'Research' department.
• SELECT FNAME, LNAME, ADDRESS
• FROM EMPLOYEE
• WHERE DNO IN (SELECT DNUMBER
• FROM DEPARTMENT
• WHERE DNAME'Research' )
• The nested query selects the number of the
  'Research' department.
• The outer query select an EMPLOYEE tuple if its
  DNO value is in the result of either nested
  query.
• The comparison operator IN compares a value v
  with a set of values V, and evaluates to TRUE if
  v is one of the elements in V.

24
Example Query

• Query Find the SSN of all employees who work the
  same (project, hours) combination on some project
  that employee whose SSN123456789 works on.
• SELECT DISTINCT ESSN FROM WORKS_ON WHERE
  (PNO, HOURS) IN (SELECT PNO, HOURS
  
  FROM WORKS_ON
• WHERE SSN123456789)

25
Nested Subqueries

• Correlated nested queries
• If a condition in the WHERE-clause of a nested
  query references an attribute of a relation
  declared in the outer query , the two queries are
  said to be correlated.
• The result of a correlated nested query is
  different for each tuple (or combination of
  tuples) of the relation(s) the outer query.

26
Example Query

• Query Retrieve the name of each employee who has
  a dependent with the same first name as the
  employee.
• SELECT E.FNAME, E.LNAME
• FROM EMPLOYEE AS E
• WHERE E.SSN IN (SELECT ESSN
• FROM DEPENDENT
• WHERE ESSNE.SSN AND
• E.FNAMEDEPENDENT_NAME)
• The nested query has a different result for each
  tuple in the outer query.

27
Example Query

• A query written with nested SELECT... FROM...
  WHERE... blocks and using the or IN comparison
  operators can always be expressed as a single
  block query. The formulation above is simply to
  illustrate SQL features. For example, the
  previous query can be written in a much simpler
  manner
• SELECT E.FNAME, E.LNAME
• FROM EMPLOYEE E, DEPENDENT D
• WHERE E.SSND.ESSN AND E.FNAMED.DEPENDENT
  _NAME

28
Definition of SOME Clause

• F ltcompgt SOME r ????t ??r? s.t. (F ltcompgt
  t)Where ltcompgt can be ?????????????

(5lt SOME
) true
(read 5 lt some tuple in the relation)
0
) false
(5lt SOME
5
0
) true
(5 SOME
5
0
(5 ? SOME
) true (since 0 ? 5)
5
( SOME) ? IN However, (? SOME) ? NOT IN
29
Definition of ALL Clause

• F ltcompgt ALL r ????t ??r? (F ltcompgt t)

(5lt ALL
) false
6
) true
(5lt ALL
10
4
) false
(5 ALL
5
4
(5 ? ALL
) true (since 5 ? 4 and 5 ? 6)
6
(? ALL) ? NOT IN However, ( ALL) ? IN
30
Set Comparison

• Query Return the names of employees whose salary
  is greater than the salary of all the employees
  in department 5.
• SELECT LNAME, FNAME FROM EMPLOYEE WHERE
  SALARY gt ALL (SELECT SALARY FROM
  EMPLOYEE WHERE DNO 5)

31
Test for Empty Relations

• The EXISTS construct returns the value TRUE if
  the argument subquery is nonempty.
• EXISTS r ?? r ? Ø
• NOT EXISTS r ?? r Ø

32
Example Query

• Query Retrieve the name of each employee who has
  a dependent with the first name as the employee.
• SELECT E.LNAME, E.FNAME FROM EMPLOYEE AS
  E
• WHERE EXISTS (SELECT FROM
  DEPENDENT WHERE E.SSN
  ESSN AND E.FNAMEDEPENDENT_NAME)
• Query Retrieve the name of each employee who has
  no dependent.
• SELECT E.LNAME, E.FNAME FROM EMPLOYEE AS
  E
• WHERE NOT EXISTS (SELECT FROM
  DEPENDENT WHERE E.SSN
  ESSN)

33
Example Query

• Query Retrieve the name of each employee who
  works on all the projects controlled by
  department 5.
• SELECT FNAME, LNAME FROM EMPLOYEE WHERE
  NOT EXISTS ( (SELECT PNUMBER FROM
  PROJECT WHERE DNUM5
• EXCEPT (SELECT PNO FROM
  WORKS_ON WHERE SSNESSN))
• Note that X Y Ø ? X?? Y
• Note Cannot write this query using ALL and its
  variants.

34
Summary of SQL Queries

• A query in SQL can consist of up to six clauses,
  but only the first two, SELECT and FROM, are
  mandatory. The clauses are specified in the
  following order
• SELECT ltattribute listgt
• FROM lttable listgt
• WHERE ltconditiongt
• GROUP BY ltgrouping attribute(s)gt
• HAVING ltgroup conditiongt
• ORDER BY ltattribute listgt
• The SELECT-clause lists the attributes or
  functions to be retrieved
• The FROM-clause specifies all relations (or
  aliases) needed in the query but not those needed
  in nested queries
• The WHERE-clause specifies the conditions for
  selection and join of tuples from the relations
  specified in the FROM-clause
• GROUP BY specifies grouping attributes
• HAVING specifies a condition for selection of
  groups
• ORDER BY specifies an order for displaying the
  result of a query
• A query is evaluated by first applying the
  WHERE-clause, then GROUP BY and HAVING, and
  finally the SELECT-clause.