Scribd
Upload
22 views

03 - SQL - Part 1

Uploaded by

berkeunver2
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
22 views

03 - SQL - Part 1

Uploaded by

berkeunver2
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 10

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

You might also like