Skip to content

Instantly share code, notes, and snippets.

@clgrogan

clgrogan/gist:825abd0ccb23b63084d44d8afa318bc0 Secret

Created December 10, 2019 20:39
Show Gist options
  • Save clgrogan/825abd0ccb23b63084d44d8afa318bc0 to your computer and use it in GitHub Desktop.
Save clgrogan/825abd0ccb23b63084d44d8afa318bc0 to your computer and use it in GitHub Desktop.
Download ZIP
week 07 - day 02 - Joins! - #23
Raw
gistfile1.txt
Time: 0.001s
company_database> CREATE TABLE departments (
................. id SERIAL PRIMARY KEY,
................. department_name TEXT,
................. building TEXT );
CREATE TABLE
Time: 0.026s
company_database> Exception in thread completion_refresh:
Traceback (most recent call last):
File "/usr/local/opt/python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 926, in _bootstrap_inner
self.run()
File "/usr/local/opt/python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 870, in runompletions...
self._target(*self._args, **self._kwargs)
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 65, in _bg_refresh
refresher(completer, executor)
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 109, in refresh_tables
completer.extend_columns(executor.table_columns(), kind="tables")
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgcompleter.py", line 218, in extend_columns
for schema, relname, colname, datatype, has_default, default in column_data:
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 591, in table_columns
for row in self._columns(kinds=["r"]):
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 586, in _columns
cur.execute(sql)
psycopg2.errors.UndefinedColumn: column def.adsrc does not exist
LINE 7: def.adsrc as default
^
company_database>
Time: 0.000s
company_database> \d
+----------+--------------------+----------+---------+
| Schema | Name | Type | Owner |
|----------+--------------------+----------+---------|
| public | departments | table | cgrogan |
| public | departments_id_seq | sequence | cgrogan |
| public | employees | table | cgrogan |
| public | employees_id_seq | sequence | cgrogan |
+----------+--------------------+----------+---------+
SELECT 4
Time: 0.014s
company_database> select * from departments
................. ;
+------+-------------------+------------+
| id | department_name | building |
|------+-------------------+------------|
+------+-------------------+------------+
SELECT 0
Time: 0.013s
company_database> ALTER TABLE employees ADD COLUMN department_id INTEGER NULL REFERENCES departments.id
................. ;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
schema "departments" does not exist
Time: 0.002s
company_database> -ls
................. ;
syntax error at or near "-"
LINE 1: -ls
^
Time: 0.001s
company_database> \d
+----------+--------------------+----------+---------+
| Schema | Name | Type | Owner |
|----------+--------------------+----------+---------|
| public | departments | table | cgrogan |
| public | departments_id_seq | sequence | cgrogan |
| public | employees | table | cgrogan |
| public | employees_id_seq | sequence | cgrogan |
+----------+--------------------+----------+---------+
SELECT 4
Time: 0.014s
company_database> ALTER TABLE employees ADD COLUMN department_id INTEGER NULL REFERENCES departments(id);
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
ALTER TABLE
Time: 0.006s
company_database> Exception in thread completion_refresh:
Traceback (most recent call last):
File "/usr/local/opt/python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 926, in _bootstrap_inner
self.run()
File "/usr/local/opt/python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 870, in runompletions...
self._target(*self._args, **self._kwargs)
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 65, in _bg_refresh
refresher(completer, executor)
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 109, in refresh_tables
completer.extend_columns(executor.table_columns(), kind="tables")
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgcompleter.py", line 218, in extend_columns
for schema, relname, colname, datatype, has_default, default in column_data:
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 591, in table_columns
for row in self._columns(kinds=["r"]):
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 586, in _columns
cur.execute(sql)
psycopg2.errors.UndefinedColumn: column def.adsrc does not exist
LINE 7: def.adsrc as default
^
company_database>
Time: 0.000s
company_database> select * from departments
................. ;
+------+-------------------+------------+
| id | department_name | building |
|------+-------------------+------------|
+------+-------------------+------------+
SELECT 0
Time: 0.013s
company_database> select * from employees;
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
| full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id |
|-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------|
| John Doe | 150000 | VP of Stuff | 12345 | False | 1 | <null> | <null> |
| Ascii Coder | 450 | software developer | 54321 | False | 4 | <null> | <null> |
| Jane Doe | 500 | cook | 12345 | False | 3 | 0123456789 | <null> |
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
SELECT 3
Time: 0.014s
................. Create queries that can do the following:
................. Given a department id, give me all employees in the department
................. Given a department name, give me the phone extensions
................. Find all orders that contain the product id of 2
................. Given an order id, display on the OrderNumber, and all the product names
................. Inserts a new product
................. Inserts a new order
................. Adds a product to an order
................. Adds a new employee to a department
................. Updating a employee's department
................. Removing a product from an order
................. Turn in the above queries as a gist
................. Adventure Mode
................. Work on your final project. Do not sleep on this.
................. Start drafting of your database schema for your final project.
................. Give these SQL Koans a try.
................. Epic Mode
................. Investigate working with C#/Ruby and Postgres
................. Additional Resources
................. PostgreSQL Documentation
................. SQL Notes
................. Join Notes
................. Recommended Practice:
................. For more practice, Hackerra
company_database> CREATE TABLE orders (
................. id SERIAL PRIMARY KEY,
................. order_number TEXT,
................. date_placed TIMESTAMPTZ,
................. email TEXT
................. ;
syntax error at end of input
LINE 6:
^
Time: 0.001s
company_database> CREATE TABLE orders (
................. id SERIAL PRIMARY KEY,
................. order_number TEXT,
................. date_placed TIMESTAMPTZ,
................. email TEXT
................. );
CREATE TABLE
Time: 0.005s
company_database> Exception in thread completion_refresh:
Traceback (most recent call last):
File "/usr/local/opt/python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 926, in _bootstrap_inner
self.run()
File "/usr/local/opt/python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 870, in runompletions...
self._target(*self._args, **self._kwargs)
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 65, in _bg_refresh
refresher(completer, executor)
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 109, in refresh_tables
completer.extend_columns(executor.table_columns(), kind="tables")
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgcompleter.py", line 218, in extend_columns
for schema, relname, colname, datatype, has_default, default in column_data:
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 591, in table_columns
for row in self._columns(kinds=["r"]):
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 586, in _columns
cur.execute(sql)
psycopg2.errors.UndefinedColumn: column def.adsrc does not exist
LINE 7: def.adsrc as default
^
company_database>
Time: 0.000s
company_database> CREATE TABLE products (
................. id SERIAL PRIMARY KEY,
................. name TEXT,
................. description TEXT,
................. price DOUBLE,
................. number_in_stock INT );
type "double" does not exist
LINE 5: price DOUBLE,
^
Time: 0.001s
company_database> CREATE TABLE products (
................. id SERIAL PRIMARY KEY,
................. name TEXT,
................. description TEXT,
................. price DOUBLE PRECISION,
................. number_in_stock INT );
CREATE TABLE
Time: 0.003s
company_database> Exception in thread completion_refresh:
Traceback (most recent call last):
File "/usr/local/opt/python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 926, in _bootstrap_inner
self.run()
File "/usr/local/opt/python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 870, in runompletions...
self._target(*self._args, **self._kwargs)
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 65, in _bg_refresh
refresher(completer, executor)
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 109, in refresh_tables
completer.extend_columns(executor.table_columns(), kind="tables")
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgcompleter.py", line 218, in extend_columns
for schema, relname, colname, datatype, has_default, default in column_data:
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 591, in table_columns
for row in self._columns(kinds=["r"]):
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 586, in _columns
cur.execute(sql)
psycopg2.errors.UndefinedColumn: column def.adsrc does not exist
LINE 7: def.adsrc as default
^
company_database>
Time: 0.000s
company_database> select * from orders
................. ;
+------+----------------+---------------+---------+
| id | order_number | date_placed | email |
|------+----------------+---------------+---------|
+------+----------------+---------------+---------+
SELECT 0
Time: 0.013s
company_database> select * from products
................. ;
+------+--------+---------------+---------+-------------------+
| id | name | description | price | number_in_stock |
|------+--------+---------------+---------+-------------------|
+------+--------+---------------+---------+-------------------+
SELECT 0
Time: 0.013s
company_database> CREATE TABLE product_orders (
................. id SERIAL PRIMARY KEY,
................. product_id INTEGER REFERENCES product(id),
................. order_id INTEGER REFERENCES orders(id));
relation "product" does not exist
Time: 0.003s
company_database> CREATE TABLE product_orders (
................. id SERIAL PRIMARY KEY,
................. product_id INTEGER REFERENCES products(id),
................. order_id INTEGER REFERENCES orders(id));
CREATE TABLE
Time: 0.004s
company_database> Exception in thread completion_refresh:
Traceback (most recent call last):
File "/usr/local/opt/python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 926, in _bootstrap_inner
self.run()
File "/usr/local/opt/python/Frameworks/Python.framework/Versions/3.7/lib/python3.7/threading.py", line 870, in runompletions...
self._target(*self._args, **self._kwargs)
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 65, in _bg_refresh
refresher(completer, executor)
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/completion_refresher.py", line 109, in refresh_tables
completer.extend_columns(executor.table_columns(), kind="tables")
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgcompleter.py", line 218, in extend_columns
for schema, relname, colname, datatype, has_default, default in column_data:
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 591, in table_columns
for row in self._columns(kinds=["r"]):
File "/usr/local/Cellar/pgcli/2.1.1_2/libexec/lib/python3.7/site-packages/pgcli/pgexecute.py", line 586, in _columns
cur.execute(sql)
psycopg2.errors.UndefinedColumn: column def.adsrc does not exist
LINE 7: def.adsrc as default
^
company_database>
Time: 0.000s
company_database> select * from departments
................. ;
+------+-------------------+------------+
| id | department_name | building |
|------+-------------------+------------|
+------+-------------------+------------+
SELECT 0
Time: 0.012s
company_database> select * from employees
................. ;
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
| full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id |
|-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------|
| John Doe | 150000 | VP of Stuff | 12345 | False | 1 | <null> | <null> |
| Ascii Coder | 450 | software developer | 54321 | False | 4 | <null> | <null> |
| Jane Doe | 500 | cook | 12345 | False | 3 | 0123456789 | <null> |
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
SELECT 3
Time: 0.014s
company_database> INSERT INTO departments(department_name,building)
................. VALUES ('Facilities', 'Main');
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO departments(department_name,building)
................. VALUES ('IT', 'Main');
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO departments(department_name,building)
................. VALUES ('Talent', 'Suntrust');
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO departments(department_name,building)
................. VALUES ('Marketing', 'Remote');
INSERT 0 1
Time: 0.001s
company_database> select * from departments
................. ;
+------+-------------------+------------+
| id | department_name | building |
|------+-------------------+------------|
| 1 | Facilities | Main |
| 2 | IT | Main |
| 3 | Talent | Suntrust |
| 4 | Marketing | Remote |
+------+-------------------+------------+
SELECT 4
Time: 0.013s
company_database> select * from employees
................. ;
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
| full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id |
|-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------|
| John Doe | 150000 | VP of Stuff | 12345 | False | 1 | <null> | <null> |
| Ascii Coder | 450 | software developer | 54321 | False | 4 | <null> | <null> |
| Jane Doe | 500 | cook | 12345 | False | 3 | 0123456789 | <null> |
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
SELECT 3
Time: 0.014s
company_database> UPDATE employees
................. SET department_id = 2
................. WHERE id = 4 ;
UPDATE 1
Time: 0.002s
company_database> UPDATE employees
................. SET department_id = 4
................. WHERE id = 1 ;
UPDATE 1
Time: 0.001s
company_database> UPDATE employees
................. SET department_id = 1
................. WHERE id = 3 ;
UPDATE 1
Time: 0.001s
company_database> select * from employees
................. ;
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
| full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id |
|-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------|
| Ascii Coder | 450 | software developer | 54321 | False | 4 | <null> | 2 |
| John Doe | 150000 | VP of Stuff | 12345 | False | 1 | <null> | 4 |
| Jane Doe | 500 | cook | 12345 | False | 3 | 0123456789 | 1 |
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
SELECT 3
Time: 0.014s
company_database> UPDATE employees
................. SET department_id = 9
................. WHERE id = 3 ;
insert or update on table "employees" violates foreign key constraint "employees_department_id_fkey"
DETAIL: Key (department_id)=(9) is not present in table "departments".
Time: 0.001s
company_database> SELECT * FROM employees
................. WHERE department_id = 2;
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
| full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id |
|-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------|
| Ascii Coder | 450 | software developer | 54321 | False | 4 | <null> | 2 |
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
SELECT 1
Time: 0.013s
company_database> SELECT employees.phone_extension
................. FROM departments
................. WHERE name = 'IT'
................. JOIN employees ON employees.department_id = departments.id;
syntax error at or near "JOIN"
LINE 4: JOIN employees ON employees.department_id = departments.id
^
Time: 0.001s
company_database> SELECT employees.phone_extension
................. FROM departments
................. WHERE department_name = 'IT'
................. JOIN employees ON employees.department_id = departments.id;
syntax error at or near "JOIN"
LINE 4: JOIN employees ON employees.department_id = departments.id
^
Time: 0.001s
company_database> SELECT employees.phone_extension
................. FROM departments
................. JOIN employees ON employees.department_id = departments.id
................. WHERE department_name = 'IT' ;
+-------------------+
| phone_extension |
|-------------------|
| 54321 |
+-------------------+
SELECT 1
Time: 0.011s
company_database> INSERT INTO products(name, description, number_in_stock, price,)
................. VALUES ('335i', 'BMW 335i Sedan', 1000, 50000.99);
syntax error at or near ")"
LINE 1: ...RT INTO products(name, description, number_in_stock, price,)
^
Time: 0.001s
company_database> INSERT INTO products(name, description, number_in_stock, price)
................. VALUES ('335i', 'BMW 335i Sedan', 1000, 50000.99);
INSERT 0 1
Time: 0.002s
company_database> INSERT INTO products(name, description, number_in_stock, price)
................. VALUES ('X5', 'BMW X5 SUV', 5, 63000.99);
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO products(name, description, number_in_stock, price)
................. VALUES ('X5 M', 'BMW X5 M SUV', 30, 89999.99);
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO products(name, description, number_in_stock, price)
................. VALUES ('M Roadster', 'BMW M Roadster', 1, 1000.00);
INSERT 0 1
Time: 0.001s
company_database> select * from products
................. ;
+------+------------+----------------+----------+-------------------+
| id | name | description | price | number_in_stock |
|------+------------+----------------+----------+-------------------|
| 1 | 335i | BMW 335i Sedan | 50000.99 | 1000 |
| 2 | X5 | BMW X5 SUV | 63000.99 | 5 |
| 3 | X5 M | BMW X5 M SUV | 89999.99 | 30 |
| 4 | M Roadster | BMW M Roadster | 1000.0 | 1 |
+------+------------+----------------+----------+-------------------+
SELECT 4
Time: 0.014s
company_database> SELECT * FROM orders;
+------+----------------+---------------+---------+
| id | order_number | date_placed | email |
|------+----------------+---------------+---------|
+------+----------------+---------------+---------+
SELECT 0
Time: 0.013s
company_database> INSERT INTO orders(order_number, date_placed, email)
................. VALUES ('1000', 2016-06-22 19:10:25-05, 'somename@gmail.com');
syntax error at or near "19"
LINE 2: VALUES ('1000', 2016-06-22 19:10:25-05, 'somename@gmail.com'...
^
Time: 0.001s
company_database> INSERT INTO orders(order_number, date_placed, email)
................. VALUES ('1000', '2016-06-22 19:10:25-05', 'somename@gmail.com');
INSERT 0 1
Time: 0.001s
company_database> SELECT * FROM orders;
+------+----------------+------------------------+--------------------+
| id | order_number | date_placed | email |
|------+----------------+------------------------+--------------------|
| 1 | 1000 | 2016-06-22 20:10:25-04 | somename@gmail.com |
+------+----------------+------------------------+--------------------+
SELECT 1
Time: 0.013s
company_database> INSERT INTO orders(order_number, date_placed, email)
................. VALUES ('1001', '2017-08-22 07:10:25-05', 'somename@gmail.com');
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO orders(order_number, date_placed, email)
................. VALUES ('1002', '2019-12-45 00:10:01-05', 'somename@gmail.com');
date/time field value out of range: "2019-12-45 00:10:01-05"
LINE 2: VALUES ('1002', '2019-12-45 00:10:01-05', 'somename@gmail.co...
^
HINT: Perhaps you need a different "datestyle" setting.
Time: 0.001s
company_database> INSERT INTO orders(order_number, date_placed, email)
................. VALUES ('1002', '2019-12-45 05:10:01-05', 'somename@gmail.com');
date/time field value out of range: "2019-12-45 05:10:01-05"
LINE 2: VALUES ('1002', '2019-12-45 05:10:01-05', 'somename@gmail.co...
^
HINT: Perhaps you need a different "datestyle" setting.
Time: 0.001s
company_database> INSERT INTO orders(order_number, date_placed, email)
................. VALUES ('1002', '2019-12-25 00:10:01-05', 'somename@gmail.com');
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO orders(order_number, date_placed, email)
................. VALUES ('1003', '2019-12-25 00:10:01-05', 'somename@gmail.com');
INSERT 0 1
Time: 0.001s
company_database> DELETE FROM orders
................. WHERE order_number='1002'
................. ;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
DELETE 1
Time: 0.002s
company_database> INSERT INTO orders(order_number, date_placed, email)
................. VALUES ('1002', '2018-10-25 23:59:01-05', 'BOB@gmail.com');
INSERT 0 1
Time: 0.001s
company_database> select * from orders
................. ;
+------+----------------+------------------------+--------------------+
| id | order_number | date_placed | email |
|------+----------------+------------------------+--------------------|
| 1 | 1000 | 2016-06-22 20:10:25-04 | somename@gmail.com |
| 2 | 1001 | 2017-08-22 08:10:25-04 | somename@gmail.com |
| 4 | 1003 | 2019-12-25 00:10:01-05 | somename@gmail.com |
| 5 | 1002 | 2018-10-26 00:59:01-04 | BOB@gmail.com |
+------+----------------+------------------------+--------------------+
SELECT 4
Time: 0.014s
company_database> SELECT * FROM employees
................. ;
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
| full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id |
|-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------|
| Ascii Coder | 450 | software developer | 54321 | False | 4 | <null> | 2 |
| John Doe | 150000 | VP of Stuff | 12345 | False | 1 | <null> | 4 |
| Jane Doe | 500 | cook | 12345 | False | 3 | 0123456789 | 1 |
+-------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+
SELECT 3
Time: 0.014s
company_database> INSERT INTO employees (full_name,salary,job_position,phone_extension,part_time,department_id)
................. VALUES ('Bob Evans', 25000, 'Sales Rep', 666, false, department_id=3);
column "department_id" does not exist
LINE 2: ...UES ('Bob Evans', 25000, 'Sales Rep', 666, false, department...
^
HINT: There is a column named "department_id" in table "employees", but it cannot be referenced from this part of the query.
Time: 0.001s
company_database> INSERT INTO employees (full_name,salary,job_position,phone_extension,part_time,department_id)
................. VALUES ('Bob Evans', 25000, 'Sales Rep', 666, false, 3);
INSERT 0 1
Time: 0.005s
company_database> SELECT * FROM orders;
+------+----------------+------------------------+--------------------+
| id | order_number | date_placed | email |
|------+----------------+------------------------+--------------------|
| 1 | 1000 | 2016-06-22 20:10:25-04 | somename@gmail.com |
| 2 | 1001 | 2017-08-22 08:10:25-04 | somename@gmail.com |
| 4 | 1003 | 2019-12-25 00:10:01-05 | somename@gmail.com |
| 5 | 1002 | 2018-10-26 00:59:01-04 | BOB@gmail.com |
+------+----------------+------------------------+--------------------+
SELECT 4
Time: 0.013s
company_database> SELECT * FROM products;
+------+------------+----------------+----------+-------------------+
| id | name | description | price | number_in_stock |
|------+------------+----------------+----------+-------------------|
| 1 | 335i | BMW 335i Sedan | 50000.99 | 1000 |
| 2 | X5 | BMW X5 SUV | 63000.99 | 5 |
| 3 | X5 M | BMW X5 M SUV | 89999.99 | 30 |
| 4 | M Roadster | BMW M Roadster | 1000.0 | 1 |
+------+------------+----------------+----------+-------------------+
SELECT 4
Time: 0.012s
company_database> \d
+----------+-----------------------+----------+---------+
| Schema | Name | Type | Owner |
|----------+-----------------------+----------+---------|
| public | departments | table | cgrogan |
| public | departments_id_seq | sequence | cgrogan |
| public | employees | table | cgrogan |
| public | employees_id_seq | sequence | cgrogan |
| public | orders | table | cgrogan |
| public | orders_id_seq | sequence | cgrogan |
| public | product_orders | table | cgrogan |
| public | product_orders_id_seq | sequence | cgrogan |
| public | products | table | cgrogan |
| public | products_id_seq | sequence | cgrogan |
+----------+-----------------------+----------+---------+
SELECT 10
Time: 0.017s
company_database> INSERT INTO product_orders(product_id,order_id)
................. VALUES (2,1);
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO product_orders(product_id,order_id)
................. VALUES (1,1);
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO product_orders(product_id,order_id)
................. VALUES (3,1);
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO product_orders(product_id,order_id)
................. VALUES (3,4);
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO product_orders(product_id,order_id)
................. VALUES (2,5);
INSERT 0 1
Time: 0.001s
company_database> INSERT INTO product_orders(product_id,order_id)
................. VALUES (4,2);
INSERT 0 1
Time: 0.001s
company_database> select * from product_orders;
+------+--------------+------------+
| id | product_id | order_id |
|------+--------------+------------|
| 1 | 2 | 1 |
| 2 | 1 | 1 |
| 3 | 3 | 1 |
| 4 | 3 | 4 |
| 5 | 2 | 5 |
| 6 | 4 | 2 |
+------+--------------+------------+
SELECT 6
Time: 0.013s
company_database> SELECT * FROM orders
................. JOIN product_orders ON product_orders.product_id = 2;
+------+----------------+------------------------+--------------------+------+--------------+------------+
| id | order_number | date_placed | email | id | product_id | order_id |
|------+----------------+------------------------+--------------------+------+--------------+------------|
| 1 | 1000 | 2016-06-22 20:10:25-04 | somename@gmail.com | 1 | 2 | 1 |
| 1 | 1000 | 2016-06-22 20:10:25-04 | somename@gmail.com | 5 | 2 | 5 |
| 2 | 1001 | 2017-08-22 08:10:25-04 | somename@gmail.com | 1 | 2 | 1 |
| 2 | 1001 | 2017-08-22 08:10:25-04 | somename@gmail.com | 5 | 2 | 5 |
| 4 | 1003 | 2019-12-25 00:10:01-05 | somename@gmail.com | 1 | 2 | 1 |
| 4 | 1003 | 2019-12-25 00:10:01-05 | somename@gmail.com | 5 | 2 | 5 |
| 5 | 1002 | 2018-10-26 00:59:01-04 | BOB@gmail.com | 1 | 2 | 1 |
| 5 | 1002 | 2018-10-26 00:59:01-04 | BOB@gmail.com | 5 | 2 | 5 |
+------+----------------+------------------------+--------------------+------+--------------+------------+
SELECT 8
Time: 0.015s
company_database> SELECT * FROM orders
................. JOIN product_orders ON product_orders.orders_id = orders.id
................. WHERE product_orders.product_id = 2;
column product_orders.orders_id does not exist
LINE 2: JOIN product_orders ON product_orders.orders_id = orders.id
^
HINT: Perhaps you meant to reference the column "product_orders.order_id".
Time: 0.001s
company_database> SELECT * FROM orders
................. JOIN product_orders ON product_orders.order_id = orders.id
................. WHERE product_orders.product_id = 2;
+------+----------------+------------------------+--------------------+------+--------------+------------+
| id | order_number | date_placed | email | id | product_id | order_id |
|------+----------------+------------------------+--------------------+------+--------------+------------|
| 1 | 1000 | 2016-06-22 20:10:25-04 | somename@gmail.com | 1 | 2 | 1 |
| 5 | 1002 | 2018-10-26 00:59:01-04 | BOB@gmail.com | 5 | 2 | 5 |
+------+----------------+------------------------+--------------------+------+--------------+------------+
SELECT 2
Time: 0.013s
company_database> SELECT orders.order_number, products.name FROM orders
................. JOIN product_orders ON product_orders.order_id = orders.id
................. JOIN products ON product_orders.product_id = product.id
................. WHERE orders.id = 1;
missing FROM-clause entry for table "product"
LINE 3: JOIN products ON product_orders.product_id = product.id
^
Time: 0.001s
company_database> SELECT orders.order_number, products.name FROM orders
................. JOIN product_orders ON product_orders.order_id = orders.id
................. JOIN products ON product_orders.product_id = products.id
................. WHERE orders.id = 1;
+----------------+--------+
| order_number | name |
|----------------+--------|
| 1000 | 335i |
| 1000 | X5 |
| 1000 | X5 M |
+----------------+--------+
SELECT 3
Time: 0.010s
company_database> DELETE FROM product_orders
................. WHERE product_orders.product_id = 2 AND product_orders.order_id = 1;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
DELETE 1
Time: 0.002s
company_database> select * from product_orders;
+------+--------------+------------+
| id | product_id | order_id |
|------+--------------+------------|
| 2 | 1 | 1 |
| 3 | 3 | 1 |
| 4 | 3 | 4 |
| 5 | 2 | 5 |
| 6 | 4 | 2 |
+------+--------------+------------+
SELECT 5
Time: 0.013s
company_database>
[F2] Smart Completion: ON [F3] Multiline: ON (Semi-colon [;] will end the line) [F4] Emacs-mode
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment