Lesson-SQL.qmd

---
title: "Biostat 823 - SQL"
author: "Hilmar Lapp"
institute: "Duke University, Department of Biostatistics & Bioinformatics"
date: "Sep 5, 2024"
format:
  revealjs:
    slide-number: true
editor: visual
knitr:
  opts_chunk:
    echo: TRUE
---

## Structured Query Language (SQL) {.smaller}

-   First published in 1974, uses Codd's relational model
-   Several successive standardizations
    -   Most universally supported: SQL-92
    -   Most recent: SQL-2016 (includes JSON)
-   Divided into parts:
    -   Data Definition Language (DDL): create table, constraint, etc
    -   Data Manipulation Language (DML): insert, update, delete
    -   Data Query Language (DQL): select
    -   Data Control Language (DCL): grant and revoke privileges

## Notation and syntax for code examples

-   SQL is case-insensitive. We will use case to signify SQL keywords and to distinguish them from table, column etc names from the E-R model.
-   We use [SQLite](https://www.sqlite.org/) as the RDBMS, but where possible try to stick to SQL-92, which should work under any modern RDBMS
-   We try to point out where constructs might need modification for other RDBMSs.

## Literate Programming with SQL {.smaller}

Knitr (the workhorse behind rendering Rmarkdown) supports a variety of "engines", including SQL:
```{r label=knitrEngines}
names(knitr::knit_engines$get())
```

In R, the [`RSQLite` package](https://rsqlite.r-dbi.org) implements a driver for SQLite to enable the API of the [`DBI` package](https://dbi.r-dbi.org). Here we create an in-memory database, and make the connection object the default for every chunk:
```{r label=setupSQLite}
library(DBI)

db <- dbConnect(RSQLite::SQLite(), ":memory:")

knitr::opts_chunk$set(connection = "db")
```

<!-- ensure foreign key enforcement is enabled, they can be off by default -->
```{sql label=enforceFKs, echo=FALSE}
PRAGMA foreign_keys = ON;
```

## DDL

-   DDL consists of *statements* (SQL commands that return a status or a count, but not a result set).
    -   `{CREATE, DROP, ALTER} TABLE`
    -   `{CREATE, DROP} VIEW`
    -   `{CREATE, DROP} INDEX`
    -   `DROP {TABLE, VIEW, INDEX}`

## E-R Model

We'll be implementing (i.e., _instantiating a physical model_) of this E-R model:

```{mermaid}
erDiagram
    Instructor        ||--|{ Lesson : teaches
    Course            ||--|{ Lesson : "consists of"
    Room              |o--o{ Course : "used for"
    Instructor {
      integer Instructor_OID PK
      string  Name
      string  Email
    }
    Course {
      integer Course_OID PK
      string Name
      string Semester
      integer Room_OID FK
    }
    Room {
      integer Room_OID PK
      string Name
      string Address
    }
    Lesson {
      string Name
      integer Instructor_OID FK
      integer Course_OID FK
    }
```

## Create and define entities

-   We use `CREATE TABLE` to define (create) relations (entities in an E-R model), along with their constraints:

```{sql label=createTable1}
-- Double minus is the standard syntax for comment (single-line).
CREATE TABLE Instructor (
  Instructor_OID INTEGER PRIMARY KEY, -- surrogate PK (implies NOT NULL)
  Name VARCHAR(128) NOT NULL,         -- by default, NULL is allowed
  Email VARCHAR(128) NOT NULL UNIQUE  -- natural primary key
);
```

::: aside
In SQLite, tables by default have a hidden property named rowid, which uniquely identifies each row. A `INTEGER PRIMARY KEY` automatically becomes an alias for rowid. To enable auto-generated surrogate primary keys for other data types, one needs to add `AUTOINCREMENT`, and other RDBMSs may need a different mechanism.
:::

## Composite natural keys

```{sql label=createTable2}
CREATE TABLE Course (
  Course_OID INTEGER PRIMARY KEY,
  Name VARCHAR(64) NOT NULL,
  Semester VARCHAR(16) NOT NULL,
  UNIQUE (Name, Semester) 
);
```

Note that _table constraints_ must be at the end of a table definition.

## Foreign key constraints

```{sql label=createTable3}
CREATE TABLE Lesson (
  Name VARCHAR(64) NOT NULL,
  Instructor_OID INTEGER NOT NULL
      -- foreign key constraint can be part of column definition
      REFERENCES Instructor (Instructor_OID)
      ON DELETE RESTRICT,
  Course_OID INTEGER NOT NULL,
  -- primary key can be composite, and be defined separately
  PRIMARY KEY (Name, Course_OID),
  -- foreign key constraints can be defined separately
  FOREIGN KEY (Course_OID)
      REFERENCES Course (Course_OID)
      ON DELETE CASCADE
);
```

-   For "weak" entities, natural PK will include a foreign key.
-   Entities that are never referenced in the E-R model by a foreign key may not need a surrogate primary key.

## Creating indexes

-   Indexes speed up queries.

    -   But they also increase transaction cost

    ```{sql label=createIdx1}
    CREATE INDEX Instructor_Name_Idx ON Instructor (Name);
    ```

-   Indexes can add a uniqueness constraint (`CREATE UNIQUE INDEX`), but better practice is to include those in the table definition

-   For transaction performance, columns in `FOREIGN KEY` constraints should typically be indexed

```{sql label=createIdx2}
CREATE INDEX Lesson_Course_Idx ON Lesson (Course_OID);
```

```{sql label=createIdx3}
CREATE INDEX Lesson_Instructor_Idx ON Lesson (Instructor_OID);
```

## Altering table definitions

-   Add table for rooms:

```{sql label=createTbl4}
CREATE TABLE Room (
  Room_OID INTEGER PRIMARY KEY,
  Name VARCHAR(32) NOT NULL UNIQUE,
  Address VARCHAR(128)
)
```

-   Now add a foreign key to the courses table:

```{sql label=alter1}
ALTER TABLE Course
  ADD COLUMN Room_OID INTEGER
  REFERENCES Room (Room_OID) ON DELETE SET NULL;
```

- Altering table definitions is usually done as part of a schema migration.

## DML

-   DML also consists of *statements* (SQL commands that return a status or a count, but not a result set).
    -   `INSERT`
    -   `UPDATE`
    -   `DELETE`

## Inserting rows: Basics

* `INSERT` statements add rows to tables.
  - Note that auto-generated surrogate primary keys should not be supplied. 
```{sql label=insert1}
INSERT INTO Instructor (Name, Email) VALUES (
  'Hilmar Lapp', 'h.lapp@duke.edu'
);
```
* Multiple rows can be specified:
```{sql label=insert2}
INSERT INTO Instructor (Name, Email) VALUES
    ('Kouros Owzar', 'k.owzar@duke.edu')
  , ('David Page', 'd.page@duke.edu')
  , ('Cliburn Chan', 'c.chan@duke.edu')
  , ('Chengxin Yang', 'chengxin@duke.edu');
```

---

* NULLable columns can be omitted, or provided as NULL
```{sql label=insertRoom1}
INSERT INTO Room (Name) VALUES ('North 100');
```
```{sql label=insertRoom2}
INSERT INTO Room (Name, Address) VALUES ('Hock 10089', NULL);
```
* This extends to optional foreign keys
```{sql label=insertCourse1}
INSERT INTO Course (Name, Semester) VALUES
    ('Biostat 823', 'Fall 2022'); 
```

## Violating constraints results in error

* Violating a uniqueness constraint (natural primary key)
```{sql label=insertErr1, error=TRUE}
INSERT INTO Instructor (Name, Email) VALUES
    ('H. Lapp', 'h.lapp@duke.edu');
```

* Violating a NOT NULL constraint (required value)
```{sql label=insertErr2, error=TRUE}
INSERT INTO Instructor (Email) VALUES ('first.last@duke.edu');
```

* Violating a foreign key constraint (non-existent row)
```{sql label=insertErr3, error=TRUE}
INSERT INTO Course (Name, Semester, Room_OID) VALUES
    ('Biostat 823', 'Fall 2024', 10);
```

## Inserting foreign keys

* To insert rows with foreign key values, the statement can include a query in place of a literal value (a.k.a. _subquery_):
```{sql label=insertRoomFK}
INSERT INTO Course (Name, Semester, Room_OID) VALUES
    ('Biostat 823', 'Fall 2024',
        (SELECT Room_OID FROM Room WHERE Name = 'Hock 10089')
    );
```
* In programming, foreign key values are typically first retrieved in a separate query and then included as a literal ($\rightarrow$ more concise and potentially faster INSERT statement).

## Updating rows: Basics

* The common case is to update specific row(s) matching a condition:

```{sql label=update1}
UPDATE Room
SET    Address = '2424 Erwin Rd, Durham, NC 27705'
WHERE  Name = 'Hock 10089'
```

* The `WHERE` clause is optional; if left off, updates _all_ rows.
* Updates that create a constraint violation result in error:

```{sql label=update2, error=TRUE}
UPDATE Instructor
SET    Email = 'first.last@duke.edu'
```

## Updating foreign keys

* Similar to `INSERT` statements, `UPDATE` statements can also use a query in place of a literal value to retrieve a value dynamically:
```{sql label=updateFk}
UPDATE Course
SET    Room_OID = (SELECT Room_OID FROM Room WHERE Name = 'Hock 10089')
WHERE  Name = 'Biostat 823' AND Semester = 'Fall 2022';
```

## Deleting rows: Basics

* The common case is to delete row(s) matching a specific condition:
```{sql label=delete1}
DELETE FROM Room
WHERE Name = 'North 100'; 
```
* The `WHERE` clause is optional; if left off, _all_ rows are deleted.

* Note that for both `UPDATE` and `DELETE`, no rows matching the condition is not an error.
```{sql label=delete2}
DELETE FROM Instructor
WHERE Name = 'John Smith';
```

## Foreign key enforcement on delete

* Enforcement action depends on FK constraint definition:

```{.sql code-line-numbers="5,6,9,10,16,17"}
CREATE TABLE Lesson (
  -- ... (non-foreign key columns etc)
  Course_OID INTEGER NOT NULL
      REFERENCES Course (Course_OID)
      -- deleting a course cascades to deleting all its lessons
      ON DELETE CASCADE,
  Instructor_OID INTEGER NOT NULL
      REFERENCES Instructor (Instructor_OID)
      -- prevent deletion of instructor if they are assigned to lessons
      ON DELETE RESTRICT
);
CREATE TABLE Course (
  -- ... (non-foreign key columns etc)
  Room_OID INTEGER
      REFERENCES Room (Room_OID)
      -- if FK is optional, setting to NULL could be appropriate
      ON DELETE SET NULL
);
```

<!-- populate other tables -->
```{sql label=insertCourse21, echo=FALSE}
INSERT INTO Course (Name, Semester) VALUES
    ('Biostat 823', 'Fall 2021'); 
```
```{sql label=insertCourse23, echo=FALSE}
INSERT INTO Course (Name, Semester, Room_OID) VALUES
    ('Biostat 823', 'Fall 2023',
        (SELECT Room_OID FROM Room WHERE Name = 'Hock 10089')
    );
```
```{sql label=insertLessonHL01, echo=FALSE}
INSERT INTO Lesson (Name, Instructor_OID, Course_OID)
SELECT l.*, i.Instructor_OID, c.Course_OID
FROM (VALUES ('Containerization'),
             ('Relational Data Modeling'),
             ('Python for AI/ML'),
             ('Reproducible Worflows')) AS l, 
     Instructor AS i, Course AS c
WHERE i.Email = 'h.lapp@duke.edu'
AND   c.Name = 'Biostat 823' AND c.Semester = 'Fall 2024'
;
```
```{sql label=insertLessonHL02, echo=FALSE}
INSERT INTO Lesson (Name, Instructor_OID, Course_OID)
SELECT l.*, i.Instructor_OID, c.Course_OID
FROM (VALUES ('Containerization'),
             ('Relational Data Modeling')) AS l, 
     Instructor AS i, Course AS c
WHERE i.Email = 'h.lapp@duke.edu'
AND   c.Name = 'Biostat 823' AND c.Semester = 'Fall 2023'
;
```
```{sql label=insertLessonDP01, echo=FALSE}
INSERT INTO Lesson (Name, Instructor_OID, Course_OID)
SELECT l.*, i.Instructor_OID, c.Course_OID
FROM (VALUES ('Databases and Graphs'),
             ('NP-Completeness'),
             ('Deep Neural Networks')) AS l, 
     Instructor AS i, Course AS c
WHERE i.Email = 'd.page@duke.edu'
AND   c.Name = 'Biostat 823' AND c.Semester = 'Fall 2022'
;
```
```{sql label=insertLessonKO01, echo=FALSE}
INSERT INTO Lesson (Name, Instructor_OID, Course_OID)
SELECT l.*, i.Instructor_OID, c.Course_OID
FROM (VALUES ('General Linear Model'),
             ('Expectation-Maximization'),
             ('Applications of vector calculus')) AS l, 
     Instructor AS i, Course AS c
WHERE i.Email = 'k.owzar@duke.edu'
AND   c.Name = 'Biostat 823' AND c.Semester = 'Fall 2023'
;
```
```{sql label=insertLessonDP02, echo=FALSE}
INSERT INTO Lesson (Name, Instructor_OID, Course_OID)
SELECT l.*, i.Instructor_OID, c.Course_OID
FROM (VALUES ('Recurrent neural networks'),
             ('Transformers'),
             ('How ChatGPT works')) AS l, 
     Instructor AS i, Course AS c
WHERE i.Email = 'd.page@duke.edu'
AND   c.Name = 'Biostat 823' AND c.Semester = 'Fall 2023'
;
```
```{sql label=insertLessonCC01, echo=FALSE}
INSERT INTO Lesson (Name, Instructor_OID, Course_OID)
SELECT l.*, i.Instructor_OID, c.Course_OID
FROM (VALUES ('Python Programming'),
             ('Python Visualization')) AS l, 
     Instructor AS i, Course AS c
WHERE i.Email = 'c.chan@duke.edu'
AND   c.Name = 'Biostat 823' AND c.Semester = 'Fall 2021'
;
```
<!-- end populating other tables -->

## DDL and DML have many more options

* For example, see the [column constraint grammar for SQLite](https://www.sqlite.org/syntax/column-constraint.html), which is part of the [`CREATE TABLE`](https://www.sqlite.org/lang_createtable.html) syntax definition.
* Not all clauses are supported by all major RDBMSs.
* When persisting data to a database in a programming language environment, table creation, inserts, etc will often be handled under the hood by a ORM (object-relational mapping) library.

## DQL

* The `SELECT` statement consists of:
  - `SELECT`: which columns (or values) to report
  - `FROM`: which table(s) to query and how to join tables
  - `WHERE`: conditions to be met for rows to be reported
  - `GROUP BY`: how to aggregate rows by certain columns
  - `HAVING`: conditions to be met for aggregated rows
  - `ORDER BY`: how to order the rows in the report
* A DQL statement returns a result set
  - Technically, it returns a cursor into a result set.

## Simple querying {.smaller}

```{sql label=select1}
SELECT * FROM Instructor;
```

## Controlling columns to be reported {.smaller .scrollable}

* We can enumerate and thus limit columns to be reported:
```{sql label=select3}
SELECT Name, Address FROM Room;
```

* We can also rename columns, transform them, and apply functions:
```{sql label=select4}
SELECT Course_OID AS ID, 
       Name || ' (' || Semester || ')' AS Course_Name,
       IFNULL(Room_OID, -1) AS Room
FROM Course;
```

## Filter matching rows: WHERE {.smaller}

```{sql label=select5}
SELECT Email from Instructor
WHERE Name = 'Hilmar Lapp';
```

* We can use functions; wildcard matches use `LIKE':
```{sql label=select6}
SELECT DISTINCT Name from Lesson   -- DISTINCT makes rows unique
WHERE UPPER(Name) LIKE '%DATA%';
```
  Note that unlike shell glob patterns, regular expressions etc, SQL uses `%` for any number of characters (including zero), and `_` for one character as wildcards.

---

* Conditions can include subqueries
```{sql label=select7}
SELECT DISTINCT Name FROM Lesson
WHERE Instructor_OID IN (
   SELECT Instructor_OID
   FROM Instructor WHERE Name LIKE 'h%'
)
```

## NULL is special {.smaller}

* The representation of NULL depends on environment; in R:
```{sql label=select2}
SELECT * FROM Course;
```

---

* Note that NULL is not equal or unequal to anything, including itself:
```{sql label=select7.1}
SELECT Name, Semester FROM Course
WHERE Room_OID = NULL OR NOT Room_OID = NULL;
```
* Instead we must use a special boolean operator:
```{sql label=select7.2}
SELECT Name, Semester FROM Course WHERE Room_OID IS NULL;
```

## Joining tables: Inner Join {.smaller}

* Table joins allow creating denormalized reports from a normalized database

```{sql label=selectJ1}
SELECT c.Name AS Course, c.Semester, l.Name AS Lesson
FROM Course AS c INNER JOIN Lesson AS l ON (c.Course_OID = l.Course_OID)
LIMIT 5;
```
* If column names for joining are the same, we can shorten with `USING`:
```{.sql code-line-numbers="3"}
SELECT c.Name AS Course, c.Semester, l.Name AS Lesson
FROM Course AS c INNER JOIN Lesson AS l
     USING (Course_OID);
```

## Joining multiple tables {.smaller .scrollable}

* Table joins can span more than 2 tables:
```{sql label=selectJ2, max.print=NA}
SELECT c.Name AS Course, substr(c.Semester,-4) AS Year,
       r.Name AS Room, l.Name AS Lesson, i.Name AS Instructor
FROM Course AS c INNER JOIN Lesson AS l USING (Course_OID)
     INNER JOIN Instructor AS i USING (Instructor_OID)
     INNER JOIN Room AS r USING (Room_OID);
```

## Joining tables: Outer join {.smaller}

* Use a left or right outer join to include rows from the left or right side of a join that don't match the join condition:

```{sql label=selectJ3}
SELECT c.Name AS Course, c.Semester, r.Name AS Room
FROM Course AS c LEFT OUTER JOIN Room AS r USING (Room_OID);
```

## Outer joins cont'd {.smaller}

* Outer joins are not limited to NULLable columns.
  - For example, reporting all instructors, and courses for them
```{sql label=selectJ4}
SELECT DISTINCT i.Name AS Instructor, c.Name AS Course, c.Semester, r.Name AS Room
FROM Instructor AS i LEFT OUTER JOIN Lesson AS l USING (Instructor_OID)
     LEFT OUTER JOIN Course AS c USING (Course_OID)
     LEFT OUTER JOIN Room AS r USING (Room_OID);
```

## Join order {.smaller}

* Join order does not matter for inner joins, but does matter for whether a join must be outer and left or right.
```{sql label=selectJ5}
SELECT DISTINCT i.Name AS Instructor, c.Name AS Course, c.Semester, r.Name AS Room
FROM Lesson AS l INNER JOIN Course AS c USING (Course_OID)
     LEFT OUTER JOIN Room AS r USING (Room_OID)
     RIGHT OUTER JOIN Instructor AS i USING (Instructor_OID)
```
* Note that multiple outer joins can seriously hamper performance.

## Notes and other join constructs 

* `INNER` is optional (and the default), but it can help understanding a query
* Additional, but rarely used join constructs include:
  - `FULL OUTER JOIN`: combines left and right outer join
  - `NATURAL JOIN`: joins by columns that have the same name, and reports only one of them
  - `CROSS JOIN`: creates a cartesian product

## Existential subquery vs join {.smaller}

* Some queries that can be answered joining related facts may be easier to define and faster to execute by querying for existence of a fact.

* For example, which instructors are teaching in a given semester:
```{sql label=selectExists1}
SELECT i.Name AS Instructor, i.Email
FROM Instructor AS i
WHERE EXISTS (
    SELECT 1 FROM Lesson AS l INNER JOIN Course c USING (Course_OID)
    WHERE c.Semester = 'Fall 2023'
    AND   l.Instructor_OID = i.Instructor_OID
)
```

## Existential subquery negation {.smaller}

* For example, instructors who _don't_ teach in a given semester
```{sql label=selectExists2}
SELECT i.Name AS Instructor, i.Email
FROM Instructor AS i
WHERE NOT EXISTS (
    SELECT 1 FROM Lesson AS l INNER JOIN Course c USING (Course_OID)
    WHERE c.Semester = 'Fall 2024'
    AND   l.Instructor_OID = i.Instructor_OID
)
```


## Aggregating rows {.smaller .scrollable}

* Rows can be aggregated into groups by one or more columns of the same value, using aggregating functions:
  - Note that `COUNT(<column>)` and `COUNT(*)` have different semantics
```{sql label=selectG1}
SELECT i.Name AS Instructor, c.Name AS Course, c.Semester,
       COUNT(l.Name) AS '#Lessons',
       COUNT(*) AS '#RowsInGroup'
FROM Lesson AS l INNER JOIN Course AS c USING (Course_OID)
     RIGHT OUTER JOIN Instructor AS i USING (Instructor_OID)
GROUP BY i.Name, c.Name, c.Semester;
```

## Grouping vs aggregating {.smaller}

* `SELECT`ed columns should be `GROUP`ed or aggregated.
  - If neither, the value is from a randomly chosen member of the group.
```{sql label=selectG1.1}
SELECT i.Name AS Instructor, c.Name AS Course,
       MAX(c.Semester) AS LastSemester, -- choose one non-NULL value from group
       COUNT(l.Name) AS '#Lessons'
FROM Lesson AS l INNER JOIN Course AS c USING (Course_OID)
     RIGHT OUTER JOIN Instructor AS i USING (Instructor_OID)
GROUP BY i.Name, c.Name;
```

## Restricting aggregate groups I {.smaller}

* We can restrict which aggregate groups match based on conditions using aggregate function(s):
```{sql label=selectG2}
SELECT i.Name AS Instructor, c.Name AS Course, c.Semester,
       COUNT(l.Name) AS '#Lessons'
FROM Lesson AS l INNER JOIN Course AS c USING (Course_OID)
     RIGHT OUTER JOIN Instructor AS i USING (Instructor_OID)
GROUP BY i.Name, c.Name, c.Semester
HAVING COUNT(l.Name) > 0
```

## Restricting aggregate groups II {.smaller .scrollable}

* `SELECT` and `HAVING` clauses can use different aggregate functions:
```{sql label=selectG2.1}
SELECT i.Name AS Instructor, c.Name AS Course, c.Semester,
       GROUP_CONCAT(l.Name,', ') AS Lessons
FROM Lesson AS l INNER JOIN Course AS c USING (Course_OID)
     RIGHT OUTER JOIN Instructor AS i USING (Instructor_OID)
GROUP BY i.Name, c.Name, c.Semester
HAVING COUNT(l.Name) > 0
```

## Restricting aggregate groups III {.smaller}

* Queries can use both row conditions and aggregate group conditions
```{sql label=selectG3}
SELECT i.Name AS Instructor, c.Name AS Course, c.Semester,
       COUNT(l.Name) AS '#Lessons'
FROM Lesson AS l INNER JOIN Course AS c USING (Course_OID)
     RIGHT OUTER JOIN Instructor AS i USING (Instructor_OID)
WHERE c.Semester = 'Fall 2022'
GROUP BY i.Name, c.Name, c.Semester
HAVING COUNT(l.Name) > 0
```

## Views

* Database views are predefined queries
  - Their definition is part of DDL
* Views are an API to the E-R model
  - Denormalized "business objects" as opposed to relational entities
  - Business objects rarely change, as opposed to how they are normalized in a relational model
* Views can be treated like tables for querying (DQL)
  - But not for DML

## Creating views

* We create views simply based on a query, which can be simple or complex
```{sql label=selectV1}
CREATE VIEW Instructor_Courses AS
SELECT i.Instructor_OID,
       MAX(i.Name) AS Instructor,
       MAX(c.Name) AS Course, MAX(c.Semester) AS Semester,
       GROUP_CONCAT(l.Name, ', ') AS Lessons,
       COUNT(l.Name) AS 'NumLessons',
       MAX(c.Room_OID) AS Room_OID
FROM Lesson AS l INNER JOIN Course AS c USING (Course_OID)
     RIGHT OUTER JOIN Instructor AS i USING (Instructor_OID)
GROUP BY i.Instructor_OID, c.Course_OID;
```
* By default, the column names of a view are determined by those of the query

## Querying views {.smaller .scrollable}

```{sql label=selectV2}
SELECT Instructor, Course, Semester, Lessons, NumLessons FROM Instructor_Courses;
```

## Views can be filtered, aggregated etc {.smaller}

* For example, report the average number of lessons per course semester 
```{sql label=selectV3}
SELECT Course, Semester, ROUND(AVG(NumLessons), 2) AS 'mean(#Lessons)'
FROM Instructor_Courses
WHERE NumLessons > 0
GROUP BY Course, Semester
```

## Views can be joined {.smaller}

* As APIs, it is useful to include surrogate primary keys of participating objects in a view, which allows joining them for auxiliary facts
```{sql label=selectV4}
SELECT Instructor, Course, Semester, r.Name AS Room
FROM Instructor_Courses 
     LEFT OUTER JOIN Room AS r USING (Room_OID)
WHERE Course IS NOT NULL
```
* Views can also be joined with another view.

## Further notes and resources {.smaller}

* Use `ORDER BY <col1>[, <col2>, ...]` as the _last_ clause of a query to control the order of rows
  - append `DESC` for descending order
  - note that ordering can have a performance cost
* In most (OO) programming language environments, SQL database interaction is handled under the hood by ORM (Object-Relational Mapping) libraries
  - e.g., [sqlalchemy](https://www.sqlalchemy.org) in Python
* Some popular data science packages for R and Python have SQL database interfaces
  - e.g., [dbplyr](https://dbplyr.tidyverse.org) in R; in Python, `pandas` [can read/write from/to a SQL database](https://pandas.pydata.org/docs/user_guide/io.html#io-sql)
* [SQLite Documentation](https://www.sqlite.org/docs.html)
* Joe Celko, _Joe Celko's SQL for Smarties: Advanced SQL Programming_. Morgan & Kaufmann, 2015