![Page 1: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/1.jpg)
1
SQL SQL (Simple Query Language)(Simple Query Language)
![Page 2: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/2.jpg)
2
Query ComponentsQuery Components
• A query can contain the following clauses – select
– from
– where
– group by
– having
– order by
• Only select and from are required
• Order of clauses is always as above
![Page 3: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/3.jpg)
3
Basic SQL QueryBasic SQL Query
SELECT [Distinct] target-list
FROM relation-list
WHERE condition;
•relation-list: A list of relation names (possibly with a range-variable after each name)
•target-list: A list of fields onto which the query projects
•condition: A Boolean condition
•DISTINCT: Optional keyword to delete duplicates
![Page 4: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/4.jpg)
4
Basic SQL QueryBasic SQL Query
SELECT [Distinct] target-list
FROM relation-list
WHERE condition;
•This is confusing! The "SELECT" clause defines the projection. Selection is defined by the WHERE clause
![Page 5: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/5.jpg)
5
Basic SQL QueryBasic SQL Query
SELECT [Distinct] target-list
FROM relation-list
WHERE condition;
Evaluation:
1. Compute the cross product of the tables in from-list.
2. Delete all rows that do not satisfy condition.3. Delete all columns that do not appear in target-list.4. If Distinct is specified eliminate duplicate rows.
![Page 6: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/6.jpg)
6
Basic SQL QueryBasic SQL Query
SELECT Distinct A1,…,An
FROM R1,…,Rm
WHERE C;
A1,…,An (C(R1 x…x Rm))
![Page 7: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/7.jpg)
7
Query Without WHEREQuery Without WHERE
SELECT Distinct A1,…,An
FROM R1,…,Rm
A1,…,An (R1 x…x Rm)
![Page 8: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/8.jpg)
8
Query Without ProjectionQuery Without Projection
SELECT Distinct *
FROM R1,…,Rm
WHERE C;
(C(R1 x…x Rm))
![Page 9: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/9.jpg)
9
Query Without Projection, Query Without Projection, Without WHEREWithout WHERE
SELECT Distinct *
FROM R1,…,Rm
R1 x…x Rm
![Page 10: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/10.jpg)
10
Example Tables UsedExample Tables Used
Reserves
sid bid day
22
58
101
103
10/10/96
11/12/96
Sailors
sid sname rating age
22
31
58
Dustin
Lubber
Rusty
7
8
10
45.0
55.5
35.0
Boats
bid bname color
101
103
Nancy
Gloria
red
green
![Page 11: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/11.jpg)
11
What Are You Asking?What Are You Asking?
SELECT DISTINCT sname, age
FROM Sailors
WHERE rating>7;
• What does this compute?
• Write it in algebra
• When would the result be different if we removed distinct?
![Page 12: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/12.jpg)
12
Sailors Who Reserved Boat 103Sailors Who Reserved Boat 103
SELECT DISTINCT sname
FROM Sailors, Reserves
WHERE Sailors.sid = Reserves.sid and
bid = 103;
sname
(Sailors.sid = Reserves.sid bid = 103 (Sailors x Reserves))
![Page 13: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/13.jpg)
13
Sailors Reserves
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
Sailors x Reserves
![Page 14: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/14.jpg)
14
Sailors Reserves
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
Sailors.sid = Reserves.sid bid = 103
![Page 15: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/15.jpg)
15
Sailors Reserves
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
Sailors x Reserves
sname
![Page 16: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/16.jpg)
16
Range VariablesRange Variables
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid and
R.bid = 103;
• Range variables are good style.
• They are necessary if the same relation appears twice in the FROM clause
• Similar to Renaming in Relational Algebra
![Page 17: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/17.jpg)
17
What does this return?What does this return?
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid and
R.bid <> 103;
![Page 18: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/18.jpg)
18
A Few SELECT OptionsA Few SELECT Options
• Select all columns:SELECT * FROM Sailors S;
• Rename selected columns:SELECT S.sname AS Sailors_Name FROM Sailors S;
• Applying functions (e.g., Mathematical manipulations) SELECT (age-5)*2 FROM Sailors S;
![Page 19: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/19.jpg)
19
TheThe WHEREWHERE ClauseClause
• Numerical and string comparison:
!=,<>,=, <, >, >=, <=, between(between val1 AND val2)
String comparison is according to the alphabetical order!
• Logical components: AND, OR
• Null verification: IS NULL, IS NOT NULL
• Example:SELECT sname
FROM Sailors
WHERE age>=40 AND rating IS NOT NULL ;
![Page 20: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/20.jpg)
20
The LIKE OperatorThe LIKE Operator
• A pattern matching operator
• Basic format: colname LIKE pattern
– Example:
_ is a single character
% is 0 or more characters
SELECT sid FROM Sailors WHERE sname LIKE ‘R_%y’;
![Page 21: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/21.jpg)
21
What is this?What is this?
SELECT S.sid
FROM Sailors S, Reserves R
WHERE S.sid = R.sid;
Would adding DISTINCT give a different result?
![Page 22: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/22.jpg)
22
Are any of these the same?Are any of these the same?
SELECT S.sid
FROM Sailors S, Reserves R
WHERE S.sid = R.sid;
SELECT DISTINCT R.sid
FROM Sailors S, Reserves R
WHERE S.sid = R.sid;
SELECT R.sid
FROM Reserves R
WHERE R.sid;
![Page 23: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/23.jpg)
23
Sailors who’ve reserved two Sailors who’ve reserved two different boatsdifferent boats
![Page 24: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/24.jpg)
24
What does this return?What does this return?
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid and
R.bid = B.bid and
B.color = 'red'
![Page 25: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/25.jpg)
25
Color of Boats Reserved by BobColor of Boats Reserved by Bob
![Page 26: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/26.jpg)
26
Order Of the ResultOrder Of the Result
• The ORDER BY clause can be used to
sort results by one or more columns
• The default sorting is in ascending
order
• Can specify ASC or DESC
![Page 27: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/27.jpg)
27
ExampleExample
SELECT sname, rating, age
FROM Sailors S
WHERE age > 50
ORDER BY rating ASC, age DESC
![Page 28: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/28.jpg)
28
Other Relational Algebra Other Relational Algebra OperatorsOperators
• So far, we have seen selection,
projection and Cartesian product
• How do we do operators UNION and
MINUS?
![Page 29: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/29.jpg)
29
Three SET OperatorsThree SET Operators
• [Query] UNION [Query]
• [Query] MINUS [Query]
• [Query] INTERSECT [QUERY]
• Note: The operators remove duplicates by default!
• How would you express intersect in Relational Algebra?
Note that the standard
is EXECPT
![Page 30: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/30.jpg)
30
What does this return?What does this return?
SELECT DISTINCT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid and
R.bid = B.bid and
(B.color = 'red' or
B.color='green')
What would happen if we replaced or by and ?
![Page 31: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/31.jpg)
31
Sailors who’ve reserved red Sailors who’ve reserved red oror green boatgreen boat
SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid = R.sid and R.bid = B.bid and B.color = ‘red’
UNION
SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid = R.sid and R.bid = B.bid and B.color = ‘green’;
What would happen if we wrote MINUS? Or INTERSECT?
![Page 32: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/32.jpg)
32
Sailors who’ve reserved red Sailors who’ve reserved red andand green boatgreen boat
SELECT S.sname
FROM Sailors S, Boats B1, Reserves R1,
Boats B2, Reserves R2
WHERE S.sid = R1.sid and R1.bid = B1.bid and B1.color = ‘red’ and
S.sid = R2.sid and R2.bid = B2.bid and B2.color = ‘green’;
![Page 33: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/33.jpg)
33
Multiset (Bag) OperatorsMultiset (Bag) Operators
• SQL standard includes 3 bag operators:
– UNION ALL
– INTERSECT ALL
– MINUS ALL
• Oracle supports only UNION ALL. Does
not remove duplicates when
performing UNION
![Page 34: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/34.jpg)
34
ExampleExample
SELECT DISTINCT sname
FROM Sailors S
UNION ALL
SELECT DISTINCT sname
FROM Sailors S
![Page 35: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/35.jpg)
35
Nested QueriesNested Queries
![Page 36: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/36.jpg)
36
Nested QueriesNested Queries
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid = 103);
Names of sailors who’ve reserved boat 103:
The SELECT, FROM and WHERE clauses can have sub-queries. Conceptually, they are computed using nested loops.
What would happen if we wrote NOT IN?
![Page 37: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/37.jpg)
37
Another ExampleAnother Example
SELECT S.sname
FROM Sailors S
WHERE S.sid NOT IN
(SELECT R.sid
FROM Reserves R
WHERE R.bid IN
(SELECT B.bid
FROM Boats B
WHERE B.color='red'))
![Page 38: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/38.jpg)
38
Rewrite the Previous Query Rewrite the Previous Query Using MINUSUsing MINUS
![Page 39: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/39.jpg)
39
Correlated Nested QueriesCorrelated Nested Queries
SELECT S.sid
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid = 103 and
S.sid = R.sid);
Names of sailors who’ve reserved boat 103:
What would happen if we wrote NOT EXISTS?
![Page 40: 1 SQL (Simple Query Language). 2 Query Components A query can contain the following clauses –select –from –where –group by –having –order by Only select](https://reader036.vdocuments.us/reader036/viewer/2022062304/56649d435503460f94a1f4a9/html5/thumbnails/40.jpg)
40
Set-Comparison QueriesSet-Comparison Queries
SELECT *
FROM Sailors S1
WHERE S1.age > ANY (SELECT S2.age
FROM Sailors S2);
Sailors who are not the youngest:
We can also use op ALL (op is >, <, =, >=, <=, or <>).