Lecture 3 - SQL SELECT

Question 1

SELECT statement in SQL?

Answer 1

SELECT
FROM
WHERE
(ORDER BY etc)

Question 2

SELECT statement with join & select.

Answer 2

SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = ‘Research’ AND Dno = Dnumber;

-> joins two tables by matching the foreign key

Question 3

Can tables be treated as variables in SQL?

Answer 3

Yes in the FROM we can specify the name for the table by using the AS word

Question 4

What is a recursive reference?

Answer 4

Having a foreign key that links to PK of the same relation e.g. employee and supervisor attributes

Question 5

Where is using variable names expecially useful?

Answer 5

When dealing with recursive relations

Question 6

What happens if we omit the WHERE and have FROM on two or more relations

Answer 6

We cross product all possible tuples (each tuple from relations is concatenated to each tuple from another relation)

-> this is computationally heavy and meaningless

Question 7

What is *?

Answer 7

Will list all possible attributes. It is a wildcard operator.

Question 8

What is a multiset?

Answer 8

a set that can contain duplicates

Question 9

Does it matter if our queries are complex and convoluted ?

Answer 9

No, as all queries will go through an optimiser