Banner

 

10 - SQL
Slide Exercises

Using the ClassicModels database, report the total value of payments for each customer to the nearest dollar and list in descending value

SELECT customerName, FORMAT(SUM(amount),0) AS Payment FROM Customers JOIN Payments
ON Customers.customerNumber = Payments.customerNumber
GROUP BY CustomerName
ORDER BY SUM(amount) DESC;

Using the ClassicModels database, write a correlated subquery to determine which employees work in the Paris office

SELECT CONCAT(firstName, ' ',lastName) FROM Employees WHERE EXISTS 
(SELECT * FROM Offices WHERE Offices.officeCode = Employees.officeCode AND city = 'Paris');

Write an SQL function to convert Fahrenheit to Celsius.

CREATE FUNCTION F_to_C(F REAL)
	   RETURNS REAL
	   RETURN (F-32)*5/9;
SELECT F_to_C(100); 

This page is part of the promotional and support material for Data Management (sixth edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 19-Oct-2016