Relational algebra and SQL 2

1
Relational algebra and SQL 2
CS 157A Lecture 9

• Prof. Sin-Min Lee
• Department of Computer Science
• San Jose State University

2
Keys

• A key of a relation r on relation scheme R is a
  subset K B1, B2 ... Bn of R with the
  following property. For any two distinct tupples
  t1 (B) ¹ t2 (B). That is, no two tupples have
  the same value on all attributes in K. We could
  write this condition as t1 (K) ¹ t2 (K). Hence
  it is sufficient to know the K value of a tupple
  to identify the tupple uniquely.
• David Maier

3
Relational Algebra (cont.)

• T1 or Project Operation Project is a unary
  operator that, instead of forming new relations
  out of a specified subset of tuples like d, p
  forms a new relation out of a subset of the
  possible attributes.

4
Form

• Form
• p Last, SS(Person_db)
• Jones 528-78-4993
• Sealy 528-47-8832
• Moranis 512-47-8354

5
XOR Join Operator

• Join is a binary operation for combining
  relations. If R Ç S Æ here r x s is the
  cartesian product of r and s.

6
Example

• r (A B) s (C D)
• a1 b1 c1 d1
• a2 b1 c2 d1
• c2 d2

• r x s (A B C D)
• a1 b1 c1 d1
• a1 b1 c2 d1
• a1 b1 c2 d2
• a2 b1 c1 d1
• a2 b1 c2 d1
• a2 b1 c2 d2

7
Boolean Operations

• If r and s are relations on the scheme R, then r
  Ç s, r È s and r - s are all obvious relations on
  R.

8
Example

• r (A B C) s (A B C)
• a1 b1 c1 a1 b2 c1
• a1 b2 c1 a2 b2 c1
• a2 b1 c2 a2 b2 c2
• r Ç s (A B C) r - s (A B C)
• a1 b2 c1 a1 b1 c1
• a2 b1 c2

• r È s (A B C)
• a1 b1 c1
• a1 b2 c1
• a2 b1 c2
• a2 b2 c1
• a2 b2 c2

9
Combinations of Operations

• What if we wanted to print all people who own
  "Jag," and only print their names?
• plast(dMake "Jag" (Person_db X Car_db))
• What if we wanted to print the make and model of
  all 1962 cars.
• pMake, Model(d Year "1962" (Car_db))

10
(No Transcript)
11
(No Transcript)
12
(No Transcript)
13
(No Transcript)
14
Suppose we have the following scheme ANTIQUES (
BUYERID, PRICE ) ANTIQUEOWNERS (FIRSTNAME, LAST
NAME , OWNERID, ITEMS) ORDER (BUYERID, PRICE,
OWNERID, ITEMS ) Write the following query in
SQL
15
(No Transcript)
16
(No Transcript)
17

• (a)    We want to see the price of the most
  expensive item bought by each owner.
• Ans.
• SELECT BUYERID, MAX(PRICE)FROM ANTIQUESGROUP BY
  BUYERID

18

• (b)    We only want to see the maximum purchase
  price if the purchase is over 1000
• Ans.
• SELECT BUYERID, MAX(PRICE)FROM ANTIQUESGROUP BY
  BUYERIDHAVING PRICE gt 1000

19

• (c) List the buyers who purchased an expensive
  item (the Price of the item is 100 greater than
  the average price of all items purchased.
• Ans.
• SELECT OWNERIDFROM ANTIQUESWHERE PRICE gt
• (SELECT AVG(PRICE) 100FROM ANTIQUES)

20

• (d)    List the Last Names of those in the
  AntiqueOwners table, ONLY if they have bought an
  item.
• Ans.
• SELECT OWNERLASTNAMEFROM ANTIQUEOWNERSWHERE
  OWNERID
• (SELECT DISTINCT BUYERIDFROM ANTIQUES)

21

• (e) If a prospective customer wanted to see the
  list of Owners only if the shop dealt in Chairs
• Ans.
• SELECT OWNERFIRSTNAME, OWNERLASTNAMEFROM
  ANTIQUEOWNERSWHERE EXISTS
• (SELECT FROM ANTIQUESWHERE ITEM 'Chair')

22
Equijoins

• An equijoin is a join with a join condition
  containing an equality operator. An equijoin
  combines rows that have equivalent values for the
  specified columns. Depending on the internal
  algorithm the optimizer chooses to execute the
  join, the total size of the columns in the
  equijoin condition in a single table may be
  limited to the size of a data block minus some
  overhead. The size of a data block is specified
  by the initialization parameter DB_BLOCK_SIZE.

23
Joins

• A join is a query that combines rows from two or
  more tables, views, or snapshots. Oracle7
  performs a join whenever multiple tables appear
  in the query's FROM clause. The query's select
  list can select any columns from any of these
  tables. If any two of these tables have a column
  name in common, you must qualify all references
  to these columns throughout the query with table
  names to avoid ambiguity

24
This equijoin returns the name and job of each
employee and the number and name of the
department in which the employee works SELECT
ename, job, dept.deptno, dname FROM emp, dept
WHERE emp.deptno dept.deptno
25
(No Transcript)
26
self join

• A self join is a join of a table to itself. This
  table appears twice in the FROM clause and is
  followed by table aliases that are used to
  qualify column names in the join condition. To
  perform a self join, Oracle7 combines and returns
  rows of the table that satisfy the join
  condition.

27
(No Transcript)
28
Cartesian Products

• If two tables in a join query have no join
  condition, Oracle7 returns their Cartesian
  product. Oracle7 combines each row of one table
  with each row of the other. A Cartesian product
  always generates many rows and is rarely useful.
  For example, the Cartesian product of two tables
  each with a hundred rows has ten thousand rows.
  Always include a join condition unless you
  specifically need a Cartesian product

29
Outer Joins

• The outer join extends the result of a simple
  join. An outer join returns all rows that
  satisf/y the join condition and those rows from
  one table for which no rows from the other
  satisfy the join condition. Such rows are not
  returned by a simple join. To write a query that
  performs an outer join of tables A and B and
  returns all rows from A, apply the outer join
  operator () to all columns of B in the join
  condition. For all rows in A that have no
  matching rows in B, Oracle7 returns NULL for any
  select list expressions containing columns of B.

30
(No Transcript)
31
Outer join queries

• are subject to the following rules and
  restrictions
• The () operator can only appear in the WHERE
  clause, not in the select list, and can only be
  applied to a column of a table or view.
• If A and B are joined by multiple join
  conditions, the () operator must be used in all
  of these conditions.
• The () operator can only be applied to a column,
  rather than to an arbitrary expression, although
  an arbitraryt expression can contain a column
  markedml with the () operator.

32

• A condition containing the () operator cannot be
  combined with another condition using the OR
  logical operator.
• A condition cannot use the IN comparison operator
  to compare a column marked with the () operator
  to another expression.
• A condition cannot compare a column marked with
  the () operator to a subquery.

33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
(No Transcript)
37
Suppose we have the following scheme ANTIQUES (
BUYERID, PRICE ) ANTIQUEOWNERS (FIRSTNAME, LAST
NAME , OWNERID, ITEMS) ORDER (BUYERID, PRICE,
OWNERID, ITEMS ) Write the following query in
SQL
38
(No Transcript)
39
(No Transcript)
40

• (a)    We want to see the price of the most
  expensive item bought by each owner.
• Ans.
• SELECT BUYERID, MAX(PRICE)FROM ANTIQUESGROUP BY
  BUYERID

41

• (b)    We only want to see the maximum purchase
  price if the purchase is over 1000
• Ans.
• SELECT BUYERID, MAX(PRICE)FROM ANTIQUESGROUP BY
  BUYERIDHAVING PRICE gt 1000

42

• (c) List the buyers who purchased an expensive
  item (the Price of the item is 100 greater than
  the average price of all items purchased.
• Ans.
• SELECT OWNERIDFROM ANTIQUESWHERE PRICE gt
• (SELECT AVG(PRICE) 100FROM ANTIQUES)

43

• (d)    List the Last Names of those in the
  AntiqueOwners table, ONLY if they have bought an
  item.
• Ans.
• SELECT OWNERLASTNAMEFROM ANTIQUEOWNERSWHERE
  OWNERID
• (SELECT DISTINCT BUYERIDFROM ANTIQUES)

44

• (e) If a prospective customer wanted to see the
  list of Owners only if the shop dealt in Chairs
• Ans.
• SELECT OWNERFIRSTNAME, OWNERLASTNAMEFROM
  ANTIQUEOWNERSWHERE EXISTS
• (SELECT FROM ANTIQUESWHERE ITEM 'Chair')