Hands on sql introduction/ tutorial

Orginally created by Darrell Aucoin in conjuection with the Stats Club, find the original here.

Installation of SQLite Browser

SQLite Browser webpage

Windows

Download and install the file below

Windows download

Mac

Download and install the package below

Mac download

Ubuntu and Linux Mint

In terminal execute the following command

sudo apt-get install sqlitebrowser

Get the material

1. Go to https://github.com/NormallySane/IntroSQL
2. Download zip file
3. Unzip downloaded file in your preferred directory

Using SQLite Browser

1. Open SQLite browser
2. Open stats_club.db database file in the downloaded directory
3. Click on ‘Execute SQL’ tab
   1. Open SQL file IntroSQL_Presentation1.sql file in the downloaded directory, IntroSQL_Presentation2.sql for second presentation.
   2. Follow along with file, executing statement as topics dictate
4. Content of the talk is on https://github.com/NormallySane/IntroSQL/wiki (open in your favorite browser)

• SQLite browser is a great tool for learning SQLite and SQL in general

Features of SQLite Browser

• Lets you see the data structure of tables in the database

• Explore the data entered in tables

• Execute SQL statements and see results

• Easily construction tables from files

• If the table is already created it will just import the data into the table, otherwise it will create a new table

• SQLite functionality can also be extended by various user created extensions

Motivation

Q: Why learn a database lanugage?

• Data is rarely tidy as they are in many stats courses
• Most of the time the data we want (outside of assignments) is in a database

Q: Why learn SQL?

• One of the most popular data analysis tools (O’Reilly Data Scientist Survey for 2014)
• One of the most in-demand skills for 2014 (Workopolis)
• SQL not only can retrieve data, but can slice, dice and transform the data as needed

Q: What is SQL?

• A programming language allowing multiple concurrent users storing, manipulating, and querying data stored in a relational database.
• Data does not necessary have to fit into memory.

Q: Why use SQLite?

• Very easy to install
• Easy to share (database is a single file)
• Good SQL language to practice with (can easily use it to preprocess data for kaggle competitions)

Different Implementations of SQL

MySQL: Highly popular open source SQL implementation.

PostgreSQL: Open source SQL designed around letting users create User Defined Functions (UDF).

SQLite: Open sources light weight SQL usually used as an embedded database for applications (web browsers, mobile applications, etc.), or light to medium traffic websites. Database is saved as a single file and only allows one writer at a time. Various OS’s have SQLite preinstalled (type sqlite3 in terminal for mac)

Oracle: SQL implementation produced and marketed by Oracle Corporation.

Microsoft SQL Server: SQL implementation developed by Microsoft.

DB2: SQL developed by IBM. The database used by University of Waterloo.

Relational Databases

Relational Database: A relational database is a system organized in tables containing records (rows) related to other records in other tables. Each entity having attributes (columns also called fields) which give additional information on an entity.

Field, Attribute (Column): An individual piece of data stored in a table.

Record (Row): A tuple of columns describing an entity or action of an entity.

Table: A collection of rows. Usually in reference a persistent table saved permanently to memory

Result set: A non-persistent table, usually the result of a query.

(Virtual) View: A named query saved into memory and performed whenever it is named. Some SQL servers have materialized views that permanently save the data for faster access.

Subquery: A query that returns a table to another query.

Primary key: A tuple of columns that uniquely define each row in a table.

Foreign key: A tuple of columns identifying a relationship to another table.

The E-R Model

The E-R (entity-relationship) Model is an analysis tool for relational databases. E refers to entity: An object R refers to relationship: As in how objects relate to each other.

Each entity has properties associated with every instance of an object.

Objects are related to each other through relationships:

E-R Relationships

1. One to one relationships: For every instance of object A, there are exactly one instance of object B.
   • Every student has exactly one quest account
2. One to many relationships: For every instance of object A, there are an indeterminate number of instances of object B.
   • A single course section has many students
3. Many to one relationships: For many instances of object A, there are a single instance of object B.
   • Many students are enrolled in a course section
4. Many to many relationships: For many instances of object A, there are a many possible instances of object B.
   • Many students take many courses

Normalization

Database Normalization is a process in relational databases to minimize redundacy. Data can be constructed so that additions, deletions, and modifications can be made in just one table.

Data goes through three forms until it is fully utilizied by a relational database:

1. First Normalized Form: Each attribute contains only atomic values and only a single value from that domain. i.e. The data is in the form of a table with no merged columns or rows.
2. Second Normalized Form: Each attribute in the table is dependent on the entity it is describing (dependent on the primary key).
3. Third Normalized Form: All non-primary key attributes are not determined by any other non-primary key attributes.

Example: For each event for Stats Club we have: 1. a list of attendies along with some basic information (email, ect.), 2. the event’s name, type (social or education), the roles for Stats Club execs for the event, time and location of the event, and a budget, 3. as well as a list of expenses for the event

A report of the event would look something like:

We first need to tabulate the data:

However this is not enough, the data is not in a form that can be easily recognized by computers. - How do you add new events? - What about members that attend multiple events? - that attend no events?

1st Normalization

First Normalized Form: Each attribute contains only atomic values and only a single value from that domain

Example: To bring the data into 1st normalized form we need to break the table into two tables: Event, and Expenses:

• This contains all of the information before but is more organized for the computers to deal with
• Still not enough, a mispelling of an event or type could make the database think there is a new event

2nd Normalization

Second Normalized Form: Each attribute in the table is dependent on the entity it is describing (dependent on the primary key).

Example: To bring the data into 2nd normalized form, we need to break the Event table again. Let’s break the table so we get important description of the events (name, type, presenter, organizer, etc.) and a list of members that attended each event.

• Attendance is 2nd normalized form if we consider the primary key as the tuple of event and quest ID.
• Attendance still has redundant information, several members can attend multiple events or none at all

3rd Normalization

Third Normalized Form: All non-primary key attributes are not determined by any other non-primary key attributes.

Example: The information on each member (name, email, etc.) is not determined by the event. We need to break the attendance table to bring into 3rd normalized form: attendance and members.

Primary Keys and Foriegn Keys

We can reconstruct the orignal table by joining tables, foreign keys with what they reference (primary keys). - We can only construct an instance of a foreign keys if their is already an instance of their reference - i.e. the set of foreign keys for one table is a subset of the primary keys they reference

Primary key: A tuple of columns that uniquely define each row in a table. (Red items below) Foreign key: A tuple of columns identifying a relationship to another table. (Blue items below)

Quiz: Normalization

Normalize the following table:

Solutions are here

Relational Algebra Operations

Projection: Returns a subset of columns.

Selection: Returns only entities where some condition is true.

Rename: Rename an attribute.

Natural Join: Tuples from one table is joined to tuples from another table based on common attributes (at least one column with the same name and possible values is common between them)

Θ__-Join and Equijoin:__ Join tuples from two different tables where some binary condition (Θ = {≥, ≤, >, <, =}) between two tables attributes is true. When Θ is =, the join is called an equijoin.

Set Operations: Set theory’s unions, set difference, and cartesian product of tuples performed on tuples of different tables.

Quiz: Relational Algebra

Q1: What kind of operation is performed on the following tables?

Q2: What kinds of joins can peform on tables?

Solutions are here

Constraints

Constraints limit what can be entered into fields in a table and help ensure encapsulation:

PRIMARY KEY constraints Uniquely identifies each record in a table (quickly referencing it)

FOREIGN KEY constraints Points to a PRIMARY KEY of another table, enabling them to easily join them

CHECK constraints Limits the range of values that a field can take.

UNIQUE constraints Enforces uniqueness on an field (column).

NOT NULL constraints Enforces a field to always contain a value.

• We can also create indexes for fields making them easily searchable

SQL Language

• Case does not matter, for presentation purposes UPPER CASE is used for SQL key words

• SQL statements are processed as a whole (ignoring white space and new lines) and ends with a ‘;’

• We will be used a database based on Stats Club, supplied with fake data:

Data Types

In SQLite3, there are 5 basic data types:

1. INTEGER The value is a signed integer.

2. REAL The value is a floating point value, stored as an 8-byte IEEE floating point number.

3. TEXT The value is a text string.

4. BLOB The value is a blob of data, stored exactly as it was input.

5. NUMERIC May contain integer or real values. The data is stored as text but converted as necessary.

SQL General Data Types

• Different implementations of SQL uses different datatypes but there are some general commonalities:

NULL Values

NULL A NULL can be thought of as an unknown value. - Any datatype can have NULL values - if either x or y is NULL:

SQL uses a three-value logic system: TRUE, FALSE, NULL:

Projection (SELECT Clause)

SELECT Statement: The SELECT statement returns a table of values (sometimes empty) as specified in the statement.

SELECT Clause: The SELECT clause of a SELECT statement specifies the columns for the result set.

SELECT col1, col2, ...
FROM table_name;

• We can also specify all columns from a table

SELECT *
FROM table_name;

• If we leave out ‘FROM table’ we are effectively taking from an empty table

In SELECT clauses, we can specify more than just columns from a table: Literials: Strings, numbers that are repeated for every row Expressions: Expressions of columns/literals Functions: Built in functions in SQL (ROUND(), etc.) User Defined Functions: Functions that a user can create within SQL to run

Example: Let’s try something simple: some literials and a calculation of 1 / 2

SELECT 1, 2, 'this is a string', 1/2;

Q: What happened here?

A: Since 1 and 2 are both integers, the expression 1/2 returns an integer. To get 0.5, we need to use a real number (a FLOAT).

SELECT 1, 2, 'this is a string', 1/2, 4/2, 5/2, 1/2., 1./2, 1/CAST(2 AS FLOAT);

SQL statements ignore white spaces and new lines, the statement is only processed when after it sees ‘;’

Example: The following two statements produce the same table.

SELECT 1, 2, 'this is a string', 1/2;

SELECT 1,
2,
             'this is a string',   1/2;

SQL Functions

There are many functions in SQLite and each implementation of SQL have different functions. Here are some random functions in SQLite:

SELECT ABS(-8), LENGTH('This is a String'), LOWER('ThIS Is A StRiNg'), RANDOM();

There are many more core functions within SQLite.

Quiz: SELECT Clause

Calculate the average of 2 random numbers.

Q: What is the upper and lower case for the string ‘UPPER or lower’?

Solutions are here

FROM Clause

FROM Clause: Specifies the table: either a persistant table, or a result set: a join of two or more tables or a subquery or some combination of the two.

SELECT col1, col2, ...
FROM table_name;

Example: What are the names, type, times and locations for Stats Club?

SELECT name, type, start_time, end_time, location
FROM event;

Quiz: FROM Clause

Q: Who are the execs for Stats Club, and what are their positions, and emails? - Projection of name, position, and email from the table exec

Solutions are here

Aggregate Functions

Aggregate Functions: Takes in the columns of a table and aggregates over the entries.

• There are more aggregate functions for other implementations of SQL

More detailed descriptions of the aggregate functions within SQLite can be found here.

SELECT COUNT( 12 ), COUNT('ssdf'), COUNT(NULL), SUM(23), SUM(0), SUM(NULL),
  AVG(0), AVG(NULL);

Lets work with some aggregate functions with the table example below:

SELECT COUNT(*), COUNT(string), COUNT(floating), AVG(number), SUM(number),
GROUP_CONCAT(string, ' ')
FROM example;

DISTINCT Prefix

In the SELECT clause we can specify to return only distinct tuples of columns

SELECT DISTINCT col1, col2, ...
FROM table_name;

• We can also use DISTINCT within aggregate functions making them only aggregate over distinct entries

SELECT aggregate_function(DISTINCT column_name)
FROM table_name;

Example: What events have members attended? - What are the distinct events where at least one member attended?

SELECT DISTINCT event
FROM attendance;

Quiz: DISTINCT

Q: What are the distinct majors of Stats Club members?

Q: How many distinct majors of Stats Club members are there?

• DISTINCT can be within aggregate functions

Solutions are here

Alias

Column Alias

To increase the readability of SQL as well as the result set, we can give columns new names:

SELECT col AS new_name
FROM table_name;

• Column aliases make the final table more readiable and workiable for subqueries

Table Alias

We can also give tables new names as well:

SELECT table_alias_1.col_name, table_alias_2.col_name, ...
FROM table_1 AS table_alias_1, table_2 AS table_alias_2;

• Very useful when tables have common column names

Note: We can reference what table a column is coming from by a ‘.’

table_name.column_name

Example: If we give column aliases for the previous table, we make the result more interpretiable:

SELECT COUNT(*) AS num_rows, COUNT(string) AS num_strings,
  COUNT(floating) AS num_float, AVG(number) AS avg_integer,
  SUM(number) AS sum_int, GROUP_CONCAT(string, ' ') AS cat_string
FROM example;

Quiz: Aliases

Perform a query using a table alias, and use this table alias when referencing the column i.e. table_alias.column_name - This will be really important for bigger, more complicated queries

Solutions are here

Selection (WHERE Clause)

WHERE clauses filters the result set, removing rows where the condition returns either FALSE or NULL.

SELECT col1, col2, ...
FROM table_name
WHERE condition;

Example: What Stats Club events are social?

SELECT name, type, start_time, end_time, location
FROM event
WHERE type = 'social';

Prediate Operators

LIKE Predicate

LIKE predicates

‘_’ means a character of any type ‘%’ means between 0 or more characters of any type

Example: What Stats Club members has a name begining with F?

SELECT *
FROM member
WHERE name LIKE 'F%';

Operator Modifiers

ALL, ANY or SOME Operator Modifiers

The operators =, <>, !=, >, <, >=, <= can be used with a list of values and the operators ALL or ANY / SOME.

• SQLite does NOT have ALL or ANY / SOME implemented

ANY, SOME Operator returns true, if operator is true for any value in the set.

ALL Operator returns true, if operator is true for all values in the set.

SELECT *
FROM table_name
WHERE column_name < ALL (subquery_returning_one_column);

Example: What are the expenses for non-social events? (ALL, ANY, and SOME are not implmented in SQLite)

SELECT * 
FROM expenses 
WHERE event != ALL (SELECT name 
                    FROM event
                    WHERE type = 'social');

AND, OR Operators

A group of filter conditions can be linked together with AND or OR operators.

SELECT col1, col2, ...
FROM table_name
WHERE (condition1 AND condition2 ) OR condition3;

Example: What Stats Club members has a name with it’s second letter as A or ends in B?

SELECT name
FROM member
WHERE name LIKE '_a%' OR  name LIKE '%b';

Quiz: Filtering (WHERE Clause)

Q: What events for Stats Club are introductory talks? - Introductory talk names start with ‘Intro’

Q: What Stats Club members have their first name starting with a letter BETWEEN A and G?

Solutions are here

GROUP BY Clause

• GROUP BY clause groups the table by a column (or tuple of columns) and applies a function to each group
  • In the SELECT portion of the statement, you can only list the grouped column(s) and aggregate functions on them.

SELECT col1, col2, ..., aggregate_function
FROM table_name
GROUP BY col1, col2, ...;

Aggregate Functions

Recall: Aggregate Functions: Takes in the columns of a table and aggregates over the entries. - If we use a GROUP BY clause, the aggregation will be over those groups

• There are more aggregate functions for other implementations of SQL

Example: What are the number of each type of event for Stats Club?

SELECT type, COUNT(*) AS num_events
FROM event
GROUP BY type;

HAVING Clause

HAVING clauses are very similar to WHERE clauses but can have aggregate function in their conditions.

You can have a WHERE and HAVING clause in the same statement.

Example: How many Stats Club members are in each major where the major has at least 2 members?

SELECT faculty, major, COUNT(*)
FROM member
GROUP BY faculty, major
HAVING COUNT(*) >= 2;

GROUP BY with ROLLUP / CUBE

The ROLLUP operator produces a result set where the aggregate function is applied to each level of the GROUP BY hierachy. - The ROLLUP operator is NOT implemented in SQLite - Useful for making reports with totals and subtotals

SELECT col1, col2, ..., aggregate_function
FROM table_name
GROUP BY col1, col2, ... WITH ROLLUP;

Example: What are the number of Stats Club members in each faculty and major, including subtotals?

SELECT faculty, major, COUNT(*) AS num_members
FROM member
GROUP BY faculty, major WITH ROLLUP;

Note that for Art and Math in faculty there is a row which has a NULL value. This is a total for those groups.

There is also one row with NULL values for faculty and major, this is the grand total of all members.

Quiz: Aggregation (GROUP BY Clause)

Q: What is the attendance for each Stats Club event?

Solutions are here

Joins

At times, we need information from multiple tables, to do this we need to join tables together. We can do this several ways:

1. CROSS JOIN: The cartesian product of rows from each table.
2. INNER JOIN: Join two tables on a join-predicate, losing rows when evaluated false/null.

3. OUTER JOIN: Retains each record for the table(s) even when it has no matching rows from the other table. The returning table has null values for missing records.

   1. LEFT OUTER JOIN: Keep each record for first table but not the table it’s joining with.

   2. RIGHT OUTER JOIN: Keep each record for second table but not the table it’s joining with.

   3. FULL OUTER JOIN: Keep all record for all tables.

4. NATURAL JOIN: Tables with the exact same column name and datatype are joined along that column.

CROSS JOIN

CROSS JOIN is the cartesian product of two tables

SELECT col1, col2, ...
FROM table1 CROSS JOIN table2;

• if table1 has n rows and table2 has m rows, then the result set has n * m rows

We can also get a CROSS JOIN by listing the tables seperated by a ‘,’

SELECT col1, col2, ...
FROM table1, table2;

Example: Suppose we are creating a games tourtament between Stats Club members where every member play every other member once. How can we create such a table?

SELECT m1.name AS Player_1, m2.name  AS Player_2
FROM member AS m1 CROSS JOIN member AS m2;

However we have have players playing themselves, and rounds with the same players in opposite roles. We just need to filter these out.

SELECT m1.name AS Player_1, m2.name  AS Player_2
FROM member AS m1 CROSS JOIN member AS m2
WHERE m1.name > m2.name;

INNER JOIN

INNER JOIN Joins two tables where the join condition returns true. Discarded when returning false or NULL.

SELECT col1, col2, ...
FROM table1 INNER JOIN table2 ON condition;

ON Clause

The ON clause specifies the join condition:

• The ON clause can use a multiple set of conditions connected by AND, OR

• USING() can also be used if both tables have the same column name and type

• Some SQL implementations constructs the ON clause from the WHERE clause (DB2)

  • filtering by the WHERE clause gives the same result but in some implementations will product an intermediate cross product of tables (making the query slower)

Example: How many events does each member attend?

• Note that this query does not include members who attended no events
  • Query is ordered by events_attended to show a comparison with a latter query

SELECT m.name, COUNT(a.event) AS events_attended
FROM member AS m INNER JOIN attendance AS a ON m.quest_id = a.member
GROUP BY m.name
ORDER BY events_attended;

OUTER JOIN

OUTER JOIN A join that returns all rows for 1 or 2 tables, even when there is no corresponding value. In these cases, NULL values are entered for these corresponding rows.

There are 3 types of OUTER JOINs:

1. LEFT OUTER JOIN: An OUTER JOIN returning all rows of the table first mentioned.

2. RIGHT OUTER JOIN: An OUTER JOIN returning all rows of the table second mentioned.

3. FULL OUTER JOIN: An OUTER JOIN returning all rows of both tables.

• Only LEFT OUTER JOIN is implemented in SQLite

Example: What are the names of Stat Club members and how many events they attended?

SELECT m.name, COUNT(a.event) AS events_attended
FROM member AS m LEFT OUTER JOIN attendance AS a ON m.quest_id = a.member
GROUP BY m.name
ORDER BY events_attended;

Natural Join

NATURAL JOIN A join condition that lets the server decide on the join conditions based on the same column names and types across columns for the tables.

Example: What are the position and duties of each Stats Club exec?

SELECT  e.name, e.position, ep.duties
FROM exec AS e NATURAL JOIN exec_position AS ep;

Quiz: Joining Tables

Q: What are the email addresses and phone numbers of stats club execs who are in change or organizing at least one event?

Solutions are here

Subqueries

Subqueries are queries contained in queries. These subqueries are contained in ‘(‘, ‘)’

There are two types of subqueries:

1. Non-Correlated Subqueries: Can be run independently of the larger query.

2. Correlated Subqueries: Must be run concurrently with the outer query. They are dependent on the outer query.

Non-Correlated Subqueries

Non-Correlated Subquery: Any valid query within query that if executed by itself will produce a result (including empty set). These are enclosed in ‘(‘, ‘)’ in FROM, WHERE, or HAVING clauses.

Example: What Stats Club execs are in charge of making posters?

SELECT e.name, e.position
FROM exec AS e
WHERE e.questid IN (SELECT poster FROM event);

Correlated Subqueries

Correlated Subquery makes references to it’s containing query, executing it for every candidate row referenced. - Correlated subqueries can appear in the SELECT, WHERE, or HAVING clauses.

Example: What majors are the current Stats Club execs enrolled in? - The member table has the information on majors but exec has the information on execs

SELECT name, position,
(SELECT faculty FROM member AS m WHERE m.quest_id = e.questid) AS faulty,
(SELECT major FROM member AS m WHERE m.quest_id = e.questid) AS major
FROM exec AS e;

Correlated vs Non-Correlated

1. Correlated subquery is dependent on outer query, non-correlated is independent.

2. Correlated subquery is executed concurrently with outer query, non- correlated is executed before.

3. In general, for speed of execution:

Quiz: Subqueries

Q: Where can a Non-Correlated subquery can be placed?

Q: Where can a Correlated subquery can be placed?

Q: Using a non-correlated subquery, what are the names, locations, and descriptions of events that served pizza? 1. Break the problem into smaller pieces: What are the events that served pizza? 2. Only retrieve values from the table event that event.name matches those values

Solutions are here

Set Operations

Set operations create a combination of rows from 2 tables into one result set. - The tables (or the projection of those tables) have to have the same number of columns and datatypes - if one column appears in a table but not in another, supply the value NULL for the missing column

1. UNION: For tables A and B, combines the rows for A and B into one result set.
2. INTERSECT: For tables A and B, returns the rows for A and B that they have in common.
3. Difference (EXCEPT): For tables A and B, return only the rows in A that are not in common with B.

UNION

UNION operator: Addition of one result set to another result set with the same number of attributes and types.

SELECT ... FROM ...
UNION [ALL]
SELECT ... FROM ...

• Just UNION removes duplicates, while UNION ALL keeps all rows from both result sets.

Example: Suppose are you need to contact everyone involved in Stats Club: members and execs. What are the names, email addresses, and phone numbers (if you have them) of all Stats Club members and execs?

SELECT name, email, NULL AS phone
FROM member
UNION
SELECT name, email, phone
FROM exec;

Intersection (INTERSECT Operator)

INTERSECT operator: Returns only tuples that are in common between two result sets. Result sets must be equal in number and type of attributes.

SELECT ... FROM ...
INTERSECT
SELECT ... FROM ...;

Example: What Stats Club execs have also signed up as Stats Club members?

SELECT name, quest_id
FROM member
INTERSECT
SELECT name, questid
FROM exec;

Difference (EXCEPT Operator)

EXCEPT operator: Returns the first result set minus anything it has in common with the second result set.

SELECT ... FROM ...
EXCEPT [ALL]
SELECT ... FROM ...

• Just EXCEPT uses set theory version of minus.

  • If B has a row in common with A then all rows matching that row is removed

• The optional ALL uses the bag semantics version.

  • If B has a row in common with A then only the number of common rows in B rows matching that row is removed

Example: What Stats Club members are not execs?

SELECT name, quest_id
FROM member
EXCEPT
SELECT name, questid
FROM exec;

WITH Clause

WITH clause: Makes a non-correlated subquery look like a table in the executed statement:

• Increases readability of the query as well as ensure that if it is used in several different places, it will only be executed once

WITH subquery_name [(colname1, ...)] AS
        (SELECT ...),
        subquery_name2 [(colname1, ...)] AS
        (SELECT ...)
SELECT ...

Example: Suppose you are doing a report for MathSoc detailing Stats Club events, budget, and expenses.

WITH cost (event, expenses) AS
    (SELECT event, SUM(price)
    FROM expenses
    GROUP BY event)
SELECT e.name, e.type, e.budget, cost.expenses
FROM event AS e INNER JOIN cost ON e.name = cost.event;

Quiz: WITH Clause (HARD)

Q: MathSoc only provides a maximum cap on social events based on the formula below. What is the max cap for social expenses and is Stats Club over this limit? - Membership fee for Stats Club is 2 dollars

Break the problem into smaller problems: - What are the total expenses for social events? - What is the max budget for social events?

Smaller Problems: - What are the total expenses for social events? - What events are social events?

• What is the max cap for social events?
  • What is the result of the formula
  (MathSoc Members) * 7 - What are the number of Stats Club members who are in the Math Faculty (aka MathSoc members)? - How do we find the max value between this number and 250?

#### Think about how to solve this for 5 mins

Solutions are here

CASE Expressions

CASE expressions: Similar to a series of if else statements executed for every entry in a table. A new value is returned for every row in the table.

CASE [column]
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        WHEN condition_n THEN result_n
        [ELSE result]
END

• The result can be of any datatype or the result of a correlated or non- correlated subquery (if the result is a single value)

• CASE expressions are performed by themselves in the SELECT clause or within a function or aggregate function

  • CASE expressions within aggregate functions allow us to do counts, sums, averages, etc. of particular occurrences

Example: Suppose like before we are preparing a report for MathSoc, but now we want to give a warning if the event is over budget or not.

WITH cost (event, expenses) AS
    (SELECT event, SUM(price)
    FROM expenses
    GROUP BY event)
SELECT e.name, e.type, e.budget, cost.expenses,
    CASE
    WHEN e.budget - cost.expenses < 0 THEN 'Over budget'
    ELSE NULL
END AS warning
FROM event AS e INNER JOIN cost ON e.name = cost.event;

Quiz: CASE Expressions

Q: Suppose we are interested in the healthiness of our food options at Stats Club events. A score of various foods was given below. What is the average ‘healthiness’ of Stats Club events?

Solutions are here

ORDER BY Clause

ORDER BY Clause: Rearranges the rows of a result set according to a tuple of columns.

SELECT column_list
FROM table_name
ORDER BY column1, column2, .. columnN [ASC | DESC];

ASC is ascending (default)

DESC is descending

Example: What is the membership list ordered by name?

SELECT name
FROM member
ORDER BY name;

LIMIT Clause

LIMIT Clause: Restrict the result set to the first n of rows.

SELECT column1, column2, columnN
FROM table_name
LIMIT no_of_rows;

• We can also offset where the restriction begins

SELECT column1, column2, columnN
FROM table_name
LIMIT no_of_rows OFFSET row_num;

Example: What is the first 10 members ordered by name?

SELECT name
FROM member
ORDER BY name
LIMIT 10;

Quiz: ORDER BY and LIMIT Clause

Q: What are the top 10 highest priced items in expenses?

Solutions are here

Further Practice Questions?

Quiz: Practice Questions

Q: What events have dates specified?

Q: What events don’t have dates specified?

Q: What Stats Club members are in Stats, Act Sci, or CS? - Recall the IN predicate operator

Q: What are the Stats Club exec positions? - Avoid duplication of positions

Q: How many different Stats Club exec positions are there? - Note that we can use DISTINCT within aggregate functions

Topics Not Covered

• How to create a database
  • Creating tables
  • Creating constraints
  • Window functions
  • Indices
  • Views
  • How to insert, delete, alter, drop, etc. data in a table