Best SQL Excercise

Simple SELECTs

Introduction to the Northwind Database

The Northwind database is a sample database used by Microsoft to demonstrate the features of some of its products, including SQL Server and Microsoft Access. The database contains the sales data for Northwind Traders, a fictitious specialty foods export-import company.

Although the code taught in this class is not specific to Microsoft products, we use the Northwind database for many of our examples because many people are already familiar with it and because there are many resources for related learning that make use of the same database.

The diagram below shows the table structure of the Northwind database.

The Northwind database has additional tables, but we will only be using the ones shown above. In this lesson, we will explore some of these tables

Comments

The standard SQL comment is two hyphens (–).
Code Sample

— Single-line comment

/*

Multi-line comment used in:

-SQL Server

-Oracle

-MySQL

*/

Whitespace and Semi-colons

Whitespace is ignored in SQL statements. Multiple statements are separated with semi-colons. The two statements in the sample below are equally valid.

Code Sample SimpleSelects

SELECT * FROM Employees;

SELECT *
FROM Employees;

Case Sensitivity

SQL is not case sensitive. It is common practice to write reserved words in all capital letters. User-defined names, such as table names and column names may or may not be case sensitive depending on the operating system used.

SELECTing All Columns in All Rows

--Retrieve all columns in the Region table
SELECT *
FROM Region;

 

SELECTing Specific Columns

/*
Select the FirstName and LastName columns from the Employees table.
*/
SELECT FirstName, LastName
FROM Employees;

 

Exercise: SELECTing Specific Columns

1. Select CategoryName and Description from the Categories table.
2. Select ContactName, CompanyName, ContactTitle and Phone from the Customers table.
3. Select EmployeeID, Title, FirstName, LastName, and Region from the Employees table.
4. Select RegionID and RegionDescription from the Region table.
5. Select CompanyName, Fax, Phone and HomePage from the Suppliers table.

Sorting Records

/*

Select the FirstName and LastName columns from the Employees table.

Sort by LastName.

*/

SELECT FirstName, LastName

FROM Employees ORDER BY LastName;

Sorting By Multiple Columns

/*
Select the Title, FirstName and LastName columns from the Employees table.
Sort first by Title and then by LastName.
*/

SELECT Title, FirstName, LastName
FROM Employees
ORDER BY Title, LastName;

Sorting By Column Position

/*

Select the Title, FirstName and LastName columns from the Employees table.

Sort first by Title (position 1) and then by LastName (position 3).

*/

SELECT Title, FirstName, LastName FROM Employees ORDER BY 1,3;

Ascending and Descending Sorts

/*
 Select the Title, FirstName and LastName columns from the Employees table.
 Sort first by Title in ascending order and then by LastName 
 in descending order.
*/

SELECT Title, FirstName, LastName
FROM Employees
ORDER BY Title ASC, LastName DESC;

Exercise: Sorting Results
Duration: 5 to 15 minutes.

1. Select CategoryName and Description from the Categories table sorted by CategoryName.
2. Select ContactName, CompanyName, ContactTitle, and Phone from the Customers table sorted by Phone.
3. Create a report showing employees’ first and last names and hire dates sorted from newest to oldest employee.
4. Create a report showing Northwind’s orders sorted by Freight from most expensive to cheapest. Show OrderID, OrderDate, ShippedDate, CustomerID, and Freight.
5. Select CompanyName, Fax, Phone, HomePage and Country from the Suppliers table sorted by Country in descending order and then by CompanyName in ascending order.

The WHERE Clause and Operator Symbols

/*
Create a report showing the title and the first and last name
of all sales representatives.
*/

SELECT Title, FirstName, LastName
FROM Employees
WHERE Title = 'Sales Representative';

Checking for Inequality

/*

Create a report showing the first and last name of all employees

excluding sales representatives.

*/

SELECT FirstName, LastName

FROM Employees WHERE Title <> ‘Sales Representative’;

Exercise: Using the WHERE clause to check for equality or inequality
Duration: 5 to 15 minutes.

1. Create a report showing all the company names and contact names of Northwind’s customers in Buenos Aires.
2. Create a report showing the product name, unit price and quantity per unit of all products that are out of stock.
3. Create a report showing the order date, shipped date, customer id, and freight of all orders placed on May 19, 1997.
4. Create a report showing the first name, last name, and country of all employees not in the United States.

Checking for Greater or Less Than

/*

Create a report showing the first and last name of all employees whose

last names start with a letter in the last half of the alphabet.

*/

SELECT FirstName, LastName

FROM Employees WHERE LastName >= ‘N’;

Exercise: Using the WHERE clause to check for greater or less than
Duration: 5 to 15 minutes.

1. Create a report that shows the employee id, order id, customer id, required date, and shipped date of all orders that were shipped later than they were required.
2. Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with “A” or “B.”
3. Create a report that shows all orders that have a freight cost of more than $500.00.
4. Create a report that shows the product name, units in stock, units on order, and reorder level of all products that are up for reorder.

Checking for NULL

/*

Create a report showing the first and last names of

all employees whose region is unspecified.

*/

SELECT FirstName, LastName

FROM Employees WHERE Region IS NULL;

/*
Create a report showing the first and last names of all
employees who have a region specified.
*/

SELECT FirstName, LastName
FROM Employees
WHERE Region IS NOT NULL;

Exercise: Checking for NULL
Duration: 5 to 15 minutes.

1. Create a report that shows the company name, contact name and fax number of all customers that have a fax number.
2. Create a report that shows the first and last name of all employees who do not report to anybody.

WHERE and ORDER BY

/*
Create a report showing the first and last name of all employees whose 
last names start with a letter in the last half of the alphabet.
Sort by LastName in descending order.
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'N'
ORDER BY LastName DESC;

Exercise: Using WHERE and ORDER BY Together
Duration: 5 to 15 minutes.

1. Create a report that shows the company name, contact name and fax number of all customers that have a fax number. Sort by company name.
2. Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with “A” or “B.” Sort by contact name in descending order.

The WHERE Clause and Operator Words

The BETWEEN Operator

/*

Create a report showing the first and last name of all employees

whose last names start with a letter between “J” and “M”.

*/

SELECT FirstName, LastName

FROM Employees

WHERE LastName BETWEEN ‘J’ AND ‘M’;

— The above SELECT statement is the same as the one below.

SELECT FirstName, LastName

FROM Employees WHERE LastName >= ‘J’ AND LastName <= ‘M’;

 

The IN Operator

/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy is "Mrs." or "Ms.". 
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy IN ('Ms.','Mrs.');

-- The above SELECT statement is the same as the one below

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy = 'Ms.' OR TitleOfCourtesy = 'Mrs.';

 

The LIKE Operator

/*
Create a report showing the title of courtesy and the first
and last name of all employees whose title of courtesy begins with "M". 
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M%';

 

The Underscore (_)

/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy begins with "M" and
is followed by any character and a period (.).
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M_.';

 

Wildcards and Performance: Using wildcards can slow down performance, especially if they are used at the beginning of a pattern. You should use them sparingly.

The NOT Operator

/*

Create a report showing the title of courtesy and the first and last name

of all employees whose title of courtesy is not “Ms.” or “Mrs.”.

*/

SELECT TitleOfCourtesy, FirstName, LastName

FROM Employees WHERE NOT TitleOfCourtesy IN (‘Ms.’,’Mrs.’);

Exercise: More SELECTs with WHERE
Duration: 5 to 15 minutes.

1. Create a report that shows the first and last names and birth date of all employees born in the 1950s.
2. Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kelly’s Homestead, and Tokyo Traders. Hint: you will need to first do a separate SELECT on the Suppliers table to find the supplier ids of these three companies.
3. Create a report that shows the shipping postal code, order id, and order date for all orders with a ship postal code beginning with “02389”.
4. Create a report that shows the contact name and title and the company name for all customers whose contact title does not contain the word “Sales”.

Checking Multiple Conditions

AND

/*
Create a report showing the first and last name of all
sales representatives whose title of courtesy is "Mr.".
*/

SELECT FirstName, LastName
FROM Employees
WHERE Title = 'Sales Representative'
 AND TitleOfCourtesy = 'Mr.';

OR

/*
 Create a report showing the first and last name and the city of all 
 employees who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City
FROM Employees
WHERE City = 'Seattle' OR City = 'Redmond';

 

Order of Evaluation: By default, SQL processes AND operators before it processes OR operators. To illustrate how this works, take a look at the following example.

/*

Create a report showing the first and last name of all sales

representatives who are from Seattle or Redmond.

*/

SELECT FirstName, LastName, City, Title

FROM Employees

WHERE City = ‘Seattle’ OR City = ‘Redmond’ AND Title = ‘Sales Representative’;

 

/*
 Create a report showing the first and last name of all sales 
 representatives who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City, Title
FROM Employees
WHERE (City = 'Seattle' OR City = 'Redmond')
 AND Title = 'Sales Representative';

The parentheses specify that the OR portion of the clause should be evaluated first.

Exercise: Writing SELECTs with Multiple Conditions
Duration: 5 to 15 minutes

1. Create a report that shows the first and last names and cities of employees from cities other than Seattle in the state of Washington.
2. Create a report that shows the company name, contact title, city and country of all customers in Mexico or in any city in Spain except Madrid.

Advance SELECTs

Calculated Fields

Concatenation

-- Select the full name of all employees. SQL SERVER.

SELECT FirstName + ' ' + LastName
FROM Employees;

Mathematical Calculations

/*
If the cost of freight is greater than or equal to $500.00,
it will now be taxed by 10%. Create a report that shows the
order id, freight cost, freight cost with this tax for all
orders of $500 or more.
*/

SELECT OrderID, Freight, Freight * 1.1
FROM Orders
WHERE Freight >= 500;

Aliases

SELECT OrderID, Freight, Freight * 1.1 AS FreightTotal
FROM Orders
WHERE Freight >= 500;

Exercise: Calculating Fields

Duration: 10 to 20 minutes.

1. Create a report that shows the unit price, quantity, discount, and the calculated total price using these three fields.
   • Note for SQL Server users only: You will be using the Order Details table. Because this table name has a space in it, you will need to put it in double quotes in the FROM clause (e.g, FROM “Order Details”).
2. Write a SELECT statement that outputs the following.

Aggregate Functions and Grouping

Aggregate Functions

-- Find the Number of Employees
SELECT COUNT(*) AS NumEmployees
FROM Employees;

-- Find the Total Number of Units Ordered of Product ID 3
/******************************
SQL Server
******************************/
SELECT SUM(Quantity) AS TotalUnits
FROM "Order Details"
WHERE ProductID=3;

-- Find the Average Unit Price of Products
SELECT AVG(UnitPrice) AS AveragePrice
FROM Products;

-- Find the Earliest and Latest Dates of Hire
SELECT MIN(HireDate) AS FirstHireDate,
 MAX(HireDate) AS LastHireDate
FROM Employees;

GROUP BY

--Retrieve the number of employees in each city

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City;

HAVING

/*
 Retrieve the number of employees in each city 
 in which there are at least 2 employees.
*/

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City
HAVING COUNT(EmployeeID) > 1;

Order of Clauses

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

/*

Find the number of sales representatives in each city that contains

at least 2 sales representatives. Order by the number of employees.

*/

SELECT City, COUNT(EmployeeID) AS NumEmployees

FROM Employees

WHERE Title = ‘Sales Representative’

GROUP BY City

HAVING COUNT(EmployeeID) > 1 ORDER BY NumEmployees;

Exercise: Working with Aggregate Functions
Duration: 10 to 20 minutes.
1. Create a report that returns the following from the Order_Details table. The report should only return rows for which TotalUnits is less than 200.

2. Create a report that returns the following from the Products table. The report should only return rows for which the average unit price of a product is greater than 70.

3. Create a report that returns the following from the Orders table. NumOrders represents the number of orders placed by a certain customer. Only return rows where NumOrders is greater than 15.

Selecting Distinct Records

/*
Find all the distinct cities in which Northwind has employees.
*/

SELECT DISTINCT City
FROM Employees
ORDER BY City

/*
Find out in how many different cities Northwind has employees.
*/

SELECT COUNT(DISTINCT City) AS NumCities
FROM Employees

Built-in Data Manipulation Functions

Common Math Functions

/*
Select freight as is and
freight rounded to the first decimal (e.g, 1.150 becomes 1.200)
from the Orders tables
*/

SELECT Freight, ROUND(Freight,1)  AS ApproxFreight
FROM Orders;

 

/*

Select the unit price as is and

unit price as a decimal with 2 places to the right of the decimal point

from the Products tables

*/

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

SQL Server and MySQL

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

SELECT UnitPrice, CAST(UnitPrice AS Decimal(8,2)) FROM Products;

Common String Functions

/*

Select first and last name from employees in all uppercase letters

*/

SELECT UPPER(FirstName), UPPER(LastName) FROM Employees;

-- Select the first 10 characters of each customer's address

/******************************
SQL Server and MySQL
******************************/
SELECT SUBSTRING(Address,1,10)
FROM Customers;

/******************************
Oracle
******************************/
SELECT SUBSTR(Address,1,10)
FROM Customers;

Common Date Functions

-- Find the hiring age of each employee

/******************************
SQL Server
******************************/
SELECT LastName, BirthDate, HireDate, DATEDIFF(year,BirthDate,HireDate) AS HireAge
FROM Employees
ORDER BY HireAge;
-- Find the Birth month for every employee

/******************************
SQL Server
******************************/
SELECT FirstName, LastName, DATENAME(month,BirthDate) AS BirthMonth
FROM Employees
ORDER BY DATEPART(month,BirthDate);

Subqueries, Joins and Unions

/*
Find the CustomerID of the company that placed order 10290.
*/

SELECT CustomerID
FROM Orders
WHERE OrderID = 10290;
-- Find the name of the company that placed order 10290.

SELECT CompanyName
FROM Customers
WHERE CustomerID = (SELECT CustomerID
   FROM Orders
   WHERE OrderID = 10290);

Exercise: Subqueries
Create a report that shows all products by name that are in the Seafood category.
Create a report that shows all companies by name that sell products in CategoryID 8.
Create a report that shows all companies by name that sell products in the Seafood category.

-- Create a report showing employee orders.

SELECT Employees.EmployeeID, Employees.FirstName,
 Employees.LastName, Orders.OrderID, Orders.OrderDate
FROM Employees JOIN Orders ON
 (Employees.EmployeeID = Orders.EmployeeID)
ORDER BY Orders.OrderDate;
-- Create a report showing employee orders using Aliases.

SELECT e.EmployeeID, e.FirstName, e.LastName,
 o.OrderID, o.OrderDate
FROM Employees e JOIN Orders o ON
 (e.EmployeeID = o.EmployeeID)
ORDER BY o.OrderDate;
/*
 Create a report that shows the number of
 employees and customers from each city that has employees in it.
*/

SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees,
 COUNT(DISTINCT c.CustomerID) AS numCompanies,
 e.City, c.City
FROM Employees e JOIN Customers c ON
 (e.City = c.City)
GROUP BY e.City, c.City
ORDER BY numEmployees DESC;

/*

Create a report that shows the number of

employees and customers from each city that has employees in it.

*/

SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees,

COUNT(DISTINCT c.CustomerID) AS numCompanies,

e.City, c.City

FROM Employees e LEFT JOIN Customers c ON

(e.City = c.City)

GROUP BY e.City, c.City ORDER BY numEmployees DESC;

/*

Create a report that shows the number of

employees and customers from each city that has customers in it.

*/

SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees,

COUNT(DISTINCT c.CustomerID) AS numCompanies,

e.City, c.City

FROM Employees e RIGHT JOIN Customers c ON

(e.City = c.City)

GROUP BY e.City, c.City ORDER BY numEmployees DESC;

/*
 Create a report that shows the number of
 employees and customers from each city.

 Note that MySQL 5.x does NOT support full outer joins.
*/

SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees,
 COUNT(DISTINCT c.CustomerID) AS numCompanies,
 e.City, c.City
FROM Employees e FULL JOIN Customers c ON
 (e.City = c.City)
GROUP BY e.City, c.City
ORDER BY numEmployees DESC;
/*
Get the phone numbers of all shippers, customers, and suppliers
*/

SELECT CompanyName, Phone
FROM Shippers
 UNION
SELECT CompanyName, Phone
FROM Customers
 UNION
SELECT CompanyName, Phone
FROM Suppliers
ORDER BY CompanyName;

Using CASE

/*
Create a report showing the customer ID and company name,
employee id, firstname and lastname, and the order id
and a conditional column called "Shipped" that displays "On Time"
if the order was shipped on time and "Late" if the order was shipped late.
*/SELECT c.CustomerID, c.CompanyName, e.EmployeeID, e.FirstName, e.LastName, OrderID,
 (CASE
  WHEN ShippedDate < RequiredDate
   THEN 'On Time'
   ELSE 'Late'
   END) AS Shipped
FROM Orders o
 JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
 JOIN Customers c ON (c.CustomerID = o.CustomerID)
ORDER BY Shipped;