SQL Data Manipulation Language with SQLite

<figure class="highlight"><pre><code class="language-text" data-lang="text">name: inverse
layout: true
class: center, middle, inverse
.indexlink[[](#) [](#index) <a href="#" class="color"></a>]

---

name: top
layout: true
class: top, left
.indexlink[[](#) [](#index) <a href="#" class="color"></a>]

---

name: normal
layout: true
class: left, middle
.indexlink[[](#) [](#index) <a href="#" class="color"></a>]

---

name: uml
template:inverse

# SQL

## Data Manipulation Language

Gonçalo Gonçalves  
[gflcg@fe.up.pt](mailto:gflcg@fe.up.pt)

.footnote[-- original slides by <a target=_blank href="http://www.fe.up.pt/~arestivo">André Restivo</a>]

---
name: index
template:inverse

# Index

.index[

.indexpill[[Inserting](#insert)]

.indexpill[[Deleting](#delete)]

.indexpill[[Updating](#update)]

]

---
name: insert
template: inverse
class: middle, center
# Inserting

---

# Inserting
To insert values into a table, we use the **INSERT** command:

.small-code[
```sql
INSERT INTO <tablename> (<col1>, <col2>, ...) VALUES (<val1>, <val2>, ...);
```
]

Example:

.small-code[
```sql
INSERT INTO Employee (id, name, salary) VALUES (1, 'John Doe', 1000);
```
]

---

# Inserting
We can omit the column names if we insert the values in the same order we used to create the table columns.
```sql
INSERT INTO Employee VALUES (1, 'John Doe', 1000);
```

---
name: delete
template: inverse
class: middle, center
# Deleting

---

# Deleting
* To delete value from a table, we use the **DELETE** command.
* The delete command can receive a **condition** specifying **which rows to delete**.
* If **no condition** is given **all rows are deleted** from the table.
* If there are foreign keys, the rule set by the **ON DELETE** clause is used.
* The condition can be as complex as those used on the **SELECT** command.

```sql
DELETE FROM <tablename> WHERE <condition>;
```

---

# Deleting - Examples
Delete employee with id 1:
```sql
DELETE FROM Employee WHERE id = 1;
```

Delete all employees:
```sql
DELETE FROM Employee;
```

Delete employees with salary greater than or equal to 1200:
```sql
DELETE FROM Employee WHERE salary >= 1200;
```

---
name: update
template: inverse
class: middle, center
# Updating

---

# Updating
* To modify values from a table, we use the **UPDATE** command.
* The update command can receive a **condition** specifying **which rows to update**.
* If **no condition** is given **all rows are updated** from the table.
* If there are foreign keys, the rule set by the **ON UPDATE** clause is used.
* The condition can be as complex as those used on the **SELECT** command.
* New values can be calculated on the fly.

.small-code[
```sql
UPDATE <tablename> SET <col1> = <val1>, <col2> = <val2>, ... WHERE <condition>;
```
]

---

# Updating - Examples
Change the salary of employee 1 to 1300:
```sql
UPDATE Employee SET salary = 1300 WHERE id = 1;
```

Increase the salary of employee 1 by 10%:
```sql
UPDATE Employee SET salary = salary * 1.1 WHERE id = 1;
```

Increase the salary of all employees by 10%:
```sql
UPDATE Employee SET salary = salary * 1.1;
```

Decrease the salary and taxes of all employees with a salary larger than 1200 by 10%: 
```sql
UPDATE Employee SET salary = salary * 0.9, taxes = taxes * 0.9 
WHERE salary > 1200;
```
 </code></pre></figure>