Java SQL

A student that completes this project shows that they can:

Query data from a single table
Query data from multiple tables
Create a new database using PostgreSQL

Introduction

Working with SQL

Instructions

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
Select the northwind database created during the guided project.
Tools -> Query Tool
- Open file northwind.sql (you used this script during the guided project)
- Execute
Look under
- northwind -> Schemas -> public -> tables
Clear query windows

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
hint
- 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
hint
- 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
hint
- 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
hint
- 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. Returns 11 records
hint
- This can be done with SELECT and WHERE clauses
- You can use length(company_name) to get the length of the name

SELECT * 
FROM suppliers
WHERE LENGTH(company_name) > 20

find all customers that include the word 'MARKET' in the contact title. Should return 19 records
hint
- 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 insensitive comparing so upper(contact_title)

SELECT * 
FROM customers
WHERE CONTACT_TITLE LIKE '%Market%'

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'
hint
- 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"
hint
- This can be done with UPDATE and WHERE clauses

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
hint
- 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

SELECT COUNT(o.customer_id), c.company_name
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
GROUP 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
hint
- This can be done by adding an ORDER BY clause to the previous answer and changing the group by field

SELECT COUNT(o.customer_id), c.contact_name 
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id 
GROUP BY c.contact_name 
ORDER BY COUNT 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
hint
- This is very similar to the previous two queries, however, it focuses on the City rather than the Customer Names

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

Data Normalization

Note: This step does not use PostgreSQL!

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
Jane	Ellie	Dog	Tiger	Cat	Toby	Turtle	No	Yes
Bob	Joe	Horse					No	No
Sam	Ginger	Dog	Miss Kitty	Cat	Bubble	Fish	Yes	No

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: Person

id	name	pet_id	dwelling_id
1	Jane	1	1
2	Bob	2	2
3	Sam	3	3

Table Name: Pet

id	owner_id	name	name2	name3	type	type2	type3	dwelling_id
1	1	Ellie	Tiger	Toby	dog	cat	turtle	1
2	2	Joe	null	null	horse	null	null	2
3	3	Ginger	Miss Kitty	Bubble	dog	cat	fish	3

Table Name: Dwelling

id	fenced_yard	in_city
1	false	true
2	false	false
3	true	false

Table Name:

Stretch Goals

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

===================================================
SELECT * (DELETE instead)
FROM customers as c
WHERE NOT EXISTS (
	SELECT * 
	FROM orders as o 
	WHERE o.customer_id = c.customer_id)
===================OR==============================	
SELECT c.* (DELETE instead)
  FROM customers c
 WHERE c.customer_id NOT IN (SELECT o.customer_id
                          FROM orders o)
===================OR===============================
SELECT * (DELETE instead)
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.customer_id
WHERE orders.customer_id IS NULL
====================================================

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:
- id, numeric value with no decimal places that should autoincrement.
- name, string, add whatever is necessary to make searching by name faster.
- budget numeric value.
constraints
- the id should be the primary key for the table.
- account name should be unique.
- account budget is required.

To see the script

Right Click on the database name
- Select Backup...
  - Set a filename
    - To put the file backup.sql in your home directory, you could use backup.sql
  - Set format to Plain
The script you want is now in the text file named above.
- Copy the script from the text file into the SQL code block above!