Created
December 12, 2016 17:14
-
-
Save kccrs/68774ee6507b80b6881004d79a7c30ac to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| caseycross:~/documents/turing/mod4-lessons/sql_deep_dive$ psql imdb < imdb.pgsqlSET | |
| SET | |
| SET | |
| SET | |
| SET | |
| SET | |
| CREATE EXTENSION | |
| COMMENT | |
| SET | |
| SET | |
| SET | |
| CREATE TABLE | |
| ERROR: role "jcasimir" does not exist | |
| CREATE SEQUENCE | |
| ERROR: role "jcasimir" does not exist | |
| ALTER SEQUENCE | |
| CREATE TABLE | |
| ERROR: role "jcasimir" does not exist | |
| CREATE TABLE | |
| ERROR: role "jcasimir" does not exist | |
| CREATE SEQUENCE | |
| ERROR: role "jcasimir" does not exist | |
| ALTER SEQUENCE | |
| CREATE TABLE | |
| ERROR: role "jcasimir" does not exist | |
| CREATE SEQUENCE | |
| ERROR: role "jcasimir" does not exist | |
| ALTER SEQUENCE | |
| CREATE TABLE | |
| ERROR: role "jcasimir" does not exist | |
| CREATE SEQUENCE | |
| ERROR: role "jcasimir" does not exist | |
| ALTER SEQUENCE | |
| CREATE TABLE | |
| ERROR: role "jcasimir" does not exist | |
| CREATE SEQUENCE | |
| ERROR: role "jcasimir" does not exist | |
| ALTER SEQUENCE | |
| CREATE TABLE | |
| ERROR: role "jcasimir" does not exist | |
| CREATE SEQUENCE | |
| ERROR: role "jcasimir" does not exist | |
| ALTER SEQUENCE | |
| CREATE TABLE | |
| ERROR: role "jcasimir" does not exist | |
| ALTER TABLE | |
| ALTER TABLE | |
| ALTER TABLE | |
| ALTER TABLE | |
| ALTER TABLE | |
| ALTER TABLE | |
| COPY 6255 | |
| setval | |
| -------- | |
| 6255 | |
| (1 row) | |
| COPY 1 | |
| COPY 2399 | |
| setval | |
| -------- | |
| 2399 | |
| (1 row) | |
| COPY 14504 | |
| setval | |
| -------- | |
| 14504 | |
| (1 row) | |
| COPY 26 | |
| setval | |
| -------- | |
| 26 | |
| (1 row) | |
| COPY 5043 | |
| setval | |
| -------- | |
| 5043 | |
| (1 row) | |
| COPY 15086 | |
| setval | |
| -------- | |
| 15086 | |
| (1 row) | |
| COPY 6 | |
| ALTER TABLE | |
| ALTER TABLE | |
| ALTER TABLE | |
| ALTER TABLE | |
| ALTER TABLE | |
| ALTER TABLE | |
| ALTER TABLE | |
| ALTER TABLE | |
| REVOKE | |
| ERROR: role "jcasimir" does not exist | |
| ERROR: role "jcasimir" does not exist | |
| GRANT | |
| caseycross:~/documents/turing/mod4-lessons/sql_deep_dive$ psql imdb | |
| psql (9.6.1) | |
| Type "help" for help. | |
| imdb=# psql> select * from movies where id=100 limit 1; | |
| ERROR: syntax error at or near "psql" | |
| LINE 1: psql> select * from movies where id=100 limit 1; | |
| ^ | |
| imdb=# select * from movies where id=100 limit 1; | |
| id | title | duration | gross | imdb | content_rating | budget | year | language | director_id | created_at | updated_at | |
| -----+-----------------------------------+----------+-----------+------------------------------------------------------+----------------+-----------+------+----------+-------------+----------------------------+---------------------------- | |
| 100 | The Hobbit: An Unexpected Journey | 182 | 303001229 | http://www.imdb.com/title/tt0903624/?ref_=fn_tt_tt_1 | PG-13 | 180000000 | 2012 | English | 17 | 2016-12-11 23:57:18.722114 | 2016-12-11 23:57:18.731694 | |
| (1 row) | |
| imdb=# SELECT * FROM movies ORDER BY gross DESC LIMIT 1 | |
| imdb-# SELECT * FROM movies ORDER BY gross DESC LIMIT 1 | |
| imdb-# select * from movies where id=100 limit 1; | |
| ERROR: syntax error at or near "SELECT" | |
| LINE 2: SELECT * FROM movies ORDER BY gross DESC LIMIT 1 | |
| ^ | |
| imdb=# | |
| imdb=# SELECT * FROM movies ORDER BY gross DESC LIMIT 1; | |
| id | title | duration | gross | imdb | content_rating | budget | year | language | director_id | created_at | updated_at | |
| ----+--------------------------------------------------------+----------+-------+------------------------------------------------------+----------------+--------+------+----------+-------------+----------------------------+---------------------------- | |
| 5 | Star Wars: Episode VII - The Force Awakens | | | http://www.imdb.com/title/tt5289954/?ref_=fn_tt_tt_1 | | | | | 5 | 2016-12-11 23:57:15.929256 | 2016-12-11 23:57:15.937848 | |
| (1 row) | |
| imdb=# SELECT * FROM movies WHERE year=2012 ORDER BY gross DESC LIMIT 1; | |
| id | title | duration | gross | imdb | content_rating | budget | year | language | director_id | created_at | updated_at | |
| ------+------------+----------+-------+------------------------------------------------------+----------------+----------+------+----------+-------------+----------------------------+--------------------------- | |
| 1137 | Foodfight! | 91 | | http://www.imdb.com/title/tt0249516/?ref_=fn_tt_tt_1 | PG | 65000000 | 2012 | English | 520 | 2016-12-11 23:57:46.657874 | 2016-12-11 23:57:46.67579 | |
| (1 row) | |
| imdb=# SELECT * FROM movies WHERE budget IS NOT NULL OR != 0; | |
| ERROR: syntax error at or near "!=" | |
| LINE 1: SELECT * FROM movies WHERE budget IS NOT NULL OR != 0; | |
| ^ | |
| imdb=# SELECT * FROM movies WHERE budget IS NOT NULL OR <> 0; | |
| ERROR: syntax error at or near "<>" | |
| LINE 1: SELECT * FROM movies WHERE budget IS NOT NULL OR <> 0; | |
| ^ | |
| imdb=# SELECT * FROM movies WHERE budget IS NOT NULL OR budget!=0;; | |
| id | title | duration | gross | imdb | content_rating | budget | year | language | director_id | created_at | updated_at | |
| ------+----------------------------------------------------------------------------------------+----------+-----------+------------------------------------------------------+----------------+-------------+------+------------+-------------+----------------------------+---------------------------- | |
| 1 | Avatar | 178 | 760505847 | http://www.imdb.com/title/tt0499549/?ref_=fn_tt_tt_1 | PG-13 | 237000000 | 2009 | English | 1 | 2016-12-11 23:57:15.687822 | 2016-12-11 23:57:15.801921 | |
| 2 | Pirates of the Caribbean: At World's End | 169 | 309404152 | http://www.imdb.com/title/tt0449088/?ref_=fn_tt_tt_1 | PG-13 | 300000000 | 2007 | English | 2 | 2016-12-11 23:57:15.849318 | 2016-12-11 23:57:15.862808 | |
| 3 | Spectre | 148 | 200074175 | http://www.imdb.com/title/tt2379713/?ref_=fn_tt_tt_1 | PG-13 | 245000000 | 2015 | English | 3 | 2016-12-11 23:57:15.876178 | 2016-12-11 23:57:15.891409 | |
| 4 | The Dark Knight Rises | 164 | 448130642 | http://www.imdb.com/title/tt1345836/?ref_=fn_tt_tt_1 | PG-13 | 250000000 | 2012 | English | 4 | 2016-12-11 23:57:15.905668 | 2016-12-11 23:57:15.915899 | |
| 6 | John Carter | 132 | 73058679 | http://www.imdb.com/title/tt0401729/?ref_=fn_tt_tt_1 | PG-13 | 263700000 | 2012 | English | 6 | 2016-12-11 23:57:15.948228 | 2016-12-11 23:57:15.962191 | |
| 7 | Spider-Man 3 | 156 | 336530303 | http://www.imdb.com/title/tt0413300/?ref_=fn_tt_tt_1 | PG-13 | 258000000 | 2007 | English | 7 | 2016-12-11 23:57:15.975879 | 2016-12-11 23:57:15.991664 | |
| 8 | Tangled | 100 | 200807262 | http://www.imdb.com/title/tt0398286/?ref_=fn_tt_tt_1 | PG | 260000000 | 2010 | English | 8 | 2016-12-11 23:57:16.005046 | 2016-12-11 23:57:16.03426 | |
| 9 | Avengers: Age of Ultron | 141 | 458991599 | http://www.imdb.com/title/tt2395427/?ref_=fn_tt_tt_1 | PG-13 | 250000000 | 2015 | English | 9 | 2016-12-11 23:57:16.048793 | 2016-12-11 23:57:16.061526 | |
| 10 | Harry Potter and the Half-Blood Prince | 153 | 301956980 | http://www.imdb.com/title/tt0417741/?ref_=fn_tt_tt_1 | PG | 250000000 | 2009 | English | 10 | 2016-12-11 23:57:16.075589 | 2016-12-11 23:57:16.094362 | |
| imdb=# SELECT COUNT(*) FROM movies WHERE budget IS NOT NULL OR budget!=0; | |
| count | |
| ------- | |
| 4551 | |
| (1 row) | |
| imdb=# SELECT COUNT(*) FROM movies WHERE year=2012; | |
| count | |
| ------- | |
| 221 | |
| (1 row) | |
| imdb=# SELECT * FROM movies WHERE gross IS NOT NULL ORDER BY gross DESC LIMIT 1; | |
| id | title | duration | gross | imdb | content_rating | budget | year | language | director_id | created_at | updated_at | |
| ----+--------+----------+-----------+------------------------------------------------------+----------------+-----------+------+----------+-------------+----------------------------+---------------------------- | |
| 1 | Avatar | 178 | 760505847 | http://www.imdb.com/title/tt0499549/?ref_=fn_tt_tt_1 | PG-13 | 237000000 | 2009 | English | 1 | 2016-12-11 23:57:15.687822 | 2016-12-11 23:57:15.801921 | |
| (1 row) | |
| imdb=# SELECT * FROM pg_catalog pg_tables; | |
| ERROR: relation "pg_catalog" does not exist | |
| LINE 1: SELECT * FROM pg_catalog pg_tables; | |
| ^ | |
| imdb=# SELECT * FROM pg_catalog.pg_tables; | |
| schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity | |
| --------------------+-------------------------+------------+------------+------------+----------+-------------+------------- | |
| public | actors | caseycross | | t | f | f | f | |
| public | ar_internal_metadata | caseycross | | t | f | f | f | |
| public | directors | caseycross | | t | f | f | f | |
| public | genre_memberships | caseycross | | t | f | f | f | |
| public | genres | caseycross | | t | f | f | f | |
| public | movies | caseycross | | t | f | f | f | |
| public | roles | caseycross | | t | f | f | f | |
| public | schema_migrations | caseycross | | t | f | f | f | |
| pg_catalog | pg_statistic | caseycross | | t | f | f | f | |
| pg_catalog | pg_type | caseycross | | t | f | f | f | |
| pg_catalog | pg_authid | caseycross | pg_global | t | f | f | f | |
| pg_catalog | pg_user_mapping | caseycross | | t | f | f | f | |
| pg_catalog | pg_attribute | caseycross | | t | f | f | f | |
| pg_catalog | pg_proc | caseycross | | t | f | f | f | |
| pg_catalog | pg_class | caseycross | | t | f | f | f | |
| pg_catalog | pg_attrdef | caseycross | | t | f | f | f | |
| pg_catalog | pg_constraint | caseycross | | t | f | f | f | |
| pg_catalog | pg_inherits | caseycross | | t | f | f | f | |
| pg_catalog | pg_index | caseycross | | t | f | f | f | |
| imdb=# SELECT * FROM movies WHERE name="Rosario Dawson"; | |
| ERROR: column "name" does not exist | |
| LINE 1: SELECT * FROM movies WHERE name="Rosario Dawson"; | |
| ^ | |
| imdb=# SELECT * FROM actors WHERE name="Rosario Dawson"; | |
| ERROR: column "Rosario Dawson" does not exist | |
| LINE 1: SELECT * FROM actors WHERE name="Rosario Dawson"; | |
| ^ | |
| imdb=# SELECT * FROM actors WHERE name='Rosario Dawson'; | |
| id | name | created_at | updated_at | |
| -----+----------------+----------------------------+---------------------------- | |
| 320 | Rosario Dawson | 2016-12-11 23:57:20.214062 | 2016-12-11 23:57:20.214062 | |
| (1 row) | |
| imdb=# SELECT * FROM roles where id=320; | |
| id | movie_id | actor_id | rank | created_at | updated_at | |
| -----+----------+----------+------+----------------------------+---------------------------- | |
| 320 | 107 | 79 | 3 | 2016-12-11 23:57:18.910056 | 2016-12-11 23:57:18.910056 | |
| (1 row) | |
| imdb=# SELECT * FROM roles where actor_id=320; | |
| id | movie_id | actor_id | rank | created_at | updated_at | |
| -------+----------+----------+------+----------------------------+---------------------------- | |
| 457 | 153 | 320 | 2 | 2016-12-11 23:57:20.21569 | 2016-12-11 23:57:20.21569 | |
| 961 | 322 | 320 | 1 | 2016-12-11 23:57:24.898059 | 2016-12-11 23:57:24.898059 | |
| 1052 | 352 | 320 | 2 | 2016-12-11 23:57:25.717448 | 2016-12-11 23:57:25.717448 | |
| 1061 | 355 | 320 | 2 | 2016-12-11 23:57:25.78996 | 2016-12-11 23:57:25.78996 | |
| 1244 | 416 | 320 | 2 | 2016-12-11 23:57:27.509913 | 2016-12-11 23:57:27.509913 | |
| 1381 | 462 | 320 | 1 | 2016-12-11 23:57:28.725404 | 2016-12-11 23:57:28.725404 | |
| 1420 | 475 | 320 | 1 | 2016-12-11 23:57:29.084169 | 2016-12-11 23:57:29.084169 | |
| 2678 | 894 | 320 | 2 | 2016-12-11 23:57:40.396192 | 2016-12-11 23:57:40.396192 | |
| 2723 | 909 | 320 | 2 | 2016-12-11 23:57:40.767131 | 2016-12-11 23:57:40.767131 | |
| 3637 | 1214 | 320 | 1 | 2016-12-11 23:57:48.60393 | 2016-12-11 23:57:48.60393 | |
| 3817 | 1274 | 320 | 1 | 2016-12-11 23:57:50.147619 | 2016-12-11 23:57:50.147619 | |
| 5834 | 1946 | 320 | 3 | 2016-12-11 23:58:07.522295 | 2016-12-11 23:58:07.522295 | |
| 6321 | 2109 | 320 | 1 | 2016-12-11 23:58:11.527124 | 2016-12-11 23:58:11.527124 | |
| 7745 | 2585 | 320 | 1 | 2016-12-11 23:58:23.306773 | 2016-12-11 23:58:23.306773 | |
| 8606 | 2872 | 320 | 1 | 2016-12-11 23:58:30.443381 | 2016-12-11 23:58:30.443381 | |
| 8840 | 2950 | 320 | 1 | 2016-12-11 23:58:32.377486 | 2016-12-11 23:58:32.377486 | |
| 9860 | 3290 | 320 | 1 | 2016-12-11 23:58:40.73529 | 2016-12-11 23:58:40.73529 | |
| 10871 | 3628 | 320 | 2 | 2016-12-11 23:58:49.268877 | 2016-12-11 23:58:49.268877 | |
| 11528 | 3847 | 320 | 2 | 2016-12-11 23:58:54.766884 | 2016-12-11 23:58:54.766884 | |
| imdb=# SELECT COUNT(*) FROM roles where actor_id=320; | |
| count | |
| ------- | |
| 21 | |
| (1 row) | |
| imdb=# SELECT * FROM directors WHERE name='Justin Lin'; | |
| id | name | created_at | updated_at | |
| ----+------------+----------------------------+---------------------------- | |
| 42 | Justin Lin | 2016-12-11 23:57:17.515632 | 2016-12-11 23:57:17.515632 | |
| (1 row) | |
| imdb=# SELECT * FROM movies WHERE director_id=42; | |
| id | title | duration | gross | imdb | content_rating | budget | year | language | director_id | created_at | updated_at | |
| ------+----------------------------------------------------+----------+-----------+------------------------------------------------------+----------------+-----------+------+----------+-------------+----------------------------+---------------------------- | |
| 58 | Star Trek Beyond | 122 | 130468626 | http://www.imdb.com/title/tt2660888/?ref_=fn_tt_tt_1 | PG-13 | 185000000 | 2016 | English | 42 | 2016-12-11 23:57:17.499572 | 2016-12-11 23:57:17.517149 | |
| 212 | Fast Five | 132 | 209805005 | http://www.imdb.com/title/tt1596343/?ref_=fn_tt_tt_1 | PG-13 | 125000000 | 2011 | English | 42 | 2016-12-11 23:57:21.839895 | 2016-12-11 23:57:21.852322 | |
| 419 | The Fast and the Furious: Tokyo Drift | 104 | 62494975 | http://www.imdb.com/title/tt0463985/?ref_=fn_tt_tt_1 | PG-13 | 85000000 | 2006 | English | 42 | 2016-12-11 23:57:27.564332 | 2016-12-11 23:57:27.575717 | |
| 4751 | Finishing the Game: The Search for a New Bruce Lee | 84 | 52850 | http://www.imdb.com/title/tt0843850/?ref_=fn_tt_tt_1 | Not Rated | 500000 | 2007 | English | 42 | 2016-12-11 23:59:17.69197 | 2016-12-11 23:59:17.698398 | |
| 4856 | Better Luck Tomorrow | 98 | 3799339 | http://www.imdb.com/title/tt0280477/?ref_=fn_tt_tt_1 | R | 250000 | 2002 | English | 42 | 2016-12-11 23:59:20.394562 | 2016-12-11 23:59:20.40639 | |
| (5 rows) | |
| imdb=# SELECT * FROM movies WHERE budget IS NOT NULL AND gross IS NOT NULL AND year=2012 ORDER BY (gross - budget) ASC LIMIT 3; | |
| id | title | duration | gross | imdb | content_rating | budget | year | language | director_id | created_at | updated_at | |
| -----+-------------+----------+----------+------------------------------------------------------+----------------+-----------+------+----------+-------------+----------------------------+---------------------------- | |
| 6 | John Carter | 132 | 73058679 | http://www.imdb.com/title/tt0401729/?ref_=fn_tt_tt_1 | PG-13 | 263700000 | 2012 | English | 6 | 2016-12-11 23:57:15.948228 | 2016-12-11 23:57:15.962191 | |
| 29 | Battleship | 131 | 65173160 | http://www.imdb.com/title/tt1440129/?ref_=fn_tt_tt_1 | PG-13 | 209000000 | 2012 | English | 22 | 2016-12-11 23:57:16.604431 | 2016-12-11 23:57:16.6227 | |
| 312 | Cloud Atlas | 172 | 27098580 | http://www.imdb.com/title/tt1371111/?ref_=fn_tt_tt_1 | R | 102000000 | 2012 | English | 179 | 2016-12-11 23:57:24.597944 | 2016-12-11 23:57:24.607523 | |
| (3 rows) | |
| imdb=# SELECT COUNT(*) FROM movies WHERE year=2012 AND budget>100000000; | |
| count | |
| ------- | |
| 22 | |
| (1 row) | |
| imdb=# SELECT * FROM actors WHERE name='Donald Glover'; | |
| id | name | created_at | updated_at | |
| -----+---------------+----------------------------+---------------------------- | |
| 511 | Donald Glover | 2016-12-11 23:57:23.683145 | 2016-12-11 23:57:23.683145 | |
| (1 row) | |
| imdb=# SELECT * FROM movies WHERE actor_id=511; | |
| ERROR: column "actor_id" does not exist | |
| LINE 1: SELECT * FROM movies WHERE actor_id=511; | |
| ^ | |
| imdb=# SELECT * FROM movies WHERE id=511; | |
| id | title | duration | gross | imdb | content_rating | budget | year | language | director_id | created_at | updated_at | |
| -----+----------------------------------+----------+-----------+------------------------------------------------------+----------------+----------+------+----------+-------------+----------------------------+---------------------------- | |
| 511 | Journey 2: The Mysterious Island | 94 | 103812241 | http://www.imdb.com/title/tt1397514/?ref_=fn_tt_tt_1 | PG | 79000000 | 2012 | English | 150 | 2016-12-11 23:57:30.089097 | 2016-12-11 23:57:30.111726 | |
| (1 row) | |
| imdb=# SELECT movie_id FROM roles WHERE actor_id=511; | |
| movie_id | |
| ---------- | |
| 279 | |
| 3716 | |
| 4419 | |
| (3 rows) | |
| imdb=# SELECT * FROM movies WHERE id=279 OR id=3716 OR id=4419; | |
| id | title | duration | gross | imdb | content_rating | budget | year | language | director_id | created_at | updated_at | |
| ------+--------------------+----------+-----------+------------------------------------------------------+----------------+-----------+------+----------+-------------+----------------------------+---------------------------- | |
| 279 | The Martian | 151 | 228430993 | http://www.imdb.com/title/tt3659388/?ref_=fn_tt_tt_1 | PG-13 | 108000000 | 2015 | English | 19 | 2016-12-11 23:57:23.664074 | 2016-12-11 23:57:23.677043 | |
| 3716 | The Lazarus Effect | 83 | 25799043 | http://www.imdb.com/title/tt2918436/?ref_=fn_tt_tt_1 | PG-13 | 3300000 | 2015 | English | 1607 | 2016-12-11 23:58:51.49489 | 2016-12-11 23:58:51.508737 | |
| 4419 | The To Do List | 104 | 3447339 | http://www.imdb.com/title/tt1758795/?ref_=fn_tt_tt_1 | R | 1500000 | 2013 | English | 1991 | 2016-12-11 23:59:09.311436 | 2016-12-11 23:59:09.321934 | |
| (3 rows) | |
| imdb=# |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment