Skip to content

queries.md

Latest commit

 

History

History
73 lines (51 loc) · 2.16 KB

queries.md

File metadata and controls

73 lines (51 loc) · 2.16 KB
Raw

Database Queries

find all customers that live in London. Returns 6 records.

SELECT * from customers
where City = 'London'

find all customers with postal code 1010. Returns 3 customers.

SELECT * from customers
where postalCode = 1010

find the phone number for the supplier with the id 11. Should be (010) 9984510.

SELECT * from suppliers
where SupplierId = 11

list orders descending by the order date. The order with date 1997-02-12 should be at the top.

SELECT * from orders
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 * from suppliers
where length(SupplierName) > 20

find all customers that include the word "market" in the name. Should return 4 records.

SELECT * from customers
where CustomerName like '%market%'

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, City, PostalCode, Country)
VALUES ('The Shire', 'Bilbo Baggins', '1 Hobbit-Hole', 'Bag End', '111', 'Middle Earth' );

update Bilbo Baggins record so that the postal code changes to "11122".

UPDATE Customers
SET PostalCode = '11122'
WHERE CustomerName = 'Bilbo_Baggins';

list orders grouped by customer showing the number of orders per customer. Rattlesnake Canyon Grocery should have 7 orders.

select distinct * from Orders
order by CustomerID

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.

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.

delete all users that have no orders. Should delete 17 (or 18 if you haven't deleted the record added) records.

//test