Lecture 9: SQL SELECT PART 3 & RA

Question 1

example of cartesian product query

Answer 1

SELECT c.clientNo, c.fname, c.lname, v.clientNo, v.propertyNo, v.viewDate, v.comment
FROM client c, viewing

Question 2

What are multi-table queries?

Answer 2

• Natural Join or Simple Join
• one of the most difficult operations to implement efficiently
• Must use join if result columns come from more than one table
• perform join, include more than one table in from clause separated by comma
• include WHERE clause to specify join columns

Question 3

Eg of three table join

Answer 3

SELECT b.branchNo, b.city, s.staffNo, fName, lName, propertyNo
FROM branch b, staff s, propertyForRent

WHERE b.branchNo = s.branchNo AND
s.staffNo = p.staffNo

ORDER BY b.branchNo, s.staffNo, propertyNo;

Question 4

list the procedures for generating results of a join

Answer 4

1. Form Cartesian product of the tables named in FROM clause
2. If there is a WHERE clause, apply the search condition to each row of the product table, retaining those rows that satisfy the condition
3. For each remaining row, determine value of each item in SELECT list to produce a single row in result table
4. If DISTINCT has been specified, eliminate any duplicate rows from the result table
5. If there is an ORDER BY clause, sort result table as required

Question 5

What are outer joins?

Answer 5

outer join operations retain rows that do not satisfy the join condition

Question 6

What are left outer joins?

Answer 6

includes rows of first (left) table unmatched with rows from second right table

columns from second table are filled nulls

Question 7

What are right outer joins?

Answer 7

• right outer joins includes those two rows of second (right) table that are unmatched with rows from first (left) table
• columns from first table are filled with NULLS

Question 8

What are full outer joins?

Answer 8

• includes rows that are unmatched in both tables

* unmatched columns are filled with nulls

Question 9

ANY and ALL

Answer 9

• any and all may be used with subqueries that produce a single column of numbers
• ALL = true when values produced by subquery
• ANY = true if it is satisfied by any values produced by subquery
• if subquery empty, all returns true, any returns false
• some may be used in place of any

Question 10

Exists and Not exists

Answer 10

EXISTS and NOT EXISTS are for use only with subqueries

Produce a simple true/false result

True if and only if there exists at least one row in result table returned by subquery

False if subquery returns an empty result table

NOT EXISTS is the opposite of EXISTS

Question 11

List the relational algebra

Answer 11

theta join
equijoin
natural join
outer join
left outer join

also division command