project: Shop Database using sql

Story points 3
Tags sql skill/databases
Hard Prerequisites
IMPORTANT: Please review these prerequisites, they include important information that will help you with this content.
  • TOPICS: Solo Learn - SQL Intermediate - 3 Working with Data
  • DOCKER: Intro to Docker and Docker-compose
  • TOPICS: Clean Code for SQL

    • Installation

    • We are using Postgres
    • Instead of installing Postgres on your computer, you can launch it with a docker composition
    • MySQL is nice and lots of people use it in industry, but it doesn’t implement standard SQL, it sort of does its own thing a bit. Postgres is a much more standard DB, and the industry loooooves it.

    Structure

    • Your repository should have a .yml file
    • Your repository should have .sql files. The different SQL commands should be saved in different descriptive script files i.e.
      • when the reviewer is looking for the commands you used for creating the database they should be able to navigate to a file named create-database.sql,
      • navigate to a file named create-tables.sql for the commands used to create the database tables, and
      • navigate to the files that contain the commands used for inserting table records and querying the database
    • Your repository should have a new .md file for documenting the database

    Instructions

    IMPORTANT

    You should know about clean sql code by now. If you need a refresher please take a look here: TOPIC: Clean Code for SQL

    The names of the tables and columns described in this project are funky (not in a good way).

    While doing this project, please adapt all names so that they hit the clean code standards.

    Part 1: Creating a database

    Save all of your instructions in script files - you will submit these files on Github.

    1. Create a database called “shop”.

    2. Create the following tables in the shop database:

      • Customers
      • Employees
      • Orders
      • Payments
      • Products

    3. Create a primary key for each table with auto-increment (make sure you correctly specify the data types, e.g. the ID field should be int).

    4. Create foreign keys so that every ID in the order and payment tables references an existing ID in the tables referenced (e.g., ProductID, EmployeeID, etc).

    5. INSERT the records in the tables below into the table you created in step 2.

    6. Document what information is stored in your database. Be sure to say what information is kept in what table, and which keys link the records between tables. This file needs to be in a .md format.

    Customers Table

    ID (int) FirstName (varchar50) LastName (varchar50) Gender (varchar) Address (varchar200) Phone (varchar 20) Email (varchar100) City (varchar20) Country (varchar50)
    1 John Hibert Male 284 chaucer st 084789657 john@gmail.com Johannesburg South Africa
    2 Thando Sithole Female 240 Sect 1 0794445584 thando@gmail.com Cape Town South Africa
    3 Leon Glen Male 81 Everton Rd,Gillits 0820832830 Leon@gmail.com Durban South Africa
    4 Charl Muller Male 290A Dorset Ecke +44856872553 Charl.muller@yahoo.com Berlin Germany
    5 Julia Stein Female 2 Wernerring +448672445058 Js234@yahoo.com Frankfurt Germany

    Employees Table

    ID (int) FirstName (varchar50) LastName (varchar50) Email (varchar100) JobTitle (varchar20)
    1 Kani Matthew mat@gmail.com Manager
    2 Lesly Cronje LesC@gmail.com Clerk
    3 Gideon Maduku m@gmail.com Accountant

    Orders Table

    ID (int) ProductID (int) PaymentID (int) FulfilledByEmployeeID (int) DateRequired (datetime) DateShipped (datetime) Status (varchar20)
    1 1 1 2 05-09-2018 Not shipped
    2 1 2 2 04-09-2018 03-09-2018 Shipped
    3 3 3 3 06-09-2018 Not shipped

    Note: When creating tables you will note that each table has an ID column, when joining data between tables the foreign key must include the name of the table the data came from, i.e. ProductID in the above table.

    Payments Table

    ID (int) CustomerID (int) PaymentDate (datetime) Amount (decimal)
    1 1 01-09-2018 R150.75
    2 5 03-09-2018 R150.75
    3 4 03-09-2018 R700.60

    Note: When creating tables you will note that each table has an ID column, when joining data between tables the foreign key must include the name of the table the data came from, i.e. CustomerID in the above table.

    #3## Products Table

    ID (int) ProductName (varchar100) Description (varchar300) BuyPrice (decimal)
    1 Harley Davidson Chopper This replica features a working kickstand, front suspension, gear-shift lever R150.75
    2 Classic Car Turnable front wheels, steering function R550.75
    3 Sportscar Turnable front wheels, steering function R700.60

    Part 2: Querying a database

    Save all of your instructions in script files - you will submit these files on Github.

    NB! Be sure to label you answers by putting the question above the code that answers it, submitting a wall of code with no explanations makes it difficult to review.

    1. SELECT ALL records from table Customers.

    2. SELECT records only from the name column in the Customers table.

    3. Show the full name of the Customer whose CustomerID is 1.

    4. UPDATE the record for CustomerID = 1 on the Customer table so that the name is “Lerato Mabitso”.

    5. DELETE the record from the Customers table for customer 2 (CustomerID = 2).

    6. Select all unique statuses from the Orders table and get a count of the number of orders for each unique status.

    7. Return the MAXIMUM payment made on the PAYMENTS table.

    8. Select all customers from the “Customers” table, sorted by the “Country” column.

    9. Select all products with a price BETWEEN R100 and R600.

    10. Select all fields from “Customers” where the country is “Germany” AND the city is “Berlin”.

    11. Select all fields from “Customers” where the city is “Cape Town” OR “Durban”.

    12. Select all records from Products where the Price is GREATER than R500.

    13. Return the sum of the Amounts on the Payments table.

    14. Count the number of shipped orders in the Orders table.

    15. Return the average price of all Products, in Rands and Dollars (assume the exchange rate is R12 to the Dollar).

    16. Using INNER JOIN create a query that selects all Payments with Customer information.

    17. Select all products that have turnable front wheels.

    Files to submit & instructions for reviewer

    • Please make sure that the learner used Postgres and not MySQL.

    • The ID columns of the tables can either be TableName + ID or just ID, either is acceptable due to recent updates.

    • The following files should be present:

      • YAML/yml file with container setup.

      • src directory with 4 SQL script files(a script to a database, create tables, populate tables and one to query the database).

      • A new .md file needs to be present in the repo where the learner has documented what information is in which table and how the tables and which keys link the records between tables. They should not edit the README.md file because they didn’t create it and it serves it’s own purpose.

        • topics/clean-code/sql

    • Did the learner adapt the naming conventions so that they matched those specified in TOPIC: Clean Code for SQL

    RAW CONTENT URL