SQL :
STRUCTURED QUERY
LANGUAGE
Ahmet Serkan Karataş
Department of Computer Engineering
What You Can Do With SQL
➢ Create relations by SQL commands
➢ Modify tuples by SQL commands
◦ Add new tuples
◦ Update existing tuples
◦ Delete existing tuples
➢ Fetch data by SQL queries
Ahmet Serkan Karataş
CEng 301 – Database Management Systems – SQL 2
Basic SQL Query
SELECT [DISTINCT] target-list
FROM relation-list
[WHERE qualification]
➢ relation-listA list of relation names (possibly with a
range-variable after each name)
➢ target-list A list of attributes of relations in relation-list
➢ qualification Comparisons (Attr op const or Attr1 op
Attr2, where op is one of , , = , , , )
combined using AND, OR and NOT
➢ DISTINCT is an optional keyword
Ahmet Serkan Karataş
CEng 301 – Database Management Systems – SQL 3
Conceptual Evaluation Strategy
➢ Semantics of an SQL query defined in terms of the
following conceptual evaluation strategy:
◦ Compute the cross-product of relation-list
◦ Discard resulting tuples if they fail qualifications
◦ Delete attributes that are not in target-list
◦ If DISTINCT is specified, eliminate duplicate rows
➢ This strategy is probably the least efficient way to
compute a query! An optimizer will find more
efficient strategies to compute the same answers.
Ahmet Serkan Karataş
CEng 301 – Database Management Systems – SQL 4
Example of Conceptual Evaluation
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid=103
(sid) sname rating age (sid) bid day
22 dustin 7 45.0 22 101 10/10/96
22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 22 101 10/10/96
31 lubber 8 55.5 58 103 11/12/96
58 rusty 10 35.0 22 101 10/10/96
58 rusty 10 35.0 58 103 11/12/96
Ahmet Serkan Karataş
CEng 301 – Database Management Systems – SQL 5
A Note on Range Variables
➢ Really needed only if the same attribute appears
twice in the WHERE clause. The previous query can
also be written as:
SELECT sname
FROM Sailors, Reserves
WHERE Sailors.sid=Reserves.sid
AND bid=103
➢ It is good style, however, to use range variables
always!
Ahmet Serkan Karataş
CEng 301 – Database Management Systems – SQL 6
Find sailors who’ve reserved at least one boat
SELECT S.sid
FROM Sailors S, Reserves R
WHERE S.sid=R.sid
➢ Would adding DISTINCT to this query make a
difference?
➢ What is the effect of replacing S.sid by S.sname in the
SELECT clause? Would adding DISTINCT to this
variant of the query make a difference?
Ahmet Serkan Karataş
CEng 301 – Database Management Systems – SQL 7
Expressions and Strings
SELECT S.age, age1=S.age - 5, 2 * S.age AS age2
FROM Sailors S
WHERE S.sname LIKE ‘B_%s’
➢ Illustrates use of arithmetic expressions and string
pattern matching: Find triples (of ages of sailors and two
fields defined by expressions) for sailors whose names begin
and end with B and s, and contain at least three characters.
➢ AS and = are two ways to name fields in result
➢ LIKE is used for string matching. ‘_’ stands for any one
character and ‘%’ stands for 0 or more arbitrary
characters
Ahmet Serkan Karataş
CEng 301 – Database Management Systems – SQL 8
Instances for Examples
Ahmet Serkan Karataş
CEng 301 – Database Management Systems – SQL 9
Find sid’s of sailors who’ve reserved a red or a green boat
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid
AND (B.color=‘red’ OR B.color=‘green’)
• If we replace OR by AND what do we get?
Ahmet Serkan Karataş
CEng 301 – Database Management Systems – SQL 10
