Skip to content

Kyle clopton2 #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
KSClopton wants to merge 2 commits into
base: master
Choose a base branch
from
Open

Kyle clopton2 #1

Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
117e3cc
Done wit hall tasks and one stretch
KSClopton Aug 5, 2020
9031bdd
Updated Tables for better optimization
KSClopton Aug 5, 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
91 changes: 63 additions & 28 deletions README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -37,6 +37,9 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

```SQL
Select contact_name, city
FROM customers
WHERE City='London'

```

Expand All @@ -48,7 +51,9 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

```SQL

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

* [ ] ***find the phone number for the supplier with the id 11. Should be (010) 9984510***
Expand All @@ -59,7 +64,9 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</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***
Expand All @@ -70,7 +77,9 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

```SQL

SELECT *
FROM orders
ORDER BY order_date DESC
```

* [ ] ***find all suppliers who have names longer than 20 characters. Returns 11 records***
Expand All @@ -82,7 +91,9 @@ 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***
Expand All @@ -95,7 +106,9 @@ 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***
Expand All @@ -112,6 +125,8 @@ 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')

```

Expand All @@ -123,6 +138,9 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

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

```

Expand All @@ -135,6 +153,11 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

```SQL
SELECT customers.company_name, count(*) as QTY
FROM orders JOIN customers
ON orders.customer_id = customers.customer_id
GROUP BY customers.company_name


```

Expand All @@ -146,6 +169,11 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

```SQL
SELECT customers.contact_name, count(*) as QTY
FROM orders JOIN customers
ON orders.customer_id = customers.customer_id
GROUP BY customers.contact_name
ORDER BY qty DESC

```

Expand All @@ -157,7 +185,11 @@ Reimport the Northwind database into PostgreSQL using pgAdmin. This is the same
</details>

```SQL

SELECT customers.city, count(*) as QTY
FROM orders JOIN customers
ON orders.customer_id = customers.customer_id
GROUP BY customers.city
ORDER BY qty DESC
```

## Data Normalization
Expand All @@ -177,45 +209,45 @@ 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: City

| | | | | | | | | |
| Person ID | Name | | | | | | | |
|------------|------------|------------|------------|------------|------------|------------|------------|------------|
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| 1 | Jane | | | | | | | |
| 2 | Bob | | | | | | | |
| 3 | Sam | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |

Table Name:
Table Name: Pets

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

Table Name:
Table Name: Dwelling Info

| | | | | | | | | |
| Person ID | Fenced Yard|City Dweller| | | | | | |
|------------|------------|------------|------------|------------|------------|------------|------------|------------|
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| 1 | No | Yes | | | | | | |
| 2 | No | No | | | | | | |
| 3 | Yes | No | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |

Table Name:
Table Name: Pet Family

| | | | | | | | | |
| Person ID | Pet Type 1 | Pet Type 2 | Pet Type 3 | Pet Name 1 | Pet Name 2 | Pet Name 3 | | |
|------------|------------|------------|------------|------------|------------|------------|------------|------------|
| | | | | | | | | |
| | | | | | | | | |
Expand All @@ -232,7 +264,10 @@ Table Name:
* [ ] ***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 DISTINCT 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***
Expand Down