Skip to content

Data Manipulation in SQL

This topic includes: Shape, Transform, Manipulate using the CASE statement, simple subqueries, correlated subqueries, and window functions

Case statements are SQL's version of an "IF this THEN that" statement.

Case statements have three parts -- a WHEN clause, a THEN clause, and an ELSE clause.

The first part -- the WHEN clause -- tests a given condition, say, x = 1. If this condition is TRUE, it returns the item you specify after your THEN clause.

You can create multiple conditions by listing WHEN and THEN statements within the same CASE statement.

The CASE statement is then ended with an ELSE clause that returns a specified value if all of your when statements are not true.

When you have completed your statement, be sure to include the term END and give it an alias. The completed CASE statement will evaluate to one column in your SQL query.

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

Add your notes here

Spinner
DataFrameas
df
variable
-- Add your own queries here
SELECT *
FROM soccer.match
LIMIT 5

Correlated subqueries use values from the outer query in order to generate the final results.

Simple subqueries can be run independently from the main query. Evaluated once in the whole query.

Correlated subqueries dependent on the main query to execute. Evaluated in loops hence it slows down query runtime.

**NESTED SUBQUERIES **

Is subquery inside another subquery.

EXTRACT (MONTH FROM date) AS month SUM ... SUM ... SELECT