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
CatRadin wants to merge 1 commit into
base: master
Choose a base branch
from
Open

MVP #1

Changes from all commits
Commits
Show all changes
1 commit
Select commit
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
99 changes: 76 additions & 23 deletions README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -14,7 +14,7 @@ Working with SQL

Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same data we used during the guided project. You can find a copy of the SQL script under the `assets` folder in this repository.

* [ ] ***pgAdmin data refresh***
* [✔️ ] ***pgAdmin data refresh***

* Select the northwind database created during the guided project.

Expand All @@ -29,7 +29,11 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

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

* [ ] ***find all customers that live in London. Returns 6 records***
* [✔️ ] ***find all customers that live in London. Returns 6 records***

SELECT contact_name, city
FROM customers
WHERE city = 'London'

<details><summary>hint</summary>

Expand All @@ -40,7 +44,11 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

```

* [ ] ***find all customers with postal code 1010. Returns 3 customers***
* [ ✔️] ***find all customers with postal code 1010. Returns 3 customers***

SELECT contact_name, postal_code
FROM customers
WHERE postal_code = '1010'

<details><summary>hint</summary>

Expand All @@ -51,7 +59,12 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

```

* [ ] ***find the phone number for the supplier with the id 11. Should be (010) 9984510***
* [✔️ ] ***find the phone number for the supplier with the id 11. Should be (010) 9984510***

SELECT phone
FROM suppliers
WHERE supplier_id = '11'


<details><summary>hint</summary>

Expand All @@ -62,7 +75,12 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

```

* [ ] ***list orders descending by the order date. The order with date 1998-05-06 should be at the top***
* [ ✔️] ***list orders descending by the order date. The order with date 1998-05-06 should be at the top***

SELECT *
FROM orders
ORDER BY order_date DESC


<details><summary>hint</summary>

Expand All @@ -73,7 +91,11 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

```

* [ ] ***find all suppliers who have names longer than 20 characters. Returns 11 records***
* [ ✔️] ***find all suppliers who have names longer than 20 characters. Returns 11 records***

SELECT company_name
FROM suppliers
WHERE length(company_name) > 20

<details><summary>hint</summary>

Expand All @@ -85,7 +107,11 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

```

* [ ] ***find all customers that include the word 'MARKET' in the contact title. Should return 19 records***
* [ ✔️] ***find all customers that include the word 'MARKET' in the contact title. Should return 19 records***

SELECT *
FROM customers
WHERE UPPER(contact_title) LIKE ('%MARKET%')

<details><summary>hint</summary>

Expand All @@ -98,14 +124,18 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

```

* [ ] ***add a customer record for***
* [ ✔️] ***add a customer record for***
* customer id is 'SHIRE'
* company name is 'The Shire'
* contact name is 'Bilbo Baggins'
* the address is '1 Hobbit-Hole'
* the city is 'Bag End'
* the postal code is '111'
* the country is 'Middle Earth'

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')

<details><summary>hint</summary>

* This can be done with the INSERT INTO clause
Expand All @@ -115,7 +145,11 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

```

* [ ] ***update _Bilbo Baggins_ record so that the postal code changes to _"11122"_***
* [✔️ ] ***update _Bilbo Baggins_ record so that the postal code changes to _"11122"_***

UPDATE customers
SET postal_code = 11122
WHERE customer_id = 'SHIRE'

<details><summary>hint</summary>

Expand All @@ -126,7 +160,13 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

```

* [ ] ***list orders grouped and ordered by customer company name showing the number of orders per customer company name. _Rattlesnake Canyon Grocery_ should have 18 orders***
* [ ✔️] ***list orders grouped and ordered by customer company name showing the number of orders per customer company name. _Rattlesnake Canyon Grocery_ should have 18 orders***

SELECT c.company_name,
count(o.customer_id) as number_of_orders
FROM orders o
JOIN customers c on o.customer_id = c.customer_id
GROUP BY c.company_name

<details><summary>hint</summary>

Expand All @@ -138,7 +178,13 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

```

* [ ] ***list customers by contact name and the number of orders per contact name. Sort the list by the number of orders in descending order. _Jose Pavarotti_ should be at the top with 31 orders followed by _Roland Mendal_ with 30 orders. Last should be _Francisco Chang_ with 1 order***
* [✔️ ] ***list customers by contact name and the number of orders per contact name. Sort the list by the number of orders in descending order. _Jose Pavarotti_ should be at the top with 31 orders followed by _Roland Mendal_ with 30 orders. Last should be _Francisco Chang_ with 1 order***

SELECT count(o.customer_id), c.company_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.company_name
ORDER BY count(o.customer_id) DESC

<details><summary>hint</summary>

Expand All @@ -149,7 +195,14 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same

```

* [ ] ***list orders grouped by customer's city showing the number of orders per city. Returns 69 Records with _Aachen_ showing 6 orders and _Albuquerque_ showing 18 orders***
* [✔️ ] ***list orders grouped by customer's city showing the number of orders per city. Returns 69 Records with _Aachen_ showing 6 orders and _Albuquerque_ showing 18 orders***

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


<details><summary>hint</summary>

Expand Down Expand Up @@ -177,33 +230,33 @@ Below are some empty tables to be used to normalize the database
* Not all of the cells will contain data in the final solution
* Feel free to edit these tables as necessary

Table Name:
Table Name: Human Table

| | | | | | | | | |
| human_id | name | fenced_yard|city_dweller| | | | | |
|------------|------------|------------|------------|------------|------------|------------|------------|------------|
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| 1 | Jane | no | Yes | | | | | |
| 2 | Bob | no | No | | | | | |
| 3 | Sam | yes | No | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |

Table Name:
Table Name: Pet Table

| | | | | | | | | |
| pet_id | human_id | pet_name |pet_type_id | | | | | |
|------------|------------|------------|------------|------------|------------|------------|------------|------------|
| | | | | | | | | |
| | | | | | | | | |
| | | Ellie | | | | | | |
| | | Tiger | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |

Table Name:
Table Name: Pet Type Table

| | | | | | | | | |
|pet_type_id | pet_type | | | | | | | |
|------------|------------|------------|------------|------------|------------|------------|------------|------------|
| | | | | | | | | |
| | | | | | | | | |
Expand Down