SQL as DML General Syntax

1
SQL as DML General Syntax

• SELECT ltattribute and function listgt
• FROM lttable listgt
• WHERE ltconditiongt
• GROUP BY ltgrouping attribute(s)gt
• HAVING ltgroup conditiongt
• ORDER BY ltattribute listgt

2
SQL as DML - Recursion

• The Missing link is the parent of the Yeti .. is
  the parent of Lucy is the parent of the
  Cro-Magnon man is the parent of . Charlie
  Chaplin is the parent of Sid Chaplin.
• Q. Can SQL be used to list all employees under a
  Manager, at any level?
• (OR the names of all descendants of an ancestor)
• A. Only explicit recursion possible (at least in
  SQL-2)
• Q8
• SELECT
• E.FNAME, E.LNAME, S.FNAME, S.LNAME
•  FROM
• EMPLOYEE AS E, EMPLOYEE AS S
•  WHERE
• E.SUPERSSNS.SSN

1
Supervises
Employee
n
3
SQL as DML - Wildcards

• SELECT FNAME, LNAME
•  FROM EMPLOYEE
•  WHERE LNAME LIKE DINAK
• (Instead of DINAKARPANDIAN (Was that just the
  last name?!!) )
• SELECT FNAME, LNAME
•  FROM EMPLOYEE
•  WHERE SSN LIKE _ _ _ _ _ 6230
• (Hacking someones confidential info knowing only
  part of the SSN )

4
SQL as DML Tables as Relations

• SELECT DISTINCT SSN
•  FROM POLICYCLAIMS
• (To get list of policy holders with at least one
  claim )
• SELECT ALL AMOUNT
•  FROM POLICYCLAIMS
• (To get all money claimed ALL is implied by
  default )
• Using UNION, EXCEPT, INTERSECT to combine results
  of multiple SFW queries implies set mode
• (For example using UNION to pool together listing
  of all Picasso paintings in the world in
  different museums)
• NOTE User is responsible for ensuring the
  information is compatible

5
SQL as DML Nesting

• SELECT MOVIENAME, LNAME, FNAME
•  FROM NEWRELEASES
•  WHERE MOVIETYPEComedy AND (LNAME, FNAME) IN
• (SELECT LASTNAME, FIRSTNAME
• FROM OLDMOVIES
•  WHERE RELEASEYEAR lt 1970)
• CONCEPTS
• -For each valid tuple in outer query, execute
  inner query (like nested FOR loops). Multilevel
  possible
• -Unqualified attributes in outer query reference
  inner query (UNLIKE nested FOR loops in
  programming languages)
• NOTE Nesting is only a convenient way of
  expressing complex queries. Equivalent to a
  complex series of JOINs in a single query

6
SQL as DML Correlated queries

• Correlated When WHERE clauses of inner query
  references outer attribute
• SFW UNIQUE (SFW)
• Only unique tuples resulting from inner SFW are
  returned to outer query
• Specification of exactly once
• SFW EXISTS (SFW)
• Outer query is considered for a given tuple if
  nested query returns at least one tuple
• Specification of at least once
• SFW NOT EXISTS (SFW)
• Opposite of above (Useful for finding something
  except something)
• Specification of zero

7
SQL as DML Explicit queries

• Matching a set of values
• SELECT SID
•  FROM STUDENTS
•  WHERE ZIPCODE IN (66212,64110)
• Look for incomplete OR only complete data
• SFW x IS NULL
• SFW y IS NOT NULL

8
SQL as DML JOINS

• Alternative to explicit joins (equality of
  attributes)
• Natural (DEFAULT), Outer left/right/full joins
  supported
• Q8B
• SELECT E.LNAME AS EMPLOYEE_NAME, S.LNAME AS
  SUPERVISOR_NAME
•  FROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS
  S ON E.SUPERSSNS.SSN)
• Q2A
• SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
•  FROM ((PROJECT JOIN DEPARTMENT ON DNUM DNUMBER)
  JOIN EMPLOYEE ON MGRSSNSSN)
•  WHERE PLOCATIONStafford

9
SQL as DML Statistics

• Q19
• SELECT SUM (SALARY), MAX (SALARY), MIN (SALARY),
  AVG (SALARY)
•  FROM EMPLOYEE
•  
• Q5
• SELECT LNAME, FNAME
•  FROM EMPLOYEE
•  WHERE (SELECT
• COUNT ()
•   FROM DEPENDENT
•   WHERE SSNESSN) gt 2
• Q26
• SELECT PNUMBER, PNAME, COUNT ()
•  FROM PROJECT, WORKS_ON
•  WHERE PNUMBERPNO
•  GROUP BY PNUMBER, PNAME
•  HAVING COUNT () gt 2

10
SQL as VDL
11
SQL as VDL

• Specify a shortcut (An alias) to execute a
  particular query
• Frame other queries based on the View
• V1
• CREATE VIEW WORKS_ON1
•  AS SELECT FNAME, LNAME, PNAME, HOURS
•  FROM EMPLOYEE, PROJECT, WORKS_ON
•  WHERE SSNESSN AND PNOPNUMBER
• QV1
• SELECT FNAME, LNAME
•  FROM WORKS_ON1
•  WHERE PNAMEProjectX

12
SQL as VDL Mapping views

• Can map base Relations to views
• Mapping updates to view to base Relations may be
  ambiguous
• If subset of a single base Relation, then
  possible (updatable)
• JOIN based, or statistical VIEWS generally not
  updatable