SQL Select Part 3

1
SQL Select (Part 3)

• Multi-Table Joins

2
Multi-Table Select

• Join combining columns (fields) from multiple
  tables
• Tables can be real or virtual
• Technically, a table can be joined to itself
  multiple times. It is known as a self-join.

3
Select Syntax

• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY

• fields / functions
• tables
• row conditions
• fields
• aggregation conditions
• fields

4
Naming Convention

• Server.Database.Owner.Table.Field
• mis325.dbo.tblStudent.FirstName
• tblEmployee.Phone
• tblCustomer.Phone
• UTadmin.Personnel.AW8137.tblEmployee.Gender

5
Implicit Inner Join

• SELECT
• FROM
• WHERE
• AND
• ORDER BY

• fields
• tblX, tblY
• tblX.fieldX tblY.fieldY
• other row conditions
• fields

6
Inner Join
Employees
Paychecks
Bad Accounting
New Employees
Old Employees
7
Referential Integrity
Employees
Paychecks
New Employees
Old Employees
8
Database Model
10,000 Employees
500,000 Paychecks
9
Example

• Show the employee concatenated first and last
  names, the paycheck date and the net amount for
  all paychecks.
• Sort the rows by ascending last and first name
  and by descending date (i.e., most recent first)

10
Paid Employees and their Paychecks

• SELECT tblEmployee.FirstName ' '
• tblEmployee.LastName AS Employee,
• tblPaycheck.PayDate,
  tblPaycheck.NetAmount
• FROM tblEmployee, tblPaycheck
• WHERE tblEmployee.EID tblPaycheck.EID
• ORDER BY tblEmployee.LastName,
• tblEmployee.FirstName,
• tblPaycheck.PayDate DESC

Note Table name is only necessary when field
name is not unique within the query
11
Paid Employees and their Paychecks

• SELECT FirstName ' ' LastName AS Employee,
• PayDate, NetAmount
• FROM tblEmployee, tblPaycheck
• WHERE tblEmployee.EID tblPaycheck.EID
• ORDER BY LastName, FirstName, PayDate DESC

Note Table name is only necessary when field
name is not unique within the query
12
Explicit Inner Join

• SELECT
• FROM
• ON
• WHERE
• ORDER BY

• fields
• tblX INNER JOIN tblY
• tblX.fieldX tblY.fieldY
• row conditions
• fields

Note INNER JOIN can be abbreviated as JOIN in
SQL Server
13
Paid Employees and their Paychecks

• SELECT FirstName ' ' LastName AS Employee,
• PayDate, NetAmount
• FROM tblEmployee INNER JOIN tblPaycheck
• ON tblEmployee.EID tblPaycheck.EID
• ORDER BY LastName, FirstName, PayDate DESC

14
Inner Join
INNER
15
Left Outer Join
16
Right Outer Join
17
Full Outer Join
18
Example

• Show the employee concatenated first and last
  names, the paycheck date and the net amount.
• Show ALL employees, even if they have never been
  paid.
• Sort the rows by ascending last and first name
  and by descending date (i.e., most recent first).

19
Database Model
10,000 Employees 9,900 Old Folks 100 New Hires
500,000 Paychecks
20
All Employees and their Paychecks

• SELECT FirstName ' ' LastName AS Employee,
• PayDate, NetAmount
• FROM tblEmployee LEFT OUTER JOIN tblPaycheck
• ON tblEmployee.EID tblPaycheck.EID
• ORDER BY LastName, FirstName, PayDate DESC

How does this differ from inner join?
21
All Employees and their Paychecks

• SELECT FirstName ' ' LastName AS Employee,
• PayDate, NetAmount
• FROM tblPaycheck RIGHT OUTER JOIN tblEmployee
• ON tblPaycheck.EID tblEmployee.EID
• ORDER BY LastName, FirstName, PayDate DESC

Note LEFT and RIGHT refer to FROM sequence,
not to the parent/child characteristics
22
Employees With No Paychecks

• Show the concatenated first and last names of the
  employees who have never been paid.
• Sort the rows by last name and first name.

23
Employees With No Paychecks
Employees
Paychecks
24
Employees With No Paychecks

• SELECT FirstName ' ' LastName AS Employee
• FROM tblEmployee LEFT OUTER JOIN tblPaycheck
• ON tblEmployee.EID tblPaycheck.EID
• WHERE PayDate IS NULL
• ORDER BY LastName, FirstName

Note The WHERE clause eliminates the employees
who actually have paychecks
25
2008 Paychecks

• Show the employee first name, last name, EID,
  check number, pay date and gross amount for all
  paychecks issued during 2008.
• Sort the rows by last name, first name and
  descending pay date (i.e., new to old).

26
2008 Paychecks

• SELECT
• FROM
• WHERE
• ORDER BY

FirstName, LastName, tblEmployee.EID, CheckNumber,
PayDate, GrossAmount tblEmployee INNER JOIN
tblPaycheck ON tblEmployee.EID
tblPaycheck.EID PayDate gt '1/1/2008' AND PayDate
lt '1/1/2009' LastName, FirstName, PayDate DESC
27
2008 Paychecks - Alternative

• SELECT
• FROM
• WHERE
• ORDER BY

FirstName, LastName, tblEmployee.EID, CheckNumber,
PayDate, GrossAmount tblEmployee INNER JOIN
tblPaycheck ON tblEmployee.EID
tblPaycheck.EID YEAR(PayDate) 2008 LastName,
FirstName, PayDate DESC
28
Employee Summary

• Show the employee first name, last name, EID,
  number of paychecks and total gross earnings for
  2008.
• Sort the rows by last name and first name.

29
Employee Summary

• SELECT
• FROM
• WHERE
• GROUP BY
• ORDER BY

FirstName, LastName, tblEmployee.EID, COUNT()
AS Checks, SUM(GrossAmount) AS Total tblEmployee
INNER JOIN tblPaycheck ON tblEmployee.EID
tblPaycheck.EID YEAR(PayDate) 2008 FirstName,
LastName, tblEmployee.EID LastName, FirstName
30
Syntax Rules

• Everything in the SELECT and ORDER BY clauses
  must be grouping fields or aggregation functions
• No aggregation functions can appear in the WHERE
  clause
• Only aggregation functions should appear in the
  HAVING clause

31
Employee Summary - 40K

• Show the employee first name, last name, EID,
  number of paychecks and total gross earnings for
  2008.
• Only show the employees earning at least 40,000
  during the year.
• Sort the rows by last name and first name.

32
Employee Summary - 40K

• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY

FirstName, LastName, tblEmployee.EID, COUNT()
AS Checks, SUM(GrossAmount) AS Total tblEmployee
INNER JOIN tblPaycheck ON tblEmployee.EID
tblPaycheck.EID YEAR(PayDate) 2008 FirstName,
LastName, tblEmployee.EID SUM(GrossAmount) gt
40000 LastName, FirstName
33
Job Title Summary

• Show the job titles, the number of people having
  the job title, and the total gross amount paid to
  those individuals during 2008.
• Sort the rows by job title.

34
Job Title Summary

• SELECT
• FROM
• WHERE
• GROUP BY
• ORDER BY

JobTitle, COUNT(DISTINCT tblPaycheck.EID) AS
People, SUM(GrossAmount) AS Total tblEmployee
INNER JOIN tblPaycheck ON tblEmployee.EID
tblPaycheck.EID YEAR(PayDate)
2008 JobTitle JobTitle
35
Job Title and Pay Date Summary

• Show the same information, but summarize it by
  job and date, not just by job.
• Sort the rows by job title and descending pay
  date.

36
Job Title and Pay Date Summary

• SELECT
• FROM
• WHERE
• GROUP BY
• ORDER BY

JobTitle, PayDate, COUNT(DISTINCT
tblPaycheck.EID) AS People, SUM(GrossAmount) AS
Total tblEmployee INNER JOIN tblPaycheck ON
tblEmployee.EID tblPaycheck.EID YEAR(PayDate)
2008 JobTitle, PayDate JobTitle, PayDate DESC
37
Gender and Pay Date Summary

• Show the gender, pay date, total number of checks
  issued, and the total gross amount during 2008.
• Sort the rows by descending pay date, then gender.

38
Gender and Pay Date Summary

• SELECT
• FROM
• WHERE
• GROUP BY
• ORDER BY

Gender, PayDate, COUNT() AS Checks,
SUM(GrossAmount) AS Total tblEmployee INNER JOIN
tblPaycheck ON tblEmployee.EID
tblPaycheck.EID YEAR(PayDate) 2008 Gender,
PayDate PayDate DESC, Gender
39
Another Job Title Summary

• Show the job titles, the number of people having
  the job title, number of paychecks issued, and
  the total gross amount paid during 2008.
• Sort the rows by job title.

40
Another Job Title Summary

• SELECT
• FROM
• WHERE
• GROUP BY
• ORDER BY

JobTitle, COUNT(DISTINCT tblPaycheck.EID) AS
People, COUNT() AS Checks, SUM(GrossAmount) AS
Total tblEmployee INNER JOIN tblPaycheck ON
tblEmployee.EID tblPaycheck.EID YEAR(PayDate)
2008 JobTitle JobTitle
41
The Last Example

• Show the same information, but also compute the
  mean amount paid per employee and the mean amount
  paid per check.

42
The Last Example

• SELECT
• FROM

JobTitle, COUNT(DISTINCT tblPaycheck.EID) AS
People, COUNT() AS Checks, SUM(GrossAmount) AS
Total, SUM(GrossAmount) / COUNT(DISTINCT
tblPaycheck.EID) AS Mean Per Person,
AVG(GrossAmount) AS Mean Per Check same as
before