SQL part 5

Question 1

three types of outer join

Answer 1

full outer join (not supported in mysql or msaccess)
left outer join
right outer join

Question 2

outer join returns

Answer 2

not only rows matching join condition, but also rows with unmatched values

Question 3

outer join basically provides an inner join plus

Answer 3

additional rows, with partial info, where join condition wasn’t true

Question 4

you have two tables. table A has emp_id 201, 202, and 203 with fname and lname. table B has emp_id 202, 203, and 204 with salary.
what is the output if you use left outer join?

Answer 4

201 emp_id, fname, lname, blank emp_id and salary
202 emp_id, fname, lname, emp_id, salary
203 emp_id, fname, lname, emp_id, salary

Question 5

you have two tables. table A has emp_id 201, 202, and 203 with fname and lname. table B has emp_id 202, 203, and 204 with salary.
what is the output if you use right outer join?

Answer 5

202 fname, lname, emp_id, salary
203 fname, lname, emp_id, salary
null, null, null, emp_id (204), salary

Question 6

if statement

Answer 6

allows for displaying results that satisfy conflicting conditions at the same time

Question 7

if syntax

Answer 7

select if (condition1, result1, result2) from…

condition1 = logical conditional (T/F)
result1 = value if true
result2 = value if false

Question 8

union of two sql statements

Answer 8

combines the output of 2 select statements
- supports combining databases
- supports representing multiple competing conditions simultaneously

Question 9

union of 2 sql statements requires

Answer 9

same number of attributes in each statement, and corresponding data types to be compatible

Question 10

t/f: union of 2 sql statements does not remove duplicates

Answer 10

false. it does.

Question 11

union syntax

Answer 11

select…from…where
UNION
select…from…where