---
title: "Relational Data<br>SQL Data Definition &amp; Manipulation"
author: "Keith VanderLinden<br>Calvin University"
output:
  xaringan::moon_reader:
    nature:
      titleSlideClass: ["left", "top"]
      highlightLines: true
      ratio: "16:9"     
---

```{r child = "../../resources/scripts/slide-setup.Rmd"}
```

```{r packages, echo = FALSE, warning = FALSE, message = FALSE}
knitr::opts_chunk$set(eval = TRUE)
options(dplyr.print_max = 12)
library(tidyverse)
```

# Creating Tables

SQL provides a *data definition language* (DDL).

```{sql eval = FALSE}
CREATE TABLE table_name (
   column1 datatype [ column_constraint ],
   column2 datatype [ column_constraint ],
   column3 datatype [ column_constraint ]
);
```

- Data [types](https://www.postgresql.org/docs/10/datatype.html) include: 
  - `integer`
  - `decimal`
  - `varchar(X)` (for up to *X* characters)
  - `date` 
- [Optional] Column [constraints](https://www.postgresql.org/docs/10/ddl-constraints.html) include: 
  - `NOT NULL`
  - `PRIMARY KEY`
  - `REFERENCES`
  
.footnote[See: https://www.postgresql.org/docs/10/datatype.html &amp; https://www.postgresql.org/docs/10/ddl-constraints.html]  

???
See the [PostgreSQL manual](https://www.postgresqltutorial.com/).

In these lecture demos, be sure to cover everything they need to make the lab/homeworks run, including:

- single rather than double quotes for INSERT
- leaving out the value (and comma) for inserting NULL
- is NULL rather than == NULL

---
# Example: SPJ Tables

```{sql eval = FALSE}
CREATE TABLE Supplier (
   ID integer PRIMARY KEY,
   name varchar(25),
   loginID varchar(10) NOT NULL,
   password varchar(20)
);
```

```{sql eval = FALSE}
CREATE TABLE Part (
   ID integer PRIMARY KEY,
   name varchar(25),
   price decimal,
   supplerID integer REFERENCES Supplier(ID)
);
```

???

---
# Changing Table Data

SQL provides a *data manipulation language* (DML).

```{sql eval = FALSE}
INSERT INTO table_name 
     VALUES ( value1, value1, ... )
```

```{sql eval = FALSE}
UPDATE table_name 
   SET fieldname = new_value, ...
 WHERE condition;
```

```{sql eval = FALSE}
DELETE FROM table_name 
      WHERE condition;
```

???

---
# Example: SPJ Data

```{sql eval = FALSE}
INSERT INTO Supplier VALUES (1, 'Acme', 'acme', 'joshua');
INSERT INTO Part VALUES (1, 'birdseed', 5.00, 1);
INSERT INTO Job VALUES (2, NULL, 'Amway');
INSERT INTO PartJob VALUES (1, 1, 10, '2018-10-20');
```

```{sql eval = FALSE}
UPDATE Supplier SET password = 'qwerty';
```

```{sql eval = FALSE}
DELETE FROM Part WHERE ID = 1;
```

???
- We can insert a `NULL` value (cf. `NA`).
- PostgreSQL will automatically *parse* the date value.
- DELETE is rarely used in production databases.

---
# Demo

We now demo the design and construction of a database using:
- [PostgreSQL](https://www.postgresql.org/), a commonly-used, open-source implementation of SQL
- [ElephantSQL](https://www.elephantsql.com/), a database hosting service that offers free or commercial PostreSQL databases.

???
Talk through `spj.sql` now.
