What is SQL (pronounced ‘See-kwel’)?

Structured Query Language (SQL) is a programming language

designed to manipulate and extract data from a Relational
Database Management System (RDBMS)
• Invented in the early 1970’s at IBM and based on relational algebra
• Became and American National Standards Institute (ANSI) standard in 1986
and in International Standards Organization (ISO) standard in 1987
Queries are pieces of code that retrieve data from database tables
Data Manipulation or Data Definition operations are used to
create or alter the database itself
Different, proprietary versions of the language exist, but most are
almost identical in basic syntax;
• This makes knowledge of SQL a highly transferable skill
• This is good, since in the real world it’s the workhorse behind data analytics
• It’s also a remarkably simple language, particularly relative to its power
SQL – Basic Functions

Defines which attributes / columns / fields I want to retrieve or calculate

SELECT (Mandatory)

Identifies the table from which I want to extract information

FROM (Mandatory)

Adds filters that restrict what rows / records are retrieved

WHERE (Optional)

Defines the level of aggregation I want if I'm summarizing data

GROUP BY (Optional)

Adds filters that restrict what aggregated rows / records are retrieved
HAVING (Optional)

Defines the sort order of the results

ORDER BY (Optional)
SQL – The SELECT Statement & FROM Clause
SELECT Defines which attributes / columns / fields I want to retrieve or calculate

FROM Identifies the table from which I want to extract information

SELECT FIELD_1, FIELD_2, ..., FIELD_N

FROM TABLE_NAME
SELECT FIELD_1, FIELD_2, ..., FIELD_N FROM TABLE_NAME

Transaction_ID Customer_Id Channel Product Price Discount

1000123 60067 Web Book 9.95
1000124 12345 Store Book 11.95
TRANSACTIONS

1000125 23451 Store DVD 14.95

1000126 70436 Reseller DVD 19.95 5
1000127 66772 Store Magazine 3.25
1000128 60067 Web Book 29.95
1000129 72045 Web DVD 9.95
1000130 82371 Reseller Magazine 2.5 0.25
1000131 12345 Store Book 7.95
SQL – The SELECT Statement & FROM Clause

SELECT CHANNEL, PRODUCT, PRICE

FROM TRANSACTIONS

Channel Product Price

Web Book 9.95
Store Book 11.95
Store DVD 14.95
Reseller DVD 19.95
Store Magazine 3.25
Web Book 29.95
Web DVD 9.95
Reseller Magazine 2.5
Store Book 7.95
SQL – The SELECT Statement & FROM Clause
Special Case – What if I want to select ALL columns / fields?
• Could type them all out in the SELECT statement, but tedious
• Instead, use the ‘wildcard’ character *

SELECT *
FROM TABLE_NAME
SQL – The WHERE Clause
WHERE Adds filters that restrict what rows / records are retrieved

WHERE <logical_condition(s)>
* In EXCEL – similar to the ‘Filter’ function

Transaction_ID Customer_Id Channel Product Price Discount

1000123 60067 Web Book 9.95
1000124 12345 Store Book 11.95
1000125 23451 Store DVD 14.95
TRANSACTIONS 1000126 70436 Reseller DVD 19.95 5
1000127 66772 Store Magazine 3.25
1000128 60067 Web Book 29.95
1000129 72045 Web DVD 9.95
1000130 82371 Reseller Magazine 2.5 0.25
1000131 12345 Store Book 7.95
SQL – The WHERE Clause

SELECT *
FROM TRANSACTIONS
WHERE CHANNEL = 'STORE'

Transaction_ID Customer_Id Channel Product Price Discount

1000124 12345 Store Book 11.95
1000125 23451 Store DVD 14.95
1000127 66772 Store Magazine 3.25
1000131 12345 Store Book 7.95
SQL – The WHERE Clause

Transaction_ID Customer_Id Channel Product Price Discount

1000123 60067 Web Book 9.95
1000124 12345 Store Book 11.95
1000125 23451 Store DVD 14.95

TRANSACTIONS
1000126 70436 Reseller DVD 19.95 5
1000127 66772 Store Magazine 3.25
1000128 60067 Web Book 29.95
1000129 72045 Web DVD 9.95
1000130 82371 Reseller Magazine 2.5 0.25
1000131 12345 Store Book 7.95

SELECT CHANNEL, PRODUCT, PRICE

Channel Product Price
FROM TRANSACTIONS
WHERE CHANNEL = 'STORE' Store Book 11.95
Store DVD 14.95
Store Magazine 3.25
Store Book 7.95