Week 6

Question 1

Join

Answer 1

a relational operation that connects 2+ tables with a common domain

combined into a single table/view

Question 2

Equi-join

Answer 2

join in which the joining condition is based on equality between values in the common columns

COMMON COLUMNS WILL APPEAR REDUNDANTLY IN RESLT TABLE

Question 3

Inner Join

Answer 3

equi-join where one of the duplicate columns is eliminated in result table

Question 4

Inner join example, when would you use this?

Answer 4

when you need info that is in both tables

SELECT ____
FROM table 1 INNER JOIN table 2 ON t1.ID=t2.ID

NOTE THAT THE ID THAT ID BEING CONNECTED NEEDS TO BE THE SAME ID!!

Question 5

if it is just JOIN

Answer 5

inner join is implict

Question 6

Outer Join (Left, Right, Full)

Answer 6

A join in which rows that do not have matching values in common columns are included in the result table

Question 7

Multi table join

Answer 7

You have to look at how the table flows and then connect the fks to each other accordingly

CONNECTED VIA INNER JOINS

Question 8

Self Join Example

Answer 8

you oin to yourself, but basically you gotta make diff aliases

Question 9

SELF JOIN CODE

Answer 9

SELECT s.EmployeeID [Supervisor ID], s.EmployeeName [Supervisor Name], e.EmployeeID,
e.EmployeeName, e.EmployeeSupervisor
FROM Employee_T s INNER JOIN Employee_T e on e.EmployeeSupervisor=s.EmployeeID
WHERE e.EmployeeSupervisor = s.EmployeeI

Question 10

Sub query

Answer 10

placing an inner select statement query inside an outer query

Question 11

where can you put the sub query?

Answer 11

a. in the condition of the WHERE CLAUSE
b. as a table of the FROM clause
c. returning a field for the SELECT clause
d. within the HAVING Clause

Question 12

2 types of subqueries

Answer 12

non correalted: executed once for the entire outer query

correlated: execture once for each row returned by the outer query

Question 13

what is the logic of subquery?

how do you have all the employees having the same supervisor as laura come?

Answer 13

SELECT *
FROM Employee_T
WHERE SupervisorName = (
SELECT SupervisorName
FROM Employee_T
WHERE EmployeeName LIKE ‘Laura%’)

Basically the where is the something = to something else, and then you solve for something else in the sub query

Question 14

How to do a subquery in the from clause?

Answer 14

First off, you can alias subqueries! calling them t3 and t4

Basically you will label each sub query a diff name

SELECT t3.____, t3._______
FROM
(SELECT ____
FROM inner join the tables
WHERE _____condition) t3,
(SELECT ____
FROM inner join the tables
WHERE _____condition) t4
WHERE t3.____=t4._______

Question 15

non corelated subqueries

Answer 15

Do not depend on data from the outer query
 Execute once for the entire outer query

Question 16

correlated subqueries

Answer 16

 Make use of data from the outer query
 Execute once for each row of the outer query
 Can use the EXISTS and ALL operators

Question 17

correlated subquery

Answer 17

?????? textbook

Question 18

non correlated subquery

Answer 18

????????? textbook

Question 19

can you connect sub queries using union intersect except? YES

Answer 19

AND

OR

THIS BUT NOT THAT

Question 20

The outer join syntax does not apply easily to a join condition of more than ________ tables

Answer 20

two

Question 21

The ________ operator is used to combine the output from multiple queries into a single result table.

Answer 21

union

Question 22

EXISTS will take a value of ________ if the subquery returns an intermediate results table which contains one or more rows.

Answer 22

true

Question 23

In order for two queries to be UNION-compatible, they must:

Answer 23

same # of rows

Question 24

A ________ is a temporary table used in the FROM clause of an SQL query.

Answer 24

derived table

Question 25

A materialized view is/are:

Answer 25

copies or replica of data based on queries.

Question 26

The UNION clause is used to:

Answer 26

combine the output from multiple queries into a single result table.

Question 27

A join in which the joining condition is based on equality between values in the common columns is called a(n):

Answer 27

both equi-join. and natural join. .

Question 28

A join in which rows that do not have matching values in common columns are still included in the result table is called a(n):

Answer 28

outer join

Question 29

A join that is based upon equality between values in two common columns with the same name and where one duplicate column has been removed is called a(n):

Answer 29

natural join