03 - 02 - Foreign Keys - The SQL

[piercebhunt]

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

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

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

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

• [x] 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

• [x] 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.

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

  • [x] Insert the following Departments

  Department Name	Building
  Development	Main
  Marketing	North

  • [x] 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

• [x] Insert the following Products

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

• [x] 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"

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

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

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

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

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

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

Adventure Mode

Epic Mode

• [ ] Give these SQL Koans a try.

Additional Resources

• PostgreSQL Documentation

[piercebhunt]

https://github.com/huntpie/sql-intro/blob/master/queries.sql

[piercebhunt]

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

“Well done!”