Relational Data SQL Data Definition & Manipulation

class: left, top, title-slide

.title[
# Relational Data<br>SQL Data Definition & Manipulation
]
.author[
### Keith VanderLinden<br>Calvin University
]

---

# Creating Tables

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

```sql
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
CREATE TABLE Supplier (
   ID integer PRIMARY KEY,
   name varchar(25),
   loginID varchar(10) NOT NULL,
   password varchar(20)
);
```

```sql
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
INSERT INTO table_name 
     VALUES ( value1, value1, ... )
```

```sql
UPDATE table_name 
   SET fieldname = new_value, ...
 WHERE condition;
```

```sql
DELETE FROM table_name 
      WHERE condition;
```

???

---
# Example: SPJ Data

```sql
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
UPDATE Supplier SET password = 'qwerty';
```

```sql
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.