A student that completes this project shows that they can:

• Query data from a single table
• Query data from multiple tables
• Create a new datadaase using PostgreSQL

Working with SQL

ReImport the Northwind database into PostgreSQL using pgAdmin. This is the same data we used during the guided project.

clone https://github.com/pthom/northwind_psql.git

• Right Click Databases

  • Create
    • type in northwind2

• Tools -> Query Tool

  • Open file northwind.sql (from cloned repo)
  • Execute

• Look under

  • northwind2 -> Schemas -> public -> tables

• Clear query windows

Answer the following data queries. Keep track of the SQL you write by pasting it into this document under its appropriate header below. You will be submitting that through the regular fork, change, pull process.

This can be done with SELECT and WHERE clauses

SELECT *
FROM customers
WHERE city = 'London'

This can be done with SELECT and WHERE clauses

SELECT * 
FROM Customers 
WHERE postal_code = '1010'

This can be done with SELECT and WHERE clauses SELECT Phone FROM suppliers WHERE supplier_id = 11

This can be done with SELECT, WHERE, and ORDER BY clauses SELECT * FROM orders ORDER BY order_date DESC

This can be done with SELECT and WHERE clauses SELECT * FROM suppliers WHERE length(company_name) > 20

This can be done with SELECT and a WHERE clause using the LIKE keyword SELECT * FROM customers WHERE contact_title LIKE 'Market%'

Don't forget the wildcard '%' symbols at the beginning and end of your substring to denote it can appear anywhere in the string in question

Remember to convert your contact title to all upper case for case insenstive comparing so upper(contact_title)

• customer id is 'SHIRE'
• company name is 'The Shire'
• contact name is 'Bilbo Baggins'
• the address is '1 Hobbit-Hole'
• ths city is 'Bag End'
• the postal code is '111'
• the country is 'Middle Earth'

This can be done with the INSERT INTO clause

INSERT INTO customers (customer_id, company_name, contact_name, address, city, postal_code, country)
VALUES ('SHIRE', 'The Shire', 'Bilbo Baggins', '1 Hobbit-Hole', 'Bag End', '111', 'Middle Earth')

This can be done with UPDATE and WHERE clauses

UPDATE customers 
SET postal_code = '11122' 
WHERE contact_name = 'Bilbo Baggins'

This can be done with SELECT, COUNT, JOIN and GROUP BY clauses. Your count should focus on a field in the Orders table, not the Customer table

There is more information about the COUNT clause on W3 Schools

SELECT c.company_name, COUNT(o.customer_id) AS num_orders
FROM orders o INNER JOIN customers c
ON o.customer_id = c.customer_id
GROUP BY c.company_name
ORDER BY c.company_name

This can be done by adding an ORDER BY clause to the previous answer

SELECT c.company_name, COUNT(o.customer_id) AS num_orders
FROM orders o JOIN customers c
ON o.customer_id = c.customer_id
GROUP BY c.company_name
ORDER BY order_count DESC

This is very similar to the previous two queries, however, it focuses on the City rather than the CustomerName

SELECT c.city, COUNT(o.customer_id) AS num_orders
FROM orders o JOIN customers c
ON o.customer_id = c.customer_id
GROUP BY c.city
ORDER BY city

Note: This step does not use PostgreSQL!

Take the following data and normalize it into a 3NF database. You can use the website https://www.tablesgenerator.com/markdown_tables# to help generate Markdown Tables.

person

owner_id owner_name fenced_yard city_dweller 1 Jane No Yes 2 Bob No No 3 Sam Yes No

pet_types

pet_id pet_type 1 Dog 2 Horse 3 Cat 4 Turtle 5 Fish

pets owner_id pet_name pet_type 1 Ellie 1 2 Joe 2 3 Ginger 1 1 Tiger 3 3 Miss Kitty 3 1 Toby 4 3 Bubble 5

• use pgAdmin to create a database, naming it budget.

• add an accounts table with the following schema:

  • id, numeric value with no decimal places that should autoincrement.
  • name, string, add whatever is necessary to make searching by name faster.
  • budget numeric value.

• constraints

  • the id should be the primary key for the table.
  • account name should be unique.
  • account budget is required.