07 advanced sql - advanced sql renaming self joins and aggregation

1CS 338: Computer Applications in Business: Databases (Fall 2014)

1992-2014 by Addison Wesley & Pearson Education, Inc., McGraw Hill, Cengage LearningSlides adapted and modified from Fundamentals of Database Systems (5/6) (Elmasri et al.), Database SystemConcepts (5/6) (Silberschatz et al.), Database Systems (Coronel et al.), Database Systems (4/5) (Connolly et al. )

CS 338: Computer Applications in Business: Databases

Advanced SQLAdvanced SQL: Renaming, Self Joins, and Aggregation


Rice University Data Center

Fall 2014

Chapter 5

Announcements

Midterm Exam 1

Friday October 10th, 2014

Review: Wednesday, October 8, 2014

Assignment 1 Due Monday October 6th, 2014 Solutions will be posted on midnight

2



Joined Relation (Joined Table)

Enable users to specify a table resulting from a join operation in the FROM clause of a query

Joins are used to relate data stored in different tables It is part of the SQL query that retrieves rows from two or more tables

If result columns come from more than one table, we must use a join To perform a join, include the name of the tables in the FROM clause

Concept of JOIN

3

DeptID Name Location1 Sales Room 2452 Purchase Room 4303 Marketing Room 212

SELECT A.Name, B.NameFROM EMPLOYEE As A, DEPARTMENT As BWHERE A.DeptID = B.DeptID

Example

Department

ID Name DeptID111 Sue 1222 John 2333 Mary 1444 Victor 3555 Mark 2666 Tony 3

Employee

Joined Relation (Joined Table)

Concept of JOIN

4

DeptID Name Location1 Sales Room 2452 Purchase Room 4303 Marketing Room 212

SELECT EMPLOYEE.Name, DEPARTMENT.Name

FROM EMPLOYEE JOIN DEPARTMENT ON EMPLOYEE.DeptID = DEPARTMENT.DeptID

Previous example can be re-written using a Join

DepartmentID Name DeptID

111 Sue 1222 John 2333 Mary 1444 Victor 3555 Mark 2666 Tony 3

Employee



Inner Join

To obtain correct results, include only the rows from both tables that have identical values in the DeptID columns:

EMPLOYEE.DeptID = DEPARTMENT.DeptID

These two columns are the matching columns for the two tables: Employee and Department

Inner join return rows when there is at least one match in both tables

Default type of join is the inner join

5

Outer Joins

Three types of outer join condition

Left

Right

Full

This join condition returns all rows from both tables whichsatisfy the join condition as well as the rows that do not satisfythe join condition

6



Outer Joins LEFT Join

LEFT JOIN will return all rows from the left table, even if there are no matches in the right table

7

SELECT Persons.LastName, Orders.OrderNumFROM PersonsLEFT JOIN OrdersON Persons.PID=Orders.PIDORDER BY Persons.LastName

Example Persons

Orders

PID LastName Address City100 Doe 123 First Ave. Toronto

200 McPherson 456 Second Ave. Oshawa

300 Garcia 789 Third Ave. Guelph400 Thompson 123 Fourth Ave. Markham

OrderID OrderNum PID4100 54841 2004101 94201 3004102 78412 1004103 71145 1004104 87741 500

LastName OrderNumDoe 78412Doe 71145

Garcia 94201McPherson 54841Thompson

The result-set will be:

The LEFT JOIN returns all the rows from the left table (Persons),although there are no matches in the right table (Orders) Columns from second table may be filled with NULLs

Outer Joins RIGHT Join

RIGHT JOIN will return all rows from the right table, even if there are no matches in the left table

8

SELECT Persons.LastName, Orders.OrderNumFROM PersonsRIGHT JOIN OrdersON Persons.PID=Orders.PIDORDER BY Persons.LastName

Example Persons

Orders






Garcia 94201McPherson 54841

87741


The RIGHT JOIN returns all the rows from the right table (Orders),although there are no matches in the left table (Persons) Columns from first table may be filled with NULLs



Outer Joins FULL Join

FULL JOIN returns all the rows from the left table (Persons), and all the rows from the right table (Orders).

9

SELECT Persons.LastName, Orders.OrderNumFROM PersonsFULL JOIN OrdersON Persons.PID=Orders.PIDORDER BY Persons.LastName

Example Persons

Orders






Garcia 94201McPherson 54841Thompson

87741


If there are rows in Persons that do not have matches in Orders (orvice versa), those rows will be listed as well Full join is the UNION of both left and right outer joins

NULL Recall

In previous lectures, NULL was used to represent a missing value

10

However, a NULL can be interpreted in any of three ways: Unknown value: exists but is not known

Example: Persons date of birth is not known

Unavailable or withheld value: exists but is purposely withheld Example: Person has a home phone but does not want it to be listed (it

is withheld)

Not applicable attribute: attribute is undefined for this row Example: Person who has no college degrees. Attribute such as

LastCollegeDegree would be NULL since it does not apply to that

person



NULL NULL Search

It is often not possible to determine which of the null meanings is intended.

11

SQL does not distinguish between different meanings of NULL In general, each individual NULL value considered to be different

from every other NULL value

SQL allows queries that check whether an attribute value is NULL Instead of using = or to compare an attribute value to NULL,

SQL uses the comparison operators IS or IS NOT NULL

NULL NULL Search

It is often not possible to determine which of the null meanings is intended.

SELECT CityFROM CarRentalsWHERE City IS NOT NULL

Example 1 CarRentals


City Year CarsRented

Chicago 2011 567

Los Angles 2010 1540

Los Angles 2011 1320

Miami 2010 512

Miami 2011 987

Miami 2009 1054

New Jersey 2010 987

New York 2011 1021

12

CityChicago

Los AnglesLos Angles

MiamiMiamiMiami

New JerseyNew York

SELECT CityFROM CarRentalsWHERE City IS NULL

Example 2

The result-set will be:City



Aggregation Aggregate Functions in SQL

SQL has many built-in functions for performing calculations on retrieved data SQL aggregate functions return a single value (based on values in a

column)

AVG returns average of values of a numeric column COUNT returns number of rows matching a specific criteria FIRST returns first value in a specified column LAST returns last value in a specified column MAX returns largest value in a specified column MIN returns smallest value in a specified column SUM returns sum of values in a specified column

13

Aggregation Aggregate Functions in SQL

Each aggregate function operates on a single column to return a single value

COUNT, MIN, and MAX apply to both numeric and non-numeric domains SUM and AVG can only be used with numeric domains

COUNT(*) counts all rows of a table, including NULLS and duplicate values (if present) All other aggregate functions eliminate NULLs first and operate on the

remaining values To eliminate duplicates, one can use DISTINCT before column name

14



Aggregation Aggregate Functions in SQL Examples

City

Miami

New Jersey

New York

Los Angles

Los Angles

Miami

Chicago

Miami

Example 1:SELECT COUNT(City) as RowsFROM CarRentals

City

Miami

New Jersey

New York

Los Angles

Chicago

Example 2:SELECT COUNT(DISTINCT City) as RowsFROM CarRentals

Rows8 Rows

5

15


CarsRented

567

1540

1320

512

987

1054

987

1021

CarsR2553

Example 3:SELECT SUM(CarsRented) as CarsRFROM CarRentals

City

512

987

1054

Example 4:SELECT SUM(CarsRented) as CarsRFROM CarRentalsWHERE City = Miami

CarsR7988

16




Example 5:SELECT MIN(CarsRented) as MinCarsRented,

MAX(CarsRented) as MaxCarsRented,AVG(CarsRented) as AvgCarsRented

FROM CarRentals

MinCarsRented MaxCarsRented AvgCarsRented512 1540 998

17

GroupingGROUP BY Clause

Partition relation into subsets of rows Based on grouping attribute(s) Apply function to each such group independently

GROUP BY clause Specifies grouping attributes If NULLs exist in grouping attribute, a separate group is created for

all rows with a NULL value in grouping attribute

Example 1: SELECT City, SUM(CarsRented) as CarsRFROM CarRentalsWHERE CarsRented IS NOT NULLGROUP BY CityORDER BY SUM(CarsRented) ASC

City CarsRChicago 567

New Jersey 987New York 1021

Miami 2553Los Angles 2860

18

Nathan WilsonCan also use CarsR here since you've defined this as an alias

10

CS 338: Computer Applications in Business: Databases (Fall 2014)


Restricting Groups HAVING Clause

HAVING clause is designed for use with GROUP BY clause to restrict groups that

appear in result-set

Similar to WHERE clause which filters individual rows, HAVINGclause filters groups

City CarsRNew York 1021

Miami 2553

Los Angles 2860

Example 1: SELECT City, SUM(CarsRented) as CarsRFROM CarRentalsWHERE CarsRented IS NOT NULLGROUP BY CityHAVING SUM(CarsRented) > 1000 ORDER BY SUM(CarsRented) ASC

19

SELECT Statement Summary

A SELECT statement can consist of up to six clauses.

SELECT [DISTINCT | ALL]{* | [column_expression [AS new_name]] [,...] }

FROM table_name [alias] [, ...][WHERE condition][GROUP BY column_list][HAVING condition][ORDER By column_list]

Only SELECT and FROM clauses are mandatory.

Order of the clauses cannot be changed

Nathan Wilson

Nathan WilsonHaving is "where" for groups

07 advanced sql - advanced sql renaming self joins and aggregation

Documents