03 - 02 - Foreign Keys - The SQL

[RobMack8932]

As we continue to explore databases and ideas, this project will help you practice model databases and relationships.

We will be expanding on our CompanyDatabase from the INSERT INTO Student (knowledge) VALUES ('SQL') assignment. You will be adding new tables and be writing queries for these new tables.

Reuse the same repository from that assignment for this assignment. Add the new queries below the queries from that assignment such that the one repository will have both sets of queries. Use the same repository URL to turn in tonight's homework.

Objectives

• Work with foreign keys
• Craft queries that join tables

Explorer Mode

• [ ] In your CompanyDatabase, add a table named Departments with the following columns:

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

• [ ] Add a Foreign key DepartmentId to your Employees Table. If you have trouble, remove the existing employees by running truncate table "Employees".

• [ ] Add tables named Products and Orders.

  • Orders should have the columns
  • Id as a primary key
  • OrderNumber as a string
  • DatePlaced as a datetime
  • Email as a string
  • Products should have the columns
  • Price as a double
  • Name as a string
  • Description as a string
  • QuantityInStock as an integer

• [ ] In our company, one Order can have many Products and one Product can have many Orders. This will be a Many-to-Many relationship. Create the necessary table ProductOrders, foreign keys, and the OrderQuantityfield needed for this to happen.

• [ ] Create queries that can do the following:

  • [ ] Insert the following Departments

  Department Name	Building
  Development	Main
  Marketing	North

  • [ ] Insert the following Employees

  FullName	Salary	JobPosition	PhoneExtension	IsPartTime	Department Id
  Tim Smith	40000	Programmer	123	false	1
  Barbara Ramsey	80000	Manager	234	false	1
  Tom Jones	32000	Admin	456	true	2

• [ ] Insert the following Products

  Price	Name	Description	QuantityInStock
  12.45	Widget	The Original Widget	100
  99.99	Flowbee	Perfect for haircuts	3

• [ ] Insert a new order with order number X529, placed on Jan 1st, 2020 at 4:55PM, by someone with the email address "person@example.com"

• [ ] Add an order quantity of 3 for the product named Widget to the order X529

• [ ] Add an order quantity of 2 for the product named Flowbee to the order X529

• [ ] Given a department id, return all employees in the department.

• [ ] Given a department name, return all the phone extensions.

• [ ] Find all orders that contain the product id of 2.

• [ ] Remove the Flowbee product from order with order number X529.

Adventure Mode

Epic Mode

• [ ] Give these SQL Koans a try.

Additional Resources

• PostgreSQL Documentation

[RobMack8932]

----New table for Departments

CREATE TABLE "Departments" ( "Building" TEXT, "DepartmentName" TEXT, "Id" SERIAL PRIMARY KEY ); --Create foreign key NEEDS WORK CREATE TABLE "Employees" ( "DepartmentId" SERIAL PRIMARY KEY, "Description" TEXT ); --Adding tables Products and Orders IMPLEMENT CREATE TABLE "Orders" ( "Id" SERIAL PRIMARY KEY, "OrderNumber" INT, "DatePlaced" DATE, "EMAIL" STRING_AGG

); --DOUBLE????? CREATE TABLE "Products" ( "Price" INT.INT , "Name" STRING_AGG, "Description" STRING_AGG, "QuantityInStock" INT );

--many to many relationship????

CREATE TABLE "ProductOrders" ( "Id" SERIAL PRIMARY KEY, "OrderId" INTEGER REFERENCES "Orders" ("Id"), "ProductId" INTEGER REFERENCES "Products" ("Id") ); --Make Quantity in Stock CREATE TABLE "Products" ( "QuantityInStock" SERIAL PRIMARY KEY, "Description" INT );

--- INSERT INTO DEPARTMENTS INSERT INTO "Departments" ("Building", "DepartmentName") VALUES ('Main', 'Development'); INSERT INTO "Departments" ("Building", "DepartmentName") VALUES ('North', 'Marketing'); ----INSERT INTO EMPLOYEES INSERT INTO "Employees" ("FullName", "Salary", "JobDescription", "PhoneExtension", "IsPartTime", "DepartmentId") VALUES ('Tim Smith', 40000, 'Programmer', 123, 'false', 1); INSERT INTO "Employees" ("FullName", "Salary", "JobDescription", "PhoneExtension", "IsPartTime", "DepartmentId") VALUES ('Barbara Ramsey', 80000, 'Manager', 234, 'false', 1); INSERT INTO "Employees" ("FullName", "Salary", "JobDescription", "PhoneExtension", "IsPartTime", "DepartmentId") VALUES ('Tom Jones', 32000, 'Admin', 456, 'true', 2); --INSERT PRODUCTS INSERT INTO "Products" ("Price", "Name", "Description", "QuantityInStock") VALUES (12.45, 'Widget', 'The Original Widget', 100); INSERT INTO "Products" ("Price", "Name", "Description", "QuantityInStock") VALUES (99.99, 'Flowbee', 'Perfect for haircuts', 3); --Insert new order INSERT INTO "Orders" ("OrderNumber", "DatePlaced", "EMAIL") VALUES (X529, 'Jan 1st, 202 at 4:55pm', 'person@example.com '); --Add order quantity of 3 to widget UPDATE "QuantityInStock" SET "OrderNumber" = 3 WHERE "Name" = 'Widget'; --Add order quantity of 2 to Flowbee UPDATE "QuantityInStock" SET "OrderNumber" = 2 WHERE "Name" = 'Flowbee'; --Given a department id return all employees 2 SELECT "DepartmentId" FROM "Employees" WHERE "DepartmentId" = 2; --Given department name return all phone extensions ? SELECT "DepartmentName", "PhoneExtension" FROM "Employees"; --Find all orders that contain the product id of 2 SELECT "ProductId" FROM "Orders" WHERE "ProductId" = 2; --Remove Flowbee from x529 DELETE FROM "Flowbee" WHERE "OrderNumber" = X529;

[RobMack8932]

Your homework 03 - 02 - Foreign Keys - The SQL was marked: Meets Expectations

“Get up and dance!”