More SQL Select

1
More SQL Select

• Database Systems Lecture 8

2
Last Lecture on DBS

• The data dictionary
• SQL SELECT
• WHERE clauses
• SELECT from multiple tables
• JOINs
• Aliases
• For more information
• Connolly and Begg Chapter 5

3
Todays Red-hot Action

• Aliases Self-joins
• Subqueries
• IN, EXISTS, ANY, ALL
• Order By
• For more information
• Connoly and Begg Chapter 5

4
Aliases

• Aliases rename columns or tables to
• Make names more meaningful
• Make names shorter and easier to type
• Resolve ambiguous names

• Two forms
• Column alias
• SELECT column
• AS newName...

• Table alias
• SELECT ...
• FROM table
• AS newName

5
Business Alias Example
Employees

• SELECT
• E.ID AS empID,
• E.Name, W.Dept
• FROM
• Employee E
• WorksIn W
• WHERE
• E.ID W.ID

6
Business Alias Example

• SELECT
• E.ID AS empID,
• E.Name, W.Dept
• FROM
• Employee E
• WorksIn W
• WHERE
• E.ID W.ID

Result
empID Name Dept 124 Dipsy
Marketing 125 La La Sales 125 La La
Marketing
7
Aliases and Self-Joins
Employee Name Dept Dipsy
Marketing La La Sales Tinky W Sales Po
Marketing Sun Marketing

• Aliases can be used to copy a table, so that a it
  can be combined with itself

Who works with Po?
SELECT A.Name FROM Employee A, Employee B WHERE
A.DeptB.Dept AND B.NamePo
8
Aliases and Self-Joins
Employee
Employee
9
Aliases and Self-Joins
SELECT FROM Employee A, Employee B
A.Name A.Dept B.Name B.Dept Dipsy
Marketing Dipsy Marketing La La Sales
Dipsy Marketing Tinky Sales Dipsy
Marketing Po Marketing Dipsy
Marketing Sun Marketing Dipsy
Marketing Dipsy Marketing La La
Sales La La Sales La La Sales Tinky
Sales La La Sales Po Marketing La
La Sales Sun Marketing La La Sales
10
Aliases and Self-Joins
SELECT FROM Employee A, Employee B WHERE
A.Dept B.Dept
A.Name A.Dept B.Name B.Dept Dipsy
Marketing Dipsy Marketing Po
Marketing Dipsy Marketing Sun
Marketing Dipsy Marketing La La Sales
La La Sales Tinky Sales La La
Sales La La Sales Tinky Sales Tinky
Sales Tinky Sales Dipsy Marketing
Po Marketing Po Marketing Po
Marketing Sun Marketing Po Marketing
11
Aliases and Self-Joins
SELECT FROM Employee A, Employee B WHERE
A.Dept B.Dept AND B.Name Po
A.Name A.Dept B.Name B.Dept Dipsy
Marketing Po Marketing Po Marketing Po
Marketing Sun Marketing Po Marketing
12
Aliases and Self-Joins
SELECT A.Name FROM Employee A, Employee B WHERE
A.Dept B.Dept AND B.Name Po
The result is the names of all employees who work
in the same department as Po.
13
Subqueries

• A SELECT statement can be nested inside another
  query to form a subquery
• The results of the subquery are passed back to
  the containing query

• E.g. Get the names of people who are in Pos
  department
• SELECT Name
• FROM Employee
• WHERE Dept
• (SELECT Dept
• FROM Employee
• WHERE NamePo)

14
Subquery Order

• SELECT Name
• FROM Employee
• WHERE Dept
• (SELECT Dept
• FROM Employee
• WHERE
• NamePo)

• First the subquery is evaluated, returning the
  value Marketing
• This result is passed to the main query
• SELECT Name
• FROM Employee
• WHERE Dept
• Marketing

15
Subquery Results

• Often a subquery will return a set of values
  rather than a single value
• You cant directly compare a single value to a set

• Options
• IN - checks to see if a value is in the set
• EXISTS - checks to see if the set is empty or not
• ALL/ANY - checks to see if a relationship holds
  for every/one member of the set

16
(NOT) IN clause

• Using IN we can see if a given value is in a set
  of values
• NOT IN checks to see if a given value is not in
  the set
• The set can be given explicitly or from a subquery

SELECT ltcolumnsgt FROM lttablesgt WHERE
ltvaluegt IN ltsetgt
SELECT ltcolumnsgt FROM lttablesgt WHERE
ltvaluegt NOT IN ltsetgt
17
(NOT) IN
SELECT FROM Employee WHERE Department IN
(Marketing, Sales)
Employee Name Department Manager John Marketing
Chris Mary Marketing Chris Chris Marketing Jane Pe
ter Sales Jane Jane Management null
Name Department Manager John Marketing Chris Ma
ry Marketing Chris Chris Marketing Jane Peter Sale
s Jane
18
(NOT) IN
SELECT FROM Employee WHERE Name NOT IN
(SELECT Manager FROM Employee)
Employee Name Department Manager John Marketing
Chris Mary Marketing Chris Chris Marketing Jane Pe
ter Sales Jane Jane Management null
19
(NOT) IN

• First the subquery
• SELECT Manager
• FROM Employee
• is evaluated giving

• This gives
• SELECT
• FROM Employee
• WHERE Name NOT
• IN
• (Chris,Jane, null)

Manager Chris Chris Jane Jane null
20
(NOT) EXISTS

• Using EXISTS we see if there is at least one
  element in a set
• NOT EXISTS is true if the set is empty
• The set is always given by a subquery

SELECT ltcolumnsgt FROM lttablesgt WHERE EXISTS
ltsetgt
SELECT ltcolumnsgt FROM lttablesgt WHERE NOT
EXISTS ltsetgt
21
(NOT) EXISTS
SELECT FROM Employee AS E1 WHERE EXISTS
( SELECT FROM Employee AS E2 WHERE
E1.Name E2.Manager)
Employee Name Department Manager John Marketing
Chris Mary Marketing Chris Chris Marketing Jane Pe
ter Sales Jane Jane Management null
22
(NOT) EXISTS
E1 Name Department Manager John Marketing Chris
Mary Marketing Chris Chris Marketing Jane Peter Sa
les Jane Jane Management null
SELECT FROM Employee AS E1 WHERE EXISTS
( SELECT FROM Employee AS E2 WHERE
E1.Name E2.Manager)
E2 Name Department Manager John Marketing Chris
Mary Marketing Chris Chris Marketing Jane Peter Sa
les Jane Jane Management null
23
(NOT) EXISTS

• Find employees who are managers
• Where condition is satisfied if subquery returns
  at least one tuple (result set is not empty)

Employee
Manager
Select From Employee E Where Exists ( Select
From Manager M Where E.Name M.Name
And E.Dept M.Dept )
24
Student
Module
Registration
 
Task Use In or Not In and Exists or Not
Exists to find the students who have not
registered for any modules
Select From Student Where SID Not In (Select
SID From Registration) Select From Student S
Where Not Exists (Select STUID From
Registration R Where S.STUID R.STUID)
25
In or Exists

• IN makes a value comparison
• The attribute of the outer query that is used in
  the subquery must be listed between Where and IN
  or between WHERE and NOT IN
• Only one attribute of the outer query can be
  tested against the outcome of the subquery

• EXISTS and Not EXISTS test the existence or non
  existence of tuples returned by a subquery
• Nothing needs to be listed between WHERE and
  EXISTS or WHERE and NOT EXISTS
• EXISTS and NOT EXISTS allow multiple attributes
  of the outer query to be used in the subquery

26
ANY and ALL

• ANY and ALL compare a single value to a set of
  values
• They are used with comparison operators like ,
  gt, lt, ltgt, gt, lt

• val ANY (set) is true if there is at least one
  member of the set equal to the value
• val ALL (set) is true if all members of the set
  are equal to the value

27
ALL

• Find the names of the employee(s) who earn the
  highest salary
• SELECT Name
• FROM Employee
• WHERE Salary gt
• ALL (
• SELECT Salary
• FROM Employee)

28
ANY

• Find the names of employee(s) who earn more than
  someone else
• SELECT Name
• FROM Employee
• WHERE Salary gt
• ANY (
• SELECT Salary
• FROM Employee)

29
Word Searches

• Word Searches
• Commonly used for searching product catalogues
  etc.
• Want to be able to search by keyword
• Want to be able to use word stemming for flexible
  searching

• EG Given a database of my books
• Searching for crypt would return
• Cryptonomicon by Neil Stephenson
• Applied Cryptography by Bruce Schneier

30
Word Searches
Items

• To do a word search we can keep
• A table of items to be searched
• A table of keywords
• A linking table saying which keywords belong to
  which items

itmID
itmTitle
Keywords
keyID
keyWord
ItemKey
itmID
keyID
31
Word Searches

• To search we can use queries like
• SELECT FROM Items
• WHERE itmID IN (
• SELECT itmID FROM ItemKey
• WHERE keyID IN (
• SELECT keyID FROM Keywords
• WHERE keyWord LIKE 'crypt))

32
Word Searches

• Sometimes you need to search for a set of words
• To find entries with all words you can link
  conditions with AND
• To find entries with any of the words use OR

• SELECT FROM Items
• WHERE itmID IN (
• SELECT itmID FROM ItemKey
• WHERE keyID IN (
• SELECT keyID FROM Keywords
• WHERE keyWord LIKE
• 'word1'))
• AND
• itmID IN (
• SELECT itmID FROM ItemKey
• WHERE keyID IN (
• SELECT keyID FROM Keywords
• WHERE keyWord LIKE
• 'word2'))

33
ORDER BY

• The ORDER BY clause sorts the results of a query
• You can sort in ascending (default) or descending
  order
• Multiple columns can be given
• You cannot order by a column which isnt in the
  result

• SELECT ltcolumnsgt
• FROM lttablesgt
• WHERE ltconditiongt
• ORDER BY ltcolsgt
• ASCENDING
• DESCENDING
• ASC DESC

34
ORDER BY Example
35
ORDER BY Example
36
Why is ORDER BY odd?

• It is part of SQL but not part of the relational
  model that underpins it. Why?
• Because as we know a database table is a
  relation, and a relation is a set.
• Sets have no order. Just elements.
• The result of an Order by is not a relation. This
  breaks the algebra.

37
LIMIT

• Limit is also outside the remit of relational
  algebra but, like group by, incredibly useful.
• The LIMIT clause can be used to constrain the
  number of rows returned by the SELECT statement.
• LIMIT takes one or two numeric arguments, which
  must both be non-negative integer constants
  (except when using prepared statements)
• LIMIT offset, row_count

38
web forum example topics
39
web forum example topics

• SELECT FROM forum_topics
• WHERE parent_board Aston Villa
• ORDER BY created_date
• LIMIT 0,50

SELECT FROM forum_topics WHERE parent_board
Aston Villa ORDER BY created_date LIMIT 50,50
40
web forum example posts
41
web forum example posts

• SELECT FROM forum_posts P
• INNER JOIN forum_post_text T
• ON (P.post_id T.post_id)
• WHERE parent_topic_id 337
• ORDER BY posted_date DESC
• LIMIT 0,20

42
Perhaps RM is missing something?

• These ordering functions seem so useful why has
  SQL had to add them on top of the RM
• in the real world that perhaps the relational
  model is missing a trick?
• Set theory also allows orderings but this has
  never been included in the RM.
• Scope for improvement perhaps. RM is the best
  data model there is, but not all she wrote.

43
This Lecture in Exams
44
This Lecture in Exams
Find a list of the names of those artists
who have a track on the CD with the title
Compilation. (4 marks) Note, this is one of
the questions from last time, but there are
alternative solutions using subqueries, try
solving this with a query where you never list
more than one table for a single SELECT
statement
45
Next Lecture

• Yet more SQL
• Aggregate functions
• GROUP BY and HAVING
• UNION etc.
• For more information
• Connoly and Begg Chapter 5