This page is a work in progress.You can help improve it. →

Foreign Keys - The SQL

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 sql-intro 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.
    • Products should have the columns
      • Id as a primary key
      • Price as a data type that can store a number with decimal places (e.g. 45.12)
      • Name as textual data
      • Description as textual data
      • QuantityInStock as a data type that can store a number WITHOUT decimal places (e.g. 42)
    • Orders should have the columns
      • Id as a primary key
      • OrderNumber as textual data
      • DatePlaced as a data type that can store a date (with Year, Month, and Day) -- or one with hours, minutes, and seconds. Student's choice.
      • Email as textual data
  • 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 OrderQuantity field needed for this to happen.
  • Create queries that can do the following:

    • Insert the following Departments
    Department NameBuilding
    DevelopmentMain
    MarketingNorth
    • Insert the following Employees
    FullNameSalaryJobPositionPhoneExtensionIsPartTimeDepartment Id
    Tim Smith40000Programmer123false1
    Barbara Ramsey80000Manager234false1
    Tom Jones32000Admin456true2
  • Insert the following Products

    PriceNameDescriptionQuantityInStock
    12.45WidgetThe Original Widget100
    99.99FlowbeePerfect for haircuts3
  • 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 building, return all employees that work in that building. Show this query for buildings named North Side, East Side, and finally a building that you actually have in your data -- even if your data doesn't have any departments in those buildings. NOTE this means you can't manually look up Ids and use them, you have to let SQL do that work. (Hint: JOIN)
  • Find all orders that contain the product id of 2.
  • Find the quantity of the Flowbee product from order with order number X529. You may not write the value of an Id in your query, let SQL do the work (HINT: JOIN)

Adventure Mode

None

Epic Mode

Additional Resources

© 2017 - 2021; Built with ♥ in St. Petersburg, Florida.