Lecture 10: SQL

1
Today’s Topics
1. SQL Functions: Scalar and aggregate,
subquery, and Group By
2. Join

2
Review
 SELECT
FROM
 ORER BY

3
Retrieving Data from Tables with
SQL Select
 Main way of getting data out of tables is with
the SELECT statement.
 SELECT syntax:
SELECT column1, column2, ... columnN
FROM tableA, tableB, ... tableZ
WHERE condition1, condition2, ...conditionM
GROUP BY column1, ...
HAVING condition
ORDER BY column1, column2, ... columnN

4
1.SQL Built-in Functions
 SQL has two main types of functions:
 Scalar functions: Operate on every record value
individually. For example
 Math functions e.g., SIN, COS, ROUND
 Text functions
 UCASE (i.e. Upcase)
 MID (/substring):
 LEN,
 FORMAT
 string concatenation (& operator).
 Aggregate Functions: Operate on a collection of
records. For example, SUM, AVG, MIN, MAX and
COUNT are all aggregate functions.

5
1(1) Scalar Function
 UCASE and Mid(string, start, length)
 Show the First initial and last name of each
employee
SELECT MID(first_name, 1, 1) AS first_init, last_name
FROM employee

 Show the First initial and last name of each

employee but make them all upper case
SELECT UCASE(MID(first_name, 1, 1)) AS first_init,
UCASE(last_name) AS Ulast_name
FROM employee

(upper/ lower)
6 Mid/ substring
Practice: Date
 Now() function/ getdate() in sql server
SELECT NOW()
FROM employee

 Employee’s Birthday
SELECT first_name, last_name, FORMAT( date_of_birth, "MMMM DD") AS Birthday
FROM employee
 d: Displays the day as a number without a leading zero (for example, 1).
 dd: Displays the day as a number with a leading zero (for example, 01).
 ddd: Displays the day as an abbreviation (for example, Sun).
 dddd: Displays the day as a full name (for example, Sunday).
 M: Displays the month as a number without a leading zero
 MM: Displays the month as a number with a leading zero (for example, 01/12/01).
 MMM: Displays the month as an abbreviation (for example, Jan).
 MMMM:Displays the month as a full month name (for example, January).
 y: Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-
defined numeric format.
 yy:Displays the year in two-digit numeric format with a leading zero, if applicable.
 yyy: Displays the year in four-digit numeric format.
 yyyy: Displays the year in four-digit numeric format.

7
8
Practice: Date
 Show all employees who have a birthday in August. this uses the
MONTH function.
SELECT first_name, last_name
FROM employee
WHERE MONTH(date_of_birth) = 8;

9
Practice: String concatenation
--- Write a letter to each employee about their new salary with a
2% raise – in access
SELECT "Dear: " & LEFT(first_name,1) & " " & last_name &
" Your new salary will be: " &
(salary * 1.02) AS NewSalary
FROM employee
----------------------------------
 SELECT 'Dear: ' + LEFT(first_name,1) + '' +
last_name +
 ' Your new salary will be: ' +
 convert(varchar,(salary * 1.02)) AS
NewSalary , salary as previous_Sal
10
 FROM employee
1(2) SQL Aggregate Functions
 COUNT
 How many employees are there? use COUNT
SELECT COUNT( employeeid ) FROM
employee
 How many employees in department 1?

 SUM
 What is the total payroll?
SELECT SUM( salary )
FROM employee
 What is the total payroll of employees who make
at least $35000

11
SQL Aggregate Functions
 MAX
 What is the highest salary ?
SELECT MAX( salary )
FROM employee
 AVG
 Average salary in the company:
SELECT AVG(salary)
FROM employee;
 Rename the created column:
SELECT AVG(salary) AS AverageSalary
FROM employee;

 What is the minimum (MIN), maximum (MAX),

average(AVG), total payroll (SUM)
12
Subquery
 Subqueries
 can be used to return either a scalar (single) value
or a row set
 Used in WHERE clause

SELECT column1, column2, ... columnN

FROM tableA, tableB, ... tableZ
WHERE condition1, condition2, condition 3 (single
condition operator: >, =, <, <>..)
(SELECT newcolumn 1
FROM newtable1
)
ORDER BY column1, column2, ... columnN
13
1(3) Aggregate Function and
Subquery
• Show the name of the employee with the highest salary in the company
(This is an example of a subquery):
SELECT first_name, last_name, salary
FROM employee
WHERE salary =
( SELECT MAX(salary) FROM employee );

 Show the employees with the highest salaries in each department ordered
by salary:
SELECT first_name, last_name, departmentid, salary
FROM employee e1
WHERE salary =
( SELECT max(e2.salary)
FROM employee e2
WHERE e1.departmentid = e2.departmentid )
ORDER BY salary DESC;

Two aliases (e1 and e2) refer to different views of the same table.

14
1(4) Group by
SELECT a, b, c, d, SUM( e )
FROM x
GROUP BY a, b, c, d

15
practice
 Show a COUNT of the number of employees in each
department
 i. e., do the count on groups of departmentid
SELECT departmentid, COUNT(employeeid) AS EmployeeCount
FROM employee
GROUP BY departmentid

16
Practice
-- aggregated results of each department
SELECT departmentid, SUM( salary), COUNT( salary ),
MAX( salary)
FROM employee
GROUP BY departmentid

-- Order the above results by max salary

SELECT departmentid, SUM( salary), COUNT( salary ),
MAX( salary)
FROM employee
GROUP BY departmentid
ORDER BY MAX( salary) DESC

-- For each project show the total number of hours pent on the project per week
SELECT project_number, SUM( hours_per_week ) AS TotalHours
FROM project_assignment
GROUP BY project_number

17
2. Select From 2 Tables (INNER
Join)
 Two ways to JOINS tables
 Method 1: From Table1, Table2.. Table N Where conditions
 Method 2: Using INNER JOIN
 List all of the employees working in New York:
 Method 1
SELECT employee.first_name, employee.last_name,
department.department_name
FROM employee , department
WHERE employee.departmentid = department.departmentid
AND department.department_location = 'NY';

 Method 2
SELECT employee.first_name, employee.last_name, department.department_name
FROM employee INNER JOIN department
ON employee.departmentid = department.departmentid
WHERE department.department_location = 'NY';

18
Select From 2 Tables (INNER Join)
 List each employee name and what state (location) they work in. List them
in order of location and name
 Method 1
SELECT employee.last_name, department.department_location
FROM employee, department
WHERE employee.departmentid = department.departmentid
ORDER BY department.department_location, employee.last_name;
 Method 2?

19
Select From 2 Tables (Outer Joins)
 When to use it?
 ALL records from one table and then fill in (using the join
condition) additional columns from another table’

A LEFT JOIN (or LEFT OUTER JOIN) A RIGHT JOIN (or RIGHT OUTER
will favor the table listed on the left JOIN) will favor the table listed on the
hand side so that all of its records will right hand side so that all of its
be shown. records will be shown.

20
Left /Right Outer Join
 List each department and all employees that
work there. Show the department and location
even if no employees work there.
SELECT department.departmentid,
department.department_location,
employee.last_name
FROM employee RIGHT JOIN department
ON employee.departmentid =
department.departmentid

21
Combining Aggregation and Join
 Combining an aggregate function and a join:
What is the highest paid salary in New York ?
SELECT MAX(employee.salary)
FROM employee INNER JOIN department
ON employee.departmentid = department.departmentid
WHERE department.department_location = 'NY';

SELECT MAX(employee.salary)
FROM employee, department
WHERE employee.departmentid = department.departmentid
AND department.department_location = 'NY';

22
Select From Multiple Tables
 To join 3 tables, at least 2 join conditions are required.
 Show each employee and project including the number of hours they work on each
project.
Method 1
SELECT first_name, last_name, project_name, hours_per_week
FROM employee, project_assignment, project
WHERE employee.employeeid = project_assignment.employeeid
AND project_assignment.project_number = project.project_id

Method 2
SELECT employee.first_name, employee.last_name, project.project_name
FROM ( employee INNER JOIN project_assignment
ON employee.employeeid = project_assignment.employeeid )
INNER JOIN project
ON project_assignment.project_number = project.project_id

23
5: Where condition IN
 Using the 'IN' syntax: IN (v1, v2, …)
 1. Show the employees who are working
in department 1, 2 or 3

 2. Show the employees who are working in

department 1, 2 or 3
5(Cont.): Where condition Like
Symb Description Example
 LIKE Wildcard ol
* Matches zero or more characters. It can wh* finds wh, what,

Character be used as the first or last character in

the character string.
white, and why

 Show the employees

whose last name ? Matches any single alphabetic character. b?ll finds ball, bell, and
bill

starts with the letter [] Matches any single character within the
brackets.
b[ae]ll finds ball and bell
but not bill

S
! Matches any character not in the b[!ae]ll finds bill and
 Show the employees brackets. bull but not ball or bell

whose social security - Matches any one of a range of

characters. You must specify the range in
b[a-c]d finds bad, bbd,
and bcd

number contains 222 ascending order (A to Z, not Z to A).

# Matches any single numeric character. 1#3 finds 103, 113, and
123
Exists
 2. Which employee has the highest salary?
(NOT Exist)

26
6: Having
 Where condition can’t be used with aggregate
function
SELECT COUNT(), SUM(), MIN(), MAX()
FROM tableA, tableB, ... tableZ
WHERE condition1, condition2, ...condition
GROUP BY
ORDER BY
 1. For each employee, please show
a. the total number of hours spent on the project
per week

 2. Show employeeid whose average

hours_per_week is lower than 10.
27
7:INLINE SQL Queries
 Recursive Queries and
Aliases
 Recursive Relationship
 A student can tutor one or more
other students. A student has
only one tutor.
 STUDENTS (StudentID, Name,
Grade, Major, Student_TutorID)

28
Part V:INLINE SQL Queries
 Provide a listing of each student and the name
of their tutor
SELECT students.name AS Student, tutors.name
AS Tutor
FROM students, students tutors
WHERE students.student_tutorid =
tutors.studentid;
 We don’t see who is tutoring Bill Smith,
how do we solve this issue
 Alternative approach using Nz
 Nz ( variant, [ value_if_null ] )

29
INLINE SQL Queries
 The result of a query is a table
 Results of a query as the basis for another query.
 Subquery becomes a table we can select FROM.
 Employees who have the highest salary (using
inline query)
select e.last_name, e.first_name, e.salary
from (select max(salary) as maxsalary from
employee) s, employee e
where s.maxsalary=e.salary

30
Practice: Query as Table
 How many students does each tutor work with
?
SELECT tutors.name AS TutorName,
COUNT(students.student_tutorid) AS NumberTutored
FROM students, students tutors
WHERE students.student_tutorid = tutors.studentid
GROUP BY tutors.name;
 What is the largest number of students one person tutors?
SELECT MAX(NumberTutored)
FROM
(SELECT tutors.name AS TutorName,
COUNT(students.student_tutorid) AS NumberTutored
FROM students, students tutors
WHERE students.student_tutorid = tutors.studentid
GROUP BY tutors.name
31
);
Union Statement
 The UNION operator is used to combine the
result-set of two or more SELECT statements.
 Each SELECT statement within UNION must have
the same number of columns
 The columns must also have similar data types
 The columns in each SELECT statement must also
be in the same order
 Syntax
 SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2

32
Practice
 1: Show all project information (both projects
and additional project tables)

33