More SQL - PowerPoint PPT Presentation

1 / 24
About This Presentation
Title:

More SQL

Description:

SQL2 provides alternative ways to specify joins: ... Also a negated version NOT BETWEEN. IST 210. Set Membership - IN / NOT IN ... – PowerPoint PPT presentation

Number of Views:35
Avg rating:3.0/5.0
Slides: 25
Provided by: thomas719
Category:
Tags: sql | more | negated

less

Transcript and Presenter's Notes

Title: More SQL


1
More SQL
  • Todd Bacastow
  • IST 210 Organization of Data

2
Simple Join
  • Consider
  • Likes (drinker, beer)
  • Sells (bar, beer, price)
  • SELECT drinker, bar
  • FROM Likes, Sells
  • WHERE Likes.beerSells.beer
  • Whats happening here????

3
Examples
Employees
Orders

4
Who has ordered a product, and what did they
order?
Result
5
Who ordered a printer?
Result

6
Other Joins in SQL2
  • R NATURAL JOIN S
  • R CROSS JOIN S
  • R JOIN S ON condition
  • R OUTER JOIN S
  • R INNER JOIN S

7
Alternative JOIN Constructs
  • SQL2 provides alternative ways to specify joins
  • SELECT FROM Likes.beer JOIN Sells.beer ON
    Likes.beer Sells.beer
  • SELECT FROM Likes JOIN Sells USING beer
  • SELECT FROM Likes NATURAL JOIN Sells
  • The first produces a table with two identical
    beer columns, remaining two produce table with
    single beer column.

8
Outer Join
  • Outerjoin natural join with dangling tuples
    padded with NULLs and included in the result
  • A tuple is dangling if it doesnt join with any
    other tuple


OUTERJOIN
9
Modifiers of OUTER JOIN
  • Optional NATURAL in front
  • Optional ON condition at end
  • Optional LEFT, RIGHT, FULL before OUTER
  • LEFT Pad dangling tuples of R ONLY
  • RIGHT Pad dangling tuples of S ONLY

10
Inner Join
  • The INNER JOIN returns all rows from both tables
    where there is a match. If there are rows in
    Employees that do not have matches in Orders,
    those rows will not be listed.
  • EXAMPLE Who has ordered a product, and what did
    they order?

Result
11
Calculated Fields
  • Query
  • Produce a list of monthly salaries for all
    staff, showing the staff number, the first and
    last names, and the salary details.
  • SELECT sno, fname, lname, salary/12
  • FROM staff

Monthly_salary
  • To name column,
  • use AS clause
  • SELECT sno, fname, lname, salary/12 AS
    monthly_salary
  • FROM staff

12
Range Search Condition - BETWEEN
  • Query
  • List all staff with a salary between 20,000 and
    30,000.
  • SELECT sno, fname, lname, position, salary
  • FROM staff
  • WHERE salary BETWEEN 20000 AND 30000

13
Range Search Condition (Cont)
  • BETWEEN test includes the endpoints of range.
    Equivalent to
  • SELECT sno, fname, lname, position, salary
  • FROM staff
  • WHERE salarygt20000 AND salary lt 30000
  • BETWEEN does not add much to SQL's expressive
    power
  • Also a negated version NOT BETWEEN.

14
Set Membership - IN / NOT IN
  • Query List all Managers and Deputy Managers.
  • SELECT sno, fname, lname, position
  • FROM staff
  • WHERE position IN ('Manager', 'Deputy')

15
Pattern Matching - LIKE
  • SQL has two special pattern matching symbols
  • sequence of zero or more characters
  • _ (underscore) any single character.
  • LIKE 'Glasgow' means a sequence of characters
    of any length containing 'Glasgow'.
  • Query Find all staff with the string 'Glasgow'
    in their address.
  • SELECT sno, fname, lname, address, salary
  • FROM staff
  • WHERE address LIKE 'Glasgow'

16
NULL Search Condition
  • NULL value can not be checked by comparison
    operators
  • Have to test for null explicitly using special
    keyword IS NULL
  • IS NOT NULL can test for non-null values.
  • Example query
  • List details of all viewings on property PG4
    where a comment has not been supplied.
  • SELECT rno, date
  • FROM viewing
  • WHERE pno 'PG4' AND
  • comment IS NULL

17
ORDER BY
  • Example Query
  • List salaries for all staff, arranged in
    descending order of salary.
  • SELECT sno, fname, lname, salary
  • FROM staff
  • ORDER BY salary DESC
  • When no (ASC/DESC) is indicated, default order is
    ascending (ASC).
  • Can sort on multiple columns.
  • SELECT pno, type, rooms, rent
  • FROM property_for_rent
  • ORDER BY type, rent DESC

18
GROUP BY and HAVING
  • Use GROUP BY clause to get sub-totals.
  • SELECT and GROUP BY closely integrated each item
    in SELECT list must be single-valued per group,
    and SELECT clause may only contain
  • Column names.
  • Aggregate functions.
  • Constants.
  • An expression involving combinations of the
    above.
  • If WHERE is used with GROUP BY, WHERE is applied
    first, then groups are formed from remaining rows
    satisfying predicate.
  • Considers two nulls to be equal for purposes of
    GROUP BY.

19
Use of GROUP BY
  • Example Find number of staff in each branch
    and their total salaries.
  • SELECT bno, COUNT(sno) AS count, SUM(salary) AS
    sum
  • FROM staff
  • GROUP BY bno
  • ORDER BY bno

59
20
Restricted Grouping - HAVING
  • HAVING clause is designed for use with GROUP BY
    clause to restrict groups that appear in final
    result table.
  • HAVING filters groups.
  • Column names in HAVING clause must also appear in
    the GROUP BY list or be contained within an
    aggregate function.
  • Example
  • For each branch with more than 1 member of
    staff, find number of staff in each branch and
    sum of their salaries.
  • SELECT bno, COUNT(sno) AS count, SUM(salary) AS
    sum
  • FROM staff
  • GROUP BY bno HAVING COUNT(sno) gt 1
  • ORDER BY bno

21
Subquery with Aggregate
  • Example List all staff whose salary is greater
    than the average salary.
  • SELECT sno, fname, lname, position, salary
  • FROM staff
  • WHERE salary gt
  • (SELECT avg(salary)
  • FROM staff)
  • Cannot write 'WHERE salary gt avg(salary)'
  • Instead, use subquery to find average salary
    (17000), and then use outer SELECT to find those
    staff with salary greater than 17000
  • Subquery SELECT list must consist of a single
    column name or expression, except for subqueries
    that use EXISTS.

22
ANY/SOME and ALL
  • If subquery preceded by ALL, condition will only
    be true if it is satisfied by all values produced
    by subquery.
  • If subquery preceded by ANY, condition will be
    true if it is satisfied by any values produced by
    subquery.
  • If subquery is empty, ALL returns true, ANY
    returns false.

23
Example of ANY and ALL
  • Example1 Find staff whose salary is larger than
    salary of at least 1 member of staff at branch
    B3.
  • SELECT sno, fname, lname, position, salary
  • FROM staff
  • WHERE salary gtANY
  • (SELECT salary
  • FROM staff
  • WHERE bno 'B3')
  • Example 2 Find staff whose salary is larger than
    salary of every member of staff at branch B3.
  • SELECT sno, fname, lname, position, salary
  • FROM staff
  • WHERE salary gt ALL
  • (SELECT salary
  • FROM staff
  • WHERE bno 'B3')

24
Sorting a join
  • Consider For each branch, list names of staff
    who manage properties.
  • SELECT s.bno, s.sno, fname, lname, pno
  • FROM staff s, property_for_rent p
  • WHERE s.sno p.sno
  • ORDER BY s.bno, s.sno, pno
Write a Comment
User Comments (0)
About PowerShow.com