SQL 2: Query
SQL 2.1: SELECT
Jianjun
Contents
The next three lectures will cover a good portion of “Data manipulation language (DML)” of RDBMS.
SELECT: Overview
SELECT [DISTINCT | ALL]
column-list FROM table-names
[WHERE condition]
[ORDER BY column-list]
[GROUP BY column-list]
[HAVING condition]
Examples in This Lecture
SELECT
In its simplest form, SELECT is the SQL version of projection:
SELECT col1[,col2…] FROM table-name;
Selection can be achieved by using a WHERE clause:
SELECT * FROM table-name
WHERE predicate;
Star (*) means all columns of table.
What does the pseudo code of
SELECT * FROM xxx WHERE f
Looks like?
5
DISTINCT and ALL
Sometimes you end up with duplicate entries
Using DISTINCT removes duplicates
Using ALL retains duplicates
ALL is used as a default if neither is supplied
These will work over multiple columns
WHERE Clauses
A WHERE clause restricts rows that are returned
It takes the form of a condition
only rows that satisfy the condition are returned
Example conditions:
Mark < 40
First = ‘John’
First <> ‘John’
First = Last
(First = ‘John’) AND (Last = ‘Smith’)
(Mark < 40) OR (Mark > 70)
<> NOT equal to
7
WHERE: Examples
SELECT * FROM Grade WHERE Mark >= 60;
SELECT DISTINCT ID
FROM Grade
WHERE Mark >= 60;
Try It Yourself
Write an SQL query to find a list of the ID numbers and Marks for students who have passed (scored 50% or more) in IAI
Write an SQL query to find the combined list of the student IDs for both the IAI and PR2 module.
?
?
9
Solution
SELECT ID, Mark FROM Grade WHERE (Code = ‘IAI’) AND (Mark >= 50);
SELECT ID FROM Grade
WHERE
(Code = ‘IAI’ OR Code = ‘PR2’);
SELECT and Cartesian Product
Cartesian product of two tables can be obtained by using:
SELECT * FROM Table1, Table2;
If the tables have columns with the same name, ambiguity will result
This can be resolved by referencing columns with the table name:
TableName.ColumnName
Cartesian Product: Example
SELECT
First, Last, Mark FROM
Student, Grade
WHERE
(Student.ID = Grade.ID)
AND (Mark >= 40);
SELECT … FROM Student, Grade WHERE …
SELECT … FROM Student, Grade WHERE
(Student.ID = Grade.ID) AND …
SELECT … FROM Student,Grade
WHERE (Student.ID = Grade.ID)
AND (Mark >= 40)
SELECT First, Last, Mark
FROM Student, Grade
WHERE (Student.ID = Grade.ID)
AND (Mark >= 40)
SELECT from Multiple Tables
WHERE clause is a key feature when selecting from multiple tables.
Unrelated combinations can be filtered out.
SELECT * FROM
Student, Grade, Course
WHERE
Student.ID = Grade.ID AND
Course.Code = Grade.Code
Try It Yourself
Write SQL statements to do the following:
Produce a list of all student names and all their enrolments (module codes)
Find a list of module titles being taken by the student named “Harrison”
Find a list of module codes and titles for all modules currently being taken by first year students
SELECT sName, mCode
FROM Student, Enrolment
WHERE Student.sID = Enrolment.sID;
SELECT mTitle
FROM Module, Student, Enrolment WHERE(Module.mCode = Enrolment.mCode)
AND(Student.sID = Enrolment.sID)
AND Student.sName = ‘Harrison’;
SELECT Module.mCode, mTitle
FROM Enrolment, Module, Student
WHERE (Module.mCode = Enrolment.mCode)
AND (Student.sID = Enrolment.sID)
AND sYear = 1;
Aliases
Aliases rename columns or tables
Can make names more meaningful
Can shorten names, making them easier to use
Can resolve ambiguous names
Two forms:
Column alias
SELECT column [AS] new-col-name
Table alias
SELECT * FROM table [AS] new-table-name
Alias Example
SELECT
E.ID AS empID,
E.Name, W.Department FROM
Employee E,
WorksIn W
WHERE
E.ID = W.ID;
Note: You cannot use a column alias in a WHERE clause
Question:
Since the where clause is the second step, can you use empID in the where clause?
23
Alias Example
SELECT
E.ID AS empID,
E.Name, W.Department FROM
Employee E,
WorksIn W
WHERE
E.ID = W.ID;
Aliases and ‘Self-Joins’
Aliases can be used to copy a table, so that it can be combined with itself:
SELECT A.Name FROM
Employee A,
Employee B
WHERE A.Dept = B.Dept
AND B.Name = ‘Andy’;
Find the names of all employees who work in the same department as Andy.
Aliases and ‘Self-Joins’
Employee A
Employee B
Aliases and ‘Self-Joins’
SELECT … FROM Employee A, Employee B …
Aliases and ‘Self-Joins’
SELECT … FROM Employee A, Employee B WHERE A.Dept = B.Dept …
Aliases and ‘Self-Joins’
SELECT … FROM Employee A, Employee B WHERE A.Dept = B.Dept AND B.Name = ‘Andy’;
Aliases and ‘Self-Joins’
SELECT A.Name FROM Employee A, Employee B
WHERE A.Dept = B.Dept AND B.Name = ‘Andy’;
Names of all employees who work in the same department as Andy.
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
For example, retrieve a list of names of people who are in Andy’s department:
SELECT Name FROM Employee WHERE Dept =
(SELECT Dept FROM Employee
WHERE Name = ‘Andy’)
Subqueries
First the subquery is evaluated, returning ‘Marketing’
SELECT Name
FROM Employee
WHERE Dept = ‘Marketing’;
This value is passed to the main query
SELECT Name FROM Employee
WHERE Dept =
(SELECT Dept
FROM Employee
WHERE Name = ‘Andy’)
Subqueries
Often a subquery will return a set of values rather than a single value
We cannot directly compare a single value to a set. Doing so will result in an error
Options for handling sets
IN : checks to see if a value is in a set
EXISTS : checks to see if a set is empty
ALL/ANY : checks to see if a relationship holds for every/one member of a set
NOT : can be used with any of the above 4
Handling sets: IN
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 can be produced in a subquery
SELECT columns
FROM tables
WHERE col
IN set;
SELECT columns
FROM tables
WHERE col
NOT IN set;
SELECT * FROM Employee
WHERE Department IN (‘Marketing’,‘Sales’);
SELECT * FROM Employee
WHERE Department = ‘Marketing’
OR Department = ‘Sales’;
Handling sets: NOT IN
SELECT *
FROM Employee
WHERE Name NOT IN
(SELECT Manager
FROM Employee);
Handling sets: NOT IN
First the subquery
SELECT Manager FROM Employee
The original query is then the same as:
SELECT * FROM Employee
WHERE Name NOT IN
(‘Chris’, ‘Jane’);
Handling sets: EXISTS
Using EXISTS we can see whether there is at least one element in a given set
NOT EXISTS is true if the set is empty
The set is always given by a subquery
SELECT columns
FROM tables
WHERE EXISTS set;
SELECT columns
FROM tables
WHERE NOT EXISTS set;
Handling sets: EXISTS
Retrieve all the info for those employees
who are also managers:
SELECT * FROM
Employee AS E1
WHERE EXISTS(
SELECT * FROM
Employee AS E2
WHERE E1.Name = E2.Manager);
Handling sets: EXISTS
SELECT * FROM Employee AS E1 WHERE EXISTS (SELECT * FROM Employee AS E2 WHERE E1.Name = E2.Manager);
SELECT * FROM Employee AS E1
WHERE EXISTS (
SELECT * FROM Employee AS E2
WHERE E1.Name = E2.Manager);
Handling sets: ANY and ALL
ANY and ALL compare a single value to a set of values
They are used with comparison operators like = , >, <, <>, >=, <=
val = ANY (set)
is true if there is at least one member of the set equal to value
val = ALL (set)
is true if all members of the set are equal to the value
Handling sets: ALL
Find the name(s) of the employee(s) who earn the highest salary
Employee:
Handling sets: ALL
Find the name(s) of the employee(s) who earn the highest salary
SELECT Name
FROM Employee
WHERE Salary >=
ALL (
SELECT Salary
FROM Employee);
Handling sets: ANY
Find the name(s) of the employee(s) who earn more than someone else
Handling sets: ANY
Find the name(s) of the employee(s) who earn more than someone else
SELECT Name
FROM Employee
WHERE Salary >
ANY (
SELECT Salary
FROM Employee);
Word Search
Word Search
Commonly used for searching product catalogues etc.
Need to search by keywords
Might need to use partial keywords
For example: Given a database of books, searching for “crypt” might return
“Cryptonomicon” by Neil Stephenson
“Applied Cryptography” by Bruce Schneier
LIKE
We can use the LIKE keyword to perform string comparisons in queries
Like is not the same as ‘=’ because it allows wildcard characters
It is NOT normally case sensitive
SELECT * FROM books
WHERE bookName LIKE “%crypt%”;
LIKE
The ‘%’ character can represent any number of characters, including none
bookName LIKE “crypt%”
Will return “Cryptography Engineering” and “Cryptonomicon” but not “Applied Cryptography”
The ‘_’ character represents exactly one character
bookName LIKE “cloud_”
Will return “Clouds” but not “Cloud” or “cloud computing”
LIKE
Sometimes you might 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 words use OR
SELECT * FROM
books
WHERE
bookName
LIKE “%crypt%”
OR bookName
LIKE “%cloud%”;
Example
Write a query to find any track title containing either the string ‘boy’ or ‘girl’
Example
SELECT Track_title FROM Track
WHERE
Track_title LIKE “%boy%”
OR
Track_title LIKE “%girl%”;
Date, Time, Datetime
3 different types for a time column
Timestamp: as Datetime, usually used to display current date and time (‘2014-11-04 15:30:43’)
Usual conditions may be used on WHERE clauses
SELECT * FROM table-name
WHERE date-of-event < ‘2012-01-01’;
Or
WHERE date-of-event LIKE ‘2014-11-%’;
Questions?
/docProps/thumbnail.jpeg