Basic SQL Queries Database Management Systems 3 ed

Basic SQL Queries Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 1

Introduction Introduce SQL, the standard query language for relational DBS. v An SQL query takes one or more input tables and returns one output table. v We’ll start with the case of one input table v Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 2

Example Instances Boats Sailors bid 101 103 colour green red Reserves Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 3

Overview Basic SQL syntax v Set Operations v Joins v Advanced Queries v § nested queries § comparing sets, strings. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 4

Sinlge-Table SQL Queries Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 5

target- SELECT [DISTINCT] FROM WHERE table qualification Basic SQL Syntaxlist table A table name (possibly with a range-variable). v target-list A list of attributes of relations in relation-list v qualification Comparisons (Attr op const or Attr 1 op Attr 2, where op is one of ) combined using AND, OR and NOT. v DISTINCT is an optional keyword indicating that the answer should not contain duplicates. Default is that duplicates are not eliminated! v Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 6

Conceptual Evaluation v Semantics of an SQL query defined in terms of the following conceptual evaluation strategy: § Retrieve the tuples in table. § Discard resulting tuples if they fail qualifications. § Delete attributes that are not in target-list. § If DISTINCT is specified, eliminate duplicate rows. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 7

BASIC OPERATORS IN SQL Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 9

SELECT (columns, values) SELECT S. sname FROM Sailors S Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 10

WHERE v The WHERE clause selects/filters rows. SELECT * FROM Sailors S WHERE S. sid = 58 Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 11

Renaming Relations: Views A view is just a relation, but we store a definition, rather than a set of tuples. v Any SQL query can be stored as a view. v Views can be dropped using the DROP VIEW command. v CREATE VIEW My. Sailors (sid, name) AS SELECT S. sid, S. sname FROM Sailors S WHERE S. age = 45. 0 OR S. rating = 10 Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 12

Renaming Columns v the AS keyword can be used to rename columns. SELECT S. sid AS Sailor. Sid, R. sid AS Reserve. Sid FROM Sailors S, Reserves R Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 13

Expressions and Strings SELECT S. age, age 1=S. age-5, FROM Sailors S WHERE S. sname LIKE ‘b_%b’ v v v 2*S. age AS age 2 Illustrates use of arithmetic expressions and string pattern matching: Find results for sailors whose names begin and end with B 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. case sensitivity Oracle on Strings Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 14

BASIC SET OPERATORS IN SQL Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 15

Union v v v Find sid’s of sailors who are 45 years old or have rating 10. UNION: Can be used to compute the union of any two union-compatible sets of tuples (which are themselves the result of SQL queries). If we replace OR by AND in the first version, what do we get? SELECT S. sid FROM Sailors S WHERE S. age = 45. 0 OR S. rating = 10 SELECT S. sid FROM Sailors S WHERE S. age = 45. 0 UNION SELECT S. sid FROM Sailors S WHERE S. rating = 10 Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 16

Intersections v v v Find sid’s of sailors who are 45 years old and have rating 10. INTERSECT: Can be used to compute the intersection of any two unioncompatible sets of tuples. Not supported in Mysql, but yes in SQL Server SELECT S. sid FROM Sailors S WHERE S. age = 45. 0 INTERSECT SELECT S. sid FROM Sailors S WHERE S. rating = 10 Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 17

Set Difference v v v Find sid’s of sailors who are 45 years old and do not have rating 10. EXCEPT: Can be used to compute the set difference of any two unioncompatible sets of tuples. Not supported in My. SQL but yes in SQL Server. SELECT S. sid FROM Sailors S WHERE S. age = 45. 0 EXCEPT SELECT S. sid FROM Sailors S WHERE S. rating = 10 Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 18

multiple tables TABLE JOINS Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 19

target- SELECT [DISTINCT] FROM WHERE relation-list qualification Basic SQL Syntaxlist relation-list A list of table names (possibly with rangevariables). v target-list A list of attributes of relations in relation-list v qualification Comparisons (Attr op const or Attr 1 op Attr 2, where op is one of ) combined using AND, OR and NOT. v Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 20

Conceptual Evaluation 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. v Short version: v compute the cross-product of the named tables. v Apply single-table evaluation to cross-product. v Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 21

Example of Conceptual Evaluation SELECT FROM WHERE * Sailors S, Reserves R S. sid=R. sid AND R. bid=103 Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 22

Cross Product v FROM List is interpreted as cross product. SELECT * FROM Sailors, Reserves v With JOIN keyword SELECT * FROM Sailors CROSS JOIN Reserves Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 23

Joining Two Tables v WHERE clause specifies selection condition SELECT FROM WHERE (sid) * Sailors S, Reserves R S. sid=R. sid sname rating age (sid) 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 Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke bid day 24

JOIN ON With JOIN keyword v ON replaces WHERE v SELECT * FROM Sailors S JOIN Reserves R ON S. sid=R. sid AND R. bid=103 Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 25

Natural Join forces equality on columns with the same name. v Not supported in SQL Server but yes in My. SQL. v Can replace INTERSECT. v SELECT * FROM Sailors NATURAL JOIN Reserves SELECT * FROM (SELECT S. sid FROM Sailors S WHERE S. age = 45. 0 NATURAL JOIN SELECT S. sid FROM Sailors S WHERE S. rating = 10) Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 26

My. SQL Natural Join Syntax SELECT * FROM (SELECT S. sid FROM Sailors S WHERE S. age = 45. 0) Sailor. Age NATURAL JOIN (SELECT S. sid FROM Sailors S WHERE S. rating = 10) Sailor. Rating; • must name derived (defined) tables • could use views instead Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 27

Examples and Exercises Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 28

Natural Join Exercise 1. What is the natural join of a table with itself? 1. e. g. select * from Sailors natural join Sailors? 2. What is the natural join of two tables that have exactly the same columns? Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 29

Find sailors who’ve reserved at least one boat SELECT S. sid FROM Sailors S, Reserves WHERE S. sid=R. sid R Would adding DISTINCT to this query make a difference? v 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? v Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 30

Exercise v 1. 2. Write the following queries in SQL: Find sid’s of sailors who have reserved a red or a green boat. Find sid’s of sailors who have reserved a red and a green boat. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 31

Operator Exercise Consider the following schema. Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) The Catalog lists the prices charged for parts by Suppliers. Write the following queries in SQL: 1. Find the pnames of parts for which there is some supplier. 2. Find the sids of suppliers who supply a red part or a green part. 3. Find the sids of suppliers who supply a red part and a green part. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 32

Intermediate SQL Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 33

Set Membership v Find sailors who are 45 years old and have rating 10. <tuple> IN <relation> returns TRUE if the tuple is contained in the relation. v Can replace INTERSECT. v SELECT * FROM Sailors S WHERE S. age = 45. 0 AND S. sid IN (SELECT sid FROM Sailors WHERE rating = 10) Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 34

NOT IN can replace EXCEPT v Find sid’s of sailors who are 45 years old and do not have rating 10. SELECT S. sid FROM Sailors S WHERE S. age = 45. 0 AND S. sid NOT IN (SELECT sid FROM Sailors WHERE rating = 10) Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 35

NESTED QUERIES Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 36

Nested Queries SELECT S. sid FROM Sailors S WHERE S. sid NOT IN (SELECT sid FROM Sailors WHERE rating = 10) A powerful feature of SQL: a WHERE clause can itself contain an SQL query! v So can FROM clauses. v To find sailors who’ve not reserved #103, use NOT IN. v To understand semantics of nested queries, think of a nested loops evaluation: For each Sailors tuple, check the qualification by computing the subquery. v Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 37

Nested Queries with Correlation Find names of sailors who’ve reserved boat #103 SELECT S. sname FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R. bid=103 AND v S. sid=R. sid) EXISTS <query> returns TRUE if the query is not empty. v Subquery must be re-evaluated for each Sailors tuple. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 38

“All” Queries in SQL Find sailors who’ve reserved all boats. • The negation trick. • Find sailors who have not reserved all boats. • Use NOT or EXCEPT to find the others. Database Management Systems 3 ed, SELECT S. sname FROM Sailors S WHERE not EXISTS exists a not reserved boat? ((SELECT B. bid FROM Boats B) find all boats EXCEPT find boats (SELECT R. bid that have FROM Reserves R been WHERE R. sid=S. sid)) reserved by Sailor S R. Ramakrishnan and J. Gehrke 39

Exercise ctd. Consider the following schema. Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) The Catalog lists the prices charged for parts by Suppliers. Write the following queries in SQL. You can use NOT EXISTS. 1. Find the sids of suppliers who supply only red parts. 2. Find the snames of suppliers who supply every part. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 40

SET COMPARISONS Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 41

More Set-Comparison Operators We’ve already seen IN, EXISTS. v Also available: op ANY, op ALL v Find sailors whose rating is greater than that of some sailor called Horatio: v SELECT * FROM Sailors S WHERE S. rating > ANY (SELECT S 2. rating FROM Sailors S 2 WHERE S 2. sname=‘Horatio’) Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 42

Simple Examples for Any and All 1 = Any {1, 3} v 1 = All {1, 3} v 1 = Any {} v 1 = All {} v True False True Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 43

Exercise (III) Consider the following schema. Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) The Catalog lists the prices charged for parts by Suppliers. Write the following query in SQL using the ALL construct, 1. Find the snames of suppliers who supply only red parts. Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 44

Summary: SQL Set Operators UNION, INTERSECT, EXCEPT behave like their set theory/relational algebra counterpart. v New Operator EXISTS tests if a relation is empty. v Can use ANY, ALL to compare a value against values in a set. v Database Management Systems 3 ed, R. Ramakrishnan and J. Gehrke 45