1 / 25

The SQL Query Language DML

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.

colton
Download Presentation

The SQL Query Language DML

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The SQL Query LanguageDML The SQL Query LanguageDML (SELECT) --The SQL Query Language DML--

  2. 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--

  3. 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--

  4. 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--

  5. 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--

  6. 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--

  7. 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--

  8. 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--

  9. 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--

  10. 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--

  11. 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--

  12. 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--

  13. 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--

  14. 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--

  15. 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--

  16. 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--

  17. 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--

  18. 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--

  19. 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--

  20. ANY • ANY is also equivalent to exists: • (246800 < ANY ) is true. • (369121 < ANY ) is false. --The SQL Query Language DML--

  21. ANY, cont. • (246800 = ANY ) is true. • (246800 <> ANY) is true. • Comparison with IN • (= ANY) IN • (<> ANY) NOT IN / --The SQL Query Language DML--

  22. 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--

  23. ALL • ALL is equivalent to for all: • (246800 < ALL ) is false. • (100000 < ALL ) is true. --The SQL Query Language DML--

  24. ALL, cont. • 246800 = ALL ) is false. • (100000 < ALL ) is true • Comparison with IN • (<> ALL)  NOT IN • (= ALL)  IN / --The SQL Query Language DML--

  25. 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--

More Related