Chapter 4: Accessing Marketing Data Using Structured Query Language

Question 1

What are the two biggest language categories of SQL?

Answer 1

Table functions and query functions

Question 2

SELECT

Answer 2

Gathers specific data from a table

Question 3

FROM

Answer 3

Establishes which table the data is gathered from
■ SELECT CustomerID
■ FROM CustomerDemo

Question 4

WHERE

Answer 4

Defines a specific condition desired in the outcome
■ SELECT CustomerID, CustomerName
● FROM CustomerDemo
● WHERE Age = 35

Question 5

GROUP BY

Answer 5

GROUP BY
○ This function tells SQL how to segment the data that is selected.
SELECT OrderNumber
FROM OrderInfo
GROUP BY State

Question 6

JOIN

Answer 6

OIN
* Combines two tables of
data
* The command “ON”
labels which two tables
to be joined

Question 7

4 Variations of SQL Joins?

Answer 7

Inner Join
Left Join
Right Join
Full Join

Question 8

Which answer most precisely reflects the expected
number of records from an INNER join of these
two tables? ● Left Table: university registrar from current term – 20,000 records
● Right Table: university email list – 100,000 records
A. ≤ 100,000
B. ≥100,000
C. Exactly 100,000
D. ≤ 20,000
E. ≥ 20,000
F. Exactly 20,000

Answer 8

D

Question 9

Which answer most precisely reflects the expected
number of records from a LEFT join of these two
tables?
● Left Table: university registrar from current term – 20,000
records
● Right Table: university email list – 100,000 records
A. ≤ 100,000
B. ≥100,000
C. Exactly 100,000
D. ≤ 20,000
E. ≥ 20,000
F. Exactly 20,000

Answer 9

F

Question 10

Which answer most precisely reflects the expected
number of records from a RIGHT join of these two tables?
● Left Table: university registrar from current term – 20,000
records
● Right Table: university email list – 100,000 records
A. ≤ 100,000
B. ≥100,000
C. Exactly 100,000
D. ≤ 20,000
E. ≥ 20,000
F. Exactly 20,000

Answer 10

C

Question 11

Which answer most precisely reflects the expected
number of records from a FULL join of these two
tables?
● Left Table: university registrar from current term – 20,000
records
● Right Table: university email list – 100,000 records
A. ≤ 100,000
B. ≥ 100,000
C. Exactly 100,000
D. ≤ 20,000
E. ≥ 20,000
F. Exactly 20,000

Answer 11

B

Question 12

COUNT()

Answer 12

Reveals the number of rows (respondents/products/etc.) that fit the stated
criteria
* Example: In a relational database of a company’s customers, one could find
the number of female customers aged 25-34 who purchased an item last week

Question 13

Summary Query Commands

Answer 13

Summary queries are SQL commands that can answer simple statistical questions of the data

Question 14

AVG()

Answer 14

Generates the average value of a numeric column
* Example: One can determine the average age of all customers

Question 15

SUM()

Answer 15

Generates the sum of a numeric column
* Example: One can retrieve the summation of sales made in a certain year

Question 16

MAX() & MIN()

Answer 16

Generates the largest/smallest value that fits a specified criteria
* Example: One can determine the customer who made the smallest or biggest purchase last week

Question 17

BETWEEN()

Answer 17

Displays Data between a specific range of values
* Example: One can select the customers between the ages of 18-25

Question 18

AND

Answer 18

Displays data that is a combination of two conditions
* Example: One can display the customers between the ages of 18-25 and live in California.

Question 19

WHERE

Answer 19

A WHERE clause is
used is filter records
from a result. The
filter occurs before
any groupings are
made

Question 20

HAVING

Answer 20

A HAVING clause is
used to filter values
from a group.