facebook google plus twitter
Webucator's Free SQL Tutorial

Lesson: Advanced SELECTs

Welcome to our free SQL tutorial. This tutorial is based on Webucator's Introduction to SQL Training course.

In this lesson you will learn to write advanced select statements using SQL functions and grouping.

Lesson Goals

  • Learn to use SELECT statements to retrieve calculated values.
  • Learn to work with aggregate functions and grouping.
  • Learn to work with SQL's data manipulation functions.

Calculated Fields

Calculated fields are fields that do not exist in a table, but are created in the SELECT statement. For example, you might want to create FullName from FirstName and LastName.

Concatenation

Concatenation is a fancy word for stringing together different words or characters. SQL Server, Oracle and MySQL each has its own way of handling concatenation. All three of the code samples below will return the following results:Full Name Results

In SQL Server, the plus sign (+) is used as the concatenation operator.

Code Sample:

AdvancedSelects/Demos/Concatenate-SqlServer.sql
-- Select the full name of all employees. SQL SERVER.

SELECT FirstName + ' ' + LastName
FROM Employees;

In Oracle, the double pipe (||) is used as the concatenation operator.

Code Sample:

AdvancedSelects/Demos/Concatenate-Oracle.sql
-- Select the full name of all employees. Oracle.

SELECT FirstName || ' ' || LastName
FROM Employees;

MySQL does this in yet another way. There is no concatenation operator. Instead, MySQL uses the CONCAT() function .

Code Sample:

AdvancedSelects/Demos/Concatenate-MySQL.sql
-- Select the full name of all employees. MySQL.
SELECT CONCAT(FirstName, ' ', LastName)
FROM Employees;

Note that concatenation only works with strings. To concatenate other data types, you must first convert them to strings.

Mathematical Calculations

Mathematical calculations in SQL are similar to those in other languages.

Mathematical Operators
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulus

Code Sample:

AdvancedSelects/Demos/MathCalc.sql
/*
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;

The above SELECT statement will return the following results:Freight Tax Results

Aliases

You will notice in the examples above that the calculated columns have the header "(No column name)". The keyword AS is used to provide a named header for the column.

Note: you cannot use aliases in a WHERE clause!

Code Sample:

AdvancedSelects/Demos/Alias.sql
SELECT OrderID, Freight, Freight * 1.1 AS FreightTotal
FROM Orders
WHERE Freight >= 500;

As you can see, the third column now has the title "FreightTotal".

Calculating Fields

Duration: 10 to 20 minutes.

In this exercise, you will practice writing SELECT statements with calculated fields.

  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.Empoyee Ext Results

Solution:

AdvancedSelects/Solutions/Calculations.sql
/******************************
SQL Server Solutions
******************************/
SELECT UnitPrice, Quantity, Discount, UnitPrice * Quantity * (1-Discount) AS TotalPrice
FROM "Order Details";

SELECT FirstName + ' ' + LastName + ' can be reached at x' + Extension + '.' AS ContactInfo
FROM Employees;

/******************************
Oracle Solutions
******************************/
SELECT UnitPrice, Quantity, Discount, UnitPrice * Quantity * (1-Discount) AS TotalPrice
FROM Order_Details;

SELECT FirstName || ' ' || LastName || ' can be reached at x' || Extension || '.' AS ContactInfo
FROM Employees;

/******************************
MySQL Solutions
******************************/
SELECT UnitPrice, Quantity, Discount, UnitPrice * Quantity * (1-Discount) AS TotalPrice
FROM Order_Details;

SELECT CONCAT(FirstName, ' ', LastName, ' can be reached at x', Extension, '.') AS ContactInfo
FROM Employees;

Aggregate Functions and Grouping

Aggregate Functions

Aggregate functions are used to calculate results using field values from multiple records. There are five common aggregate functions.

Common Aggregate Functions
Aggregate Function Description
COUNT() Returns the number of rows containing non-NULL values in the specified field.
SUM() Returns the sum of the non-NULL values in the specified field.
AVG() Returns the average of the non-NULL values in the specified field.
MAX() Returns the maximum of the non-NULL values in the specified field.
MIN() Returns the minimum of the non-NULL values in the specified field.

Code Sample:

AdvancedSelects/Demos/Aggregate-Count.sql
-- Find the Number of Employees

SELECT COUNT(*) AS NumEmployees
FROM Employees;

Returns 9.

Code Sample:

AdvancedSelects/Demos/Aggregate-Sum.sql
-- Find the Total Number of Units Ordered of Product ID 3

/******************************
SQL Server
******************************/
SELECT SUM(Quantity) AS TotalUnits
FROM "Order Details"
WHERE ProductID=3;

/******************************
Oracle and MySQL
******************************/
SELECT SUM(Quantity) AS TotalUnits
FROM Order_Details
WHERE ProductID=3;

Returns 328.

Code Sample:

AdvancedSelects/Demos/Aggregate-Avg.sql
-- Find the Average Unit Price of Products

SELECT AVG(UnitPrice) AS AveragePrice
FROM Products;

Returns 28.8663.

Code Sample:

AdvancedSelects/Demos/Aggregate-MinMax.sql
-- Find the Earliest and Latest Dates of Hire

SELECT MIN(HireDate) AS FirstHireDate,
	MAX(HireDate) AS LastHireDate
FROM Employees;

The above SELECT statement will return April 1, 1992 and November 15, 1994 as the FirstHireDate and LastHireDate, respectively. The date format will vary from database to database.Hire Dates Results

Grouping Data

GROUP BY

With the GROUP BY clause, aggregate functions can be applied to groups of records based on column values. For example, the following code will return the number of employees in each city.

Code Sample:

AdvancedSelects/Demos/Aggregate-GroupBy.sql
-- Retrieve the number of employees in each city

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

The above SELECT statement will return the following results:Group By Results

Since the GROUP BY clause is used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the results by a column (or more than one column), there's no need for GROUP BY if there is no aggregate function in the query.

HAVING

The HAVING clause is used to filter grouped data. For example, the following code specifies that we only want information on cities that have more than one employee.

Code Sample:

AdvancedSelects/Demos/Aggregate-Having.sql
/*
	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;

The above SELECT statement will return the following results:Having Results

Order of Clauses

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

Code Sample:

AdvancedSelects/Demos/Aggregate-OrderOfClauses.sql
/*
	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;

The above SELECT statement will return the following results:Clause Order Results

Grouping Rules

  • Every non-aggregate column that appears in the SELECT clause must also appear in the GROUP BY clause.
  • You may not use aliases in the HAVING clause.
  • You may use aliases in the ORDER BY clause.
  • You may only use calculated fields in the HAVING clause.
  • You may use calculated field aliases or actual fields in the ORDER BY clause.

Selecting Distinct Records

The DISTINCT keyword is used to select distinct combinations of column values from a table. For example, the following example shows how you would find all the distinct cities in which Northwind has employees.

Code Sample:

AdvancedSelects/Demos/Distinct.sql
/*
Find all the distinct cities in which Northwind has employees.
*/

SELECT DISTINCT City
FROM Employees
ORDER BY City

DISTINCT is often used with aggregate functions. The following example shows how DISTINCT can be used to find out in how many different cities Northwind has employees.

Code Sample:

AdvancedSelects/Demos/Distinct-Count.sql
/*
Find out in how many different cities Northwind has employees.
*/

SELECT COUNT(DISTINCT City) AS NumCities
FROM Employees

Working with Aggregate Functions

Duration: 10 to 20 minutes.

In this exercise, you will practice working with aggregate functions. For all of these questions, it's okay if your result set's rows are in a different order.

  1. Create a report that returns the following from the Order_Details table.Aggregate Order Details ResultsThe report should only return rows for which TotalUnits is less than 200.
  2. Create a report that returns the following from the Products table.Aggregate Orders Results 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.Aggregate Products NumOrders represents the number of orders placed by a certain customer. Only return rows where NumOrders is greater than 15.

Query number 2 above has something strange about it. It is, in fact, a ridiculous query. Why? Try to get the exact same results without using an aggregate function.

Solution:

AdvancedSelects/Solutions/Grouping.sql
SELECT ProductID, SUM(Quantity) AS TotalUnits
FROM Order_Details /* SQL Server users should use "Order Details" */
GROUP BY ProductID
HAVING SUM(Quantity) < 200;

SELECT ProductID, AVG(UnitPrice) AS AveragePrice
FROM Products
GROUP BY ProductID
HAVING AVG(UnitPrice) > 70
ORDER BY AveragePrice;

SELECT CustomerID, COUNT(OrderID) AS NumOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 15
ORDER BY NumOrders DESC;

/*CHALLENGE ANSWER:*/
SELECT ProductID, UnitPrice
FROM Products
WHERE UnitPrice > 70
ORDER BY UnitPrice;

Built-in Data Manipulation Functions

In this section, we will discuss some of the more common built-in data manipulation functions. Unfortunately, the functions differ greatly between databases, so you should be sure to check your database documentation when using these functions.

The tables below show some of the more common math, string, and date functions.

Common Math Functions

Common Math Functions
Description SQL Server Oracle MySQL
Absolute value ABS ABS ABS
Smallest integer >= value CEILING CEIL CEILING
Round down to nearest integer FLOOR FLOOR FLOOR
Power POWER POWER POWER
Round ROUND ROUND ROUND
Square root SQRT SQRT SQRT
Formatting numbers to two decimal places CAST(num AS decimal(8,2)) CAST(num AS decimal(8,2)) FORMAT(num,2) or CAST(num AS decimal(8,2))

Code Sample:

AdvancedSelects/Demos/Functions-Math1.sql
/*
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;

The above SELECT statement will return the following results (not all rows shown):Functions Math Results

Code Sample:

AdvancedSelects/Demos/Functions-Math2.sql
/*
Select the unit price as is and
unit price as a CHAR(10)
from the Products tables
*/
SELECT UnitPrice, CAST(UnitPrice AS CHAR(10))
FROM Products;

/******************************
ADD CONCATENATION
******************************/
/******************************
SQL Server
******************************/
SELECT UnitPrice, '$' + CAST(UnitPrice AS CHAR(10))
FROM Products;

/******************************
Oracle
******************************/
SELECT UnitPrice, '$' || CAST(UnitPrice AS CHAR(10))
FROM Products;

/******************************
MySQL
******************************/
SELECT UnitPrice, CONCAT('$',CAST(UnitPrice AS CHAR(10)))
FROM Products;

The above SELECT statement will return the following results (not all rows shown):Cast Results

Note that the CHAR(10) creates space for 10 characters and if the unit price required more than 10 characters you would need to increase it accordingly.

Common String Functions

Common String Functions
Description SQL Server Oracle MySQL
Convert characters to lowercase LOWER LOWER LOWER
Convert characters to uppercase UPPER UPPER UPPER
Remove trailing blank spaces RTRIM RTRIM RTRIM
Remove leading blank spaces LTRIM LTRIM LTRIM
Returns part of a string SUBSTRING SUBSTR SUBSTRING

Code Sample:

AdvancedSelects/Demos/Functions-String1.sql
/*
Select first and last name from employees in all uppercase letters
*/
SELECT UPPER(FirstName), UPPER(LastName)
FROM Employees;

The above SELECT statement will return the following results:Uppercase Results

Code Sample:

AdvancedSelects/Demos/Functions-String2.sql
-- 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;

The above SELECT statement will return the following results (not all rows shown):Substring Results

Common Date Functions

Common Date Functions
Description SQL Server Oracle MySQL
Date addition DATEADD (use +) DATE_ADD
Date subtraction DATEDIFF (use -) DATEDIFF
Convert date to string DATENAME TO_CHAR DATE_FORMAT
Convert date to number DATEPART TO_NUMBER(TO_CHAR) EXTRACT
Get current date and time GETDATE SYSDATE NOW

Code Sample:

AdvancedSelects/Demos/Functions-Date1.sql
-- Find the hiring age of each employee

/******************************
SQL Server
******************************/
SELECT LastName, BirthDate, HireDate, DATEDIFF(year,BirthDate,HireDate) AS HireAge
FROM Employees
ORDER BY HireAge;

/******************************
Oracle
******************************/
SELECT  LastName, BirthDate, HireDate, FLOOR((HireDate - BirthDate)/365.25) AS HireAge
FROM Employees
ORDER BY HireAge;

/******************************
MySQL
******************************/
-- Find the hiring age of each employee
-- in versions of MySQL prior to 4.1.1
SELECT LastName, BirthDate, HireDate, YEAR(HireDate)-YEAR(BirthDate) AS HireAge
FROM Employees;

-- In MySQL 4.1.1 and later, DATEDIFF() returns the number of days between
-- two dates. You can then divide and floor to get age.
SELECT LastName, BirthDate, HireDate, FLOOR(DATEDIFF(HireDate,BirthDate)/365) AS HireAge
FROM Employees
ORDER BY HireAge;

The above SELECT statement will return the following results in SQL Server:Date Diff Results

And like this in Oracle:Date Diff Results in Oracle

Note for SQL Server users: SQL Server is subtracting the year the employee was born from the year (s)he was hired. This does not give us an accurate age. We'll fix this in an upcoming exercise.

Code Sample:

AdvancedSelects/Demos/Functions-Date2.sql
-- Find the Birth month for every employee

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

/******************************
Oracle
******************************/
SELECT FirstName, LastName, TO_CHAR(BirthDate,'MONTH') AS BirthMonth
FROM Employees
ORDER BY TO_NUMBER(TO_CHAR(BirthDate,'MM'));

/******************************
MySQL
******************************/
SELECT FirstName, LastName, DATE_FORMAT(BirthDate, '%M') AS BirthMonth
FROM Employees
ORDER BY EXTRACT(MONTH FROM BirthDate);

The above SELECT statement will return the following results:Date Part Results

Data Manipulation Functions

Duration: 10 to 20 minutes.

In this exercise, you will practice using data manipulation functions.

  1. Create a report that shows the units in stock, unit price, the total price value of all units in stock, the total price value of all units in stock rounded down, and the total price value of all units in stock rounded up. Sort by the total price value descending.
  2. SQL SERVER AND MYSQL USERS ONLY: In an earlier demo, you saw a report that returned the age of each employee when hired. That report was not entirely accurate as it didn't account for the month and day the employee was born. Fix that report, showing both the original (inaccurate) hire age and the actual hire age. The result will look like this.Functions Results
  3. Create a report that shows the first and last names and birth month (as a string) for each employee born in the current month.
  4. Create a report that shows the contact title in all lowercase letters of each customer contact.

Solution:

AdvancedSelects/Solutions/Functions.sql
/******************************
SQL Server
******************************/
SELECT UnitsInStock, UnitPrice,
	UnitsInStock * UnitPrice AS TotalPrice,
	FLOOR(UnitsInStock * UnitPrice) AS TotalPriceDown,
	CEILING(UnitsInStock * UnitPrice) AS TotalPriceUp
FROM Products
ORDER BY TotalPrice DESC;

SELECT DATEDIFF(day,BirthDate,HireDate)/365.25 AS HireAgeAccurate,
	DATEDIFF(year,BirthDate,HireDate) AS HireAgeInaccurate
FROM Employees;

SELECT FirstName, LastName, DATENAME(month,BirthDate) AS BirthMonth
FROM Employees
WHERE DATEPART(month,BirthDate) = DATEPART(month,GETDATE());

SELECT LOWER(ContactTitle) AS Title
FROM Customers;

/******************************
Oracle
******************************/
SELECT UnitsInStock, UnitPrice,
	UnitsInStock * UnitPrice AS TotalPrice,
FLOOR(UnitsInStock * UnitPrice) AS TotalPriceDown,
CEIL(UnitsInStock * UnitPrice) AS TotalPriceUp
FROM Products
ORDER BY TotalPrice DESC;

SELECT  FLOOR((HireDate - BirthDate)/365.25) AS HireAgeInAccurate,
	(HireDate - BirthDate)/365.25 AS HireAgeAccurate
FROM Employees; 


SELECT FirstName, LastName, TO_CHAR(BirthDate,'MONTH') AS BirthMonth
FROM Employees
WHERE TO_CHAR(BirthDate,'MM') = TO_CHAR(SYSDATE,'MM');

SELECT LOWER(ContactTitle) AS Title
FROM Customers;

/******************************
MySQL
******************************/
SELECT UnitsInStock, UnitPrice,
	UnitsInStock * UnitPrice AS TotalPrice,
	FLOOR(UnitsInStock * UnitPrice) AS TotalPriceDown,
	CEILING(UnitsInStock * UnitPrice) AS TotalPriceUp
FROM Products
ORDER BY TotalPrice DESC;

SELECT (TO_DAYS(HireDate)-TO_DAYS(BirthDate))/365.25 AS HireAgeAccurate,
	YEAR(HireDate)-YEAR(BirthDate) AS HireAgeInaccurate
FROM Employees;

SELECT FirstName, LastName, DATE_FORMAT(BirthDate, '%M') AS BirthMonth
FROM Employees
WHERE EXTRACT(MONTH FROM BirthDate) = EXTRACT(MONTH FROM NOW());

SELECT LOWER(ContactTitle) AS Title
FROM Customers;