SQL: Multisets, Queries, and Relational Algebra, Lecture notes of Database Management Systems (DBMS)

Download SQL: Multisets, Queries, and Relational Algebra and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

5– 1 SQL ( 1. 1. 2 )

SQL

Oct 2017 Version: 1. 1. 2

Overview

✥ A review of SQL ✦ Basic Select statements ✦ UNION, INTERSECT, EXCEPT ✦ Nested queries ✦ Aggregate operations ✦ GROUP BY and HAVING ✦ NULL ✦ Constraints

sname age Dustin 45 Brutus 33 Lubber 55. Andy 25. Rusty 35 Horatio 35 Zorba 16 Horatio 35 Art 25. Bob 63.

Example

✥ SELECT S.sname, S.age FROM Sailors S ✥

Example, without DISTINCT

✥ This could include several copies of the same row SELECT S.sname, S.age FROM Sailors S ✥ This result is known as a multiset sname age Dustin 45 Brutus 33 Lubber 55. Andy 25. Rusty 35 Horatio 35 Zorba 16 Horatio 35 Art 25. Bob 63.

Another Example

✥ (Q 11 ) Find all sailors with a rating above 7 SELECT S.sid, S.sname, S.rating, S.age FROM Sailors AS S WHERE S.rating > 7 ✥ Notice the use of AS to as an alternative for an alias sid sname rating age 31 32 58 71 74 Lubber Andy Rusty Zorba Horatio 8 8 10 10 9

35 16 35

Another Example... using *

✥ * shorthand for “all columns” in the order in which they are defined in the table schema ✥ Poor programming style. Query changes if the schema changes SELECT * FROM Sailors AS S WHERE S.rating > 7 sid sname rating age 31 32 58 71 74 Lubber Andy Rusty Zorba Horatio 8 8 10 10 9

35 16 35

But, what is the meaning of a query?

✥ A query does not tells us how to compute it ✥ The result of a query is a relation , which is a multiset of rows ✥ A conceptual evaluation strategy (easy to understand, but not necessarily what the database uses–in fact, it is quite inefficient)

1. Compute the cross product of the tables in the from-list
2. Delete the rows in the cross-product that fail the qualification conditions
3. Delete all columns that do not appear in the select-list
4. If DISTINCT is specified, eliminate duplicate rows

sid sname rating age 22 31 58 Dustin Lubber Rusty 7 8 10 45

35

Example of Query Evaluation

✥ Q1 Find the names of sailors who have reserved boat number 103 SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid= 103 ; ✥ Assume these instances: sid bid day 22 101 1998 - 10 - 10 58 103 1998 - 11 - 12

✥ Finally, we do projection: sname Rusty

5– 14 SQL ( 1. 1. 2 )

Expressions and Strings in the SELECT

✥ Each item in the select-list can be an expression of the form expression AS column name where expression is any arithmetic or string expression over columns and constants ✥ column name becomes the name of the result column ✥ It can also contain aggregates (discussed later) ✥ Some DBMS allow the use of UD (user defined) and library functions ✥ Example: SELECT S.sname, S.rating+ 1 AS rating FROM Sailors S, Reserves R1, Reserves R 2 WHERE S.sid = R1.sid AND S.sid = R2.sid AND R1.day = R2.day AND R1.bid <> R2.bid

Pattern Matching

✥ SQL provides very rudimentary pattern matching: ✥ LIKE operator ✦ %: Wild card, match zero or more arbitrary characters ✦ : Match exactly one arbitrary character ✥ ’ AB%’ matches any string that has at least 3 chars, A as second char, and B as third one. ✥ Example: Q 18 : Find the ages of sailors whose name begins and ends with B and has at least 3 characters SELECT S.age FROM Sailors S WHERE S.sname LIKE ’B_%B%’ ✥ Notice the use of the % at the end of the string. It matches the trailing spaces

UNION

✥ Computes^ the^ union^ between^ two^ SELECT statements ✥ Q 5 : Find the names of sailors who have reserved a red or a green boat (or both) 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’) ✥ Using UNION: SELECT s.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid UNION SELECT s.sname FROM Sailors S, Reserves R, Boats B AND B.color = ’red’ WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ’green’

EXCEPT

✥ Computes^ the^ set^ difference^ between^ two^ SELECT^ statements ✥ Q 19 : Find the sids of all sailors who have reserved red boats but not green boats. SELECT s.sid FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ’red’ EXCEPT SELECT s.sid FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ’green’ ✥ Or the simpler query: SELECT r.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ’red’ EXCEPT SELECT R.sid FROM Reserves R, Boats B WHERE R.bid = B.bid AND B.color = ’green’

Nested Queries

5– 20 SQL ( 1. 1. 2 ) ✥ In SQL you can embed queries ( subqueries ) inside queries ✥ Subqueries can include conditions that refer to a relation that needs to be computed ✥ Subqueries usually appear in the WHERE clause, but can also appear in the FROM (or HAVING) ✥ Q 1 : Find the names of the sailors who have reserved boat 103 SELECT s.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid = 103 ) ✥ Q 1 : Find the names of the sailors who have NOT reserved boat 103 ✥ Replace IN with NOT IN