Live Lecture Materials Week 12

Question 1

The 2 different techniques used to query data from multiple tables

Answer 1

Join and subquery

Question 2

If you want to use a query but don’t know the specific obscure values necessary to do so, what can you do?

Answer 2

Use a select statement + a where clause to find the obscure value(s)

ie.
SELECT SKU
FROM SKU_DATA
WHERE Department = ‘Sport’

Question 3

Another term for a subquery

Answer 3

A nested query

Question 4

T/F - Data is stored in individual tables, it’s the relationship between the tables that make the data meaningful

Answer 4

True

Question 5

Explicit join

Answer 5

The JOIN operator is used as part of the SQL statement

Question 6

Implicit join

Answer 6

The JOIN operator is not used as part of the SQL statement

Question 7

Joins can be used on

Answer 7

Tables
Views
Materialized views

Question 8

NATURAL JOIN

Answer 8

• Creates a join based on common attributes
• No duplicates returned
• If there’s no common column, it’s downgraded to a CROSS JOIN

Question 9

CROSS JOIN

Answer 9

Combines each row in one table with every row in another table

Question 10

The problem with CROSS JOINs or “Cartesian Products”

Answer 10

The nature of CROSS JOINs is illogical for database work, we want rows and tables that are related to each other, not random

Question 11

An Inner join matches _______ ____ and ________ ____ of two tables

Answer 11

Primary and foreign keys

Question 12

When you’re using an implicit join and a field name is the same in both tables, you need

Answer 12

To add the table name when you type a field

ie. Inventory.name

Question 13

Equijoin

Answer 13

A join based on identical column values

The two tables are joined using an “equal to” condition

Question 14

Today ________ joins are considered proper, and _______ joins are considered archaic

Answer 14

explicit, implicit

Question 15

You can create a table alias using ________ joins

Answer 15

explicit

Question 16

In an inner join, records that have ________ ______ are selected. ____ values are omitted

Answer 16

matching values, NULL

Question 17

Left outer join

Answer 17

• Returns all records from the left table, only matching records from right
• If there are no matches in the right table, it returns NULL

Question 18

Right outer join

Answer 18

• Returns all records from right table, only matching records from left
• If 0 matches, the join still returns a column, but it will be NULL in each row

Question 19

What is a set? What is set theory?

Answer 19

A set is a collection of distinct data items. Set theory refers to
Mathematical operations on sets.

Question 20

A relational database table meets the definition of a set, so SQL includes a group of ___ _________ to use in SQL

Answer 20

set operators

Question 21

The standard method of visualizing sets and their relationships

Answer 21

Venn diagrams

Question 22

In a Venn diagram a set is represented by a _______ ______, a ______ is a portion of a set that is contained entirely within the set

Answer 22

labelled circle, subset

Question 23

The entire area of both sets in a Venn diagram is called a _____, it is the equivalent to an __ operator

Answer 23

Union, OR

Question 24

Venn Diagram: the ____________ part of two sets is fittingly called an ____________ and is equivalent to an ___ operator

Answer 24

intersecting intersection, AND

Question 25

Venn Diagram: The ___________ is the area of a set that is not part of the set it is intersecting with. It is equivalent to a ___ operator

Answer 25

complement, NOT

Question 26

Prerequisites to use SET operations

Answer 26

1. The number of columns must be same 2. The data types must be identical

Question 27

Union operator meaning

Answer 27

The entire collection of values in 1 or 2 tables, depending on context

Question 28

Intersect operator meaning

Answer 28

The shared values of two tables

Question 29

Except operator meaning

Answer 29

All non-shared values of both tables