Download SQL SELECT: Aliasing, Self-Joins, and Subqueries and more Slides Introduction to Database Management Systems in PDF only on Docsity!
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
SQL SELECT Overview
SELECT
[DISTINCT | ALL] <column-list>
FROM <table-names>
[WHERE ]
[ORDER BY <column-list>]
[GROUP BY <column-list>]
[HAVING ]
([] - optional, | - or)
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Aliases
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
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Example
SELECT
E.ID AS empID,
E.Name, W.Dept
FROM
Employee E
WorksIn W
WHERE
E.ID = W.ID
Employee
ID Name
123 John
124 Mary
WorksIn
ID Dept
123 Marketing
124 Sales
124 Marketing
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Example
SELECT
E.ID AS empID,
E.Name, W.Dept
FROM
Employee E
WorksIn W
WHERE
E.ID = W.ID
empID Name Dept
123 John Marketing
124 Mary Sales
124 Mary Marketing
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Aliases and ‘Self-Joins’
Aliases can be used to
copy a table, so that a
it can be combined
with itself:
SELECT A.Name FROM Employee A, Employee B
WHERE A.Dept=B.Dept
AND B.Name=‘Andy’
Employee
Name Dept
John Marketing
Mary Sales
Peter Sales
Andy Marketing
Anne Marketing
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Aliases and Self-Joins
B
Name Dept
John Marketing
Mary Sales
Peter Sales
Andy Marketing
Anne Marketing
A
Name Dept
John Marketing
Mary Sales
Peter Sales
Andy Marketing
Anne Marketing
Employee A Employee B
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Aliases and Self-Joins
A.Name A.Dept B.Name B.Dept John Marketing John Marketing Mary Sales John Marketing Peter Sales John Marketing Andy Marketing John Marketing Anne Marketing John Marketing John Marketing Mary Sales Mary Sales Mary Sales Peter Sales Mary Sales Andy Marketing Mary Sales Anne Marketing Mary Sales
SELECT … FROM Employee A, Employee B …
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Aliases and Self-Joins
A.Name A.Dept B.Name B.Dept John Marketing John Marketing Andy Marketing John Marketing Anne Marketing John Marketing Mary Sales Mary Sales Peter Sales Mary Sales Mary Sales Peter Sales Peter Sales Peter Sales John Marketing Andy Marketing Andy Marketing Andy Marketing Anne Marketing Andy Marketing
SELECT … FROM Employee A, Employee B
WHERE A.Dept = B.Dept
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Aliases and Self-Joins
A.Name A.Dept B.Name B.Dept John Marketing Andy Marketing Andy Marketing Andy Marketing Anne Marketing Andy Marketing
SELECT … FROM Employee A, Employee B
WHERE A.Dept = B.Dept AND B.Name = ‘Andy’
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Aliases and Self-Joins
SELECT A.Name FROM Employee A, Employee B
WHERE A.Dept = B.Dept AND B.Name = ‘Andy’
A.Name John Andy Anne
The result is the names of all employees who work in the
same department as Andy.
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Subqueries
can be nested inside
another query to
form a subquery
subquery are passed
back to the
containing query
•E.g. Get the names
of people who are in
Andy’s department:
SELECT Name FROM Employee WHERE Dept = (SELECT Dept FROM Employee WHERE Name=‘Andy’)
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Subqueries
SELECT Name
FROM Employee
WHERE Dept =
(SELECT Dept
FROM Employee
WHERE
Name=‘Andy’)
evaluated, returning
the value ‘Marketing’
to the main query
SELECT Name FROM Employee WHERE Dept = ‘Marketing’
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
(NOT) EXISTS
Employee Name Department Manager John Marketing Chris Mary Marketing Chris Chris Marketing Jane Peter Sales Jane Jane Management (^) Name Department Manager
Chris Marketing Jane Jane Management
SELECT *
FROM Employee AS E
WHERE EXISTS (
SELECT * FROM
Employee AS E
WHERE E2.Name =
E1.Manager)
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
ANY and ALL
compare a single
value to a set of
values
comparison
operators like =, >,
true if there is at
least one member of
the set equal to the
value
true if all members of
the set are equal to
the value
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
ALL
Find the names of the
employee(s) who earn
the highest salary
SELECT Name FROM Employee WHERE Salary >= ALL ( SELECT Salary FROM Employee)
Name Salary Mary 20, John 15, Jane 25, Paul 30,
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
ANY
Find the names of
employee(s) who
earn more than
someone else
SELECT Name
FROM Employee
WHERE Salary >
ANY (
SELECT Salary
FROM Employee)
Name Salary Mary 20, John 15, Jane 25, Paul 30,
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
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 - “ Crypt onomicon” by Neil Stephenson - “Applied Crypt ography” by Bruce Schneier
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Word Searches
we can keep
- A table of items to be searched
- A table of keywords
- A linking table saying which keywords belong to which items
Items
itmID itmTitle
Keywords
keyID keyWord
ItemKey
itmID keyID
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
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%‘))
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Word Searches
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%'))
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
This Lecture in Exams
Track
cID Num Title Time aID
1 1 Violent 239 1 1 2 Every Girl 410 1 1 3 Breather 217 1 1 4 Part of Me 279 1 2 1 Star 362 1 2 2 Teaboy 417 2
CD cID Title Price 1 Mix 9. 2 Compilation 12.
Artist aID Name 1 Stellar 2 Cloudboy
More SQL SELECT G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
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
