BASIC SQL AND
AGGREGATION
Dante Louise Sapalo
OBJECTIVES
At the end of the session, students are expected to:
Understand basic concepts in data pre-processing
using SQL
Understand and apply SQL if/then logic as well as using
SQL functions for data type conversion, date formatting
and string cleaning.
Getting comfortable with cleaning and pre-processing
data using SQL.
OBJECTIVES
At the end of the session, students are expected to:
Understand nested queries and performing operations
in multiple steps.
Perform aggregated calculations that retain separate
identities
Take data that is formatted for analysis and pivot it for
presentation or charting.
CASE
SQL’s way of handling if/then logic
Followed by at least one pair of WHEN and THEN
statements (similar to IF/THEN in Excel)
Every case statement must end with END statement
ELSE statement is optional
CASE
This query checks if a student passes based on their
GWA.
SELECT
campus, gender, first_name, last_name, gwa,
CASE
WHEN gwa > 75 THEN 'passed'
ELSE 'fail' END AS remark
FROM students
CASE
With multiple WHEN statements, they get evaluated in
the order they were written.
SELECT
campus, first_name, last_name, gwa,
CASE
WHEN gwa > 95 THEN 'President lister'
WHEN gwa > 90 THEN 'Dean lister'
WHEN gwa > 75 THEN 'Passed'
ELSE 'Fail' END AS remark
FROM students
CASE
A better way of writing the WHEN statements is to set
actual ranges or limits
SELECT
campus, first_name, last_name, gwa,
CASE
WHEN gwa >= 95 THEN 'President lister'
WHEN gwa >= 90 AND gwa < 95 THEN 'Dean lister'
WHEN gwa >= 75 AND gwa < 90 THEN 'Passed'
ELSE 'Fail' END AS remark
FROM students
CASE
String together multiple conditional statements with
AND and OR the same way you might in a WHERE clause
SELECT
campus, first_name, last_name, gwa,
CASE
WHEN gwa >= 95 THEN 'President lister'
WHEN gwa >= 90 AND gwa < 95 THEN 'Dean lister'
WHEN gwa >= 75 AND gwa < 90 THEN 'Passed'
ELSE 'Fail' END AS remark
FROM students
CASE
Using CASE with aggregate functions allows us to count
on multiple conditions (using where clause only allows
counting on one condition)
SELECT
CASE
WHEN gwa >= 95 THEN 'President lister'
WHEN gwa >= 90 AND gwa < 95 THEN 'Dean lister'
WHEN gwa >= 75 AND gwa < 90 THEN 'Passed'
ELSE 'Fail' END AS remark,
COUNT(1) AS count
FROM students
GROUP BY 1
DATA TYPES
As previously demonstrated, certain functions work
on some data types, but not on others.
COUNT works with any data type (numeric, non-
numeric)
SUM only works with numerical data
In some instances, data imported into a database
may appear to be numeric but aren’t stored as
such. This is because numerical columns cannot
contain commas in relational databases.
DATA TYPES
SQL databases can also store data in many different
formats with different levels of precision, for
example:
INTEGER data type stores whole numbers (no
decimals) DOUBLE PRECISION can store 15 and 17
significant decimal digits DATE is formatted as
YYYY-MM-DD etc.
DATA TYPES
To change a column’s data type, The CAST()
function is used to convert a value (of any type) into
a specified datatype.
CAST(column_name AS data_type)
CAST(cost AS integer)
CAST(age AS string)
DATE FORMAT
Dates are formatted year-first (i.e. YYYY-MM-DD).
This allows sorting in chronological order as
opposed to dates formatted as MM-DD-YYYY.
Valid date formats:
'YYYY-MM-DD', 'YYYY-MM-DD HH:MM:SS'
Invalid date formats:
‘MM/DD/YYYY’, ‘MON/DD/YYYY’
INTERVAL
Assuming dates are properly stored as date or time
data types, we can perform arithmetic on dates
(subtracting one date from another, adding date
intervals, etc)
SELECT
first_name, last_name, birthdate,
birthdate - INTERVAL '9 months' AS conception_date
FROM students
NOW()
You can add the current time (time of query run)
into the code and use it to change dates.
SELECT
first_name, last_name, birthdate,
NOW() - birthdate AS age
FROM students
EXTRACT
Use EXTRACT to obtain date-related attributes like
year, month, day, hour, etc.
SELECT
first_name, last_name, birthdate,
EXTRACT('year' FROM birthdate) AS birthyear
FROM students
CURRENT
To get today’s date, time and timestamp, we simply
use CURRENT_DATE, CURRENT_TIME and
CURRENT_TIMESTAMP, respectively.
COALESCE
use COALESCE to replace occasional Null values in a
column with a default value.
SELECT
first_name, last_name, birthdate,
COALESCE(birthdate, '1980-01-01')
FROM students
LEFT, RIGHT AND
LENGTH
LEFT - pull a certain number of characters from the
left side of a string using syntax LEFT(string,
num_characters)
SELECT
first_name,
LEFT(first_name, 3) AS nickname,
last_name, birthdate
FROM students
LEFT, RIGHT AND
LENGTH
RIGHT - pull a certain number of characters from the
right side of a string using syntax RIGHT(string,
num_characters)
SELECT
first_name,
RIGHT(first_name, 3) AS nickname,
last_name, birthdate
FROM students
LEFT, RIGHT AND
LENGTH
LENGTH - returns the length of a string.
SELECT
first_name,
LENGTH(first_name) AS namelength,
last_name, birthdate
FROM students
LEFT, RIGHT AND
LENGTH
LENGTH can be used to make the query to be more
dynamic with respect to the actual length of the
string rather than setting an absolute number of
characters to pull using LEFT/RIGHT
SELECT
first_name,
LEFT(first_name, LENGTH(first_name)/2)
AS nickname,
last_name, birthdate
FROM students
POSITION AND STRPOS
POSITION allows to specify a substring, then returns
a numerical value equal to the character number
(from the left) that substring first appears.
SELECT
first_name,
last_name,
POSITION('A' IN first_name) AS a_position
FROM students
NOTE: This function is case-sensitive: ‘A’ is different form ‘a’
POSITION AND STRPOS
STRPOS does the same, but with a different syntax.
SELECT
first_name,
last_name,
STRPOS(first_name, ‘A’) AS a_position
FROM students
NOTE: This function is case-sensitive: ‘A’ is different form ‘a’
SUBSTR
Use SUBSTR to find characters in the middle of a
string
SELECT
first_name, last_name,birthdate,
SUBSTR(CAST(birthdate AS varchar), 6, 2)
AS day
FROM students
CONCAT
CONCAT combines string from several columns
together. Using two pipe characters, (||) will perform
the same operation
SELECT
first_name, last_name,
CONCAT(first_name, ' ', last_name) AS full_name,
first_name || ' ' || last_name AS full_name2
FROM students
TRIM
Used to remove leading and trailing spaces (or
other specified characters) from a string.
TRIM([characters FROM ]string)
TRIM(“ string ”)
returns “string”
TRIM(“#! ” FROM “ #string! ”)
returns “string”
UPPER AND LOWER
Use LOWER or UPPER to lower-case or upper-case
any string/data
SELECT
UPPER(first_name) AS upper_first_name,
LOWER(last_name) AS lower_last_name
FROM students
STRINGS TO DATES
The most commonly erroneous data type and
format is a date. Some of the things that cause
these screw-ups are:
Data was manipulated in Excel, and the dates
were changed to month-first, as in MM/DD/YYYY
format
Data was manually entered by someone who
uses whatever formatting convention he/she
was most familiar with
Data uses text (Jan, Feb, etc) instead of numbers
STRINGS TO DATES
All of which, as previously discussed, are non-
compliant with SQL’s strict standards and are
impossible to sort/manipulate/perform arithmetic
on.
In order to make use of date operations like
INTERVAL, we need the date field formatted
appropriately. This involves text manipulation
followed by a CAST. Being able to manipulate text
and turn them into other formats comes with
practice.
EQUI-JOIN
A join in which the joining condition is based on
equality between values in the common columns.
Common columns appear (redundantly) in the
result table.
SELECT customers.customer_id, orders.customer_id,
first_name, order_id
FROM customers, orders
WHERE customers.customer_id = orders.customer_id
ORDER BY order_id
INNER JOIN
The INNER JOIN returns only rows that have
matching values in both tables.
SELECT customers.customer_id, orders.customer_id,
first_name, order_id
FROM customers INNER JOIN orders ON
customers.customer_id = orders.customer_id
ORDER BY order_id;
SUBQUERIES
A query nested inside another SQL query. It allows
complex filtering, aggregation and data
manipulation by using the result of one query inside
another.
SELECT first_name, address
FROM customers
WHERE customers.customer_id =
(SELECT orders.customer_id
FROM orders
WHERE orders = 200);
WINDOW FUNCTIONS
Performs a calculation across a set of table rows
that are related to the current row. It can perform
aggregation while retaining separate identities and
also able to access more than just the current row
of the query result.
PARTITION BY – Divides rows into groups (like GROUP BY but
without collapsing rows).
ORDER BY – Defines the order of rows for the calculation.
Frame specification – Limits the rows considered for the
calculation (e.g., last 3 rows).
WINDOW FUNCTIONS
<window_function>(expression)
OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression]
[ROWS or RANGE frame_specification]
)
WINDOW FUNCTIONS
Common Window Functions
Aggregate SUM(), AVG(), MIN(), MAX(), COUNT()
ROW_NUMBER(), RANK(), DENSE_RANK(),
Ranking
NTILE()
LAG(), LEAD(), FIRST_VALUE(),
Value
LAST_VALUE()
WINDOW FUNCTIONS
SELECT
employee_id, department_id, salary, SUM(salary)
OVER
(PARTITION BY department_id ORDER BY employee_id )
AS running_total
FROM employees;
This query groups by department_id (PARTITION BY),
orders by employee_id, and then calculates a
cumulative sum of salaries.
WINDOW FUNCTIONS
SELECT employee_id, department_id, salary,
RANK() OVER
(PARTITION BY department_id ORDER BY salary DESC)
AS salary_rank
FROM employees;
This query ranks employees within each department
by salary. Any ties get the same rank, and the next
rank is skipped.
WINDOW FUNCTIONS
SELECT order_id, order_date, amount,
LAG(amount, 1)
OVER (ORDER BY order_date) AS previous_amount,
amount - LAG(amount, 1)
OVER (ORDER BY order_date) AS difference
FROM orders;
LAG() gets the previous row’s value. LEAD is its opposite,
where it gets the succeeding row’s value.
The query calculates the difference between current and
previous amounts.
PIVOTING DATA IN SQL
Pivoting basically turns data that looks like the left picture into
a format that looks the right picture and vice versa.
PIVOTING DATA IN SQL
This is our initial table.
PIVOTING DATA IN SQL
First, aggregate the data to show the number of
players of each year in each conference
PIVOTING DATA IN SQL
Finally, we create a separate column for each
relevant column using CASE and SUM. In this case it’s
(FR, SO, JR, SR)
PIVOTING DATA IN SQL
This is our final table.
PIVOTING DATA IN SQL
In the table below, its difficult to compute for the average
magnitude of an earthquake. it would be easier to transform
the table into 3 columns: “magnitude”, “year”, and “number of
earthquakes”
PIVOTING DATA IN SQL
First, create a table that lists (manually) all of the columns
from the original table
PIVOTING DATA IN SQL
Second, do a cross join (joins each values of one table to
each values of another) into worldwide_earthquakes
PIVOTING DATA IN SQL
Lastly, use CASE statements
to pull data from the correct
column in the
worldwide_earthquakes
table given the value in the
year column
PIVOTING DATA IN SQL
This is our final table.
SEE YOU IN THE
NEXT SESSION!