Lecture 91: SQL DML

1
Lecture 9-1 SQL (DML)

• SQL Background
• SQL Features
• Basic Queries in SQL
• More Complex SQL Queries
• Data Definition in SQL
• Insert/Delete/Update
• Views/Assertions/Triggers

2
SQL Background

• SQL
• Structured Query Language
• the standard query language for commercial
  relational DBMSs
• advantages of having a standard portability,
  compatibility, training,
• disadvantages creativity,
• a DBMS may have its own extensions

3
SQL Background

• SQL History
• SEQUEL at IBM (1974)
• IMB DB2 SQL (1983)
• SQL-86 (SQL1) first standard version by ANSI
• SQL-89 more embedded SQL features
• SQL-92 (SQL2) current ANSI standard, more
  DDL/DML features
• SQL3 (in progress) object-oriented concepts

4
SQL vs. Relational Algebra

• Relational Algebra
• is procedural (a series of operations)
• specifies how to execute a query
• used for query processing and optimization
• SQL
• is declarative (higher-level language)
• specifies what the result is to be
• more user-friendly syntax

5
SQL Features

• Comprehensive DB Language
• can create a relational DB schema
• can create/modify/delete tables
• can extract information from the DB (query)
• can define views on the database
• can specify constraints, security, authorization
• can be embedded in a general-purpose programming
  language (C, PASCAL, Java)

6
SQL Features

• As Data Definition Language (DDL)
• to declare database schemas
• VDL (View Definition L.) external schema
• SDL (Storage Definition L.) internal schema
• As Data Manipulation Language (DML)
• to modify and query database states
• high-level (describing which data to retrieve)
  not low-level (describing how to retrieve it)

7
Basic Queries in SQL

• Query Syntax (SELECT Statement)
• SELECT ltattribute listgt
• FROM lttable listgt
• WHERE ltconditiongt
• Definition
• SELECT specifies attributes to be selected
• FROM specifies tables to be accessed
• WHERE specifies conditions for choosing a subset
  of tuples

8
Basic Queries in SQL

• SQL Query Example
• SELECT FNAME, LNAME
• FROM EMPLOYEE
• WHERE DNO 4
• SQL Query Characteristics
• corresponding Relational Algebra expression
  RESULT ? ?LNAME,FNAME(?DNO4(EMPLOYEE))
• SQL query result is not a mathematical set (may
  have duplicate tuples)

9
Basic Queries in SQL

• SELECT SALARY
• FROM EMPLOYEE
• WHERE DNO 4
• Example 1
• Q1) retrieve the name and address of all
  employees who work for the Research Dept.
• SELECT FNAME, LNAME, ADDRESS
• FROM DEPARTMENT, EMPLOYEE
• WHERE DNAMEResearch AND DNUMBERDNO

10
Basic Queries in SQL

• Example 1 (cont)
• Relational Algebra (a) for Q1 D_E?DEPARTMENT(DNU
  MBER),(DNO)EMPLOYEE Result??LNAME,FNAME,ADDRESS(?D
  NAMERESEARCH(D_E))
• Relational Algebra (b) for Q1
• RESEARCH(DNO) ? ?DNUMBER (?DNAMERESEARCH
  (DEPARTMENT))
• RESULT ? ?LNAME,FNAME,ADDRESS (RESEARCH
  EMPLOYEE)

11
Basic Queries in SQL

• Example 2
• Q2) for every project located in Houston, list
  the project , the controlling department , and
  the department managers last name
• SELECT PNUMBER, DNUM, LNAME
• FROM PROJECT, DEPARTMENT, EMPLOYEE   
• WHERE PLOCATIONHouston AND
  DNUMDNUMBER AND MGRSSNSSN

12
Basic Queries in SQL

• Example 2 (cont)
• Relational Algebra for Q2
• HOUSTON(PNO, DNUMBER) ? ?PNUMBER, DNUM
  (?PLOCATIONHOUSTON(PROJECT))
• H_DEPT(PNO,DNUM,SSN) ? ?PNO,DNUMBER,MGRSSN
  (HOUSTON DEPARTMENT)
• RESULT ? ?PNO,DNUM,LNAME (H_DEPT EMPLOYEE)

13
Basic Queries in SQL

• Qualifying Attributes
• qualify attributes by dot notation
• format table.attribute
• Q1) retrieve the name and address of all
  employees who work for the Research Dept.
• SELECT FNAME, LNAME, ADDRESS
• FROM DEPARTMENT, EMPLOYEE
• WHERE DNAMEResearch AND
  DEPARTMENT.DNUMBEREMPLOYEE.DNO

14
Basic Queries in SQL

• Relation Renaming
• format relation_name AS new_name (FROM)
• Q3) for each employee, retrieve the employees
  first and last name and the first and last name
  of his immediate supervisor
• hint qualify attributes by renaming
• SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME 
• FROM EMPLOYEE AS E, EMPLOYEE AS S   
• WHERE E.SUPERSSNS.SSN

15
Basic Queries in SQL

• Relation Renaming (cont)
• Relational Algebra for Q3
• SUPERVISOR(SUPERSSN,SFN,SLN) ?
  ?SSN,FNAME,LNAME(EMPLOYEE)
• RESULT ? ?FNAME, LNAME,SFN,SLN(EMPLOYEE
  SUPERVISOR)

16
Basic Queries in SQL

• Attribute Renaming
• format new_name (new attribute name list)
• Q3)
• SELECT EFN, ELN, SFN, SLN 
• FROM EMPLOYEE AS E(EFN, ELN, ESSN, EBD,
  ESEX, ESALARY, ESSSN, EDNO),
• EMPLOYEE  AS S(SFN, SLN, SSSN, SBD,
  SSEX, SSALARY, SSSSN, SDNO)   
• WHERE ESSSNSSSN

17
Basic Queries in SQL

• Unspecified WHERE
• no condition is applied to SELECT
• SELECT SSN, DNAME
• FROM EMPLOYEE, DEPARTMENT
• All attributes as
• SELECT
• FROM EMPLOYEE
• WHERE DNO5

18
Basic Queries in SQL

• Tables as Sets in SQL
• to eliminate duplicate tuples
• SELECT DISTINCT SALARY
• FROM EMPLOYEE
• WHERE DNO5
• default is ALL
• SELECT ALL SALARY
• FROM EMPLOYEE
• WHERE DNO5

19
Basic Queries in SQL

• Set Operations in SQL
• UNION, EXCEPT, INTERSECT (union, set difference,
  intersection in relational algebra)
• two tables must be union compatible
• Q4) make a list of all project names 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

20
Basic Queries in SQL

• Set Operations in SQL
• (SELECT DISTINCT PNAME
• FROM EMPLOYEE, DEPARTMENT, PROJECT
• WHERE LNAMESmith AND SSNMGRSSN AND
  DNUMBERDNUM)
• UNION
• (SELECT DISTINCT PNAME
•  FROM EMPLOYEE, WORKS_ON, PROJECT
•  WHERE LNAMESmith AND SSNESSN AND
  PNOPNUMBER)

21
Basic Queries in SQL

• Substring Comparison
• comparison condition on only parts of a character
  string
• format
• LIKE comparison operator
• arbitrary number of characters
• _ a single character
• WHERE FNAME LIKE Mc AND
• BDATE LIKE 195 _ _ _ _ _ _ _

22
Basic Queries in SQL

• Arithmetic Operations
• addition, subtraction, multiplication, division
  , -, , /
• SELECT FNAME, LANME, 1.2SALARY
• BETWEEN
• WHERE (SALARY BETWEEN 20000 AND 30000)
• ((SALARY ? 20000) AND (SALARY ? 30000))

23
Basic Queries in SQL

• Tuple Ordering
• Q5) retrieve the name and salary of all employees
  whose salary is in between 30000 and 50000 in the
  descending order of salary
• SELECT SALARY, LNAME, FNAME
• FROM EMPLOYEE
• WHERE SALARY BETWEEN 30000 AND 50000
• ORDER BY SALARY DESC
• default is ascending (ASC) ORDER BY LNAME

24
More Complex SQL Queries

• Nested Query
• a (inner) query can be nested within the
  WHERE-clause of another (outer) query
• apply the outer query to the result of the inner
  query
• can have several levels of nested queries
• comparison operators are used to compare values
  of union compatible tuples
• IN gt, lt, , ltgt ALL, SOME, ANY

25
More Complex SQL Queries

• IN Operator
• compares a value v (from outer query) with a set
  of values V (inner query) and evaluates to TRUE
  if v is one of the elements in V
• Q6) Who is the oldest employee working on the
  project 5?
• Hint create the first query for the list of
  proj5 employees, then create the second query to
  apply the MIN(BDATE) function on the result

26
More Complex SQL Queries

• IN Operator
• SELECT MIN(BDATE)
• FROM EMPLOYEE
• WHERE SSN IN (SELECT ESSN
• FROM WORKS_ON
• WHERE PNO5)
• PROJ5(SSN) ? ?ESSN(?PNO5(PROJECT))
• RESULT ? ?MIN BDATE(EMPLOYEE PROJ5)
• ?MIN BDATE(EMPLSSNESSN(?ESSN(?PNO5(PROJECT)))

27
More Complex SQL Queries

• Other Comparison Operators
• Q7) Select the name 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 DNO5)

28
More Complex SQL Queries

• Correlated Nested Query
• a condition in the WHERE-clause of the inner
  query references some attributes of a relation
  declared in the outer query
• the nested query is evaluated once for each tuple
  in the outer query
• Q8) Retrieve the name of each employee who has a
  dependent with the same sex as the employee

29
More Complex SQL Queries

• Correlated Nested Query
• SELECT E.FNAME, E.LNAME
• FROM EMPLOYEE AS E
• WHERE E.SSN IN (SELECT ESSN
• FROM DEPENDENT
• WHERE E.SEXSEX)
• SELECT E.FNAME, E.LNAME
• FROM EMPLOYEE AS E, DEPENDENT AS D
• WHERE E.SSND.ESSN AND E.SEXD.SEX

30
More Complex SQL Queries

• EXISTS/NOT EXISTS
• to check whether the result of a correlated
  nested query is empty or not
• EXISTS(Q) returns TRUE if there is at least one
  tuple in the result of query Q
• NOT EXISTS(Q) returns TRUE if there are no tuples
  in the result of query Q
• if it returns true, the tuple in the outer query
  which is being compared is selected

31
More Complex SQL Queries

• EXISTS/NOT EXISTS
• Q9) Retrieve the names of employees who have no
  dependents
• SELECT FNAME, LNAME
• FROM EMPLOYEE
• WHERE NOT EXISTS (SELECT
• FROM DEPENDENT
• WHERE SSNESSN)

32
More Complex SQL Queries

• EXISTS/NOT EXISTS (Q10)
• SELECT FNAME, LNAME
• FROM EMPLOYEE
• WHERE EXISTS (SELECT
• FROM DEPENDENT
• WHERE SSNESSN)
• AND NOT EXISTS (SELECT
• FROM WORKS_ON
• WHERE PNOPNUMBER)

33
More Complex SQL Queries

• Explicit Sets
• use an explicit (enumerated) set of values in the
  WHERE-clause rather than a nested query
• Q11) Retrieve the social security numbers of all
  employees who work on the project number 1, 2, or
  3
• SELECT DISTINCT ESSN 
• FROM WORKS_ON 
• WHERE PNO IN (1,2,3)

34
More Complex SQL Queries

• NULLS
• to check if a value is NULL (missing or undefined
  or not applicable) 
• Q12) Retrieve the names of all employees who do
  not have supervisors 
• SELECT FNAME, LNAME
• FROM EMPLOYEE
• WHERE SUPERSSN IS NULL

35
More Complex SQL Queries

• Attribute Renaming 2
• to rename any attribute that appears in the
  result of a query
• SELECT attribute_name AS new_name
• Q3) employee and his/her supervisor name
• SELECT E.LNAME AS EMPLOYEE_NAME, S.LNAME
  AS SUPERVISOR_NAME
• FROM EMPLOYEE AS E, EMPLOYEE AS S   
• WHERE E.SUPERSSNS.SSN

36
More Complex SQL Queries

• Joined Tables
• to specify a table resulting from a join
  operation in the FROM-clause of a query
• INNER JOIN (same as JOIN), LEFT OUTER JOIN, RIGHT
  OUTER JOIN, and FULL OUTER JOIN
• SELECT FNAME, LNAME, ADDRESS (Q1)
• FROM EMPLOYEE JOIN DEPARTMENT ON DNODNUMBER
• WHERE DNAMEResearch

37
More Complex SQL Queries

• Nested Joined Tables
• one of the tables in a join may itself be a
  joined table
• Q2) projno, deptno, mgr for project in houston
• SELECT PNUMBER, DNUM, LNAME
• FROM (PROJECT JOIN DEPARTMENT ON
  DNUMDNUMBER) JOIN EMPLOYEE ON MGRSSNSSN
• WHERE PLOCATIONHouston

38
More Complex SQL Queries

• Aggregated Functions
• COUNT, SUM, MAX, MIN, and AVG 
• Q13) Find the maximum salary, the minimum salary,
  and the average salary among employees who work
  for the 'Research' department
• SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
• FROM EMPLOYEE, DEPARTMENT
• WHERE DNODNUMBER AND DNAME'Research'

39
More Complex SQL Queries

• Aggregated Functions with Grouping
• Q14) For each project, select 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

40
More Complex SQL Queries

• HAVING-CLAUSE
• Q14) 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
• SELECT PNUMBER, PNAME, COUNT ()
• FROM PROJECT, WORKS_ON
• WHERE PNUMBERPNO
• GROUP BY PNUMBER, PNAME
• HAVING COUNT () gt 2