Problem 1: Which shippers do we have?
We have a table called Shippers. Return all the fields from all the shippers.
Solution
select * from Shippers;
Problem 2: Certain fields from Categories
In the Categories table, selecting all the fields using this SQL: Select * from Categories …will return 4 columns. We only want to see two columns, CategoryName and Description.
Solution
select CategoryName, Description from Categories;
Problem 3: Sales Representatives
We’d like to see just the FirstName, LastName, and HireDate of all the employees with the Title of Sales Representative. Write a SQL statement that returns only those employees.
Solution
select FirstName, LastName, HireDate
from Employees
where Title = 'Sales Representative';
Problem 4: Sales Representatives in the United States
Now we’d like to see the same columns as above, but only for those employees that both have the title of Sales Representative, and also are in the United States.
Solution
select FirstName, LastName, HireDate
from Employees
where Title = 'Sales Representative' and Country = 'USA';
Problem 5: Orders placed by specific EmployeeID
Show all the orders placed by a specific employee. The EmployeeID for this Employee (Steven Buchanan) is 5.
Solution
select OrderID, OrderDate from Orders
where EmployeeID = 5;
Problem 6: Suppliers and ContactTitles
In the Suppliers table, show the SupplierID, ContactName, and ContactTitle for those Suppliers whose ContactTitle is not Marketing Manager.
Solution
select SupplierID, ContactName, ContactTitle
from Suppliers
where ContactTitle != 'Marketing Manager';
Problem 7: Products with “queso” in ProductName
In the products table, we’d like to see the ProductID and ProductName for those products where the ProductName includes the string “queso”.
Solution 1
select ProductID, ProductName
from Products
where CHARINDEX('queso', ProductName) != 0;
Solution 2
select ProductID, ProductName
from Products
where ProductName like '%queso%';
Problem 8: Orders shipping to France or Belgium
Looking at the Orders table, there’s a field called ShipCountry. Write a query that shows the OrderID, CustomerID, and ShipCountry for the orders where the ShipCountry is either France or Belgium.
Solution
select OrderID, CustomerID, ShipCountry
from Orders
where ShipCountry = 'France' OR ShipCountry = 'Belgium';
Problem 9: Orders shipping to any country in Latin America
Now, instead of just wanting to return all the orders from France of Belgium, we want to show all the orders from any Latin American country. But we don’t have a list of Latin American countries in a table in the Northwind database. So, we’re going to just use this list of Latin American countries that happen to be in the Orders table:
- Brazil
- Mexico
- Argentina
- Venezuela
Solution
select OrderID, CustomerID, ShipCountry
from Orders
where ShipCountry in (
'Brazil', 'Mexico', 'Argentina', 'Venezuela');
Problem 10: Employees, in order of age
For all the employees in the Employees table, show the FirstName, LastName, Title, and BirthDate. Order the results by BirthDate, so we have the oldest employees first.
Solution
select FirstName, LastName, Title, BirthDate
from Employees
order by BirthDate;
Problem 11: Showing only the Date with a DateTime field
In the output of the query above, showing the Employees in order of BirthDate, we see the time of the BirthDate field, which we don’t want. Show only the date portion of the BirthDate field.
Solution
select FirstName, LastName, Title,
CONVERT(DATE, BirthDate) AS DateOnlyBirthDate
from Employees
order by BirthDate;
Problem 12: Employees full name
Show the FirstName and LastName columns from the Employees table, and then create a new column called FullName, showing FirstName and LastName joined together in one column, with a space in between.
Solution
select FirstName, LastName, FirstName + ' ' + LastName as FullName
from Employees;
Problem 13: OrderDetails amount per line item
In the OrderDetails table, we have the fields UnitPrice and Quantity. Create a new field, TotalPrice, that multiplies these two together. We’ll ignore the Discount field for now. In addition, show the OrderID, ProductID, UnitPrice, and Quantity. Order by OrderID and ProductID.
Solution
select OrderID, ProductID, UnitPrice, Quantity,
UnitPrice * Quantity As TotalPrice
from OrderDetails
order by OrderID, ProductID;
Problem 14: How many customers?
How many customers do we have in the Customers table? Show one value only, and don’t rely on getting the record count at the end of a result set.
Solution
select count(*) As TotalCustomers
from Customers;
Problem 15: When was the first order?
Show the date of the first order ever made in the Orders table.
Solution
select MIN(OrderDate) as FirstOrder
from Orders
Problem 16: Countries where there are customers
Show a list of countries where the Northwind company has customers.
Solution
select DISTINCT(Country) As Country
from Customers;
Problem 17: Contact titles for customers
Show a list of all the different values in the Customers table for ContactTitles. Also include a count for each ContactTitle. This is similar in concept to the previous question “Countries where there are customers”, except we now want a count for each ContactTitle.
Solution
select ContactTitle, count(*) As TotalContactTitle
from Customers
group by ContactTitle
order by TotalContactTitle DESC;
Problem 18: Products with associated supplier names
We’d like to show, for each product, the associated Supplier. Show the ProductID, ProductName, and the CompanyName of the Supplier. Sort by ProductID.
Solution
select P.ProductID, P.ProductName, S.CompanyName As Supplier
from Products AS P JOIN Suppliers AS S on P.SupplierID = S.SupplierID
order by P.ProductID;
Problem 19: Orders and the Shipper that was used
We’d like to show a list of the Orders that were made, including the Shipper that was used. Show the OrderID, OrderDate (date only), and CompanyName of the Shipper, and sort by OrderID. In order to not show all the orders (there’s more than 800), show only those rows with an OrderID of less than 10300.
Solution
select O.OrderID, CONVERT(DATE, O.OrderDate) AS OrderDate,
S.CompanyName
from Orders As O
JOIN Shippers AS S
ON O.ShipVia = S.ShipperID
where O.OrderID < 10300;