Session IV | Introduction to MySQL

class: center, middle

# Introduction to MySQL<br/>Session IV

## Oct 23, 2014<br/>Coalition for Queens

#### Dan Goldin<br/>dan@dangoldin.com

---

# Agenda

- Quick review
- Updating data
- Inserting data
- Deleting data
- Modifying tables
- Creating tables
- Dropping tables

---

# Survey time

- [https://c4qnyc.wufoo.com/forms/sql-workshop-survey/](https://c4qnyc.wufoo.com/forms/sql-workshop-survey/)

---

# Quick review

- What does this query do?

```sql
SELECT t.name as team_name,
  q.name as position,
  sum(points) as total_points
FROM players p
JOIN positions q ON p.position_id = q.id
JOIN stats s ON p.id = s.player_id
JOIN teams t on p.team_id = t.id
GROUP BY t.name, q.name;
```

---

# Quick review

- How about this one?
```sql
SELECT name
FROM players
WHERE id IN (
  SELECT player_id
  FROM stats
  GROUP BY player_id
  HAVING AVG(passing_tds) > 1
    AND AVG(rushing_yds) > 20
);
```

---

# Updating data

- Modify existing rows in a table
- UPDATE [table] SET [field1] = [value1], [field2] = [value2] WHERE [condition];

---

# Updating data

```sql
UPDATE players SET name = 'Dan Goldin'
  WHERE id = 11;

UPDATE players SET name = 'Dan Goldin',
  team_id = 2 WHERE id = 11;

UPDATE schedule SET week = 18
  WHERE week > 10 AND week < 11;
```

---

# Exercises

- Write queries to do the following:
  * Put everyone with the last name Johnson on team_id 10
  * Update Eli Manning's record to use his birth name: "Elisha Nelson Manning"

---

# Inserting data

- Insert new rows into a table
- INSERT INTO [table] VALUES ([values]);
- INSERT INTO [table] ([fields]) VALUES ([values]);
- INSERT INTO [table] ([fields]) VALUES ([values]),([values]);

---

# Inserting data

```sql
INSERT INTO positions VALUES (5, 'K');

INSERT INTO positions (name) VALUES ('K');

INSERT INTO players (name, position_id, team_id)
  VALUES ('Dan Goldin', 1, 22);

INSERT INTO players (name, position_id, team_id)
  VALUES ('Dan Goldin', 1, 22),
  ('Someone Else', 2, 10);
```

---

# Exercises

- Write queries to do the following:
  * Create a new NFL team
  * Add a week 18 game to the schedule
  * Add a player named after you to the players table

---

# Deleting data

- Delete row(s) from a table
- DELETE FROM [table] WHERE [condition];

---

# Deleting data

```sql
DELETE FROM positions WHERE name = 'QB';

DELETE FROM schedule
WHERE week IN (1,3,5,7);

DELETE FROM schedule
WHERE home_id IN (
  SELECT id FROM teams
  WHERE name in ('Ari', 'Atl')
);
```

---

# Exercises

- Write queries to do the following:
  * Delete the player you created
  * Delete all players with the first name 'Ryan'
  * Delete all players that average less than 10 points a week

---

# Modifying tables

- Modify the columns in a table
- ALTER [table] ADD COLUMN [column_name] [column_definition];
- ALTER [table] MODIFY [column_name] [new_column_definition];
- ALTER [table] DROP COLUMN [column_name];

---

# Modifying tables

```sql
ALTER TABLE teams ADD COLUMN
  long_name VARCHAR(20);

ALTER TABLE teams MODIFY
  long_name VARCHAR(40);

ALTER TABLE teams DROP COLUMN
  long_name;
```

---

# Exercises

- Add a first name and a last name column to the players table
- Add a price_per_ticket column to the schedule table

---

# Creating tables

- Create new tables
- CREATE TABLE [table] AS (fields);

---

# Creating tables

```sql
CREATE TABLE stadiums (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  team_id int(10) unsigned NOT NULL,
  name varchar(100) NOT NULL
);
```

---

# Exercises

- Think of a useful or interesting table and add it to the database.

---

# Dropping tables

- Remove a table from our database
- DROP TABLE [table];

---

# Dropping tables

```sql
DROP TABLE positions;

DROP TABLE stadiums;
```

---

# Questions?