A student that completes this project shows that they can:

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

Working with SQL

Surf to SQL Try Editor at W3Schools.com

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

This can be done with SELECT and WHERE clauses

ANSWER:

	SELECT *
	FROM customers
	where city = "London"

This can be done with SELECT and WHERE clauses

ANSWER:

	SELECT *
	FROM customers
	WHERE postalcode = 1010

This can be done with SELECT and WHERE clauses

ANSWER:

	SELECT phone
	FROM suppliers
	WHERE supplierid = 11

This can be done with SELECT, WHERE, and ORDER BY clauses

ANSWER:

	SELECT *
	FROM Orders
	ORDER BY OrderDate DESC

This can be done with SELECT and WHERE clauses

ANSWER:

	SELECT SupplierName
	FROM suppliers
	WHERE length(SupplierName) > 20

This can be done with SELECT and a WHERE clause using the LIKE keyword

ANSWER:

	SELECT *
	FROM customers
	WHERE customerName LIKE '%market%'

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

This can be done with the INSERT INTO clause

ANSWER:

	INSERT INTO customers(customerName, contactName, address, city, postalcode, country)
	VALUES ('The Shire', 'Bilbo Baggins', '1 Hobbit-Hole', 'Bag End', 111, 'Middle Earth')

This can be done with UPDATE and WHERE clauses

ANSWER:

	UPDATE customers
	SET postalcode = 11122
	WHERE postalcode = 111

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

ANSWER:

	SELECT c.CustomerName, *, COUNT(o.OrderID) AS OrderSum FROM Orders AS o
	JOIN Customers c ON o.CustomerID = c.CustomerID
	GROUP BY c.CustomerID
	ORDER BY OrderSum DESC, c.CustomerName
	LIMIT 5;

This can be done by adding an ORDER BY clause to the previous answer

ANSWER:

	SELECT c.CustomerName, COUNT(o.OrderID) AS OrderSum FROM Orders AS o
	JOIN Customers c ON o.CustomerID = c.CustomerID
	GROUP BY c.CustomerID
	ORDER BY OrderSum DESC, c.CustomerName
	LIMIT 4;

This is very similar to the previous two queries, however, it focuses on the City rather than the CustomerName

ANSWER:

	SELECT c.City, COUNT(o.OrderID)
	AS OrderSum FROM Orders AS o
	JOIN Customers c ON o.CustomerID = c.CustomerID
	GROUP BY c.City

Note: This step does not use PostgreSQL!

Take the following data and normalize it into a 3NF database.

Persons table

This is done with a DELETE query

In the WHERE clause, you can provide another list with an IN keyword this list can be the result of another SELECT query. Write a query to return a list of CustomerIDs that meet the criteria above. Pass that to the IN keyword of the WHERE clause as the list of IDs to be deleted

Use a LEFT JOIN to join the Orders table onto the Customers table and check for a NULL value in the OrderID column

• 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.