Skip to content

MVP #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
LindseyCason wants to merge 5 commits into
base: master
Choose a base branch
from
Open

MVP #1

Changes from all commits
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
627762a
MVP
LindseyCason Oct 8, 2019
b470f40
Update README.md
LindseyCason Oct 9, 2019
ff97e44
Update README.md
LindseyCason Oct 9, 2019
e080b95
Update README.md
LindseyCason Oct 9, 2019
37cec39
Update README.md
LindseyCason Oct 9, 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
104 changes: 104 additions & 0 deletions README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -33,6 +33,58 @@ clone https://github.com/pthom/northwind_psql.git
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.


<!-- /////// -->

SELECT contact_name
FROM customers
WHERE city = 'London'

SELECT contact_name
FROM customers
WHERE postal_code = '1010'

SELECT phone
FROM suppliers
WHERE supplier_id = '11'

SELECT order_date
FROM orders
ORDER BY (order_date)DESC

SELECT company_name
FROM suppliers
WHERE length(company_name) > 20

SELECT contact_title
FROM suppliers
WHERE UPPER(contact_title) LIKE('%MARKET%')

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 customers
SET postal_code = '11122'
WHERE contact_name = 'Bilbo Baggins'

SELECT COUNT(customer_id), ship_name
FROM orders
WHERE ship_name = ('Rattlesnake Canyon Grocery')
GROUP BY ship_name

SELECT COUNT(customer_id), ship_name
FROM orders
GROUP BY ship_name
ORDER BY COUNT(customer_id)DESC

SELECT COUNT(customer_id), ship_city
FROM orders
GROUP BY ship_city
ORDER BY ship_city


<!-- /////// -->


### find all customers that live in London. Returns 6 records.
> This can be done with SELECT and WHERE clauses

Expand Down Expand Up @@ -102,7 +154,59 @@ Take the following data and normalize it into a 3NF database. You can use the w
| Bob | Joe | Horse | | | | | No | No |
| Sam | Ginger | Dog | Miss Kitty | Cat | Bubble | Fish | Yes | No |


BASED ON DATA ABOVE
---

PERSON TABLE
---

| PERSON ID | PERSON NAME | PET TYPE | PET TYPE 2 | PET TYPE 3 | FENCED | CITY DWELLER |
|-----------|-------------|----------|------------|------------|-----------|--------------|
| 1 | JANE | 1 | 3 | 4 | 2 | 1 |
| 2 | BOB | 2 | | | 2 | 2 |
| 3 | SAM | 1 | 3 | 5 | 1 | 2 |


PET TABLE
---

| PET ID | PERSON ID | PET TYPE | PET NAME |
|--------|-----------|----------|------------|
| 1 | 1 | 1 | ELLIE |
| 2 | 2 | 2 | JOE |
| 3 | 3 | 1 | GINGER |
| 4 | 1 | 3 | TIGER |
| 5 | 1 | 4 | TOBY |
| 6 | 3 | 3 | MISS KITTY |
| 7 | 3 | 5 | BUBBLE |

---

YARD TABLE
---

| PERSON ID | FENCED | CITY DWELLER |
|-----------|-----------|--------------|
| 1 | 2 | 1 |
| 2 | 2 | 2 |
| 3 | 1 | 2 |

---

PET TYPE TABLE
---

| PET TYPE | SPECIES |
|----------|---------|
| 4 | TURTLE |
| 5 | FISH |
| 1 | DOG |
| 2 | HORSE |
| 3 | CAT |



## Stretch Goals

### delete all customers that have no orders. Should delete 17 (or 18 if you haven't deleted the record added) records.
Expand Down