Basic SQL Retrievals
SQL AS A DATA
MANIPULATION LANGUAGE
1
Basic Retrieval Queries: Results and
Semantics
• Recall that the Relational Model (RM) is set-theory
based
– Relations in RM are defined to be sets of tuples
• E.g., {a, b, c, ..}
• Unlike the RM, SQL DBs allow tables to contain
duplicates of tuples
– ==> Multi-set semantics, or bag semantics
• E.g., {a, b, a, c, b, …}
• Why allow duplicates?
– SQL query results are also tables.
– It is expensive to delete duplicates:
• This requires a sorting operation, which is expensive
2
Basic Retrieval Queries: Results and
Semantics
• Duplicates (in tables – query answers) may also be
desirable:
– e.g., different ‘entities’ have same value – (same
name or same address, or same car, …)
– e.g., for aggregates (AVG,SUM,COUNT,…)
• But, we have said that there must be a way to
uniquely identify each tuple in a table
– Remember keys, etc.
– A tuple-id (e.g., increment counter) may be used
as a key to ensure uniqueness.
3
Basic Retrieval with SQL
SELECT <attribute1,…attributei>
FROM <table list>
WHERE <condition>
• <attribute1,…attributei> is a list of attributes to be retrieved
• <table list> is a list of the relation names/tables needed to process
the query … ie the tables congaining these attributes
• <condition> is a boolean expression
– only return tuples whose attribute values satisfy the condition
(evaluates to a true value for this expression)
4
Example
students Note 1: No condition !
studentID studentName • Trivially all tuples
1 Bob
evaluate to true è are
2 Alice
3 Bob selected in the result!
SELECT studentName
FROM students; Note 2:
• Output is a new table
derived from students!
studentName
Bob
• Note that the table contains
Alice duplicates – bag semantics
Bob
5
Example: Use DISTINCT to remove duplicates
students
studentID studentName
1 Bob
2 Alice
3 Bob
• The use of the DISTINCT
SELECT DISTINCT studentName keyword removes any
FROM students; duplicates from the output
• It turns bags to sets!
studentName
Bob
Alice
6
Example – Add Conditions using WHERE
students
studentID studentName • The first row in the table
1 Bob fails the WHERE clause,
2 Alice – the ‘Bob’ in the
3 Bob
output has come from
row 3
SELECT studentName
FROM students
WHERE studentID > 1; WHERE can have multiple
boolean expressions (they
resolve down to a single True
or False).
studentName
Alice • <expr1> AND <expr2>
Bob • <expr1> OR <expr2>
• …NOT, combinations,…
7
Asterisk in SELECT
SELECT *
FROM students;
• Wildcard matching
• Useful shortcut - fetches all attributes
8
Naming Qualification
Qualification
SELECT [Link] FROM students;
• Specifically references the studentID column/attribute from
the students Table
• Useful if tables in your table list have a shared attribute name
between them - removes ambiguity
• Simplifies code understanding
9
Naming Qualification and Aliasing
Qualification + Aliasing
SELECT [Link] FROM students S;
The students table has been aliased to the name S.
• The name S is now used for any qualification (including in
WHERE clauses)
• Can also write: ... FROM students AS S
10
Select-From-Where: More Examples
Principal form review:
SELECT desired attributes
FROM one or more tables
WHERE boolean expression of tuple-attribute values of interest;
Running example database schema:
Beers(name, manf) • Logical comparison
Bars(name, addr, license) operators:
Drinkers(name, addr, phone) =, <, <=, >, >=, and <>
• Can also use BETWEEN
Likes(drinker, beer)
low-value AND high-value
Sells(bar, beer, price)
Frequents(drinker, bar)
Notation: underlined attributes are keys.
11
Another Example of a Basic Query
• Query table Beers(name, manf), to find out what beers are
made by Anheuser-Busch?
SELECT name
FROM Beers
WHERE manf = ’Anheuser-Busch’;
SQL uses single-quotes for strings.
• SQL is case-insensitive, except inside strings.
12
Result of Query
name The answer is a relation with
Bud • a single attribute, name, specified in
Bud Lite SELECT, and
• tuples for which manf =Anheuser-
Michelob Busch, such as Bud.
13
Compute Result for a Single-Relation Query
• Start with the relation in the FROM clause.
• Apply the row-selection indicated by the WHERE clause.
• Apply the column-projection indicated by the SELECT
clause.
Operational Semantics
Imagine a tuple variable ranging over all tuples of the
relation mentioned in FROM.
For each tuple:
• Check if it satisfies the predicates/expressions in WHERE
clause.
• If so, compute the attributes or expressions of the SELECT
clause using the components of this tuple.
• Move to next tuple
• A SQL query only produces a tuple in the answer if its truth
value for the WHERE-clause evaluates to TRUE.
14
Operational Semantics
tv name manf
Bud Anheuser-Busch
…. ….
…. …
Check if
Anheuser-Busch Include [Link]
in the result
SELECT name
FROM Beers
WHERE manf = ’Anheuser-Busch’;
15
Semantics of Single Table Queries
Compare to a Java method defined on a class with the same variables
as columns in the table.
SELECT dateofBirth, houseNumber, street
FROM Employee
WHERE city = ‘Warwick’;
public void employeeDetails() // in class Employee
{
println( “dateofBirth” + “ “ + “houseNumber” + “ “ + “street” )
for (int e=0; e<nEmployees; e++)
if (city == “Warwick”)
println( dateofBirth + “ “ + houseNumber + “ “ + street );
}
• we iterate through the set of tuples
– for each one we evaluate the boolean expression in WHERE
– If TRUE, output the values specified in SELECT clause
16
Wildcards for attributes, ordering result tuples
Beers(name, manf)
SELECT *
FROM Beers
WHERE manf = 'Anheuser-Busch’
ORDER BY name;
Sorting by one or more attributes:
ORDER BY attribute(s) [ASC|DESC]
name manf
Bud Anhauser-Busch
Bud Lite Anhauser-Busch
Michelob Anhauser-Busch
17
Ordering of Query Results
students studentID studentName
1 Bob
2 Alice
3 Brian
SELECT * FROM students
ORDER BY studentName;
studentID studentName
2 Alice
1 Bob
3 Brian
Rows are sorted by the studentName column (in this case,
alphabetically) 18
Ordering of Query Results
students
studentID studentName
1 Bob
2 Alice
3 Brian
SELECT * FROM students SELECT * FROM students
ORDER BY studentID ASC; ORDER BY studentID DESC;
studentID studentName studentID studentName
1 Bob 3 Brian
2 Alice 2 Alice
3 Brian 1 Bob
Rows are sorted by the studentID, in ascending or descending order
19
Can use ORDER BY also after a WHERE clause.
Renaming columns
• If you want the result to have different attribute names,
use “AS < new name>” to rename an attribute
Beers(name, manf)
SELECT name AS beername, manf
FROM Beers
WHERE manf = 'Anheuser-Busch‘;
beername manf
Bud Anhauser-Busch
Bud Lite Anhauser-Busch
Michelob Anhauser-Busch
20
Expressions as Values in Columns
• Any expression that makes sense can appear as an
element of a SELECT clause. Numeric value
Sells (bar, beer, price)
SELECT bar, beer, price*147 AS priceInYen
FROM Sells;
bar beer priceInYen
Joe’s Bud 300
Sue’s Miller 360
21
Constant Expressions – Sophisticated Outputs
• If you want an answer with a particular string (a constant)
in each row, use that constant as an expression.
String value
Likes(drinker, beer)
SELECT drinker, 'likes Bud' AS whoLikesBud
FROM Likes
WHERE beer = 'Bud';
drinker whoLikesBud
Sally likes Bud
Fred likes Bud
22
Conditions in WHERE Clause
• Query: From Sells(bar, beer, price), find the price Joe’s Bar
charges for Bud:
SELECT price Notice how we
get a single-quote
FROM Sells in strings.
WHERE bar = ’Joe’’s Bar’ AND
beer = ’Bud’;
Conditions in WHERE clause can use AND, OR, NOT
and parentheses in the usual way Boolean conditions
are built.
23
Strings in WHERE Clauses
• WHERE clauses can also have conditions for strings, in which a
string is compared with a pattern, to see if it matches.
• General form:
– <StringAttribute> LIKE <pattern> or
– <StringAttribute> NOT LIKE <pattern>
• Pattern is a quoted string with special characters
• % = “any string”;
• _ = “any character”
• % is used to stand for zero or more characters
• _ stands for exactly one character
Example: From Drinkers(name, addr, phone), find drinkers
whose phone has area code 167.
SELECT name
FROM Drinkers
WHERE phone LIKE '%167-_ _ _ _ _ _ _’;
24
NULL values
• Tuples in SQL relations can have NULL as a “special
value” for one or more attributes.
• This needs special care:
– NULLs are not real values
• e.g., they cannot be compared to with expressions
• Useful in many contexts. Three common cases:
– Unknown value
– Unavailable value: e.g., we know Joe’s Bar has some address, but
we don’t know what it is.
– Inapplicable: e.g., the value of attribute spouse for an unmarried
person.
– NULLs are not really values – just indicate the above.
• Each individual NULL value considered to be different
from every other NULL value
– If Peter’s phoneNumber is NULL and Penny’s phoneNumber is
NULL, we can NOT conclude that they have the same phone
number! 25
Comparing NULL’s to Values
• Recall: The WHERE clause in SQL provides logical
Boolean expressions (using AND/OR/NOT…) in
order to identify the tuples desired by the query.
– A SQL query only produces a tuple in the answer
if its truth value for the WHERE-clause evaluates
to TRUE.
– Consider
• SELECT manf WHERE beer = ‘AMSTEL’;
• What if a tuple has NULL as its beer-name value?
– To account for NULLs, however, the logic of
conditions in SQL is not just TRUE/FALSE, but a
3-valued logic instead, yielding: TRUE, FALSE,
and UNKNOWN.
• When any value is compared with NULL, the truth
value is UNKNOWN 26
NULLs and 3-Valued Logic
AND TRUE FALSE UNKNOWN
TRUE TRUE FALSE UNKNOWN
FALSE FALSE FALSE FALSE
UNKNOWN UNKNOWN FALSE UNKNOWN
OR TRUE FALSE UNKNOWN
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE UNKNOWN
UNKNOWN TRUE UNKNOWN UNKNOWN
NOT
TRUE FALSE
FALSE TRUE
UNKNOWN UNKNOWN 27
How to Use NULL in Predicates
• We need to be able to test whether a value is NULL.
• Syntax: for checking if attribute A is NULL
• “A IS NULL”
– – use instead of “A = NULL”
– “Attribute=NULL” is never true
• “<Attribute> IS NOT NULL”
The query
SELECT bar
FROM Sells
WHERE price=NULL;
will produce no tuples!
28
Next video lecture is on
SQL for DB updates
THANKS !
29