SQL: Structured Query Language

1
Lecture 19 (10/24/2005)

• SQL Structured Query Language

2
String Operations

• Retrieve all employees whose address is in
  Houston, Texas. Here, the value of the ADDRESS
  attribute must contain the substring 'Houston,TX
• SELECT FNAME, LNAMEFROM EMPLOYEEWHERE ADDRESS
  LIKE 'Houston, TX
• Retrieve all employees who were born during the
  1950s.
• Here, '5' must be the 7th character of the string
  (according to our format for date), so the BDATE
  value is '_ _ _ _ _ _5_', with each underscore
  as a place holder for a single arbitrary
  character
• SELECT FNAME, LNAMEFROM EMPLOYEEWHERE BDATE
  LIKE '_ _ _ _ _ _ 5 _
• The LIKE operator allows us to get around the
  fact that each value is considered atomic and
  indivisible hence, in SQL, character string
  attribute values are not atomic

3
MySQL Comments

• Like most DBMSs is far from the standard
• Use databaseName
• Show databases
• Show Tables
• \. Filename
• Every statement must be followed by a
• Cant enforce check constraints
• Planned for 5.1
• Cant create new domains
• Use TypeInnoDB to enforce referential ICs
• Assignment 3 (SQL) is up

4
String Operations

• Find the names of all employees whose first name
  ends with the substring IM and is exactly of
  length 3
• SELECT FROM EMPLOYEEWHERE FNAME LIKE _IM
• SQL supports a variety of string operations such
  as
• concatenation (using or concat function)
• Show full names of employees
• converting from upper to lower case (and vice
  versa)
• finding string length, extracting substrings,
  etc.
• Like abcd escape ? matches to abcd
• Like abcd escape ? matches to abcd
• Default \

5
SOUNDEX operation - SKIP

• Matching words that might not look alike, but
  sound alike
• Useful for finding strings for which the sound is
  known but the precise spelling is not
• SOUNDEX (ARGUMENT)
• The argument can be a character string that is
  either a CHAR or VARCHAR not exceeding 4,000
  bytes.
• Returns a 4 character code representing the sound
  of the word in the argument
• The result of the function is CHAR(4)
• The result is null if the argument is null
• Result used to compare with the sound of other
  strings

6
SOUNDEX operation - SKIP

• How does it work
• The first letter of the soundex code corresponds
  to the first letter of the argument
• Every subsequent character in the name is looked
  up according to the scheme presented on the next
  slide and encoded as a digit between 1 and 6
• A,E,I,O,U,Y,W,H are ignored along with double
  letters
• Ignore everything after the 4th character

7
SOUNDEX operation - SKIP

• Nonalphabetic characters terminate the soundex
  evaluation
• 1 B,P,F,V
• 2 C,S,G,J,K,Q,X,Z
• 3 D,T
• 4 L
• 5 M,N
• 6 R
• All other letters (A,E,I,O,U,Y,W,H) ignored
• SOUNDEX(SMITH) and SOUNDEX(SMYTHE)
• If there are insufficient characters remaining in
  the name, the coding is padded with zeros

8
DIFFERENCE operation - SKIP

• There is also a related function called
  DIFFERENCE function
• Used to compare strings based upon their Soundex
  values
• DIFFERENCE(Argument1,Argument2)
• Usually returns an integer result ranging in
  value from 0 (least similar) to 4 (most similar).

9
DIFFERENCE operation - SKIP

• Let's suppose you overheard a male employee
  talking in the hallway but didn't quite catch his
  name
• You might have overheard a name that sounded like
  "Ann" but you're positive it was a male
• The DIFFERENCE function is ideal here
• We'd probably begin by specifying a threshold
  value of 4 to limit the number of results returned

10
DIFFERENCE operation - SKIP

• SELECT FNAME, DIFFERENCE (FNAME,'ANN') AS
  'DIFFERENCE' FROM EMPLOYEES WHERE
  DIFFERENCE(FIRSTNAME,'ANN')4
• FirstName Difference ---------- ----------- Ann
  4 Anne 4
• Unfortunately, none of the results were male
  names
• What can we do?
• ? So we try to lower the threshold to 2

11
DIFFERENCE operation - SKIP

• SELECT FNAME, DIFFERENCE (FNAME,'ANN') AS
  'DIFFERENCE' FROM EMPLOYEES WHERE
  DIFFERENCE(FIRSTNAME,'ANN')gt2
• FirstName Difference ---------- ----------- Ann
  4 Andrew 2 Janet 2 Laura 2 Anne 4
• looks like Andrew might be our man!

12
Practice queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• Find and increment (by 1) the ratings of persons
  who have sailed two different boats on the same
  day.
• SELECT S.sname, S.rating1 AS rating FROM
     Sailors S, Reserves R1, Reserves R2 WHERE
  S.sidR1.sid AND S.sidR2.sid AND
  R1.dateR2.date AND R1.bidltgt R2.bid
• Find the ages of sailors whose name begins and
  ends with B and has at least three characters.
• SELECT S.age FROM Sailors S WHERE S.sname LIKE
  B_B

13
Practice queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• Find the names of sailors who have reserved a red
  or a green boat.
• SELECT S.sname FROM Sailors S, Boats B, Reserves
  R WHERE S.sidR.sid AND R.bidB.bid AND
  (B.colorred OR B.colorgreen)
• SELECT S1.sname FROM       Sailors S1, Boats B1,
  Reserves R1 WHERE     S1.sidR1.sid AND
  R1.bidB1.bid AND B1.colorred UNION SELECT
     S2.sname FROM Sailors S2, Boats B2, Reserves
  R2 WHERE    S2.sidR2.sid AND R2.bidB2.bid AND
  B2.colorgreen

14
Practice queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• Find the names of sailors who have reserved both
  a red and a green boat
• SELECT S1.sname FROM Sailors S1, Boats B1,
  Reserves R1 WHERE S1.sidR1.sid AND
  R1.bidB1.bid AND B1.colorred INTERSECT
  SELECT S2.sname FROM   Sailors S2, Boats B2,
  Reserves R2 WHERE S2.sidR2.sid AND
  R2.bidB2.bid AND B2.colorgreen

15
Practice queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• Find the sids of all sailors who have reserved
  red boats but not green boats.
• SELECT R1.sid FROM  Boats B1, Reserves R1
  WHERE  R1.bidB1.bid AND B1.colorred EXCEPT
  SELECT R2.sid FROM  Boats B2, Reserves R2
  WHERE  R2.bidB2.bid AND B2.colorgreen  

16
Practice queries

• Sailors (sid, sname, rating, age)
• Boats (bid, bname, color)
• Reserves (sid, bid, date)
• IMP For each rating with at least two sailors,
  find the age of the youngest sailor who is
  eligible to vote (gt18 years)
• SELECT S.rating, MIN (S.age) AS minage
  FROM Sailors S WHERE S.age gt 18 GROUP BY
  S.rating HAVING COUNT () gt 1

17
Explicit Sets

• It is possible to use an explicit (enumerated)
  set of values in the WHERE-clause
• Query 13 Retrieve the social security numbers of
  all employees who work on project number 1, 2, or
  3
• SELECT DISTINCT ESSNFROM WORKS_ONWHERE PNO
  IN (1, 2, 3)
• Check Constraints
• Check Attribute in (Set of values)
• Movie rental company A video is given a category
  such as Action, Adult, Children, Drama, Horror,
  or Sci-Fi.

18
Explicit Sets

• Query 13 Retrieve the social security numbers of
  all employees who work for department 1,2, or 3
  and on project number 1, 2, or 3
• SELECT DISTINCT ESSNFROM EMPLOYEE,
  WORKS_ONWHERE PNO IN (1, 2, 3) and DNO IN
  (1,2,3)
• Query 13 Retrieve the social security numbers of
  all employees who work for department 1,2, or 3
  and on a project number with the same number
• SELECT DISTINCT ESSNFROM EMPLOYEE,
  WORKS_ONWHERE PNO, DNO IN ((1,1), (2,2), (3,3))

19
Nesting of Queries

• A complete SELECT query, called a nested query ,
  can be specified within the WHERE-clause of
  another query, called the outer query
• Query 1 Retrieve the name and address of all
  employees who work for either the 'Research' or
  Development department.
• SELECT FNAME, LNAME, ADDRESSFROM
  EMPLOYEEWHERE DNO IN (SELECT DNUMBER
  FROM DEPARTMENT WHERE DNAME'Research'
  OR DNAMEDevelopement' )
• 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 (or multi-set) of values V, and
  evaluates to TRUE if v is one of the elements in V

20
The SOME (or ANY) keyword Existential Quantifier

• Attribute ltCOMPgt SOME r ????t ??r? such that
  (Attribute ltcompgt t)Where ltcompgt can be
  ???????? ????

(read 5 lt some tuple in the relation)
(5lt some
)
0
)
(5lt some
5
0
)
(5 some
5
0
(5 ? some
)
5
21
The SOME (or ANY) keyword Existential Quantifier

• Find employees whose salaries are greater than
  the salary of some employee in the Research
  Department
• SELECT S.FNAME, S.LNAME FROM DEPARMENT D,
  EMPLOYEE R, EMPLOYEE SWHERE D.DNAMEResearch
  AND D.DNUMBERR.DNO R.SALARY lt S.SALARY
• SELECT FNAME, LNAME FROM EMPLOYEE
  SWHERE SALARY gt SOME (SELECT SALARY
  FROM EMPLOYEE, DEPARTMENT WHERE
  DNAMEResearch AND DNUMBERDNO)

22
The ALL keyword Universal Quantifier
Attribute ltCOMPgt all r ????t ??r? such that
(Attribute ltcompgt t)
(5lt all
)
6
)
(5lt all
10
4
)
(5 all
5
4
(5 ? all
)
6
23
The ALL keyword Universal Quantifier

• Find employees whose salaries are greater than
  the salary of all employees in the Research
  Department
• SELECT FNAME, LNAME FROM EMPLOYEE
  SWHERE SALARY gt ALL (SELECT SALARY FROM
  EMPLOYEE, DEPARTMENT WHERE DNAMEResearch
  AND DNUMBERDNO)
• Works only in some of the universal
  quantification problems

24
Nesting of Queries

• Can have several levels of nested queries
• A reference to an unqualified attribute refers to
  the relation declared in the innermost nested
  query (if found)
• Two types of nesting
• The nested query is not correlated with the
  outer query
• SELECT FNAME, LNAME, ADDRESSFROM
  EMPLOYEEWHERE DNO IN (SELECT DNUMBER
  FROM DEPARTMENT WHERE DNAME'Research' )
• 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

25
Correlated Nested Queries

• Query 12 Retrieve the name of each employee who
  has a dependent with the same first name as the
  employee
• SELECT E.FNAME, E.LNAMEFROM EMPLOYEE AS
  EWHERE E.SSN IN ( SELECT ESSN
  FROM DEPENDENT WHERE ESSNE.SSN
  AND E.FNAMEDEPENDENT_NAME)
• Which is more computationally expensive
  correlated or non-correlated?

26
Correlated Nested Queries

• A query
• written with nested SELECT-FROM-WHERE blocks
• using the IN (belongs to a set) or (is equal to
  single value) comparison operators
• can always be expressed as a single block query
• For example (previous query)
• SELECT E.FNAME, E.LNAMEFROM EMPLOYEE E,
  DEPENDENT DWHERE E.SSND.ESSN AND E.FNAMED.
  DEPENDENT_NAME