TylerBerrett · TylerBerrett · Nov 5, 2019 · Nov 6, 2019 · Nov 6, 2019 · Nov 6, 2019
diff --git a/README.md b/README.md
@@ -36,30 +36,41 @@ 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 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.
+### find all customers that include the word 'MARKET' in the contact title. Should return 18 records.
 > This can be done with SELECT and a WHERE clause using the LIKE keyword
 
 > Don't forget the wildcard '%' symbols at the beginning and end of your substring to denote it can appear anywhere in the string in question
 
 > Remember to convert your contact title to all upper case for case insenstive comparing so upper(contact_title)
 
+## SELECT * FROM customers  WHERE UPPER(contact_title) LIKE 'MARKET%'
+
 
 ### add a customer record for   
 * customer id is 'SHIRE'
@@ -70,25 +81,36 @@ Answer the following data queries. Keep track of the SQL you write by pasting it
 * the postal code is '111'
 * 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
 
 > There is more information about the COUNT clause on [W3 Schools](https://www.w3schools.com/sql/sql_count_avg_sum.asp)
 
+## SELECT c.company_name, COUNT(\*) as thecount FROM customers c JOIN orders o on c.customer_id = o.customer_id GROUP BY c.customer_id
+
 
 ### 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 c.company_name, COUNT(\*) as thecount FROM customers c JOIN orders o on c.customer_id = o.customer_id GROUP BY c.customer_id ORDER BY thecount 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 c.city, COUNT(\*) as thecount  FROM customers c JOIN orders o on c.customer_id = o.customer_id GROUP BY c.city ORDER BY city
+
 
 ## Data Normalization
 
@@ -102,14 +124,46 @@ 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           |
 
+| Person Id | Person Name |
+|-----------|-------------|
+| 1         | Jane        |
+| 2         | Bob         |
+| 3         | Sam         |
+
+| Pet Id | Person Id | Pet Name   | Type Id |
+|--------|-----------|------------|---------|
+| 1      | 1         | Ellie      | 1       |
+| 2      | 1         | Tiger      | 2       |
+| 3      | 1         | Toby       | 3       |
+| 4      | 2         | Joe        | 4       |
+| 5      | 3         | Ginger     | 1       |
+| 6      | 3         | Miss Kitty | 2       |
+| 7      | 3         | Bubble     | 5       |
+
+| Type Id | Pet Type |
+|---------|----------|
+| 1       | Dog      |
+| 2       | Cat      |
+| 3       | Turtle   |
+| 4       | Horse    |
+| 5       | Fish     |
+
+| Person Id | Fenced Yard | City Dweller |
+|-----------|-------------|--------------|
+| 1         | No          | Yes          |
+| 2         | No          | No           |
+| 3         | Yes         | No           |
+
 ---
 ## Stretch Goals
 
 ### delete all customers that have no orders. Should delete 2 (or 3 if you haven't deleted the record added) records.
 
+## DELETE FROM customers WHERE company_name NOT IN (SELECT c.company_name FROM customers c JOIN orders o on c.customer_id = o.customer_id GROUP BY c.customer_id)
+
 ## 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. 
+### 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_:
@@ -122,3 +176,22 @@ Take the following data and normalize it into a 3NF database.  You can use the w
   - the `id` should be the primary key for the table.
   - account `name` should be unique.
   - account `budget` is required.
+
+  CREATE DATABASE budget
+      WITH
+      OWNER = postgres
+      ENCODING = 'UTF8'
+      LC_COLLATE = 'C'
+      LC_CTYPE = 'C'
+      TABLESPACE = pg_default
+      CONNECTION LIMIT = -1;
+
+CREATE TABLE public.accounts
+(
+    id numeric NOT NULL,
+    name text COLLATE pg_catalog."default",
+    budget numeric NOT NULL,
+    CONSTRAINT accounts_pkey PRIMARY KEY (id),
+    CONSTRAINT accounts_name_key UNIQUE (name)
+
+)