Chapter 4: Accessing Marketing Data Using Structured Query Language Flashcards
What are the two biggest language categories of SQL?
Table functions and query functions
SELECT
Gathers specific data from a table
FROM
Establishes which table the data is gathered from
■ SELECT CustomerID
■ FROM CustomerDemo
WHERE
Defines a specific condition desired in the outcome
■ SELECT CustomerID, CustomerName
● FROM CustomerDemo
● WHERE Age = 35
GROUP BY
GROUP BY
○ This function tells SQL how to segment the data that is selected.
SELECT OrderNumber
FROM OrderInfo
GROUP BY State
JOIN
OIN
* Combines two tables of
data
* The command “ON”
labels which two tables
to be joined
4 Variations of SQL Joins?
Inner Join
Left Join
Right Join
Full Join
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
D
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
F
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
C
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
B
COUNT()
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
Summary Query Commands
Summary queries are SQL commands that can answer simple statistical questions of the data
AVG()
Generates the average value of a numeric column
* Example: One can determine the average age of all customers
SUM()
Generates the sum of a numeric column
* Example: One can retrieve the summation of sales made in a certain year
MAX() & MIN()
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
BETWEEN()
Displays Data between a specific range of values
* Example: One can select the customers between the ages of 18-25
AND
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.
WHERE
A WHERE clause is
used is filter records
from a result. The
filter occurs before
any groupings are
made
HAVING
A HAVING clause is
used to filter values
from a group.
