Question

Create a report showing the Order ID, the name of the company that placed the order, and the first and last name of the associated employee. Only show orders placed after January 1, 1998 that shipped after they were required. Sort by Company Name.

Answer 1

Answer:

/******************************

Both of the queries below will work in SQL Server

Oracle

******************************/

SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName

FROM Orders o

JOIN Employees e ON (e.EmployeeID = o.EmployeeID)

JOIN Customers c ON (c.CustomerID = o.CustomerID)

WHERE o.ShippedDate > o.RequiredDate AND o.OrderDate > '1-Jan-1998'

ORDER BY c.CompanyName;

/******************************

MySQL

******************************/

SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName

FROM Orders o

JOIN Employees e ON (e.EmployeeID = o.EmployeeID)

JOIN Customers c ON (c.CustomerID = o.CustomerID)

WHERE o.ShippedDate > o.RequiredDate AND o.OrderDate > '1998-01-01'

ORDER BY c.CompanyName;