SQL - Structured Query Language

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)