old sql query demo slides - superseded by query-demo.Rmd.

---
# Queries

These two commands *query* all the records from the part table.

.pull-left[
**SQL**

```{sql eval = FALSE}
SELECT *
  FROM Part
```
]
.pull-right[
**dplyr**

```{r eval = FALSE}

part
```
]
.pull-left[
The formatting of the output of these two queries/commands is different, but the basic content of that output is the same.

| id  |  name |   price |  supplierid |
|-----|-------|---------|-------------|
|1   |birdseed    |0.5e1   |1
|2   |TNT   |0.9e2   |1
|3   |lead shot   |0.2e2   |1
|4   |pocket fisherman    |0.1999e2    |3
]

???
Demo these queries.

---
# Multiple-Table Queries: One-to-Many 

These two commands *query* all the parts with their suppliers.

.pull-left[
**SQL**

```{sql eval = FALSE}
SELECT * 
  FROM Part
  JOIN Supplier ON supplierID = Part.ID
```
]
.pull-right[
**dplyr**

```{r eval = FALSE}
  part %>%
    left_join(supplier, 
              by = c("supplierid" = "id")
              )
```
]
.pull-left[
|id   |name   |price   |supplierid    |loginid   |password
|-|-|-|-|-|-|
|1    |Acme    |0.5e1   |1   |acme    |joshua
|2    |Ronco   |0.5e1   |1   |ronc    |ronc
|3    |Popiel    |0.5e1   |1   |popi    |pp123
]

???
Demo these queries.

---
# Multiple-Table Queries: Many-to-Many 

These two commands *query* all job-supply events, with associated part and job information.

.pull-left[
**SQL**

```{sql eval = FALSE}
SELECT Part.name AS PartName, 
       Job.name AS JobName, 
       quantity, date, address
  FROM PartJob
  JOIN Part ON partID = Part.ID
  JOIN Job ON jobID = Job.ID
```
]
.pull-right[
**dplyr**

```{r eval = FALSE}
partjob %>%
  inner_join(part, 
             by = c("partid" = "id")) %>%
  inner_join(job, 
             by = c("jobid" = "id")) %>%
  select(name.x, name.y, 
         quantity, date, address)
```
]
.pull-left[
|partname	|jobname&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |quantity	|date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|address
|-|-|-|-|-
|birdseed	|Student Union	|10	|2018-10-20	|Calvin
|TNT	|Student Union	|2	|2018-10-21	| Calvin
|birdseed	|	|2	|2018-10-21	|Amway
]

???
Demo these queries.
