09 - SQL Joins - Structuring Data

[chesiree-balogh]

Foreign Keys - The SQL

As we continue to explore databases and ideas, this project will help you practice model databases and relationships, as well as help you get started on your capstone project.

First, you will expand your CompanyDatabase to have Departments, Products and Orders. You will be crafting queries for these new tables.

After that, this weekend is the next big push your final project, work on the HTML and CSS; As well as the database schema.

Objectives

• Work with Foreign Keys
• Craft Queries

Requirements

• Add a few tables and foreign keys to an existing database
• Create the Schema for your final project

Explorer Mode

• [x] In your Company Database, add a table named Departments. Give this table a few columns:

  • Id as a primary key
  • DepartmentName as text
  • Building as text

• [x] Add a Foreign key DepartmentId to your Employee Table

• [x] Add tables named Products and Orders.

  • Orders should have the columns
  • OrderNumber as string
  • Id as a primary key
  • DatePlaced as datetime
  • Email as string
  • Product should have the columns
  • Id as a primary key
  • Price as double
  • Name as string
  • Description as string
  • NumberInStock as int

• [x] In our company, 1 Order can have many Products and 1 Product can have many Orders. This will be a Many-to-Many relationship. Create the 3 tables (Orders, Products and ProductOrders) and foreign keys needed for this to happen

• [x] Create queries that can do the following:

  • [x] Given a department id, give me all employees in the department
  • [x] Given a department name, give me the phone extensions
  • [x] Find all orders that contain the product id of 2
  • [x] Given an order id, display on the OrderNumber, and all the product names
  • [x] Inserts a new product
  • [x] Inserts a new order
  • [x] Adds a product to an order
  • [x] Adds a new employee to a department
  • [x] Updating a employee's department
  • [x] Removing a product from an order

• [x] Turn in the above queries as a gist

Adventure Mode

• [ ] Model out and implement a database for your Bank app from the past weekend

Epic Mode

• [ ] Give these SQL Koans a try.
• [ ] Investigate working with C# and Postgres, HINT: entity framework core
• LINQ Notes
• EF Core notes

Additional Resources

• PostgreSQL Documentation
• SQL Notes
• Join Notes

Recommended Practice:

• For more practice, Hackerrank has a SQL Track

[chesiree-balogh]

Last login: Tue Feb 25 09:09:01 on ttys000

The default interactive shell is now zsh. To update your account to use zsh, please run chsh -s /bin/zsh. For more details, please visit https://support.apple.com/kb/HT208050. Chesirees-MBP:~ chesireebalogh$ cd documents Chesirees-MBP:documents chesireebalogh$ ls Untitled document.pdf cohort-17 Chesirees-MBP:documents chesireebalogh$ cd cohort-17/ Chesirees-MBP:cohort-17 chesireebalogh$ ls DotNetCoreKoans deck shuffler rock, paper, scissors FirstBankOfSuncoast jurassic park variables blackjack net-iteration Chesirees-MBP:cohort-17 chesireebalogh$ clear

Chesirees-MBP:cohort-17 chesireebalogh$ pgcli company_database Server: PostgreSQL 12.2 Version: 2.2.0 Chat: https://gitter.im/dbcli/pgcli Home: http://pgcli.com company_database> \dt
+----------+-----------+--------+----------------+ | Schema | Name | Type | Owner | |----------+-----------+--------+----------------| | public | employees | table | chesireebalogh | +----------+-----------+--------+----------------+ SELECT 1 Time: 0.019s company_database> SELECT * FROM employees;
+------------------+----------+--------------------+-------------------+-------------+------+-----------------------+ | full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | |------------------+----------+--------------------+-------------------+-------------+------+-----------------------| | Alexa Lewis | 165000 | Senior Dev | 4321 | no | 2 | 2837485960 | | Frank Balogh Jr. | 200000 | President | 1025 | no | 5 | 4958695849 | | Chesiree Balogh | 165000 | Senior Dev | 2389 | no | 6 | 4958695848 | | Michaellt Balogh | 180000 | Vice President | 2562 | no | 7 | 4958695847 | | Jordan Bryan | 100000 | TA | 1234 | no | 1 | 4958695843 | | Grayson Wolfe | 500 | Cook | 3884 | no | 10 | 4958695842 | | Heather Logan | 450 | Software Developer | 9393 | no | 9 | 4958695841 | +------------------+----------+--------------------+-------------------+-------------+------+-----------------------+ SELECT 7 Time: 0.019s company_database> CREATE TABLE Departments (id SERIAL PRIMARY KEY, DepartmentName TEXT, Building TEXT);
CREATE TABLE Time: 0.011s company_database> \dt
+----------+-------------+--------+----------------+ | Schema | Name | Type | Owner | |----------+-------------+--------+----------------| | public | departments | table | chesireebalogh | | public | employees | table | chesireebalogh | +----------+-------------+--------+----------------+ SELECT 2 Time: 0.017s company_database> CREATE TABLE Products (id SERIAL PRIMARY KEY, Price FLOAT, ProductName TEXT, Description TEXT, NumberInStoc ................. k INT);
CREATE TABLE Time: 0.006s company_database> CREAT TABLE Orders (OrderNumber TEXT, id SERIAL PRIMARY KEY, DatePlaced DATE, Email TEXT);
syntax error at or near "CREAT" LINE 1: CREAT TABLE Orders (OrderNumber TEXT, id SERIAL PRIMARY KEY,... ^

Time: 0.005s company_database> CREATE TABLE Orders (OrderNumber TEXT, id SERIAL PRIMARY KEY, DatePlaced DATE, Email TEXT);
CREATE TABLE Time: 0.004s company_database> \dt
+----------+-------------+--------+----------------+ | Schema | Name | Type | Owner | |----------+-------------+--------+----------------| | public | departments | table | chesireebalogh | | public | employees | table | chesireebalogh | | public | orders | table | chesireebalogh | | public | products | table | chesireebalogh | +----------+-------------+--------+----------------+ SELECT 4 Time: 0.017s company_database>

Time: 0.000s 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): \dt Error: \dt is not a valid boolean You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! ALTER TABLE Time: 0.009s company_database> \dt
+----------+-------------+--------+----------------+ | Schema | Name | Type | Owner | |----------+-------------+--------+----------------| | public | departments | table | chesireebalogh | | public | employees | table | chesireebalogh | | public | orders | table | chesireebalogh | | public | products | table | chesireebalogh | +----------+-------------+--------+----------------+ SELECT 4 Time: 0.018s company_database> SELECT * FROM employees;
+------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ | full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id | |------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------| | Alexa Lewis | 165000 | Senior Dev | 4321 | no | 2 | 2837485960 | | | Frank Balogh Jr. | 200000 | President | 1025 | no | 5 | 4958695849 | | | Chesiree Balogh | 165000 | Senior Dev | 2389 | no | 6 | 4958695848 | | | Michaellt Balogh | 180000 | Vice President | 2562 | no | 7 | 4958695847 | | | Jordan Bryan | 100000 | TA | 1234 | no | 1 | 4958695843 | | | Grayson Wolfe | 500 | Cook | 3884 | no | 10 | 4958695842 | | | Heather Logan | 450 | Software Developer | 9393 | no | 9 | 4958695841 | | +------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ SELECT 7 Time: 0.018s company_database>

[F2] Smart Completion: ON [F3] Multiline: ON (Semi-colon [;] will end the line) [F4] Emacs-mode company_database>

company_database>

Time: 0.000s company_database> \dt
+----------+-------------+--------+----------------+ | Schema | Name | Type | Owner | |----------+-------------+--------+----------------| | public | departments | table | chesireebalogh | | public | employees | table | chesireebalogh | | public | orders | table | chesireebalogh | | public | products | table | chesireebalogh | +----------+-------------+--------+----------------+ SELECT 4 Time: 0.018s company_database> CREATE TABLE ProductOrders (id SERIAL PRIMARY KEY, orders

[F2] Smart Completion: ON [F3] Multiline: ON (Semi-colon [;] will end the line) [F4] Emacs-mode company_database> CREATE TABLE ProductOrders (id SERIAL PRIMARY KEY, orders_id INTEGER REFERENCES orders (id), products_id INTEGER REFERENCES products (id));
CREATE TABLE Time: 0.005s company_database> \dt
+----------+---------------+--------+----------------+ | Schema | Name | Type | Owner | |----------+---------------+--------+----------------| | public | departments | table | chesireebalogh | | public | employees | table | chesireebalogh | | public | orders | table | chesireebalogh | | public | productorders | table | chesireebalogh | | public | products | table | chesireebalogh | +----------+---------------+--------+----------------+ SELECT 5 Time: 0.017s company_database> SELECT FROM employees;
+------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ | full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id | |------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------| | Alexa Lewis | 165000 | Senior Dev | 4321 | no | 2 | 2837485960 | | | Frank Balogh Jr. | 200000 | President | 1025 | no | 5 | 4958695849 | | | Chesiree Balogh | 165000 | Senior Dev | 2389 | no | 6 | 4958695848 | | | Michaellt Balogh | 180000 | Vice President | 2562 | no | 7 | 4958695847 | | | Jordan Bryan | 100000 | TA | 1234 | no | 1 | 4958695843 | | | Grayson Wolfe | 500 | Cook | 3884 | no | 10 | 4958695842 | | | Heather Logan | 450 | Software Developer | 9393 | no | 9 | 4958695841 | | +------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ SELECT 7 Time: 0.019s company_database> SELECT FROM departments ................. SELECT FROM departmetns;
syntax error at or near "SELECT" LINE 2: SELECT FROM departmetns ^

Time: 0.001s company_database>

company_database>

[F2] Smart Completion: ON [F3] Multiline: ON (Semi-colon [;] will end the line) [F4] Emacs-mode company_database> SELECT FROM productorders;
+------+-------------+---------------+ | id | orders_id | products_id | |------+-------------+---------------| +------+-------------+---------------+ SELECT 0 Time: 0.014s company_database> SELECT FROM departments;
+------+------------------+------------+ | id | departmentname | building | |------+------------------+------------| +------+------------------+------------+ SELECT 0 Time: 0.015s company_database> INSERT INTO departments (departmentname) VALUES ('Human Resources');
INSERT 0 1 Time: 0.002s company_database> INSERT INTO departments (departmentname) VALUES ('Delivery Crew');
INSERT 0 1 Time: 0.001s company_database> INSERT INTO departments (departmentname) VALUES ('Call Center');
INSERT 0 1 Time: 0.001s company_database> SELECT FROM departments;
+------+------------------+------------+ | id | departmentname | building | |------+------------------+------------| | 1 | Human Resources | | | 2 | Delivery Crew | | | 3 | Call Center | | +------+------------------+------------+ SELECT 3 Time: 0.016s company_database> UPDATE departments SET building = 'A' WHERE id in (1);
UPDATE 1 Time: 0.001s company_database> SELECT FROM departments;
+------+------------------+------------+ | id | departmentname | building | |------+------------------+------------| | 2 | Delivery Crew | | | 3 | Call Center | | | 1 | Human Resources | A | +------+------------------+------------+ SELECT 3 Time: 0.016s company_database> UPDATE departments SET building = 'B' WHERE id in (2);
UPDATE 1 Time: 0.001s company_database> UPDATE departments SET building = 'C' WHERE id in (3);
UPDATE 1 Time: 0.001s company_database> SELECT FROM departments;
+------+------------------+------------+ | id | departmentname | building | |------+------------------+------------| | 1 | Human Resources | A | | 2 | Delivery Crew | B | | 3 | Call Center | C | +------+------------------+------------+ SELECT 3 Time: 0.016s company_database> SELECT FROM employees;
+------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ | full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id | |------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------| | Alexa Lewis | 165000 | Senior Dev | 4321 | no | 2 | 2837485960 | | | Frank Balogh Jr. | 200000 | President | 1025 | no | 5 | 4958695849 | | | Chesiree Balogh | 165000 | Senior Dev | 2389 | no | 6 | 4958695848 | | | Michaellt Balogh | 180000 | Vice President | 2562 | no | 7 | 4958695847 | | | Jordan Bryan | 100000 | TA | 1234 | no | 1 | 4958695843 | | | Grayson Wolfe | 500 | Cook | 3884 | no | 10 | 4958695842 | | | Heather Logan | 450 | Software Developer | 9393 | no | 9 | 4958695841 | | +------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ SELECT 7 Time: 0.019s company_database> UPDATE employees SET department_id = 1 WHERE id in (2,5,6,7);
UPDATE 4 Time: 0.002s company_database> Select From employees;
+------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ | full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id | |------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------| | Jordan Bryan | 100000 | TA | 1234 | no | 1 | 4958695843 | | | Grayson Wolfe | 500 | Cook | 3884 | no | 10 | 4958695842 | | | Heather Logan | 450 | Software Developer | 9393 | no | 9 | 4958695841 | | | Alexa Lewis | 165000 | Senior Dev | 4321 | no | 2 | 2837485960 | 1 | | Frank Balogh Jr. | 200000 | President | 1025 | no | 5 | 4958695849 | 1 | | Chesiree Balogh | 165000 | Senior Dev | 2389 | no | 6 | 4958695848 | 1 | | Michaellt Balogh | 180000 | Vice President | 2562 | no | 7 | 4958695847 | 1 | +------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ SELECT 7 Time: 0.019s company_database> UPDATE employees SET department_id = 2 WHERE id in (10);
UPDATE 1 Time: 0.002s company_database> Select From employees;
+------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ | full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id | |------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------| | Jordan Bryan | 100000 | TA | 1234 | no | 1 | 4958695843 | | | Heather Logan | 450 | Software Developer | 9393 | no | 9 | 4958695841 | | | Alexa Lewis | 165000 | Senior Dev | 4321 | no | 2 | 2837485960 | 1 | | Frank Balogh Jr. | 200000 | President | 1025 | no | 5 | 4958695849 | 1 | | Chesiree Balogh | 165000 | Senior Dev | 2389 | no | 6 | 4958695848 | 1 | | Michaellt Balogh | 180000 | Vice President | 2562 | no | 7 | 4958695847 | 1 | | Grayson Wolfe | 500 | Cook | 3884 | no | 10 | 4958695842 | 2 | +------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ SELECT 7 Time: 0.018s company_database> UPDATE employees SET department_id = 3 WHERE id in (1,9);
UPDATE 2 Time: 0.001s company_database> Select * From employees;
+------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ | full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id | |------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------| | Alexa Lewis | 165000 | Senior Dev | 4321 | no | 2 | 2837485960 | 1 | | Frank Balogh Jr. | 200000 | President | 1025 | no | 5 | 4958695849 | 1 | | Chesiree Balogh | 165000 | Senior Dev | 2389 | no | 6 | 4958695848 | 1 | | Michaellt Balogh | 180000 | Vice President | 2562 | no | 7 | 4958695847 | 1 | | Grayson Wolfe | 500 | Cook | 3884 | no | 10 | 4958695842 | 2 | | Jordan Bryan | 100000 | TA | 1234 | no | 1 | 4958695843 | 3 | | Heather Logan | 450 | Software Developer | 9393 | no | 9 | 4958695841 | 3 | +------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ SELECT 7 Time: 0.020s company_database> SELECT employees.ful_name, departments.departmentname FROM employees JOIN departments ON employees.id = departments.id;
column employees.ful_name does not exist LINE 1: SELECT employees.ful_name, departments.departmentname FROM e... ^ HINT: Perhaps you meant to reference the column "employees.full_name".

Time: 0.002s company_database> SELECT employees.full_name, departments.departmentname FROM employees JOIN departments ON employees.id = departments.id;
+--------------+------------------+ | full_name | departmentname | |--------------+------------------| | Jordan Bryan | Human Resources | | Alexa Lewis | Delivery Crew | +--------------+------------------+ SELECT 2 Time: 0.016s company_database> SELECT employees.full_name, departments.departmentname FROM employees JOIN departments ON employees.department_id = departments.id;
+------------------+------------------+ | full_name | departmentname | |------------------+------------------| | Alexa Lewis | Human Resources | | Frank Balogh Jr. | Human Resources | | Chesiree Balogh | Human Resources | | Michaellt Balogh | Human Resources | | Grayson Wolfe | Delivery Crew | | Jordan Bryan | Call Center | | Heather Logan | Call Center | +------------------+------------------+ SELECT 7 Time: 0.015s company_database> SELECT employees.phone_extension, departments.departmentname FROM employees JOIN departments ON employees.department_id = departments.id;
+-------------------+------------------+ | phone_extension | departmentname | |-------------------+------------------| | 4321 | Human Resources | | 1025 | Human Resources | | 2389 | Human Resources | | 2562 | Human Resources | | 3884 | Delivery Crew | | 1234 | Call Center | | 9393 | Call Center | +-------------------+------------------+ SELECT 7 Time: 0.015s company_database> Select * From products;
+------+---------+---------------+---------------+-----------------+ | id | price | productname | description | numberinstock | |------+---------+---------------+---------------+-----------------| +------+---------+---------------+---------------+-----------------+ SELECT 0 Time: 0.016s company_database> INSERT INTO products (price, productname, description, numberinstock) ................. INSERT INTO products (price, productname, description, numberinstock) VALUES (199.99, 'Leather Harness', 'Aged real leather', 13);
syntax error at or near "INSERT" LINE 2: INSERT INTO products (price, productname, description, numbe... ^

Time: 0.002s company_database> INSERT INTO products (price, productname, description, numberinstock) VALUES (199.99, 'Leather Harness', 'Aged real leather', 13);
INSERT 0 1 Time: 0.002s company_database> Select From products;
+------+---------+-----------------+-------------------+-----------------+ | id | price | productname | description | numberinstock | |------+---------+-----------------+-------------------+-----------------| | 1 | 199.99 | Leather Harness | Aged real leather | 13 | +------+---------+-----------------+-------------------+-----------------+ SELECT 1 Time: 0.016s company_database> INSERT INTO products (price, productname, description, numberinstock) VALUES (99.99, 'Under Arm Harness', 'Cellphone harness', 15);
INSERT 0 1 Time: 0.001s company_database> Select From products;
+------+---------+-------------------+-------------------+-----------------+ | id | price | productname | description | numberinstock | |------+---------+-------------------+-------------------+-----------------| | 1 | 199.99 | Leather Harness | Aged real leather | 13 | | 2 | 99.99 | Under Arm Harness | Cellphone harness | 15 | +------+---------+-------------------+-------------------+-----------------+ SELECT 2 Time: 0.016s company_database> INSERT INTO products (price, productname, description, numberinstock) VALUES (50.00, 'Dog Collar Wallet Combo', 'Matching owner pet set', 2) ................. ;
INSERT 0 1 Time: 0.001s company_database> Select From products;
+------+---------+-------------------------+------------------------+-----------------+ | id | price | productname | description | numberinstock | |------+---------+-------------------------+------------------------+-----------------| | 1 | 199.99 | Leather Harness | Aged real leather | 13 | | 2 | 99.99 | Under Arm Harness | Cellphone harness | 15 | | 3 | 50.0 | Dog Collar Wallet Combo | Matching owner pet set | 2 | +------+---------+-------------------------+------------------------+-----------------+ SELECT 3 Time: 0.016s company_database> UPDATE products SET numberinstock = 22 WHERE id in (3);
UPDATE 1 Time: 0.001s company_database> Select From products;
+------+---------+-------------------------+------------------------+-----------------+ | id | price | productname | description | numberinstock | |------+---------+-------------------------+------------------------+-----------------| | 1 | 199.99 | Leather Harness | Aged real leather | 13 | | 2 | 99.99 | Under Arm Harness | Cellphone harness | 15 | | 3 | 50.0 | Dog Collar Wallet Combo | Matching owner pet set | 22 | +------+---------+-------------------------+------------------------+-----------------+ SELECT 3 Time: 0.017s company_database> SELECT FROM orders;
+---------------+------+--------------+---------+ | ordernumber | id | dateplaced | email | |---------------+------+--------------+---------| +---------------+------+--------------+---------+ SELECT 0 Time: 0.014s company_database> INSERT INTO orders (ordernumber, dateplaced, email) VALUES ('1313', '08-23-2019', 'joe@gmail.com');
INSERT 0 1 Time: 0.002s company_database> SELECT FROM orders;
+---------------+------+--------------+---------------+ | ordernumber | id | dateplaced | email | |---------------+------+--------------+---------------| | 1313 | 1 | 2019-08-23 | joe@gmail.com | +---------------+------+--------------+---------------+ SELECT 1 Time: 0.016s company_database> INSERT INTO orders (ordernumber, dateplaced, email) VALUES ('3849', '02-22-2020', 'lez@gmail.com');
INSERT 0 1 Time: 0.001s company_database> SELECT FROM orders;
+---------------+------+--------------+---------------+ | ordernumber | id | dateplaced | email | |---------------+------+--------------+---------------| | 1313 | 1 | 2019-08-23 | joe@gmail.com | | 3849 | 2 | 2020-02-22 | lez@gmail.com | +---------------+------+--------------+---------------+ SELECT 2 Time: 0.016s company_database> ALTER TABLE orders ADD COLUMN product_id INT NULL REFERENCES products(id);
You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! ALTER TABLE Time: 0.003s company_database> SELECT FROM orders;
+---------------+------+--------------+---------------+--------------+ | ordernumber | id | dateplaced | email | product_id | |---------------+------+--------------+---------------+--------------| | 1313 | 1 | 2019-08-23 | joe@gmail.com | | | 3849 | 2 | 2020-02-22 | lez@gmail.com | | +---------------+------+--------------+---------------+--------------+ SELECT 2 Time: 0.016s company_database> UPDATE orders SET product_id = 2 WHERE id in 1;
syntax error at or near "1" LINE 1: UPDATE orders SET product_id = 2 WHERE id in 1 ^

Time: 0.001s company_database> UPDATE orders SET product_id = 2 WHERE id in (1);
UPDATE 1 Time: 0.002s company_database> SELECT * FROM orders;
+---------------+------+--------------+---------------+--------------+ | ordernumber | id | dateplaced | email | product_id | |---------------+------+--------------+---------------+--------------| | 3849 | 2 | 2020-02-22 | lez@gmail.com | | | 1313 | 1 | 2019-08-23 | joe@gmail.com | 2 | +---------------+------+--------------+---------------+--------------+ SELECT 2 Time: 0.017s company_database> UPDATE orders SET product_id = 12 WHERE id in (2);
insert or update on table "orders" violates foreign key constraint "orders_product_id_fkey" DETAIL: Key (product_id)=(12) is not present in table "products".

Time: 0.002s company_database> UPDATE orders SET product_id = 2 WHERE id in (2);
UPDATE 1 Time: 0.001s company_database> SELECT FROM orders;
+---------------+------+--------------+---------------+--------------+ | ordernumber | id | dateplaced | email | product_id | |---------------+------+--------------+---------------+--------------| | 1313 | 1 | 2019-08-23 | joe@gmail.com | 2 | | 3849 | 2 | 2020-02-22 | lez@gmail.com | 2 | +---------------+------+--------------+---------------+--------------+ SELECT 2 Time: 0.016s company_database> UPDATE orders SET product_id = 1 WHERE id in (2);
UPDATE 1 Time: 0.001s company_database> SELECT FROM orders;
+---------------+------+--------------+---------------+--------------+ | ordernumber | id | dateplaced | email | product_id | |---------------+------+--------------+---------------+--------------| | 1313 | 1 | 2019-08-23 | joe@gmail.com | 2 | | 3849 | 2 | 2020-02-22 | lez@gmail.com | 1 | +---------------+------+--------------+---------------+--------------+ SELECT 2 Time: 0.016s company_database> SELECT * FROM orders WHERE product_id = 2;
+---------------+------+--------------+---------------+--------------+ | ordernumber | id | dateplaced | email | product_id | |---------------+------+--------------+---------------+--------------| | 1313 | 1 | 2019-08-23 | joe@gmail.com | 2 | +---------------+------+--------------+---------------+--------------+ SELECT 1 Time: 0.014s company_database> SELECT orders.ordernumber, product.productname, orders.product_id FROM orders JOIN products ON orders.product_id = products.id;
missing FROM-clause entry for table "product" LINE 1: SELECT orders.ordernumber, product.productname, orders.produ... ^

Time: 0.002s company_database> SELECT orders.ordernumber, products.productname, orders.product_id FROM orders JOIN products ON orders.product_id = products.id;
+---------------+-------------------+--------------+ | ordernumber | productname | product_id | |---------------+-------------------+--------------| | 1313 | Under Arm Harness | 2 | | 3849 | Leather Harness | 1 | +---------------+-------------------+--------------+ SELECT 2 Time: 0.016s company_database> Select From products;
+------+---------+-------------------------+------------------------+-----------------+ | id | price | productname | description | numberinstock | |------+---------+-------------------------+------------------------+-----------------| | 1 | 199.99 | Leather Harness | Aged real leather | 13 | | 2 | 99.99 | Under Arm Harness | Cellphone harness | 15 | | 3 | 50.0 | Dog Collar Wallet Combo | Matching owner pet set | 22 | +------+---------+-------------------------+------------------------+-----------------+ SELECT 3 Time: 0.017s company_database> INSERT INTO products (price, productname, description, numberinstock) VALUES (20.00, 'leash', 'dog leash', 33);;
INSERT 0 1 Time: 0.002s company_database> select from products;
+------+---------+-------------------------+------------------------+-----------------+ | id | price | productname | description | numberinstock | |------+---------+-------------------------+------------------------+-----------------| | 1 | 199.99 | Leather Harness | Aged real leather | 13 | | 2 | 99.99 | Under Arm Harness | Cellphone harness | 15 | | 3 | 50.0 | Dog Collar Wallet Combo | Matching owner pet set | 22 | | 4 | 20.0 | leash | dog leash | 33 | +------+---------+-------------------------+------------------------+-----------------+ SELECT 4 Time: 0.017s company_database> Select * From orders;
+---------------+------+--------------+---------------+--------------+ | ordernumber | id | dateplaced | email | product_id | |---------------+------+--------------+---------------+--------------| | 1313 | 1 | 2019-08-23 | joe@gmail.com | 2 | | 3849 | 2 | 2020-02-22 | lez@gmail.com | 1 | +---------------+------+--------------+---------------+--------------+ SELECT 2 Time: 0.017s company_database> INSERT INTO orders (ordernumber, dateplaced, email, product_id) VALUES ('9898', '01-22-2020', 'pita@aol.com', 33);
insert or update on table "orders" violates foreign key constraint "orders_product_id_fkey" DETAIL: Key (product_id)=(33) is not present in table "products".

Time: 0.002s company_database> INSERT INTO orders(ordernumber, dateplaced, email, product_id) VALUES ('9898', '01-22-2020', 'pita@aol.com', 33);
insert or update on table "orders" violates foreign key constraint "orders_product_id_fkey" DETAIL: Key (product_id)=(33) is not present in table "products".

Time: 0.002s company_database> INSERT INTO orders(ordernumber, dateplaced, email) VALUES ('9898', '01-22-2020', 'pita@aol.com');
INSERT 0 1 Time: 0.001s company_database> Select From orders;
+---------------+------+--------------+---------------+--------------+ | ordernumber | id | dateplaced | email | product_id | |---------------+------+--------------+---------------+--------------| | 1313 | 1 | 2019-08-23 | joe@gmail.com | 2 | | 3849 | 2 | 2020-02-22 | lez@gmail.com | 1 | | 9898 | 5 | 2020-01-22 | pita@aol.com | | +---------------+------+--------------+---------------+--------------+ SELECT 3 Time: 0.016s company_database> UPDATE orders SET product_id = 3 WHERE id in (5);
UPDATE 1 Time: 0.001s company_database> Select From orders;
+---------------+------+--------------+---------------+--------------+ | ordernumber | id | dateplaced | email | product_id | |---------------+------+--------------+---------------+--------------| | 1313 | 1 | 2019-08-23 | joe@gmail.com | 2 | | 3849 | 2 | 2020-02-22 | lez@gmail.com | 1 | | 9898 | 5 | 2020-01-22 | pita@aol.com | 3 | +---------------+------+--------------+---------------+--------------+ SELECT 3 Time: 0.014s company_database> SELECT FROM employees;
+------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ | full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id | |------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------| | Alexa Lewis | 165000 | Senior Dev | 4321 | no | 2 | 2837485960 | 1 | | Frank Balogh Jr. | 200000 | President | 1025 | no | 5 | 4958695849 | 1 | | Chesiree Balogh | 165000 | Senior Dev | 2389 | no | 6 | 4958695848 | 1 | | Michaellt Balogh | 180000 | Vice President | 2562 | no | 7 | 4958695847 | 1 | | Grayson Wolfe | 500 | Cook | 3884 | no | 10 | 4958695842 | 2 | | Jordan Bryan | 100000 | TA | 1234 | no | 1 | 4958695843 | 3 | | Heather Logan | 450 | Software Developer | 9393 | no | 9 | 4958695841 | 3 | +------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ SELECT 7 Time: 0.019s company_database> INSERT INTO employees (full_name, salary, job_position, phone_extension, part_time, department_id) VALUES ('Aidana Baez', 196000, 'badass bi ................. tch', 2434, 'no', 1);
INSERT 0 1 Time: 0.002s company_database> SELECT FROM employees;
+------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ | full_name | salary | job_position | phone_extension | part_time | id | parking_spot_number | department_id | |------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------| | Alexa Lewis | 165000 | Senior Dev | 4321 | no | 2 | 2837485960 | 1 | | Frank Balogh Jr. | 200000 | President | 1025 | no | 5 | 4958695849 | 1 | | Chesiree Balogh | 165000 | Senior Dev | 2389 | no | 6 | 4958695848 | 1 | | Michaellt Balogh | 180000 | Vice President | 2562 | no | 7 | 4958695847 | 1 | | Grayson Wolfe | 500 | Cook | 3884 | no | 10 | 4958695842 | 2 | | Jordan Bryan | 100000 | TA | 1234 | no | 1 | 4958695843 | 3 | | Heather Logan | 450 | Software Developer | 9393 | no | 9 | 4958695841 | 3 | | Aidana Baez | 196000 | badass bitch | 2434 | no | 13 | | 1 | +------------------+----------+--------------------+-------------------+-------------+------+-----------------------+-----------------+ SELECT 8 Time: 0.019s company_database> UPDATE employees SET department_id = 3 WHERE full_name = 'Aidana Baez';
UPDATE 1 Time: 0.001s company_database> SELECT employees.full_name, departments.departmentname FROM employees JOIN departments ON employees.department_id = departments.id;
+------------------+------------------+ | full_name | departmentname | |------------------+------------------| | Alexa Lewis | Human Resources | | Frank Balogh Jr. | Human Resources | | Chesiree Balogh | Human Resources | | Michaellt Balogh | Human Resources | | Grayson Wolfe | Delivery Crew | | Jordan Bryan | Call Center | | Heather Logan | Call Center | | Aidana Baez | Call Center | +------------------+------------------+ SELECT 8 Time: 0.016s company_database> SELECT * FROM orders;
+---------------+------+--------------+---------------+--------------+ | ordernumber | id | dateplaced | email | product_id | |---------------+------+--------------+---------------+--------------| | 1313 | 1 | 2019-08-23 | joe@gmail.com | 2 | | 3849 | 2 | 2020-02-22 | lez@gmail.com | 1 | | 9898 | 5 | 2020-01-22 | pita@aol.com | 3 | +---------------+------+--------------+---------------+--------------+ SELECT 3 Time: 0.016s company_database> DELETE FROM orders WHERE ordernumber = 13';

[F2] Smart Completion: ON [F3] Multiline: ON (Semi-colon [;] will end the line) [F4] Emacs-mode
[Restored Feb 25, 2020 at 11:10:44 PM] Last login: Tue Feb 25 23:10:39 on console

The default interactive shell is now zsh. To update your account to use zsh, please run chsh -s /bin/zsh. For more details, please visit https://support.apple.com/kb/HT208050. Chesirees-MacBook-Pro:cohort-17 chesireebalogh$ [Restored Feb 25, 2020 at 11:16:11 PM] Last login: Tue Feb 25 23:16:07 on console

The default interactive shell is now zsh. To update your account to use zsh, please run chsh -s /bin/zsh. For more details, please visit https://support.apple.com/kb/HT208050. Chesirees-MacBook-Pro:cohort-17 chesireebalogh$ pgcli company_database could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Chesirees-MacBook-Pro:cohort-17 chesireebalogh$ pgcli company_database could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Chesirees-MacBook-Pro:cohort-17 chesireebalogh$

[chesiree-balogh]

https://github.com/chesiree-balogh/cohort-17

[chesiree-balogh]

https://gist.github.com/chesiree-balogh/49501ae942e4ddb6b464e318d2364ac0