SQL Practice Problems - Basic

Solutions to basic problems from the book - SQL Practice Problems.
SQL
Published

December 8, 2019

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:

  1. Brazil
  2. Mexico
  3. Argentina
  4. 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;
Back to top