Lecture 8

Question 1

How to do Cartesian product of relations R and S using SQL?

Answer 1

SELECT * FROM R, S;

Question 2

How to do Theta-Join using SQL?

Answer 2

SELECT * FROM R, S WHERE R.E = S.E;

Question 3

How to create alias of table name in SQL?

Answer 3

FROM R AS R1

Question 4

What are the operators for union, intersection and difference in SQL?

Answer 4

UNION, INTERSECT, and EXCEPT (note MySQL does not support INTERSECT and EXCEPT and UNION drops duplicates, but UNION ALL does not).

Question 5

How to do a subquery in SQL?

Answer 5

Just put query between brackets, and include it in the FROM statement.

Question 6

How to test if R is not empty?

Answer 6

EXISTS R

Question 7

How to do join in SQL?

Answer 7

After FROM A JOIN B on A.a = B.a

Question 8

How to do outer join in SQL? And left and right?

Answer 8

FULL OUTER JOIN, not supported in MySQL, so there we create the UNION of the following(if you do not care about duplicates).

LEFT OUTER JOIN and RIGHT OUTER JOIN