Data Engineering

SQL

1

SQL: Structured Query Language (or “sequel”)

• High level data transformation language
• supported by relational databases and other data systems (e.g., Spark)
• Declarative rather than imperative
• Describe what rather how
• Recall query-centric vs. code-centric
• Users issue SQL queries
• The core functionality of SQL is restricted
• Say, in comparison to Python
• By design: allows the system to automatically optimize the queries
• Still quite powerful: can do most things you want to do with data
• And extensions allow us to bridge the gap

2

The Basic Form: Select From Where

Basic Form:

​

SELECT attributes

FROM tables

WHERE condition about tuples in tables

​

If you’re trying to map this to RA, beware that SELECT is projection, and WHERE is selection!

• This confusion has persisted across decades… e.g., Spark’s choice of .select() for projection

​

Let’s start with some single table examples…

3

Basic Examples

SELECT * FROM Stops

​

Basic Examples

SELECT gender, citation

FROM Stops

​

Duplicates are preserved!

​

Basic Examples

SELECT gender, citation

FROM Stops

WHERE citation = True

​

Basic Examples

SELECT DISTINCT gender, citation

FROM Stops

WHERE citation = True

​

Basic Examples

SELECT * FROM Stops

WHERE gender != “M” AND age > 30

​

Null Values

​

• Tuples can have “NULL” values for some attributes
• Either means missing (exists but don’t know what it is)
• or inapplicable (value doesn’t apply: occupation for a child)
• Need to be careful in how to deal with NULLs.

​

• NULLs do not satisfy conditions:
• length > 100 evaluates to FALSE for a NULL value of length
• length <= 100 evaluates to FALSE for a NULL value of length
• Thus, if we want all tuples, we need to explicitly test for NULLs
• length > 100 OR length <= 100 OR length IS NULL

​

• For more on NULLs, see three-valued logic
• We’ll return to NULLs in aggregation

​

9

Multi-relation Queries

• List them in the FROM clause
• Like in relational algebra, refer to attributes as <relation>.<attribute> if needed
• Can also use “AS” to rename them if needed (OK to omit as well, still allowed)

​

​

​

10

Multi-relation Queries

• List them in the FROM clause
• Like in relational algebra, refer to attributes as <relation>.<attribute> if needed
• Can also use “AS” to rename them if needed (OK to omit as well, still allowed)

​

​

11

SELECT * FROM Stops, Zips WHERE Stops.location = Zips.location

SELECT * FROM Stops AS S, Zips AS Z WHERE S.location = Z.location

​

SELECT race, zipcode FROM Stops AS S, Zips AS Z WHERE S.location = Z.location // only keep race and zipcode

​

​

How to Read a SQL Query w/ Mult. Tables

• Start with the FROM clause: lists the tables
• Imagine that we are considering every combination of tuples in the tables via a cartesian product
• For each combination do the following steps:
• Apply the selection in the WHERE clause
• Apply the projection in the SELECT clause

​

12

New Demo Dataset: IMDB

Real subset of the IMDB dataset!

• As messy as it comes
• 6 tables
• Schema: https://www.imdb.com/interfaces/
• Will use postgreSQL

Example

• Let’s get the names and IDs of Morgan Freeman movies.
• Let’s also get the Morgan Freeman movies with IMDB ratings >= 8.0
• What do we need here?
• List of movies with rating >= 8.0
• List of Morgan Freeman movies
• …but let’s not get ahead of ourselves.

14

Step 1: What tables does the DB have?

• \dt command in postgres is helpful!
• Example output:
•             List of relations

 Schema |   Name   | Type  |    Owner    

--------+----------+-------+-------------

 public | akas     | table | lakshyajain

 public | crew     | table | lakshyajain

 public | episodes | table | lakshyajain

 public | people   | table | lakshyajain

 public | ratings  | table | lakshyajain

 public | titles   | table | lakshyajain

​

If we want to get the movies Morgan Freeman has acted in, what tables should we use?

​

We can’t tell from this alone! We have a good heuristic from names -- the tables of interest are probably titles, people, and crew, at the least. But we need to investigate further

Step 2: What are the schemas of the tables?

• \d tablename is useful

​

imdb=# \d titles

                        Table "public.titles"

     Column      |       Type        | Collation | Nullable | Default 

-----------------+-------------------+-----------+----------+---------

 title_id        | character varying |           | not null | 

 type            | character varying |           |          | 

 primary_title   | character varying |           |          | 

 original_title  | character varying |           |          | 

 is_adult        | integer           |           |          | 

 premiered       | integer           |           |          | 

 ended           | integer           |           |          | 

 runtime_minutes | integer           |           |          | 

 genres          | character varying |           |          | 

Indexes:

    "titles_pkey" PRIMARY KEY, btree (title_id)

​

Schemas cont’d

imdb=# \d crew

                      Table "public.crew"

  Column   |       Type        | Collation | Nullable | Default 

-----------+-------------------+-----------+----------+---------

 title_id  | character varying |           |          | 

 person_id | character varying |           |          | 

 category  | character varying |           |          | 

 job       | character varying |           |          | 

​

Schemas cont’d

imdb=# \d people

                     Table "public.people"

  Column   |       Type        | Collation | Nullable | Default 

-----------+-------------------+-----------+----------+---------

 person_id | character varying |           | not null | 

 name      | character varying |           |          | 

 born      | integer           |           |          | 

 died      | integer           |           |          | 

Indexes:

    "people_pkey" PRIMARY KEY, btree (person_id)

​

What do we do?

• Titles doesn’t have the actor info, but it has the names of tables we want
• People doesn’t have movie info, but it has the names of actors
• Crew has neither movie names nor actor names, but it has a person_id to title_id mapping showing who acted in what movie.

​

• Time to perform a join!

Query time!

• What’s our join condition?
• titles inner join crew

on crew.title_id = titles.title_id��inner join people

on people.person_id = crew.person_id

• What do we need to select?
• Primary Title, Title ID

​

What do we want?

• Our current query:
• SELECT DISTINCT titles.primary_title, titles.title_id � �FROM titles INNER JOIN crew

ON crew.title_id = titles.title_id��INNER JOIN people

ON people.person_id = crew.person_id

​

• We were asked for two things: Morgan Freeman movies and Morgan Freeman movies with rating >= 8.0
• Select DISTINCT primary
• So, first, we want to filter our query to return only the Morgan Freeman movies.
• Add a WHERE clause!
• where people.name = 'Morgan Freeman' and titles.type = 'movie';

​

​

​

Query 1: All Morgan Freeman movies

• SELECT DISTINCT titles.primary_title, titles.title_id� FROM titles INNER JOIN crew� ON crew.title_id = titles.title_id� INNER JOIN people� ON people.person_id = crew.person_id� WHERE people.name = 'Morgan Freeman’ AND titles.type = 'movie';

OK, what do I do with the result of a query?

Many things; you can define/create

• new full-fledged tables
• virtual views
• inlined view CTEs
• materialized views

​

​

Option 1: Create a New Table

CREATE TABLE CitationStops AS

(SELECT gender, citation

FROM Stops

WHERE citation = True)

​

Treated as a regular table after invocation

If base table changes, we must manually change any derived tables

​

Option 2: Create a Virtual View (View for short)

CREATE VIEW CitationStops AS

(SELECT gender, citation

FROM Stops

WHERE citation = True)

​

Output is not stored; computed on demand as part of the query

Think of this as a variable or as a virtual relation that is more convenient to query than the base table

e.g., refer as usual: SELECT * FROM CitationStops

Sometimes defined for access control – more on that later

Option 3: Create an Inlined View: Common Table Expression

WITH CitationStops AS

(SELECT gender, citation

FROM Stops

WHERE citation = True)

SELECT * FROM CitationStops

​

Like virtual views, CTEs are computed on demand

• CTEs cannot be indexed.

​

​

Option 4: Create a Materialized View

CREATE MATERIALIZED VIEW CitationStops AS

(SELECT gender, citation

FROM Stops

WHERE citation = True)

​

Output is stored on the disk, just like a regular table, and unlike views

Key benefit over regular tables: MVs automatically updated as base table(s) change

• SQL Server, Oracle keep MVs up-to-date, as does data warehouse systems like Google’s BigQuery

However, many MVs can add unnecessary overhead to any base table updates

• So, its use must be judicious
• As a result, some systems, e.g., PostgreSQL don’t automatically keep MVs up-to-date
• Instead require the user (rather than the system) to manually “refresh” – can be automated via scripts.

​

​

​

So…let’s cache our table.

• CREATE TABLE morgan_freeman_movies AS��SELECT DISTINCT titles.primary_title, titles.title_id� FROM titles INNER JOIN crew� ON crew.title_id = titles.title_id� INNER JOIN people� ON people.person_id = crew.person_id� WHERE people.name = 'Morgan Freeman’ AND titles.type = 'movie';

​

Query 2: RATINGS

• So, let’s get the names of movies with rating > 8.0
• SELECT * from ratings NATURAL JOIN morgan_freeman_movies WHERE rating > 8.0;

​

Query 2: RATINGS

• So, let’s get the names of movies with rating > 8.0
• SELECT * from ratings NATURAL JOIN morgan_freeman_movies WHERE rating > 8.0;�

title_id  | rating |  votes  |         primary_title         

-----------+--------+---------+-------------------------------

 tt0111161 |    9.3 | 2128705 | The Shawshank Redemption

 tt0105695 |    8.2 |  345874 | Unforgiven

 tt1256638 |    8.7 |       9 | Where the Water Meets the Sky

​

​

Translation to Relational Algebra

• SELECT a1, a2, …, FROM R1, R2, …, WHERE <cond>

31

Translation to Relational Algebra

• SELECT a1, a2, …, FROM R1, R2, …, WHERE <cond>

​

​

​

​

• Select-From-Where = Join (or Cross Product)-Select-Project
• Equivalent semantics:
• consider every combination of tuples from R1 x R2 x …,
• apply the condition in WHERE clause,
• add to the output as described in the SELECT clause.

32

Recap

• Data-savviness is the future!
• Notion of a DBMS
• The relational data model and algebra: bags and sets
• SQL queries
• SFW and its semantics
• LIKE, AS, *, %, …
• Null values
• Single and multiple relations

33

Queries

• Any other queries you’d like to try?

34