The SQL Query Language

1
The SQL Query Language

• Logical and Algebraic Features
• Relationship to Relational Theory

2
The Query Language SQL 1

• Developed at IBM San Jose Research Laboratories.
• Originally called Sequel.
• ANSI standard established in 1986.
• SQL has DDL, DML, embedded DML, view definition
• authorisation, integrity, transaction control
• SQL Data Manipulation Language has both
  relational algebra tuple relational calculus
  features ...

3
The Query Language SQL 2

• Typical SQL query
• select A1, A2, ..., An
• from R1, R2, ..., Rm
• where p
• Relational algebra interpretation
• select attributes projection
• from relations product
• where condition selection
• Can use select to select all attributes
• Can omit where-clause interpret as where true

4
The Query Language SQL 3

• Typical SQL query
• select A1, A2, ..., Ak
• from R1, R2, ..., Rm
• where p
• Equivalent relational algebra expression
• Õ a1,a2,...,ak (sP (R1 R2 ... Rm))
• where a1,a2,...,ak are appropriately chosen
  indices
• SQL can return duplicate tuple in relations ...

5
The Query Language SQL 4

• Relational queries should really produce distinct
  tuples
• ... time consuming to implement, so have
• select distinct ...
• from ...
• to impose distinctness. By default, have
  duplicates.
• NB select all is used to guarantee duplicates
• Note select refers to attributes
• all and distinct refer to tuples.

6
The Query Language SQL 5

• Set operations
• Union of relations
• (select A1, A2, ..., An
• from R1, R2, ..., Rm
• where p)
• union
• (select A1, A2, ..., An
• from S1, S2, ..., Sk
• where q)

7
The Query Language SQL 6

• Set operations
• Intersection / difference of relations
• (select A1, A2, ..., An
• from R1, R2, ..., Rm
• where p)
• intersect / minus
• (select A1, A2, ..., An
• from S1, S2, ..., Sk
• where q)
• Standard SQL included union but not minus
  intersect

8
The Query Language SQL 7

• Set Membership
• Can use the in connective for set membership,
• where set is specified by a select clause
• If R S have same set of attributes A1, A2, ...,
  An
• can represent R intersect minus S via
• select from R
• where ltR.A1, R.A2, ..., R.Angt not in (select
  from S)

9
The Query Language SQL 8

• Cartesian Product RS
• select from R, S
• defines the Cartesian product of R and S
• Projection ? a1, a2, ..., ak(R)
• select A1, A2, ..., Ak from R
• where a1, a2, , ak correspond to A1, A2, ...,
  Ak
• Selection sF(R)
• select from R where F

10
The Query Language SQL 9

• Natural join
• No direct representation for natural join. Can
  express as projection of selection of join if
  A1, A2, ..., Ak are the attributes common to the
  relations R S, and those in R\S S\R are B1,
  , Bi and C1, , Cj, use
• select R.A1, R.A2, , R.Ak, B1, , Bi, C1, ,
  Cj
• from R, S
• where R.A1 S. A1
• and R.A2 S.A2
• ........................
• and R.Ak S.Ak

11
Illustrating the use of SQL 1

• Happy Valley Food Coop
• MEMBERS (name, address, balance)
• ORDERS (order_no, name, item, quantity)
• SUPPLIERS (sname, saddress, item, price)
• 1. Print members with negative balance
• select name
• from members
• where balance lt 0

12
Illustrating the use of SQL 2

• MEMBERS (name, address, balance)
• ORDERS (order_no, name, item, quantity)
• SUPPLIERS (sname, saddress, item, price)
• 2. Print names, items prices of all suppliers
  supplying at least one item ordered by Brooks
• select sname, item, price
• from suppliers, orders
• where suppliers.item orders.item
• and orders.name Brooks
• Note in expression of form rel_name.attr_name
• rel_name is in effect an implicitly defined tuple
  variable

13
Illustrating the use of SQL 3

• MEMBERS (name, address, balance)
• ORDERS (order_no, name, item, quantity)
• SUPPLIERS (sname, saddress, item, price)
• SQL also has explicit tuple variables
• For example, can rewrite above query as
• select sname, item, price
• from suppliers S, orders O
• where S.item O.item and O.name Brooks

14
Illustrating the use of SQL 4

• MEMBERS (name, address, balance)
• ORDERS (order_no, name, item, quantity)
• SUPPLIERS (sname, saddress, item, price)
• SQL also has explicit tuple variables can use
  tuple variables to compare 2 tuples in same
  relation
• select distinct T.name
• from orders S, orders T
• where S.item T.item and S.name Brooks
• ... who has ordered an item ordered by Brooks

15
The Query Language SQL 10

• General set comparison constructs
• Have illustrated in for set-membership test
• Can also express rich set of boolean conditions
• involving sets and elements
• Use (select ... from ... where ... ) to define
  set
• Then have constructs to compare contents of sets,
  or compare elements with all or specific values
  in a set

16
The Query Language SQL 11

• Illustrating (select ... from ... where ... ) to
  define set ...
• 1. is element x gt everything in a set?
• x gt all (select .... from ... where ... )
• 2. is element x gt something in a set?
• x gt some (select .... from ... where ... )
• 3. does one set contain another?
• (select .... from ... where ... )
• contains
• (select .... from ... where ... )
• 4. is a set empty?
• exists (select .... from ... where ... )

17
The Query Language SQL 12

• Ordering the Display of tuples
• Can append to a select ... from .. where
  query
• order by ltattributegt
• to display relation in ascending order by
  attribute
• Can specify more sophisticated sorting processes,
  e.g.
• order by A desc, B asc
• exploits two keys in the ordering process

18
The Query Language SQL 13

• Aggregate functions
• Can use group by to associate tuples into
  classes, then use aggregate operators avg, min,
  max, sum, count
• select item, avg(price) from suppliers group by
  item
• NB must retain duplicates in computing average
• Use aggregate function count to find cardinality
• select count () from members
• ... find out how many members the HVFC has

19
The Query Language SQL 14

• Modifying the Database deletion
• delete R where P
• ... delete those tuples t from R that satisfy P
• Note that delete operates only on one relation
• Example of a deletion
• delete suppliers
• where price gt ( select avg(balance) from
  members)
• ... delete supply records for more expensive
  items
• ... can be many relations referenced in an
  embedded select in this way

20
The Query Language SQL 15

• Semantics of delete
• Example of plausible deletion
• delete members
• where balance lt (
• select balance
• from members
• where name Brooks)
• ... delete all members with balance lt that of
  Brooks

21
The Query Language SQL 16

• Example of potential problem deletion
• delete members
• where balance lt (select avg(balance) from
  members)
• .... as members is updated, avg(balance) changes
• Standard SQL bans updates where delete from a
  relation referenced in an embedded select
• ... doesn't adopt convention of mark, then delete

22
The Query Language SQL 17

• Modifying the database inserting updating
• Illustrative examples
• insert into suppliers
• values (Goldilocks, Tweehouse, honey, 2)
• update members
• set balance balance1.05

23
The Query Language SQL 18

• Modifying the database inserting updating
• Problems through self-reference can again arise
• e.g. insert into members
• select from members
• can lead to infinite behaviour
• Sometimes like to insert incomplete tuples
• e.g. insert into members
• values (Mafia, null, coke, 1000)

24
The Query Language SQL 19

• Null values raise semantic problems
• comparisons involving null yield false
• null() is predicate used to test for null
• all aggregate fns except count ignore nulls since
  can't do arithmetic with nulls
• e.g. select sum (balance) from members

25
The Query Language SQL 20

• DDL for SQL
• Two aspects
• creation of user views
• specification of physical set-up
• Views in SQL
• create view v as ltquery expressiongt
• where ltquery expressiongt is a legal query
  expression

26
The Query Language SQL 21

• Views in SQL
• create view v as ltquery expressiongt
• where ltquery expressiongt is a legal query
  expression
• create view debtors as
• select name, address
• from members
• where balance lt 0
• Once a view is defined, it can be referred to in
  the same way as a standard relation, as in
• select name from debtors where balance lt -100

27
The Query Language SQL 22

• Modifying the database via a view ... a view can
  be referred to in the same way as a standard
  relation.
• This in principle allows update via a view, as
  in
• insert into debtors
• values (Lamont, 11 Downing St)
• ... leads to insertion into members relation,
  with null value where unspecified balance value.
• Restriction on practical systems modification of
  DB forbidden via view except on views derived
  from a single actual relation.

28
The Query Language SQL 23

• DDL for physical set-up
• As for Ingres, can specify many kinds of info on
  the set of relations that make up a DB. For each
  relation
• scheme for the relation
• domain of values assoc with the relation
• set of indices to be maintained
• security and authorisation info
• integrity constraints
• physical storage structure on disk

29
The Query Language SQL 24

• Essential commands in the DDL are
• create table R (A1 D1, A2 D2, ...,An Dn)
• where Ai is the name of an attribute, and Di is
  the corresponding domain of values
• drop table R
• ... to eliminate table R from the database
• alter table R add A D
• ... to add a new attribute to an existing
  relation

30
To follow Relational database design theory