BUSM 361 SQL Exam

Create a table called "Contracts." Create columns for ContractID, SupplierID (from the "Suppliers" table), ContractValue, and ContractStart. The identifiers and contract value should be integers. The contract start should be a date.

CREATE TABLE Contracts( ContractID int NOT NULL PRIMARY KEY, SupplierID int NOT NULL, ContractValue int NOT NULL, ContractStart date NOT NULL );

Insert one complete record into the contracts table. Where ContractID = 1 SupplierID = 2 ContractValue = 1000000 ContractStart = 2018-11-05

INSERT INTO Contracts VALUES(1,2,1000000,2018-11-05);

Identify all customers from Germany. Sort the results by city from Z to A. Show all columns.

SELECT * FROM Customers WHERE Country='Germany' ORDER BY City DESC;

Identify all orders in the "Orders" table that were from 1997 or later and that were shipped by "Federal Shipping." Sort them from newest to oldest. Include all columns from the table.

SELECT * FROM Orders WHERE OrderDate>='1997-01-01' AND ShipperID=3 ORDER BY OrderDate DESC;

SELECT Products.ProductID, Categories.CategoryName FROM Products LEFT JOIN Categories ON Products.CategoryID = Categories.CategoryID ORDER BY CategoryName; Adjust the previous SQL statement in the following ways: Count the number of products by CategoryName and refer to that column as "Products in Category". Then, sort the results alphabetically by CategoryName. (Only show the CategoryName and count in your results.)

SELECT Categories.CategoryName, count(*) AS "Products in Category" FROM Products LEFT JOIN Categories ON Products.CategoryID = Categories.CategoryID GROUP BY Categories.CategoryName ORDER BY Categories.CategoryName;

Count the number of customers that we have in each country. In the results, only show the country and the count. Refer to the column with counts as "Number of Customers." Sort the data by the number of customers from largest to smallest.

SELECT Country, count(*) AS 'Number of Customers' FROM Customers GROUP BY Country ORDER BY "Number of Customers" DESC;

Create a SQL statement that counts orders by employee's last name and shipper's name (in that order). Only show the employee's last name (and call it 'Employee Last Name'), shipper name, and count of orders. Sort the results by employee's last name in alphabetical order and then by order count descending. (Only pull records where the joined tables intersect, and the "Orders" table should be the "left" table.)

SELECT Employees.LastName, Shippers.ShipperName, count(*) FROM ((Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID) GROUP BY Employees.LastName, Shippers.ShipperName ORDER BY Employees.LastName, Shippers.ShipperName DESC;

Use a left join to combine records in the "Orders" and "Employees" table. Essentially, we want to extract data from the "Orders" table, but we want to include the actual name of the employee on the order in the results. Only show the OrderID, EmployeeID, FirstName, and LastName columns. Sort the data alphabetically by the employee's last name.

SELECT Orders.OrderID, Employees.EmployeeID, Employees.FirstName, Employees.LastName FROM Orders LEFT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Employees.LastName;

Create a SQL statement that, for each order, extracts the order ID and employee's last name and first name. Ensure the results are sorted alphabetically by last name and then first name. (Only pull records where the joined tables intersect.)

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY LastName, FirstName;

Use a join to combine records from the orders, employees, and shippers tables. Extract the order ID, employee's last name, and shipper's name. Sort the results by the employee's last name in reverse alphabetical order. (Only pull records where the joined tables intersect, and the "Orders" table should be the "left" table.)

SELECT Orders.OrderID, Employees.LastName, Shippers.ShipperName FROM ((Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID) ORDER BY Employees.LastName DESC

Sum the quantities ordered by product that are documented in the "OrderDetails" table. In your results, only show the product ID and the sum of product quantities. Sort by the sum descending.

SELECT ProductID, sum(Quantity) FROM OrderDetails GROUP BY ProductID ORDER BY sum(Quantity) DESC;

Use a left join to combine the ProductID in the "Products" table with the CategoryName in the "Categories" table. Only show those two columns in the results. "Products" will be the "left" table. Sort the results alphabetically by CategoryName.

SELECT Products.ProductID, Categories.CategoryName FROM Products LEFT JOIN Categories ON Products.CategoryID=Categories.CategoryID ORDER BY CategoryName;

Determine the average product price for each Supplier. Use the "Products" table. In your results, only show the SupplerID and average price. Sort the results from highest to lowest average price.

SELECT SupplierID, avg(Price) FROM Products GROUP BY SupplierID ORDER BY avg(Price) DESC

Determine the average product price for each Supplier. Use the "Products" table. In your results, only show the SupplerID and average price. Sort the results from highest to lowest average price.

SELECT SupplierID, avg(Price) FROM Products GROUP BY SupplierID ORDER BY avg(Price) DESC;

DELETE THE "Suppliers" Table Create the SQL statement by filling in the blank below: ___________ ___________ Suppliers;

drop, table

One of your suppliers has changed their name. Update the "Suppliers" table and change "Tokyo Traders" to "Kyoto Products." Create the SQL statement by filling in the blanks below: _________Suppliers _________SupplierName = "Kyoto Products" _________SupplierName = "Tokyo Traders";

update, set, where