Webucator's Free SQL Tutorial

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:

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

## Code Sample:

```-- 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:

```-- 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:

```-- 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:

```/*
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:

### 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:

```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.

## Solution:

```/******************************
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:

```-- Find the Number of Employees

SELECT COUNT(*) AS NumEmployees
FROM Employees;```

Returns 9.

## Code Sample:

```-- 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:

```-- Find the Average Unit Price of Products

SELECT AVG(UnitPrice) AS AveragePrice
FROM Products;```

Returns 28.8663.

## Code Sample:

```-- 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.

### 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:

```-- 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:

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:

```/*
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:

### Order of Clauses

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

## Code Sample:

```/*
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:

### 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:

```/*
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:

```/*
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.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.

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:

```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;

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:

```/*
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):

## Code Sample:

```/*
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;

/******************************
******************************/
/******************************
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):

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:

```/*
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:

## Code Sample:

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

/******************************
SQL Server and MySQL
******************************/
FROM Customers;

/******************************
Oracle
******************************/
FROM Customers;```

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

### 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:

```-- 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:

And like this 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:

```-- 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:

## 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.
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:

```/******************************
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;
```