SQL part 4

Question 1

different ways to join tables

Answer 1

cross join, inner join, outer join

Question 2

inner join aka and types

Answer 2

“old-style” join
- equijoin
- theta join
JOIN ON

Question 3

outer join types

Answer 3

left join, right join

Question 4

mandatory and optional (order mandatory)

Answer 4

mandatory: SELECT, FROM
optional: WHERE, GROUP BY, HAVING, ORDER BY

Question 5

cross join format

Answer 5

select column_list from table1, table2

Question 6

cross join provides the _____

Answer 6

cartesian product of 2 tables

Question 7

cross join result

Answer 7

single table of info
# cols = # cols selected (tb1) + # cols selected (tb2)

rows = # rows (tb1) * # rows (tb2)

Question 8

“old style” inner join format

Answer 8

select column_list from table1, table2
where table1.col1 = table2.col2

Question 9

“join on” inner join format

Answer 9

select column_list from table1 join table2 on table1.col1 = table2.col2

Question 10

if fk and pk have same name, how would you do join on

Answer 10

select column_list from table1 join table2 using (col1)

Question 11

an inner join is a

Answer 11

traditional join in which only rows that meet given criteria are selected

Question 12

if inner join criteria involves equality, it is called

Answer 12

equijoin or natural join

Question 13

if join criteria involves an inequality, it is called a

Answer 13

theta join

Question 14

an inner join typically uses _____ to link tables together

Answer 14

PK - FK relationship

Question 15

self-join

Answer 15

type of inner join, used to find information by comparing rows within same table

Question 16

self-join format (T1 and T2 are aliases)

Answer 16

select column_list from table1 T1, table2 T2 where T1.col1 = T2.col2

Question 17

t/f: table aliases can be used in any type of join

Answer 17

true

Question 18

general approach for inner joins with multiple tables

Answer 18

• specify attributes to be displayed or referenced
• include tables from which those attributes come
• include tables that lie between these tables
• include table linkages to tie all tables together
• further restrict rows as necessary

Question 19

general approach: include tables that lie between these tables

Answer 19

every table must be linked directly to another to avoid cross joins

Question 20

general approach: include table linkages to tie all tables together

Answer 20

where (e.pk = f.fk) and (f.pk = g.pk) and…

Question 21

general approach: further restrict rows as necessary

Answer 21

(where x=y)