LS 181 Study Guide

SQL

What is it?

Joining Tables: Types of Joins

What are joins?

INNER JOIN

SELECT artwork.title, artists.name, artwork.year
FROM artwork
INNER JOIN artists
ON artwork.artist_id = artists.id;
title | name | year
------------------------+---------------------+--------
Cafe Terrace at night | Vincent Van Gogh | 1888
Antibes | Claude Monet | 1888

LEFT OUTER JOIN

SELECT artwork.title, artists.name, artwork.year
FROM artwork
LEFT OUTER JOIN artists
ON artwork.artist_id = artists.id;
title | name | year
--------------------------+---------------------+--------
Cafe Terrace at night | Vincent Van Gogh | 1888
Antibes | Claude Monet | 1888
untitled watercolor | | 1880

RIGHT OUTER JOIN

SELECT artwork.title, artists.name, artwork.year
FROM artwork
RIGHT OUTER JOIN artists
ON artwork.artist_id = artists.id;
title | name | year
--------------------------+---------------------+--------
Cafe Terrace at night | Vincent Van Gogh | 1888
Antibes | Claude Monet | 1888
| Paul Cezánne |
| Edgar Degas |

FULL OUTER JOIN

SELECT artwork.title, artists.name, artwork.year
FROM artwork
FULL OUTER JOIN artists
ON artwork.artist_id = artists.id;
title | name | year
--------------------------+---------------------+--------
Cafe Terrace at night | Vincent Van Gogh | 1888
Antibes | Claude Monet | 1888
untitled watercolor | | 1880
| Paul Cezánne |
| Edgar Degas |

CROSS JOIN

SELECT artwork.title, artists.name, artwork.year
FROM artwork
CROSS JOIN artists
title | name | year
--------------------------+---------------------+--------
Cafe Terrace at night | Vincent Van Gogh | 1888
Antibes | Vincent Van Gogh | 1888
untitled watercolor | Vincent Van Gogh | 1880
Cafe Terrace at night | Claude Monet | 1888
Antibes | Claude Monet | 1888
untitled watercolor | Claude Monet | 1880
Cafe Terrace at night | Paul Cezánne | 1888
Antibes | Paul Cezánne | 1888
untitled watercolor | Paul Cezánne | 1880
Cafe Terrace at night | Edgar Degas | 1888
Antibes | Edgar Degas | 1888
untitled watercolor | Edgar Degas | 1880

Sublanguages of SQL: DML, DDL, and DCL

“NAME THAT SUBLANGUAGE!”

DDL

CREATE TABLE artists (
id serial PRIMARY KEY
name varchar(256)
/* column definitions include type, name, optional constraints */
)
ALTER TABLE artists
ADD COLUMN birthday date NOT NULL
;
ALTER TABLE artists
ALTER COLUMN birthday
DROP NOT NULL,
ALTER COLUMN name TYPE text
;
ALTER TABLE artists
DROP COLUMN birthday
;
ALTER TABLE artwork
RENAME COLUMN title TO artwork_title
;
ALTER TABLE artwork
RENAME TO artworks
;
ALTER TABLE artwork
ALTER COLUMN title
DROP DEFAULT;
DROP TABLE artwork;

DML

SELECT * FROM artists;INSERT INTO artists (name)
VALUES ('Matisse');
UPDATE artists
SET name = 'Anonymous'
WHERE id = 3
;
DELETE FROM artists
WHERE id = 3
;

DCL

Making Queries: GROUP BY, ORDER BY, WHERE, and HAVING

GROUP BY

SELECT artists.name AS artist_name, count(artwork.id) AS num_works
FROM artists
INNER JOIN artwork
ON artists.id = artwork.artist_id
GROUP BY artists.name;
artist_name | num_works
--------------------------+-----------
Vincent Van Gogh | 2
Claude Monet | 1
SELECT artists.name AS artist_name, string_agg(artwork.title, ', ') AS works
FROM artists
INNER JOIN artwork
ON artists.id = artwork.artist_id
GROUP BY artists.name;
artist_name | works
--------------------------+-----------------------------------------
Vincent Van Gogh | Cafe Terrace at night, The Starry Night
Claude Monet | Antibes
SELECT artists.name, count(artwork.id) AS num_works, artwork.year
FROM artists
INNER JOIN artwork
ON artists.id = artwork.artist_id
GROUP BY artists.id
;
ERROR: column "artwork.year" must appear in the GROUP BY clause or be used in an aggregate function/* The above query throws an error. It cannot return the year of an artwork when grouped by artist. */name | num_works | year
--------------------------+--------------+--------------------------
Vincent Van Gogh | 2 | /* what could go here? */
Claude Monet | 1 |

ORDER BY

SELECT title, year FROM artwork
ORDER BY year ASC
;
title | year
--------------------------+--------
untitled watercolor | 1880
Cafe Terrace at night | 1888
Antibes | 1888
SELECT title, year FROM artwork
ORDER BY year ASC, title ASC
;
title | year
--------------------------+--------
untitled watercolor | 1880
Antibes | 1888
Cafe Terrace at night | 1888
SELECT year FROM artwork
GROUP BY year
ORDER BY year DESC
;
year
--------
1888
1880

WHERE

SELECT title, year FROM artwork
WHERE year > 1881
;
title | year
--------------------------+-----------
Cafe Terrace at night | 1888
Antibes | 1888

SELECT year, title FROM artwork
WHERE year > 1881
AND title LIKE '%night'
;
year | title
----------+---------------------
1888 | Cafe Terrace at night

HAVING

SELECT artists.name AS artist_name, count(artwork.id) AS num_works
FROM artists
INNER JOIN artwork
ON artists.id = artwork.artist_id
GROUP BY artists.name --note that artists.name must be used here
HAVING num_works = 2;
/* note also that having is evaluated before select, so you cannot use aliases from the select statement */artist_name | num_works
--------------------------+-----------
Vincent Van Gogh | 2

Data Integrity: Using Constraints

What are constraints?

Why use constraints?

CHECK CONSTRAINTS

/* Adding a CHECK constraint on CREATE */
CREATE TABLE artwork (
id serial PRIMARY KEY,
title text,
year numeric CHECK(year BETWEEN 1400 AND 2022)
);
/* Adding a CHECK constraint on ALTER */
ALTER TABLE artwork
ADD CHECK (year BETWEEN 1300 AND 2022)
;
ALTER TABLE artwork
ADD CONSTRAINT artwork_year_check CHECK (year BETWEEN 1300 AND 2022);
/* Dropping a CHECK constraint */
ALTER TABLE artwork
DROP CONSTRAINT artwork_year_check
;
ALTER TABLE artwork
DROP CONSTRAINT artwork_year_check
;

UNIQUE CONSTRAINTS

/* Adding a UNIQUE constraint on CREATE */
CREATE TABLE artwork (
id serial NOT NULL,
title text UNIQUE
);
/* Adding a UNIQUE constraint on ALTER */
ALTER TABLE artwork
ADD CONSTRAINT id_unique UNIQUE (id)
;
/* Dropping a UNIQUE constraint */
ALTER TABLE artwork
DROP CONSTRAINT id_unique
;

NOT NULL CONSTRAINTS

/* Adding a NOT NULL constraint on CREATE */
CREATE TABLE artwork (
id serial UNIQUE NOT NULL,
title text
);
/* Adding a NULL constraint on ALTER */
ALTER TABLE artwork
ALTER COLUMN title SET NOT NULL;
/* Dropping a NOT NULL constraint */
ALTER TABLE artwork
ALTER COLUMN title DROP NOT NULL;

PRIMARY KEY CONSTRAINTS

/* Adding a PRIMARY KEY constraint on CREATE */
CREATE TABLE artwork (
id serial PRIMARY KEY,
title text
);
CREATE TABLE artwork (
id serial,
title text,
PRIMARY KEY (id)
);
/* Dropping a PRIMARY KEY constraint */
ALTER TABLE artwork
DROP CONSTRAINT artwork_pkey
;
/* Adding a PRIMARY KEY constraint on ALTER */
ALTER TABLE artwork
ADD PRIMARY KEY (id)
;

FOREIGN KEY

/* Adding a FOREIGN KEY constraint on CREATE */
CREATE TABLE artwork (
id serial PRIMARY KEY,
artist_id integer REFERENCES artists (id)
ON DELETE CASCADE
);
CREATE TABLE artwork (
id serial PRIMARY KEY,
artist_id integer,
FOREIGN KEY (artist_id) REFERENCES artists (id)
ON DELETE CASCADE
);
/* Adding a FOREIGN KEY constraint on ALTER */
ALTER TABLE artwork
ADD CONSTRAINT artwork_artist_id_fkey
FOREIGN KEY (artist_id) REFERENCES artists (id)
ON DELETE CASCADE
;
/* Dropping a FOREIGN KEY constraint */
ALTER TABLE artwork
DROP CONSTRAINT artwork_artist_id_fkey
;

Subqueries

What is it?

SELECT name FROM artists 
WHERE id NOT IN
(SELECT artist_id FROM artwork
WHERE artist_id IS NOT NULL);
/* list of artists whose work is not in artwork table */
/* note the is not null clause for the NOT IN statement. you can't check id against null values */
name
-----------------
Paul Cezánne
Edgar Degas

Why Use Subqueries?

Common Subquery Expressions:

SELECT 1 WHERE EXISTS
(SELECT id FROM artists
WHERE name = 'Claude Monet');
/* if there are any artists named Claude Monet, it returns 1 */?column?
-----------
1
SELECT title FROM artwork WHERE EXISTS
(SELECT id FROM artists
WHERE name = 'Claude Monet'
AND artists.id = artwork.artist_id);
/* checks each row in artwork, and if there are any artists named Claude Monet whose id matches the artist_id of that row in artwork, the exists statement returns true, and adds that row of artwork to data set */title
-----------
Antibes
SELECT name FROM artists WHERE NOT EXISTS
(SELECT id FROM artwork
WHERE artists.id = artwork.artist_id);
/* checks each row in artists, and if there are any artworks whose artist_id matches the id of that row in artists, the NOT EXISTS statement returns false. */name
-----------------
Paul Cezánne
Edgar Degas
SELECT name FROM artists WHERE id IN
(SELECT artist_id FROM artwork
WHERE title LIKE 'Ant%');
/* goes through each row of artists table and compares the id value of that row to a table of artist_id's that correspond to a title beginning with 'Ant'. If the id value is in the subquery result, the name of the artist is added to the returned dataset. */name
---------------------
Claude Monet
SELECT name FROM artists
WHERE id NOT IN
(SELECT artist_id FROM artwork
WHERE artist_id IS NOT NULL);
/* list of artists whose work is not in artwork table */
/* note the is not null clause for the NOT IN statement. you can't check id against null values */
name
-----------------
Paul Cezánne
Edgar Degas
SELECT name FROM artists WHERE length(name) > ANY
(SELECT length(title) FROM artwork);
/* Returns a set of artist names that have a length greater than any of the artwork titles (here, all of them have a length greater than 'Antibes') */name
------------------
Vincent Van Gogh
Claude Monet
Paul Cezánne
Edgar Degas
SELECT title FROM artwork WHERE artist_id = ANY
(SELECT id FROM artists);
/* Returns a set of artwork titles that have a corresponding artist id in the artists table. Here two of the titles have ids in the table. */title
-----------------------
Cafe Terrace at night
Antibes
SELECT name FROM artists WHERE length(name) > ALL
(SELECT length(title) FROM artwork);
/* Returns a set of artist names that have a length greater than all of the artwork titles (here, none of them have a length greater than 'Cafe Terrace at night') */name
------------------

JOINs rewritten as Subqueries

/* JOIN */SELECT artwork.title
FROM artwork
INNER JOIN artists
ON artwork.artist_id = artists.id;
title
-----------------------
Cafe Terrace at night
Antibes

/* Subqueries */
SELECT title FROM artwork
WHERE EXISTS
(SELECT id FROM artists
WHERE id = artwork.artist_id);
SELECT title FROM artwork
WHERE artist_id IN
(SELECT id FROM artists
WHERE id = artwork.artist_id);
SELECT title FROM artwork
WHERE artist_id = ANY
(SELECT id FROM artists
WHERE id = artwork.artist_id);
/* JOIN */
SELECT artwork.year, artwork.title FROM artwork
INNER JOIN artists ON artwork.artist_id = artists.id
WHERE artwork.year > 1881
AND artists.name LIKE '%Monet'
;
/* Subqueries */
SELECT year, title FROM artwork
WHERE year > 1881 AND
EXISTS (SELECT id FROM artists
WHERE id = artwork.artist_id
AND name LIKE '%Monet');
SELECT year, title FROM artwork
WHERE year > 1881 AND artist_id =
ANY (SELECT id FROM artists
WHERE id = artwork.artist_id
AND name like '%Monet');
SELECT year, title FROM artwork
WHERE year > 1881 AND artist_id IN
(SELECT id FROM artists WHERE id = artwork.artist_id
AND name like '%Monet');

PostgreSQL

What is it?

How it works?

Sequences

What Are They?

/* How to create a sequence */
CREATE SEQUENCE counter;
/* How to access a sequence number */
SELECT nextval('counter'); /* note the single quotations */
/* How to drop a sequence */
DROP SEQUENCE counter;

Column types

Common data types

How serial pseudotype works? (aka how to create an auto-incrementing column)

How to change a column’s type?

ALTER TABLE artists
ALTER COLUMN name TYPE text
;

Default values

What is a default value?

title                     |     name            |  year
--------------------------+---------------------+--------
untitled watercolor | unknown | 1880

How does it work?

/* Define a default value with CREATE TABLE */
CREATE TABLE artists2 (
id serial PRIMARY KEY,
name text DEFAULT 'unknown'
);
/* Define a default value with ALTER TABLE */
ALTER TABLE artists2
ALTER COLUMN name SET DEFAULT 'unknown';
/* Drop a default value */
ALTER TABLE artists
ALTER COLUMN name DROP DEFAULT;
INSERT INTO artists (name) VALUES (null);     name            
---------------------
null
ALTER TABLE artists
ADD CONSTRAINT name_check CHECK (name LIKE '% %');
/* adds new constraint to require space between words */
ALTER TABLE artists
ALTER COLUMN name
SET DEFAULT 'unknown';
/* no error thrown even though 'unknown' is one word */
INSERT INTO artists (id) VALUES (8);
/* error thrown. can't insert new entry without specifying value for name, because default violates constraint */
ERROR: new row for relation "artists" violates check constraint "name_check"

Relating Data with Keys

What are keys?

PRIMARY KEYs

FOREIGN KEYs

NATURAL KEYs

SURROGATE KEYs

Database Diagrams

Schema

What is schema?

Conceptual schema

Conceptual Crow’s Foot Diagram with information about entities, relationship, and modality.

Physical schema

Physical schema diagram, containing information about keys, constraints, identifiers, and data types.

Logical schema

Modeling Schema: Cardinality & Modality

Cardinality

Modality

Representing Cardinality and Modality with crow’s foot notation

Each user is required to have one username, and each username is required to be associated with one user.
Each ticket holder must have a ticket. Each ticket may or may not be assigned to one ticket holder.
Each student can have up to one baseline test score. Each baseline test score must correspond to one student.
Each artist can make multiple works of art, but artists are not required to have made a work of art. Each work of art is required to be associated with one artist.
Each ticket must correspond to a seat. Seats may be associated with zero, one, or more tickets.
Each Student may or may not have received multiple test scores. Each test score must belong to only one student.
A product can be sold to many customers or to no customers. A customer must buy one or more products.
Events must sell one or more seats. Seats may be associated with zero, one, or multiple events.
Each student may or may not have taken multiple practice tests. Each practice test may or may not have been taken by multiple students.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store