Structured Query Language SQL

1
Structured Query LanguageSQL
2
Reading Assignments

• Database Systems The Complete Book Chapters 6.1,
  6.2
• Oracle8 Programming Chapters 2.1, 2.2, 2.3, 2.4
• Following lecture slides are modified from Jeff
  Ullmans slides for Fall 2002 -- Stanford

3
SQL

• High level language
• Allows programmer to avoid data manipulation
  details
• SQL queries are optimized ? efficient execution
• SQL query answer relation
• Based on set and relational operations with
  certain modifications and enhancements

4
SQL Statement

• A typical SQL query form SELECT A1, A2, ...,
  An FROM r1, r2, ..., rm WHERE C
• Ais represent attributes to be returned
• ris represent relations
• C is a condition
• Equivalent to the relational algebra expression.
• ?A1, A2, ..., An(?C (r1 x r2 x ... x
  rm))

5
SELECT

• SQL allows duplicates in relations as well as in
  query results.
• To force the elimination of duplicates, insert
  the keyword distinct after select.
• The keyword all specifies that duplicates not be
  removed.

6
Example

• Dog-Kennel database
• Dog(name,age,weight,breed)
• Kennel(name,license,address,phone)
• Owner(name, SSN,phone)
• Boards(K.name,K.address,D.name,D.breed)
• Owns(O.SSN,D.name,D.breed)
• Pays(O.SSN, K.name,K.address,amount)

7
Example

• List the name of the Germanshepherd dogs
• SELECT name
• FROM Dog
• WHERE breedG.S.
• Result

8
Meaning of Single Relation Query

• Begin with the relation in the FROM clause
• Check for satisfaction of WHERE condition
• If condition satisfied, apply the projection
  indicated in SELECT clause

9
Operational Semantics

• There is a tuple variable ranging over each tuple
  of the relation in FROM clause.
• For each instantiation of the tuple variable (for
  each tuple) check if the condition in WHERE is
  satisfied.
• Create expression in SELECT using the current
  instantiation.

10
SELECT

• When there is only 1 relation in FROM, in
  SELECT means all attributes
• SELECT
• FROM Dog
• WHERE breedG.S.
• Result

11
Renaming

• If you want the result with different attribute
  name
• SELECT name AS smartest dogs, age
• FROM Dog
• WHERE breedG.S.
• Result

12
Expression in SELECT

• Give the name and weight in kg of G.S. dogs
• SELECT name, weight/2.2 AS weight in kg
• FROM Dog
• WHERE breedG.S.
• Result

13
Expression in SELECT

• Constant in SELECT expression
• SELECT name, breed, friendly AS behavior
• FROM Dog
• WHERE breedG.S.
• Result

14
Complex Conditions in WHERE

• Find the name, age, and weight of G.S. that are
  younger than 6 years
• SELECT name, weight
• FROM Dog
• WHERE breedG.S. AND age lt 6
• Result

15
Conditions in WHERE

• Conditions can be
• AND
• OR
• NOT
• ( ) used for grouping
• Outcomes of boolean condition
• TRUE
• FALSE
• SQL case INSENSITIVE, except for quoted strings

16
String Matching

• In WHERE clause, a string can be compared to a
  pattern to see if it matches
• General form
• ltAttributegt LIKE ltpatterngt
• ltAttributegt NOT LIKE ltpatterngt
• Pattern quoted string
• any string
• _ any character
• \ escape character

17
Example

• List the name of the Germanshepherd dogs if their
  name begins with P
• SELECT name
• FROM Dog
• WHERE breedG.S. AND name LIKE P
• Result

18
NULL Values

• Tuples may have NULL values for some of the
  attributes
• Meaning of NULL values
• Missing value there is a value but it is
  unknows, e.g., age of a dog
• Inapplicable the value is meaningless for that
  tuple, e.g., rank information of a non-show dog.

19
Comparing NULL Values

• Logic condition results
• TRUE
• FALSE
• UNKNOWN
• When a value is compared with a NULL value, the
  result is UNKNOWN
• A query produces results only when the condition
  is TRUE

20
Three Valued Logic

• Any comparison with null returns unknown
• 9 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

21
Multi-relational Queries

• Combine data from more than 1 relation
• List all relations in the FROM clause
• Attributes with the same name must be
  distinguished

22
Example

• List the name of the owner and the name of
  his/her dog for all G.S. dogs
• SELECT Owner.name, D.name
• FROM Owns, Owner
• WHERE D.breedG.S. AND
  Owner.SSNO.SSN
• Result

23
Semantics of multi-relational queries

• Start with the product of all the relations in
  FROM
• Apply selection condition from WHERE
• Project on the attributes and expressions on the
  SELECT

24
Operational Semantics

• Assume a tuple variable for each relation in the
  FROM
• Tuple variables visit each combination of tuples,
  one from each relation
• If the tuple variables satisfy the WHERE
  condition, than use this instantiation to create
  the instantiation for SELECT

25
Renaming Relations

• When a query needs to use two or more tuples from
  the same relation
• Distinguish copies of the same relation with
  different tuple variable names in the FROM clause
• You can also create shortcut to the relation
  names this way

26
Example

• Find Germanshepherd dogs that differ in less than
  15 lb in weight
• SELECT d1.name, d2.name
• FROM Dog d1, Dog d2
• WHERE breedG.S. AND d1.weight-d2.weight lt
  15 AND d1.weight ltd2.weight
• Result

27
Subqueries

• Parenthesized SELECT-FROM-WHERE can be used in
  FROM and WHERE clauses
• In FROM replaces relations with the result
  (relation) of a query
• In WHERE good when the answer to the subquery is
  a single tuple otherwise error may occur

28
Example

• Find the name and breed of the dogs that weight
  the same as Pepper
• SELECT name, breed
• FROM Dog
• WHERE weight(SELECT weight
• FROM Dog
• WHERE namePepper)
• Result

29
IN Operation

• lttuplegt IN ltrelationgt is true if and only if the
  tuple is a member of the relation
• IN expression appears in WHERE
• The relation is often subquery

30
Example

• Find the name, breed and age of the dogs that
  that were boarded in White Oak Kennel in Irmo.
• SELECT name, breed, age
• FROM Dog
• WHERE name IN (SELECT D.name
• FROM Boards
• WHERE K.nameW.O AND
  K.addressIrmo )
• Result

31
More Operators

• EXISTS
• ANY
• ALL
• Used similarly to IN