Skip to content

Diana Grams #1

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
deegrams221 wants to merge 2 commits into
base: master
Choose a base branch
from
Open

Diana Grams #1

Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
fa28a2f
initial project setup
deegrams221 Oct 8, 2019
b568bef
finished mvp
deegrams221 Oct 8, 2019
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
73 changes: 73 additions & 0 deletions README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -36,22 +36,42 @@ Answer the following data queries. Keep track of the SQL you write by pasting it
### find all customers that live in London. Returns 6 records.
> This can be done with SELECT and WHERE clauses

SELECT *
FROM customers
WHERE city = 'London'


### find all customers with postal code 1010. Returns 3 customers.
> This can be done with SELECT and WHERE clauses

SELECT *
FROM customers
WHERE postal_code = '1010'


### find the phone number for the supplier with the id 11. Should be (010) 9984510.
> This can be done with SELECT and WHERE clauses

SELECT supplier_id, phone
FROM suppliers
WHERE supplier_id = '11'


### list orders descending by the order date. The order with date 1998-05-06 should be at the top.
> This can be done with SELECT, WHERE, and ORDER BY clauses

SELECT *
FROM orders
ORDER BY order_date desc


### find all suppliers who have names longer than 20 characters. You can use `length(company_name)` to get the length of the name. Returns 11 records.
> This can be done with SELECT and WHERE clauses

SELECT *
FROM suppliers
WHERE length(company_name) > 20


### find all customers that include the word 'MARKET' in the contact title. Should return 19 records.
> This can be done with SELECT and a WHERE clause using the LIKE keyword
Expand All @@ -60,6 +80,10 @@ Answer the following data queries. Keep track of the SQL you write by pasting it

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

SELECT contact_title
FROM customers
WHERE upper(contact_title) like '%MARKET%'


### add a customer record for
* customer id is 'SHIRE'
Expand All @@ -71,36 +95,85 @@ Answer the following data queries. Keep track of the SQL you write by pasting it
* 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')


### update _Bilbo Baggins_ record so that the postal code changes to _"11122"_.
> This can be done with UPDATE and WHERE clauses

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


### list orders grouped by customer showing the number of orders per customer. _Rattlesnake Canyon Grocery_ should have 18 orders.
> 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

SELECT COUNT(o.order_id), c.contact_name , c.company_name
FROM orders o JOIN customers c
ON o.customer_id = c.customer_id
GROUP BY c.contact_name , c.company_name


> There is more information about the COUNT clause on [W3 Schools](https://www.w3schools.com/sql/sql_count_avg_sum.asp)


### list customers names and the number of orders per customer. Sort the list by number of orders in descending order. _Save-a-lot Markets should be at the top with 31 orders followed by _Ernst Handle_ with 30 orders. Last should be _Centro comercial Moctezuma_ with 1 order.
> This can be done by adding an ORDER BY clause to the previous answer

SELECT COUNT(o.order_id), c.contact_name , c.company_name
FROM orders o JOIN customers c
ON o.customer_id = c.customer_id
GROUP BY c.contact_name , c.company_name
ORDER BY COUNT(o.order_id) desc


### list orders grouped by customer's city showing number of orders per city. Returns 69 Records with _Aachen_ showing 6 orders and _Albuquerque_ showing 18 orders.
> This is very similar to the previous two queries, however, it focuses on the City rather than the CustomerName

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


## Data Normalization

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 Name | Pet Name | Pet Type | Pet Name 2 | Pet Type 2 | Pet Name 3 | Pet Type 3 | Fenced Yard | City Dweller |
|-------------|----------|----------|------------|------------|------------|------------|-------------|--------------|
| Jane | Ellie | Dog | Tiger | Cat | Toby | Turtle | No | Yes |
| Bob | Joe | Horse | | | | | No | No |
| Sam | Ginger | Dog | Miss Kitty | Cat | Bubble | Fish | Yes | No |
|-------------|----------|----------|------------|------------|------------|------------|-------------|--------------|


|-------------|-------------|--------------|-----------|
| Person Name | Fenced Yard | City Dweller | Person Id |
|-------------|-------------|--------------|-----------|
| Jane | No | Yes | 1 |
| Bob | No | No | 2 |
| Sam | Yes | No | 3 |
|-------------|-------------|--------------|-----------|

|-------------|-------------|--------------|-----------|
| Person Id | Pet Id | Pet Name | Pet Type |
|-------------|-------------|--------------|-----------|
| 1 | 1 | Ellie | Dog |
| 1 | 2 | Tiger | Cat |
| 1 | 3 | Toby | Turtle |
| 2 | 4 | Joe | Horse |
| 3 | 5 | Ginger | Dog |
| 3 | 6 | Miss Kitty | Cat |
| 3 | 7 | Bubble | Fish |
|-------------|-------------|--------------|-----------|


---
## Stretch Goals
Expand Down