Skip to content

Completed MVP + stretch #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
kai-blt wants to merge 15 commits into
base: master
Choose a base branch
from
Open

Completed MVP + stretch #1

Changes from all commits
Commits
Show all changes
15 commits
Select commit Hold shift + click to select a range
8dbcec0
added first query
kai-blt Nov 30, 2020
23f99ba
added second query
kai-blt Nov 30, 2020
8ceccc1
added third query
kai-blt Nov 30, 2020
c59dadc
added fourth query
kai-blt Nov 30, 2020
282e9dc
added fifth query and made FROM keyword uppercase
kai-blt Nov 30, 2020
a1700ab
added sixth query
kai-blt Nov 30, 2020
9cd07d2
added 7th query
kai-blt Nov 30, 2020
a5f0cca
added 8th query
kai-blt Nov 30, 2020
e11d8b6
added 9th query
kai-blt Dec 1, 2020
0daacc5
added 10th query
kai-blt Dec 1, 2020
5968ffb
added 11th query
kai-blt Dec 1, 2020
07c0776
normalized data set
kai-blt Dec 1, 2020
d2f5f3d
added backup.sql plaintext
kai-blt Dec 1, 2020
8c5dd61
Update README.md
kai-blt Dec 2, 2020
d611622
Update README.md
kai-blt Dec 2, 2020
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
227 changes: 153 additions & 74 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.

* [ ] ***pgAdmin data refresh***
* [x] ***pgAdmin data refresh***

* Select the northwind database created during the guided project.

Expand All @@ -29,51 +29,55 @@ 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***
* [x] ***find all customers that live in London. Returns 6 records***

<details><summary>hint</summary>

* This can be done with SELECT and WHERE clauses
</details>

```SQL

SELECT * FROM customers
WHERE city = 'London';
```

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

<details><summary>hint</summary>

* This can be done with SELECT and WHERE clauses
</details>

```SQL

SELECT * FROM customers
WHERE postal_code = '1010';
```

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

<details><summary>hint</summary>

* This can be done with SELECT and WHERE clauses
</details>

```SQL

SELECT 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***
* [x] ***list orders descending by the order date. The order with date 1998-05-06 should be at the top***

<details><summary>hint</summary>

* This can be done with SELECT, WHERE, and ORDER BY clauses
</details>

```SQL

SELECT * FROM orders
ORDER BY order_date DESC;
```

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

<details><summary>hint</summary>

Expand All @@ -82,10 +86,11 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

```SQL

SELECT * FROM suppliers
WHERE length(company_name) > '20';
```

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

<details><summary>hint</summary>

Expand All @@ -95,10 +100,11 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

```SQL

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

* [ ] ***add a customer record for***
* [x] ***add a customer record for***
* customer id is 'SHIRE'
* company name is 'The Shire'
* contact name is 'Bilbo Baggins'
Expand All @@ -112,21 +118,24 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

```SQL

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"_***
* [x] ***update _Bilbo Baggins_ record so that the postal code changes to _"11122"_***

<details><summary>hint</summary>

* This can be done with UPDATE and WHERE clauses
</details>

```SQL

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

* [ ] ***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***
* [x] ***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***

<details><summary>hint</summary>

Expand All @@ -135,36 +144,48 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

```SQL

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

* [ ] ***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***
* [x] ***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***

<details><summary>hint</summary>

* This can be done by adding an ORDER BY clause to the previous answer and changing the group by field
</details>

```SQL

SELECT c.contact_name, count(*) total_orders
FROM orders o JOIN customers c
ON o.customer_id = c.customer_id
GROUP BY c.contact_name
ORDER BY total_orders DESC
```

* [ ] ***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***
* [x] ***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***

<details><summary>hint</summary>

* This is very similar to the previous two queries, however, it focuses on the City rather than the Customer Names
</details>

```SQL

SELECT c.city, count(*) total_orders
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***
* [x] ***Take the following data and normalize it into a 3NF database***

| Person Name | Pet Name | Pet Type | Pet Name 2 | Pet Type 2 | Pet Name 3 | Pet Type 3 | Fenced Yard | City Dweller |
|-------------|----------|----------|------------|------------|------------|------------|-------------|--------------|
Expand All @@ -177,65 +198,51 @@ 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:

| | | | | | | | | |
|------------|------------|------------|------------|------------|------------|------------|------------|------------|
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |

Table Name:

| | | | | | | | | |
|------------|------------|------------|------------|------------|------------|------------|------------|------------|
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |

Table Name:

| | | | | | | | | |
|------------|------------|------------|------------|------------|------------|------------|------------|------------|
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
Table Name: people

| person_id | person_name | fenced_yard|city_dweller|
|------------|-------------|------------|------------|
| 1 | Jane | No | Yes |
| 2 | Bob | No | No |
| 3 | Sam | Yes | No |

Table Name: pets

| pet_id | pet_name | pet_type_id| person_id |
|------------|------------|------------|------------|
| 1 | Ellie | 1 | 1 |
| 2 | Joe | 2 | 2 |
| 3 | Ginger | 1 | 3 |
| 4 | Tiger | 3 | 1 |
| 5 | Miss Kitty | 3 | 3 |
| 6 | Toby | 4 | 1 |
| 7 | Bubble | 5 | 3 |

Table Name: pet_type

| pet_type_id| pet_type |
|------------|------------|
| 1 | Dog |
| 2 | Horse |
| 3 | Cat |
| 4 | Turtle |
| 5 | Fish |



---

### Stretch Goals

* [ ] ***delete all customers that have no orders. Should delete 2 (or 3 if you haven't deleted the record added) records***
* [x] ***delete all customers that have no orders. Should delete 2 (or 3 if you haven't deleted the record added) records***

```SQL

DELETE
FROM customers
WHERE customer_id NOT IN (SELECT customer_id from orders)
```

* [ ] ***Create Database and Table: After creating the database, tables, columns, and constraint, generate the script necessary to recreate the database. This script is what you will submit for review***
* [x] ***Create Database and Table: After creating the database, tables, columns, and constraint, generate the script necessary to recreate the database. This script is what you will submit for review***

* use pgAdmin to create a database, naming it `budget`.
* add an `accounts` table with the following _schema_:
Expand All @@ -250,6 +257,78 @@ Table Name:
* account `budget` is required.

```SQL
--
-- PostgreSQL database dump
--

-- Dumped from database version 13.1
-- Dumped by pg_dump version 13.1

-- Started on 2020-11-30 17:46:51

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 200 (class 1259 OID 16618)
-- Name: accounts; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.accounts (
id integer NOT NULL,
name character varying[],
budget integer NOT NULL
);


ALTER TABLE public.accounts OWNER TO postgres;

--
-- TOC entry 2983 (class 0 OID 16618)
-- Dependencies: 200
-- Data for Name: accounts; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.accounts (id, name, budget) FROM stdin;
\.


--
-- TOC entry 2850 (class 2606 OID 16625)
-- Name: accounts accounts_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.accounts
ADD CONSTRAINT accounts_pkey PRIMARY KEY (id);


--
-- TOC entry 2852 (class 2606 OID 16627)
-- Name: accounts name_unique; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.accounts
ADD CONSTRAINT name_unique UNIQUE (name);


-- Completed on 2020-11-30 17:46:51

--
-- PostgreSQL database dump complete
--


```

Expand Down