Relational Databases &

Datawarehousing
SQL: basic concepts revisited
Introduction
SQL: basic concepts revisited - Introduction

Overview
• (Microsoft) SQL
– Working with 1 table: SELECT, Statististical functions,
GROUP BY
– Working with > 1 tables: JOIN, UNION, subquery's,
correlated subquery's
– Modifying data: insert, update, delete
– Views
SQL: basic concepts revisited - Introduction

SQL Server
• SQL Server:
– Management
• Installation, configuration and security of SQL Server.
• Database creation
• Database management: backup, restore, ...
• Use SQL Server Management Studio
SQL: basic concepts revisited - Introduction

Writing queries
• Use SQL Server Management Studio
 SQL: basic concepts revisited - Introduction

Help!
• The help menu offers online help about Microsoft SQL

How do I write a correct SELECT statement? Help!

‘Northwind’ DB
diagram
 SQL: basic concepts revisited - Introduction

SQL - standards and dialects

• Definition
– Relational data language for relational database systems.
– Non procedural language

Year Name
• Standards Comments
1986 SQL-86 First formalized by ANSI.
1989 SQL-89 Minor revision that added integrity constraints, adopted as FIPS 127-1.
1992 SQL-92 Major revision (ISO 9075), Entry Level SQL-92 adopted as FIPS 127-2.
Added regular expression matching, recursive queries (e.g. transitive closure), triggers, support for procedural and control-of-flow statements, non-scalar types, and some
1999 SQL:1999
object-oriented features (e.g. structured types). Support for embedding SQL in Java (SQL/OLB) and vice versa (SQL/JRT).

2003 SQL:2003 Introduced XML-related features (SQL/XML), window functions, standardized sequences, and columns with auto-generated values (including identity-columns).

ISO/IEC 9075-14:2006 defines ways that SQL can be used with XML. It defines ways of importing and storing XML data in an SQL database, manipulating it within the
2006 SQL:2006 database, and publishing both XML and conventional SQL-data in XML form. In addition, it lets applications integrate queries into their SQL code with XQuery, the XML
Query Language published by the World Wide Web Consortium (W3C), to concurrently access ordinary SQL-data and XML documents.[40]

2008 SQL:2008 Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers. Adds the TRUNCATE statement. [41]
2011 SQL:2011 Adds temporal data definition and manipulation.
2016 SQL:2016 Adds row pattern matching, polymorphic table functions, JSON.
 SQL: basic concepts revisited - Introduction

Why Microsoft SQL Server?

Source: [Link]
SQL: basic concepts revisited - Introduction

SQL - Overview
• SQL consists of 3 sub languages
– Data Definition Language (DDL)
• creation of a database, defining database objects (tables, stored procedures,
views,…)
• CREATE, ALTER, DROP
– Data Manipulation Language (DML)
• Querying and manipulating data in a database
• SELECT, INSERT, UPDATE, DELETE
– Data Control Language (DCL)
• Data security and authorisation
• GRANT, REVOKE, DENY
SQL: basic concepts revisited - Introduction

SQL - Overview
• Additional language elements: operators, functions,
control of flow (dialects!)
SELECT
SQL: basic concepts revisited - Select

DML – Consulting data

• Consulting one table
– Basic form
– SELECT clause
– WHERE clause
– Row formatting
– Statistical functions
– Grouping
• Consulting >1 table
SQL: basic concepts revisited - Select

Basic form of SELECT statement

• SELECT for consulting one table
SELECT [ALL | DISTINCT] {*|expression [, expression ...]}
FROM table name
[WHERE conditions(s)]
[GROUP BY column name [, column name ...]
[HAVING conditions(s)]
[ORDER BY {column name |seq nr}{ASC|DESC}[,...]

– SELECT clause: specifies the columns to show in the ouput.

DISTINCT filters out duplicate lines
– FROM clause: table name
– WHERE clause : filter condition on individual lines in the output
– GROUP BY : grouping of data
– HAVING clause : filter condition on groups
– ORDER BY clause : sorting
SQL: basic concepts revisited - Select

SELECT
• SELECT clause: specification of the columns
– All columns from table: use *
• SELECT *
– Specific columns: use columns names or expression
• SELECT column1 , column2, column3*column4, …
SQL: basic concepts revisited - Select

SELECT
• Example: Show all data of all products
SELECT *
FROM Products
SQL: basic concepts revisited - Select

SELECT
• Example: Show for all a products productID,
name and unitprice
SELECT productid, productname, unitprice
FROM Products
SQL: basic concepts revisited - Select

SELECT … WHERE
• WHERE clause
– Specification of conditions for individual rows
• Example: Show productid, productname and unitprice of
all products from category 1
SELECT productid, productname, unitprice
FROM Products
WHERE categoryID = 1
SQL: basic concepts revisited - Select

SELECT … WHERE
• Use of literals
– Numeric values: ... WHERE categoryID = 1
– Alphanumeric values: ... WHERE productName = 'Chai'
– Dates: ... WHERE orderDate = '4/15/2018' (15th april 2018)
SQL: basic concepts revisited - Select

SELECT … WHERE
• Conditions for rows
– Comparison operators
– Wildcards
– Logical operators
– Interval of specific values
– List of values
– Unknown values
– Use brackets () to overrule priority rules and enhance readability
SQL: basic concepts revisited - Select

SELECT … WHERE
• Comparison operators
– =, >, >=, <, <=, <>
– Example: Show productID, name, units in stock for all products
with less than 5 units in stock
SELECT productid, productname, unitprice
FROM Products
WHERE UnitsInStock < 5

– Example: Show productID, name, units in stock for all products

for which the name starts with A
SELECT productid, productname, unitprice
FROM Products
WHERE productname >= 'A' AND productname < 'B'
SQL: basic concepts revisited - Select

SELECT … WHERE
• Wildcards (searching for patterns)
– Always in combination with operator LIKE, NOT LIKE
– Wildcard symbols:
• %  arbitrary sequence of 0, 1 or more characters
• _  1 character
• [ ]  1 character in a specified range
• [^]  every character not in the specified range
– Example: Show productID and name of the products for which
the second letter is in the range a-k
SELECT productid, productname
FROM Products
WHERE productname LIKE '_[a-k]%'
SQL: basic concepts revisited - Select

SELECT … WHERE
• Logical operators
– OR, AND, NOT (ascending priority)
– Example
SELECT ProductID, ProductName, SupplierID, UnitPrice
FROM Products
WHERE ProductName LIKE 'T%' OR (ProductID = 46 AND UnitPrice > 16.00)
SQL: basic concepts revisited - Select

SELECT … WHERE
• Values in an interval
– BETWEEN, NOT BETWEEN
– Example: Select the products (name and unit price) for which the
unit price is between 10 and 15 euro (boundaries included)
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice BETWEEN 10 AND 15
SQL: basic concepts revisited - Select

SELECT … WHERE
• List of values
– IN, NOT IN
– Example: Show ProductID, ProductName and SupplierID of the
products supplied by suppliers with ID 1, 3 or 5
SELECT ProductID, ProductName, SupplierID
FROM Products
WHERE SupplierID in (1,3,5)
SQL: basic concepts revisited - Select

SELECT … WHERE
• Test for unknow (or empty) values
– IS NULL, IS NOT NULL
• NULL values occur if no value has been specified for a column when creating a
record
• A NULL is not equal to 0 (for numerical values), blank or empty string
(for character values)!
• NULL fields are considered as equal (for e.g. testing with DISTINCT)
• If a NULL value appears in an expression the result is always NULL
– Example: Select suppliers from an unknown region

SELECT CompanyName, Region

FROM Suppliers
WHERE Region IS NULL
SQL: basic concepts revisited - Select

SELECT … WHERE
• Be careful with NULL!
SELECT CompanyName, Region SELECT CompanyName, Region
FROM Suppliers FROM Suppliers
WHERE Region <> 'OR' WHERE Region <> 'OR' OR Region IS NULL
SQL: basic concepts revisited - Select

SELECT + formatting results

• Sorting data
• Elimination of duplicates
• Change column name in output
• Calculated output columns
• Comments
– /* comments */
– -- comments (rest of line is comment)
SQL: basic concepts revisited - Select

SELECT … ORDER BY
• Sorting of data
– ORDER BY clause
• Sorting according to one or more sorting criteria
• Each sorting criterion can be specified by either a column name, an expression or a
sequence number that corresponds to the order of columns in the SELECT clause
(starting from 1)
• Sorting criteria are evaluated left to right
• Default sort occurs in ascending order (ASC: default), if descending order is required
specify DESC after the criterion
– Example: Show an alphabetic list of product names

SELECT ProductName
FROM Products
ORDER BY ProductName -- or ORDER BY 1
SQL: basic concepts revisited - Select

SELECT … ORDER BY
– Example: Show productid, name, categoryID of the products
sorted by categoryID. If the category is the same products with
the highest price appear first.
SELECT ProductID, ProductName, CategoryID, UnitPrice
FROM Products
ORDER BY CategoryID, UnitPrice DESC
SQL: basic concepts revisited - Select

SELECT DISTINCT/ALL
• Uniqueness of rows
• DISTINCT filters out duplicates lines in the output
– ALL (default) shows all rows, including duplicates
– Example: Show all suppliers that supply products
SELECT SupplierID SELECT DISTINCT SupplierID
FROM Products FROM Products
ORDER BY SupplierID ORDER BY SupplierID
SQL: basic concepts revisited - Select

Exercises
-- 1. Give the names of all products containing the word 'bröd' or with a name of 7
characters.

-- 2. Show the productname and the reorderlevel of all products with a level between 10 and
50 (boundaries included)
SQL: basic concepts revisited - Select

Exercises – Solutions
-- 1. Give the names of all products containing the word 'bröd' or with a name of 7
characters.
SELECT ProductName
FROM Products
WHERE ProductName LIKE '%bröd%' or ProductName LIKE '_______'

-- 2. Show the productname and the reorderlevel of all products with a level between 10 and
50 (boundaries included)
SELECT ProductName, ReorderLevel
FROM Products
WHERE ReorderLevel BETWEEN 10 AND 50
SQL: basic concepts revisited - Select

SELECT and aliases

• Column names in output
– Default : column title = name of column in table; calculated
columns are unnamed
– The AS keyword allows you to give a column a new title
• Remark: the new column name can only be used in ORDER BY
(not in WHERE, HAVING, GROUP BY)
– Example: Select ProductID, ProductName of the products.

SELECT ProductID AS ProductNummer, ProductName AS 'Name Product'

FROM Products
SQL: basic concepts revisited - Select

SELECT with calculated results

• Calculated result columns
– Arithmetic operators : +, -, /, *
– Example: Give name and inventory value of the products
SELECT ProductName, UnitPrice * UnitsInStock AS InventoryValue
FROM Products
SQL: basic concepts revisited - Select

SELECT and use of functions

• Functies
– String functions: left, right, len, ltrim, rtrim, substring, replace, ...
– DateTime functions: DateAdd, DateDiff, DatePart, Day, Month, Year
• GETDATE(): returns current date and time in DATETIME format specified by MS-SQL Server.
– Arithmetic functions: round, floor, ceiling, cos, sin, ...
– Aggregate functions: AVG, SUM, ...
– ISNULL: replaces NULL values with specified value
– Reference document: [Link]
SELECT ISNULL(UnitPrice, 10.00)
FROM Products
SQL: basic concepts revisited - Select

SELECT and data type conversion

• Implicit conversions
– Sometimes possible
– Example: UnitsInStock * 0.5
UnitInStock (int) is automatically converted to decimal
SQL: basic concepts revisited - Select

SELECT and data type conversion

• Explicit conversions
– CAST (<value expression> AS <data type>)
– Example: PRINT CAST(-25.25 AS INTEGER) -> -25
– CONVERT (<data type, <expression> [, <style>])
SELECT CONVERT(VARCHAR, getdate(), 106) As Today

– FORMAT
SELECT *
FROM Orders
WHERE FORMAT(ShippedDate,'dd/MM/yyyy')='10/07/2020'
SQL: basic concepts revisited - Select

String functions
SQL SERVER

concatenate SELECT CONCAT(Address,' ',City) FROM Employees

SELECT Address + ' ' + City FROM Employees
substring SELECT SUBSTRING(Address, 1, 5) FROM Employees

left part SELECT LEFT(Address,5) FROM Employees

right part SELECT RIGHT(Address,5) FROM Employees
length SELECT LEN(Address) FROM Employees
lowercase SELECT LOWER(Address) FROM Employees
uppercase SELECT UPPER(Address) FROM Employees
remove spaces left and right SELECT RTRIM(LTRIM(Address)) FROM Employees
SQL: basic concepts revisited - Select

Date / time functions

SQL SERVER

System date SELECT GETDATE()

Add years, months, days to date SELECT DATEADD (year, 2, GETDATE())

SELECT DATEADD (month, 2, GETDATE())
SELECT DATEADD (day, 2, GETDATE())
Number of years, months, days SELECT DATEDIFF(day,BIRTHDATE,GETDATE()) As NumberOfDays
between 2 dates FROM Employees
Day of the month SELECT DAY(GETDATE())

Month of the year SELECT MONTH(GETDATE())

Year SELECT YEAR(GETDATE())

SQL: basic concepts revisited - Select

Date / time: examples

• [Link]
us/library/[Link]
SELECT GETDATE()

SELECT GETUTCDATE()

SELECT SYSDATETIME()

SELECT SYSDATETIMEOFFSET()
SQL: basic concepts revisited - Select

Arithmetic functions
SQL SERVER

Absolute value SELECT ABS(-10) -- 10

Round to give number of decimals SELECT ROUND(10.75, 1) -- 10.8

Largest integer thas is lower SELECT FLOOR(10.75) -- 10

Smallest integer that is higher SELECT CEILING(10.75) -- 11

SQL: basic concepts revisited - Select

The case function

• Simple CASE expression:
SELECT City, Region,
CASE region
WHEN 'OR' THEN 'West'
WHEN 'MI' THEN 'North'
ELSE 'Elsewhere'
END As RegionElaborated
FROM Suppliers
SQL: basic concepts revisited - Select

The case function

• Searched CASE expression:
SELECT CONVERT(varchar(20), ProductName) As 'Shortened ProductName',
CASE
WHEN UnitPrice IS NULL THEN 'Not yet priced'
WHEN UnitPrice < 10 THEN 'Very Reasonable Price'
WHEN UnitPrice >= 10 and UnitPrice < 20 THEN 'Affordable'
ELSE 'Expensive!'
END AS 'Price Category'
FROM Products
ORDER BY UnitPrice
SQL: basic concepts revisited - Select

SELECT and strings

• String operator: concatenate
SELECT STR(ProductID) + ',' + ProductName AS Product
FROM Products

• Use of literal text (literals)

SELECT ProductName, '$' As Currency, Unitprice
FROM Products
SQL: basic concepts revisited

GROUP BY
and
statististical functions
SQL: basic concepts revisited – Group by

Statistical functions
• Statistical functions (aka aggregate functions)
– SQL has 5 standard functions
• SUM(expression): sum
• AVG(expression): average
• MIN(expression): minimum
• MAX(expression): maximum
• COUNT(*|[DISTINCT] column name): count

– These functions give one answer per column (or group: see further) and can
never be used in a where-clause
SQL: basic concepts revisited – Group by

sum and average

• SUM
– Returns the sum of all (numeric) values in a column
– Can only be used with numeric columns
– Example: Give the total stock value
SELECT SUM(UnitsInStock * UnitPrice) as InventoryValue
FROM Products
SQL: basic concepts revisited – Group by

sum and average

• AVG
– Returns the average of NOT NULL numeric values in a columns
– Can only be used with numeric columns
– Example: What is the average number of products in stock?
SELECT AVG(UnitsInStock) AS AverageStock
FROM Products
SQL: basic concepts revisited – Group by

Count the number of rows

• COUNT
– Returns the number of rows, or a number of NOT NULL values in a
column
• COUNT(*) – counts the number of rows in a SELECT
• Example: Count the number of products (= all rows)

• COUNT (column name) – counts the number of not empty fields in a column
• Example: Countasthe
SELECT COUNT(*) number of NOT NULL values in column CategoryID
NumberOfProducts
FROM Products

SELECT COUNT(CategoryID) as NumberOfCategoryID

FROM Products
SQL: basic concepts revisited – Group by

Count the number of rows

• COUNT
– Returns the number of rows, or a number of NOT NULL values in
a column
• COUNT(DISTINCT column name) - count the number of different NOT NULL
values in column producttypeid
• Example: Count the number of different NOT NULL values in column
CategoryID
SELECT COUNT(DISTINCT CategoryID) as NumberOfCategoryID
FROM Products
SQL: basic concepts revisited – Group by

minimum and maximum

• MIN and MAX
– Returns the smallest and largest value in a column
– Applicable for both numeric, alphanumeric and datetime fields
– Example: What is the cheapest and most expensive unit price?
SELECT MIN(UnitPrice) AS Minimum, MAX(UnitPrice) AS Maximum
FROM Products
SQL: basic concepts revisited – Group by

Statistical functions - Remark

• Since a statistical function returns only 1 result, either all
expressions in the SELECT clause have to contain a statistical
function, or none!
This is slightly different if you use group by (see further).
• Statistical functions do not take into account NULL values.
Exception : COUNT(*) also counts rows with NULL values.
SQL: basic concepts revisited – Group by

Transact-SQL dialect
• Some statistical functions only exists in MS Transact-SQL
– STDEV: standard deviation of column values
– VAR: variance of column values
– TOP:
• Example: Select the top 5 of the cheapest products
SELECT TOP 5 ProductID, UnitPrice
FROM Products
ORDER BY UnitPrice

• Example: Select the 5 most expensive products

SELECT TOP 5 ProductID, UnitPrice
FROM Products
ORDER BY UnitPrice DESC
SQL: basic concepts revisited – Group by

Grouping with GROUP BY

• Grouping – Statistical functions per group.
– GROUP BY clause :
• The table is divided into groups of rows with common characteristics.
• Per group one unique row!
• For each group statistical functions can be applied.
• The column names (or grouping criteria) mentioned in the GROUP BY clause
can also appear in the SELECT clause
SQL: basic concepts revisited – Group by

Grouping with GROUP BY

• Some examples
– Show the number of products per category
SELECT CategoryID, COUNT(ProductID) As NumberOfProductsPerCategory
FROM Products
GROUP BY CategoryID

– Show per category the number of products with

UnitPrice > 15

SELECT CategoryID, COUNT(ProductID) As NumberOfProductsPerCategory

FROM Products
WHERE UnitPrice > 15
GROUP BY CategoryID
SQL: basic concepts revisited – Group by

Filter on groups with HAVING

• HAVING clause
– Select or reject groups based on group characteristics
– Some examples:
• Show the categories that contain more than 10 products
SELECT CategoryID, COUNT(ProductID) As NumberOfProductsPerCategory
FROM Products
GROUP BY CategoryID
HAVING COUNT(ProductID) > 10

• Show the categories that contain more than 10 products with UnitPrice > 15
SELECT CategoryID, COUNT(ProductID) As NumberOfProductsPerCategory
FROM Products
WHERE UnitPrice > 10
GROUP BY CategoryID
HAVING COUNT(ProductID) > 10
SQL: basic concepts revisited – Group by

WHERE vs HAVING
• Remarks
– WHERE vs HAVING
• WHERE – works on individual rows
• HAVING – works on groups / conditions on aggregation functions
– Statistical functions can only be used in SELECT, HAVING, ORDER
BY - not in WHERE, GROUP BY
– If statistical functions appear in the SELECT, then all items in the
SELECT-list have to be either statistical functions or group
identifications
SELECT CategoryID, MIN(UnitPrice) As Minimum
FROM Products
SQL: basic concepts revisited – Group by

Exercises
-- 1. Count the amount of products (columnname 'amount of products'), AND the amount of products in stock (=
unitsinstock not empty) (columnname 'Units in stock')
-- 2. How many employees have a function of Sales Representative (columnname 'Number of Sales
Representative')?
-- 3. Give the date of birth of the youngest employee (columnname 'Birthdate youngest') and the oldest
(columnname 'Birthdate oldest').
-- 4. What's the number of employees who will retire (at 65) within the first 20 years?
-- 5. Show a list of different countries where 2 of more suppliers are from. Order alphabeticaly.
-- 6. Which suppliers offer at least 5 products with a price less than 100 dollar? Show supplierId and the
number of different products.
-- The supplier with the highest number of products comes first.
SQL: basic concepts revisited

Working with more

than 1 table: JOIN
SQL: basic concepts revisited – Join

Consult > 1 table

• JOIN • Set Operators
– Inner join • Common Table
– Outer join Expressions
– Cross join
• UNION
• Subquery's
– Simple nested query's
– Correlated subquery's
– Operator EXISTS
SQL: basic concepts revisited – Join

JOIN
• Select columns from several tables
– JOIN keyword : specifies which tables have to be joined and how
• Inner join
• Outer join
• Cross join
• ON keyword : specifies the JOIN condition
– Produces 1 result set, joining the rows of both tables
– Basic form (ANSI JOIN (SQL-92) <-> Old style join)

SELECT expression SELECT expression

FROM table1 JOIN table2 ON condition FROM table1, table2 [, table3...]
[JOIN table2 ON condition...] WHERE condition(s)
SQL: basic concepts revisited – Join

INNER JOIN
• Joins rows from one table with rows from another table
based on common criteria in the corresponding tables.
• The relation between the fields in the corresponding
tables is expressed through:
– = (equi-join)
– <
– >
– <>
– >=
SQL: basic concepts revisited – Join

INNER JOIN
• Example of equi-join
– Give the productID, productName and CategoryName for each
product
• ANSI JOIN (SQL-92)
SELECT ProductID, ProductName, CategoryName
FROM Products JOIN Categories
ON [Link] = [Link]

• OR "old style join"

SELECT ProductID, ProductName, CategoryName
FROM Products, Categories
WHERE [Link] = [Link]
SQL: basic concepts revisited – Join

Aliases
• USE tables aliasses (via 'AS' or blank)
– SQL-92
SELECT ProductID, ProductName, CategoryName
FROM Products p JOIN Categories c
ON [Link] = [Link]

– OR "old style join"

SELECT ProductID, ProductName, CategoryName
FROM Products p, Categories c
WHERE [Link] = [Link]
SQL: basic concepts revisited – Join

Remarks
• If the same column name is used in several tables in a query, then
each column name has to be preceeded by the table name or its
alias.
• Inner joins only return rows that meet the ON condition.
• If you omit (forget) the where clause in the old style join all
combinations are returned
= CROSS JOIN (= carthesian product) (see further)
SQL: basic concepts revisited – Join

INNER JOIN of > 2 tables

• JOIN of more than 2 tables
– Example: Give for each product the ProductName, the
CategoryName and the CompanyName of the supplier
– SQL-92 :
SELECT [Link], [Link], [Link], [Link]
FROM Products p JOIN Categories c ON [Link] = [Link]
JOIN Suppliers s ON [Link] = [Link]
– Old style join
SELECT [Link], [Link], [Link], [Link]
FROM Products p, Categories c, Suppliers s
WHERE [Link] = [Link] AND [Link] = [Link]
SQL: basic concepts revisited – Join

INNER JOIN of a table with itself

• Example: Show all employees and the name of whom
they have to report to
SELECT [Link], [Link] + ' ' + [Link] As Employee,
[Link] + ' ' + [Link] As ReportsTo
FROM Employees e1 JOIN Employees e2
ON [Link] = [Link]
SQL: basic concepts revisited – Join

OUTER JOIN
• Returns all records from 1 table, even if there is no
corresponding record in the other table
• 3 types of an outer join
– LEFT OUTER JOIN
• Returns all rows of the first table in the FROM clause(SQL-92)
– RIGHT OUTER JOIN
• Returns all rows of the second table in the FROM clause(SQL-92)
– FULL OUTER JOIN
• Returns all rows of the first and the second table in the FROM clause(SQL-92)
even if there is no corresponding record in the other table
SQL: basic concepts revisited – Join

OUTER JOIN
SQL: basic concepts revisited – Join

LEFT OUTER JOIN

• Example: Show the number of shippings per Shipper
SELECT [Link], [Link], COUNT(OrderID) As NumberOfShippings
FROM Shippers s JOIN Orders o
ON [Link] = [Link]
GROUP BY [Link], [Link]

SELECT [Link], [Link], COUNT(OrderID) As NumberOfShippings

FROM Shippers s LEFT JOIN Orders o
ON [Link] = [Link]
GROUP BY [Link], [Link]
SQL: basic concepts revisited – Join

RIGHT OUTER JOIN

• Example: Give the employees to whom no one reports
SELECT [Link] + ' ' + [Link] As Employee,
[Link] + ' ' + [Link] As ReportsTo
FROM Employees e1 RIGHT JOIN Employees e2
ON [Link] = [Link]
WHERE [Link] + ' ' + [Link] IS NULL
SQL: basic concepts revisited – Join

FULL OUTER JOIN

• FULL OUTER JOIN is the combination
(=UNION) of
LEFT and RIGHT OUTER JOIN
SELECT [Link], [Link], [Link]
FROM Shippers s FULL OUTER JOIN Orders o
ON [Link] = [Link]
SQL: basic concepts revisited – Join

CROSS JOIN
• In a cross join the number of rows in the result set equals
the number of rows in the first table multiplied by the
number of rows in the second table
• Application: Generate all combinations
• Example: Make a schedule in which each employee
should contact each customer
SELECT [Link], [Link] + ' ' + [Link], [Link],
[Link], [Link], [Link], [Link]
FROM Employees e CROSS JOIN Customers c
SQL: basic concepts revisited

SET OPERATORS:
UNION – INTERSECT -
EXCEPT
SQL: basic concepts revisited - Union

UNION
• A UNION combines the result of 2 or more queries
– Basic form SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...
ORDER BY ...
– Rules
• Both SELECTs have to contain an equal number of columns
• Corresponding columns from both SELECTs should have compatible data types
• The columns names or aliases from the first SELECT or shown
• The result set does not contain duplicates. To keep duplicates use UNION ALL
• At the end an ORDER BY can be added.
Column names or expressions can't be used in the ORDER BY if they differ
between the two SELECTs. In this case use column numbers for sorting.
SQL: basic concepts revisited - Union

UNION
• Example: Give an overview of all employees (lastname
and firstname, city and postal code) and all customers
(name, city and postal code)
SELECT LastName + ' ' + FirstName as Name, City, Postalcode
FROM Employees
UNION
SELECT CompanyName, City, Postalcode
FROM Customers
SQL: basic concepts revisited - Intersect

INTERSECT
• Which records are in the intersection?
SELECT City, Country FROM Customers
INTERSECT
SELECT City, Country FROM Suppliers
SQL: basic concepts revisited - Except

EXCEPT
• The EXCEPT operator subtracts a result set from another
result set.
– Example: Which products have never been ordered?
SELECT ProductID
FROM Products
EXCEPT
SELECT ProductID
FROM OrderDetails
SQL: basic concepts revisited

Exercises
-- 1. Which suppliers (SupplierID and CompanyName) deliver Dairy Products?
-- 2. Give for each supplier the number of orders that contain products of that supplier.
-- Show supplierID, companyname and the number of orders.
-- Order by companyname.
-- 3. What’s for each category the lowest UnitPrice? Show category name and unit price.
-- 4. Give for each ordered product: productname, the least (columnname 'Min amount ordered') and the most
ordered (columnname 'Max amount ordered'). Order by productname.
-- 5. Give a summary for each employee with orderID, employeeID and employeename.
-- Make sure that the list also contains employees who don’t have orders yet.
SQL: basic concepts revisited

Exercises – Solutions
-- 1. Which suppliers (SupplierID and CompanyName) deliver Dairy Products?
SELECT DISTINCT [Link], [Link]
FROM Suppliers s JOIN Products p ON [Link] = [Link]
JOIN Categories c ON [Link] = [Link]
WHERE [Link] LIKE '%Dairy%'

-- 2. Give for each supplier the number of orders that contain products of that supplier.
-- Show supplierID, companyname and the number of orders.
-- Order by companyname.
select [Link], [Link], count(DISTINCT [Link]) As NrOfOrders
from Suppliers s join Products p ON [Link] = [Link]
JOIN OrderDetails od ON [Link] = [Link]
GROUP BY [Link], [Link]
ORDER BY [Link]

-- 3. What’s for each category the lowest UnitPrice? Show category name and unit price.
SELECT [Link], MIN([Link]) As 'Minimum UnitPrice'
FROM Products p join Categories c ON [Link] = [Link]
GROUP BY [Link]
SQL: basic concepts revisited

Exercises – Solutions
-- 4. Give for each ordered product: productname, the least (columnname 'Min amount ordered') and the most
ordered (columnname 'Max amount ordered'). Order by productname.
SELECT [Link], MIN([Link]) As 'Min amount ordered', Max([Link]) As 'Max amount ordered'
FROM Products p join OrderDetails od ON [Link] = [Link]
GROUP BY [Link]
ORDER BY [Link]

-- 5. Give a summary for each employee with orderID, employeeID and employeename.
-- Make sure that the list also contains employees who don’t have orders yet.
SELECT [Link], [Link] + ' ' + [Link] As 'Name', [Link]
FROM Employees e left join Orders o on [Link] = [Link]