Store your data

Applying SQL to Real-World Problems

Dmitriy (Dima) Gorenshteyn

Lead Data Scientist, Memorial Sloan Kettering Cancer Center

Three ways to store your data

  1. Create a TABLE using new data
  2. Create a TABLE using existing data
  3. Create a VIEW using existing data
Applying SQL to Real-World Problems

Create a TABLE using new data

Columns in address table

  • address_id
  • address
  • district
  • city
  • postal_code
  • phone
Applying SQL to Real-World Problems

Create a TABLE using new data

postal_code distance
53182 3.4
15540 10.2
67912 1.9
81766 21
... ...
Applying SQL to Real-World Problems

Create a TABLE using new data

1) Create a new table

CREATE TABLE zip_distance (
    postal_code INT,
    distance FLOAT
);

2) Insert data into table

INSERT INTO zip_distance (postal_code, distance)
VALUES
(53182, 3.4),                
(15540, 10.2),                
(67912, 1.9);
Applying SQL to Real-World Problems

Create a TABLE using existing data


SELECT film_id, title
FROM film
WHERE rating = 'G';
Applying SQL to Real-World Problems

Create a TABLE using existing data

CREATE TABLE family_films AS
SELECT film_id, title
FROM film
WHERE rating = 'G';
Applying SQL to Real-World Problems

Create a VIEW using existing data

CREATE VIEW family_films AS
SELECT film_id, title
FROM film
WHERE rating = 'G';
Applying SQL to Real-World Problems

TABLE vs VIEW

TABLE

  • Data is stored (static)
  • Data can be modified directly

VIEW

  • Query is stored (dynamic)
  • Underlying data must be modified in original tables
Applying SQL to Real-World Problems

Time to store your data.

Applying SQL to Real-World Problems

Preparing Video For Download...