WorksheetsSQL - Structured Query Language
Total questions: 12
Worksheet time: 13mins
Find all customers from Berlin
SELECT * FROM Customers
SELECT * FROM CustomerName
SELECT * FROM Customers WHERE city = 'Berlin'
FROM CustomersSELECT * WHERE city = 'Berlin'
Find all customer names from whose postcode is 1000 or greater
(the table name is Customers)
(a)
Find all customer names whose names start with "Al"
(the table name is Customers)
(a)
All employee names who made an order in 1996
SELECT FirstName, LastName FROM Employees, OrderID WHERE Employees.EmployeeID = Orders.EmployeeID AND OrderDate Like "1996%"
SELECT EmployeeName FROM Employees, OrderID WHERE Employees.EmployeeID = Orders.EmployeeID AND OrderDate.year > 1996
SELECT FirstName, LastName FROM Employees, WHERE Employees.EmployeeID = OrderDate = 1996
SELECT FirstName, LastName FROM Employees, OrderID WHERE Employees.OrderID = Orders.OrderID AND OrderDate Like "%1996%"
Count of how many orders each employee made (see bottom of image)
(a)
Draw the ER diagram of Employees, Orders and Customers tables
Names of customers who ordered with any employee named Nancy.
Add a new employee named "North Zarit", employeeID is 1112, date of birth is 4-1-1991, the pic is meme.png, he got his medical degree from Chula
INSERT INTO Employees (EmployeeID, LastName, FirstName, BirthDate, Photo, Notes) VALUES (1112, "Zarit", "North", "4-1-1991", "meme.png", "Got MED from Chula")
INSERT INTO Employees (EmployeeID, LastName, FirstName, BirthDate, Photo, Notes) VALUES (1112, "North", "Zarit", "4-1-1991", "meme.png", "Got MED from Chula")
INSERT INTO Employees (EmployeeID, LastName, FirstName, BirthDate, Photo, Notes) ("North", "Zarit", "4-1-1991", "meme.png", "Got MED from Chula")
INSERT INTO Employees DATA (1112, "North", "Zarit", "4-1-1991", "meme.png", "Got MED from Chula")
Sack all the staff! Delete the table employees.
(a)
Employee first name and surname with the most amount of orders (use orderID)
Use Inner Join
(a)
Change North's name to Gorth
UPDATE Employees set FirstName = "Gorth" WHERE Firstname = "North"
CHANGE Employees set FirstName = "Gorth" FROM Firstname = "North"
SELECT Employees set FirstName = "Gorth" FROM EMPLOYEES WHERE Firstname = "North"
SET Employees FROM FirstName = "Gorth" WHERE Firstname = "North"
Make a table called "Dogs", with fields DogID, DogName, Breed, Size (integer), weight (integer), and colour)
[no spaces before or after brackets]
[don't worry about NOT NULL]
(a)
