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.
Clicking the Restore Database button in the page will repopulate the database with the original data and discard all changes you have made.
SELECT * FROM CUSTOMERS where city = 'London'
SELECT * FROM Customers WHERE PostalCode = 1010
SELECT phone FROM suppliers WHERE SupplierID = 11
SELECT * FROM Orders Where OrderDate ORDER BY OrderDate DESC
find all suppliers who have names longer than 20 characters. You can use length(SupplierName) to get the length of the name. Returns 11 records.
SELECT SupplierName FROM suppliers WHERE LENGTH(SupplierName) > 20
SELECT CustomerName 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
add a customer record for "The Shire", the contact name is "Bilbo Baggins" the address is "1 Hobbit-Hole" in "Bag End", postal code "111" and the country is "Middle Earth".
INSERT INTO Customers(CustomerName, ContactName, Address, PostalCode, Country) VALUES('The Shire', 'Bilbo Baggins', '1 Hobbit-Hole. Bag End', '111', 'Middle Earth')
UPDATE Customers SET PostalCode = 11122 WHERE PostalCode = 111
list orders grouped by customer showing the number of orders per customer. Rattlesnake Canyon Grocery should have 7 orders.
SELECT COUNT(OrderID) AS OrdersPerCustomer, CustomerName FROM Orders o Join Customers c ON o.CustomerID = c.CustomerID GROUP BY c.CustomerName
There is more information about the COUNT clause on W3 Schools
list customers names and the number of orders per customer. Sort the list by number of orders in descending order. Ernst Handel should be at the top with 10 orders followed by QUICK-Stop, Rattlesnake Canyon Grocery and Wartian Herkku with 7 orders each.
AS OrdersPerCustomer, CustomerName FROM Orders o Join Customers c ON o.CustomerID = c.CustomerID GROUP BY c.CustomerName ORDER BY OrdersPerCustomer DESC
list orders grouped by customer's city showing number of orders per city. Returns 58 Records with Aachen showing 2 orders and Albuquerque showing 7 orders.
SELECT COUNT(OrderID) AS OrdersPerCity, CustomerName, City FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID GROUP BY c.City ORDER BY City
delete all customers that have no orders. Should delete 17 (or 18 if you haven't deleted the record added) records.
DELETE FROM customers WHERE customerid IN (SELECT customerid FROM (SELECT c.customerid, o.orderid FROM orders o LEFT JOIN customers c ON c.customerid = o.customerid GROUP BY o.customerid) where orderid is null)
-
use pgAdmin to create a database, naming it
budget. -
add an
accountstable 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.budgetnumeric value.
-
constraints
- the
idshould be the primary key for the table. - account
nameshould be unique. - account
budgetis required.
CREATE TABLE account (user_id INT PRIMARY KEY, user_name VARCHAR (50) UNIQUE NOT NULL, budget serial NOT NULL);
- the