250 likes | 368 Views
The SQL Query Language DML. The SQL Query Language DML (SELECT). Outline - The SELECT statement. Single table Projection Selection Multiple tables Cartesian product and join Set operations Subqueries Optional clauses Ordering results Computing aggregates on groups Additional joins.
E N D
The SQL Query LanguageDML The SQL Query LanguageDML (SELECT) --The SQL Query Language DML--
Outline - The SELECT statement • Single table • Projection • Selection • Multiple tables • Cartesian product and join • Set operations • Subqueries • Optional clauses • Ordering results • Computing aggregates on groups • Additional joins --The SQL Query Language DML--
Retrieval Queries in SQL: SELECT • SQL has one basic statement for retrieving information from a database; the SELECT statement. • The basic form of the SQL SELECT statement is called a mapping or a select-from-where block. SELECT column list FROM table list WHERE condition --The SQL Query Language DML--
SELECT: Projecting a Column • Find the names of all Customers. • This query is termed a projection query. • Duplicates are eliminated by specifying DISTINCT. • In which cities do customers live, each city listed once? SELECT DISTINCT City FROM Customer --The SQL Query Language DML--
SELECT: Projecting all Columns • All the columns can be specified with *. • Make a copy of the Customer table. SELECT * FROM Customer • Can eliminate duplicate rows. SELECT DISTINCT * FROM Customer --The SQL Query Language DML--
SELECT: Renaming Columns and Tables • Columns can be renamed in generalized projection. • Project the City column as Towns • Tables can be given different names within the body of the SELECT statement. These are called correlation variables or names. • Refer to the Customer table as C. --The SQL Query Language DML--
SELECT: Selecting Rows • WHERE clause is used to choose only rows that meet some condition. • Condition can be simple comparison of a column value to • a constant • a column value • result of a SELECT • Which customers live in Dublin? SELECT DISTINCT Name FROM Customer WHERE City = ’Dublin’ --The SQL Query Language DML--
SELECT: Using Logical Operators • AND, OR, NOT may be used on result of comparison • List outrageously priced films (over $4 or under $1). SELECT *FROM FilmWHERERentalPrice > 4 OR RentalPrice < 1 • List films that are not outrageously priced. More than one table can be listed in the from clause. SELECT DISTINCT *FROM FilmWHERENOT(RentalPrice > 4 OR RentalPrice < 1) --The SQL Query Language DML--
Outline - The SELECT statement • Single table • Projection • Selection • Multiple tables • Cartesian product and join • Set operations • Subqueries • Optional clauses • Ordering results • Computing aggregates on groups • Additional joins --The SQL Query Language DML--
SELECT: Two or more tables • FROM clause can specify a list of tables, evaluates as Cartesian product of all the tables specified • List the titles of the reserved films. SELECT DISTINCT TitleFROM Film, ReservedWHERE Reserved.FilmID = Film.FilmID --The SQL Query Language DML--
SELECT: Queries Over Several Tables • List the customers who have reserved an expensive film. SELECTDISTINCT NameFROM Customer, Film, ReservedWHERE Reserved.CustomerID = Customer.CustomerIDAND Reserved.FilmID = Film.FilmIDAND RentalPrice > 4 • List the streets of customers who have reserved foreign films. SELECT StreetFROM Customer, Film, ReservedWHERE Reserved.CustomerID = Customer.CustomerID AND Reserved.FilmID = Film.FilmID AND Kind = ’F’ --The SQL Query Language DML--
SELECT: Self Joins • Tables can be referenced several times, using correlation names. • Which customers have reserved a film that customer 123456 has reserved? SELECT B.CustomerIDFROM Reserved A, Reserved BWHERE A.CustomerID = 123456 AND A.FilmID = B.FilmID AND A.CustomerID <> B. CustomerID • Which films have a higher rental price than some foreign film? SELECT DISTINCT A.TitleFROM Film A, Film BWHERE A.RentalPrice > B.RentalPrice AND B.Kind = ’F’ --The SQL Query Language DML--
Union • The algebraic operators of union (), intersection () and difference (-) are available, as UNION, INTERSECT, and EXCEPT. • These operations eliminate duplicates! • Must be union-compatible • List the outrageously priced films (over $4 or under $1). (SELECT Title FROM Film WHERE RentalPrice > 4) UNION (SELECT Title FROM Film WHERE RentalPrice < 1) --The SQL Query Language DML--
Outline - The SELECT statement • Single table • Projection • Selection • Multiple tables • Cartesian product and join • Set operations • Subqueries • Optional clauses • Ordering results • Computing aggregates on groups • Additional joins --The SQL Query Language DML--
SELECT: Subqueries • A SELECT may be nested SELECT … FROM … WHERE <cond> ( SELECT … FROM … WHERE …) • Subqueries may produce • A scalar (single value) • A single--column table • ANY, ALL, IN, EXISTS • A multiple-column table • EXISTS • Correlated subqueries --The SQL Query Language DML--
Scalar Producing Subquery • The subquery produces a single value that can be compared • What are the IDs of customers with the same name as the customer with ID 123456? SELECT CustomerID FROM Customer WHERE name = (SELECT name FROM Customer WHERE CustomerID = 123456) --The SQL Query Language DML--
Single Attribute Producing Subquery • The subquery produces a table with a single column These operations eliminate duplicates! • IN • true if value exists in result of subquery • Comparison Operator ANY • true for comparison with at least one tuple in subquery produced table • Comparison Operator ALL • true for comparison with every tuple in subquery produced table --The SQL Query Language DML--
IN • IN is equivalent to a restricted form of exists: • (246800 IN ) is true. • (333333 IN ) is false. • (333333NOT IN ) is true --The SQL Query Language DML--
IN Query • List the ID numbers of the films that are expensive and have been reserved. SELECT FilmID FROM Film WHERE RentalPrice > 4 AND FilmID IN ( SELECT FilmID FROM Reserved) • List the ID numbers of the expensive films that have not been reserved. SELECT FilmID FROM Film WHERE RentalPrice > 4 AND FilmID NOT IN (SELECT FilmID FROM Reserved) --The SQL Query Language DML--
ANY • ANY is also equivalent to exists: • (246800 < ANY ) is true. • (369121 < ANY ) is false. --The SQL Query Language DML--
ANY, cont. • (246800 = ANY ) is true. • (246800 <> ANY) is true. • Comparison with IN • (= ANY) IN • (<> ANY) NOT IN / --The SQL Query Language DML--
ANY Query • Which films rent for more than some foreign film? SELECT TitleFROM FilmWHERE RentalPrice > ANY(SELECT RentalPriceFROM FilmWHEREKind= ’F’) --The SQL Query Language DML--
ALL • ALL is equivalent to for all: • (246800 < ALL ) is false. • (100000 < ALL ) is true. --The SQL Query Language DML--
ALL, cont. • 246800 = ALL ) is false. • (100000 < ALL ) is true • Comparison with IN • (<> ALL) NOT IN • (= ALL) IN / --The SQL Query Language DML--
ALL Query • Find the film(s) with the highest rental price. SELECT TitleFROM FilmWHERE RentalPrice >= ALL ( SELECT RentalPrice FROM Film ) --The SQL Query Language DML--