Find the right table
Applying SQL to Real-World Problems
Dmitriy (Dima) Gorenshteyn
Lead Data Scientist, Memorial Sloan Kettering Cancer Center
What table should I use?
- What columns are in your tables?
- What is the content in these columns?
SELECT *
FROM payment;
LIMIT your results
SELECT *
FROM payment;
rental_id rental_date inventory_id customer_id return_date
2 2005-05-24 22:54:33 1525 459 2005-05-28 19:40:33
3 2005-05-24 23:03:39 1711 408 2005-06-01 22:12:39
4 2005-05-24 23:04:41 2452 333 2005-06-03 01:43:41
5 2005-05-24 23:05:21 2079 222 2005-06-02 04:33:21
6 2005-05-24 23:08:07 2792 549 2005-05-27 01:32:07
7 2005-05-24 23:11:53 3995 269 2005-05-29 20:34:53
8 2005-05-24 23:31:46 2346 239 2005-05-27 23:33:46
...................16035 MORE ROWS......................................
-
LIMIT your results
SELECT *
FROM payment
LIMIT 5;
rental_id rental_date inventory_id customer_id return_date
2 2005-05-24 22:54:33 1525 459 2005-05-28 19:40:33
3 2005-05-24 23:03:39 1711 408 2005-06-01 22:12:39
4 2005-05-24 23:04:41 2452 333 2005-06-03 01:43:41
5 2005-05-24 23:05:21 2079 222 2005-06-02 04:33:21
What tables are in my database?
PostgreSQL:
SELECT *
FROM pg_catalog.pg_tables
;
schemaname tablename tableowner
public address postgres
public actor postgres
public film_actor postgres
public language postgres
... ... ...
What tables are in my database?
PostgreSQL:
SELECT *
FROM pg_catalog.pg_tables
WHERE schema_name = 'public;
schemaname tablename tableowner
public address postgres
public actor postgres
public film_actor postgres
public language postgres
... ... ...
What tables are in my database?
PostgreSQL:
SELECT * FROM pg_catalog.pg_tables;
SQL Server - TSQL:
SELECT * FROM INFORMATION_SCHEMA.TABLES;
MySQL:
SHOW TABLES;
...
Find the tables you need!
Applying SQL to Real-World Problems
