1 / 76

Section 3 - Select Statement

Section 3 - Select Statement . The Select statement allows you to... Display Data Specify Selection Criteria Sort Data Group Data for reporting Use Functions Join Tables Nest Selects within other Selects. Basic SELECT Syntax. SELECT list_of_columns FROM table[s] [WHERE search_conditions].

sergiol
Télécharger la présentation

Section 3 - Select Statement

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. Section 3 - Select Statement • The Select statement allows you to... • Display Data • Specify Selection Criteria • Sort Data • Group Data for reporting • Use Functions • Join Tables • Nest Selects within other Selects

  2. Basic SELECT Syntax • SELECT list_of_columnsFROM table[s][WHERE search_conditions]

  3. Example • Sample data in table:Au_lname Au_fname City------------ ------------- -------------Adams John New YorkSmith Sam ChicagoThomson Tom Los Angeles

  4. Example - cont. • SELECT cityFROM authorsWHERE au_lname = 'Smith'; • Results:City------------Chicago

  5. Discussion • This statement returns one row from the sample data where the last name was equal to 'Smith' • Then the city column is the only column displayed from that row

  6. WHERE Clause • The Where clause restricts the number of rows returned from a table • SELECT au_lname, au_fname, stateFROM authorsWHERE state = 'CA'; • Returns only rows where the author lives in California • SELECT au_lname, au_fname, stateFROM authors; • Returns all the rows in the table

  7. Full SELECT Syntax • SELECT [ALL|DISTINCT] select_listFROM {table_name|view_name} [,{table_name|view_name}]...[WHERE search_conditions][GROUP BY column_1 [, column_2]...] HAVING search_conditions][ORDER BY {column_1 | 1} [ASC| DESC] [,{column_2| 2 } [ASC | DESC ] ...] ;

  8. Discussion • Each of these clauses will be explained in detail as we proceed through the class Lessons • The ‘search-conditions’ part of the statement will be expanded greatly

  9. Order of Clauses • SQL is a Free-form language • You may add spaces, skip lines, etc. • But, the CLAUSEs must be in syntactical order • e.g. FROM must come before WHERE GROUP BY must come before HAVING

  10. Qualification of Names • Prefix column names with the table name and/or owner id • e.g. SELECT dba.authors.au_lname • Qualification is always allowed • Qualification MUST be used if there is any ambiguity • e.g. you are referencing two tables in the SELECT statement and both tables have a column named CITY

  11. The SELECT list • You may include the following in a Select list: • ALL vs. DISTINCT (removes duplicates) • Column(s)... or * (show all columns) • Expressions (e.g. price * 2) • Constants (e.g. 'DOG') • Functions (e.g. Avg(advance) ) • Any Combination of Above

  12. Examples • SELECT * FROM authors; • SELECT title, price * 2 FROM titles; • SELECT 'BOOK: ', title, 14.95/5 FROM titles • SELECT AVG(advance)FROM titles

  13. Computations with Constants • Available arithmetic operators + addition - subtraction / division * multiplication • Used with Numeric datatypes • + may be used with character types to concatenate strings (SQLAnywhere only)

  14. Arithmetic Order Precedence • Expressions inside parenthesis are interpreted first • Then from left to right multiplication & division are calculated • Last from left to right subtraction & addition are computed

  15. What Happens Here? • SELECT title_id, ytd_sales - price * advanceFROM titles;

  16. Discussion • SELECT title_id, price - 2 * advanceFROM titles;For each row returned, first the‘2 * advance’ is calculated, then it is subtracted from the price

  17. Parenthesis to Clarify • SELECT title_id, (price - 2) * advanceFROM titles; • Here we subtract 2 from the price, then multiply it by the advance • Always use parenthesis to aid with clarity

  18. Exercise • List all books and their price. Reduce the price by $3.00 then reduce it again by 25%.Display each book's current revenue based on the original price.

  19. Discussion • First we list the books and their priceSELECT title, priceFROM titles

  20. Discussion • Now we reduce the price by $3.00SELECT title, price - 3FROM titles

  21. Discussion • Now we reduce the price by another 25%SELECT title, price - 3 * .75FROM titles

  22. Discussion • Wait a minute, that won't work. It will first multiply 3 * .75 and then subtract it from price. Let's use parenthesis.SELECT title, (price - 3) * .75FROM titles

  23. Discussion • How do we get the current revenue for each book? We multiple the price by the ytd_sales count.SELECT title, (price - 3) * .75, ytd_sales * price FROM titles

  24. FROM Clause • Lists the tables or views that contain the columns referred to in the Select list or other clauses • Syntax: SELECT <select_list> FROM [qualifier] {table|view} [, [qualifier] {table|view} ] ... ;

  25. Examples • SELECT cityFROM authors; • SELECT cityFROM dba.authors; • SELECT title, pub_nameFROM titles, publishersWHERE title.pub_id = publishers.pub_id;

  26. Aliases • Use may use table name aliases to save typing • Specify a short-name for a table at least one space after the table name(e.g. FROM authors a, titleauthors ta'a' could now be used in the rest of the statement to represent the table authors and 'ta' could be used instead of titleauthors

  27. Examples • instead of:SELECT authors.au_id, au_ordFROM authors, titleauthorsWHERE authors.au_id = titleauthors.au_id; • use:SELECT a.au_id, au_ordFROM authors a, titleauthors taWHERE a.au_id = ta.au_id;

  28. Exercise • Produce a list of all books printed by each publisher. Use aliases.

  29. Discussion • SELECT pub_name, titleFROM publishers p, title tWHERE p.pub_id = t.pub_id;This connects (joins) the publishers table to the titles table using the pub_id column they share in common.

  30. WHERE Clause • Used to select rows from a set of search criteria • Syntax:SELECT select_listFROM table_listWHERE search_conditions;

  31. Comparison Operators • =, < > Equal, Not Equal • <, > Less than, Greater than • >=, <= Greater than or Equal to, Less than or Equal to • NOT Negates the entire expression

  32. Example • SELECT title, advanceFROM titlesWHERE advance > ytd_sales * price;This displays the title and advance where the advance is greater than the product of ytd_sales multiplied by price

  33. Numeric vs. Character, Date • You can use comparison operators with all datatypes... • Numeric: > 3 means greater than three • Character: > 'C' means 'D' or later in the alphabet • Date: > '97/10/02' means greater than Oct. 2, 1997

  34. Example • SELECT title, priceFROM titlesWHERE price > 15.00;Returns all rows that have a price greater than $15.00

  35. Example • SELECT au_lname, au_fnameFROM authorsWHERE au_lname > 'McBadden';Returns all authors whose last name comes after McBadden alphabetically

  36. Logical Operators • Use when there is more than one condition • AND • both conditions must be true for a row to be returned in the result • OR • At least one condition must be true for a row to be returned in the result • NOT • Negates the results of the following condition

  37. Example • SELECT au_lname, au_fnameFROM authorsWHERE city = 'Springfield'AND state = 'CA';This will return authors who live in Springfield, CA but not authors who live in Springfield, IL

  38. Example • SELECT au_lname, au_fnameFROM authorsWHERE zip_code = '92008'OR zip_code = '92009';This will return authors who live in zip code 92008 and authors who live in zip code 92009

  39. Example • SELECT au_lname, au_fnameFROM authorsWHERE NOT state = 'CA';This determines which authors live in California and then negates the result and returns all authors who do not live in California

  40. Example • SELECT au_lname, au_fnameFROM authorsWHERE state <> 'CA';This returns the authors who do not live in California. This is another way to get the same result as the last statement.

  41. Combinations of Conditions • You can combine conditions • SELECT title, advance, ytd_sales, priceFROM titlesWHERE (advance < 500 OR ytd_sales >= 5000) AND (NOT price > 20.00);

  42. Logical Operator Precedence • Parenthesis • Multiplication & Division • Subtraction & Addition • NOT • AND • OR

  43. Exercise • List all business books and list all psychology books that have an advance greater than $5000

  44. Discussion • SELECT title, type, advanceFROM titlesWHERE type = 'business' OR type = 'psychology'AND advance > 5000;What's the order of precedence?

  45. Discussion • The AND is evaluated before the OR so the statement will work, but it is always better to use parenthesis for clarity • SELECT title, type, advanceFROM titlesWHERE type = 'business' OR (type = 'psychology'AND advance > 5000);

  46. Question • What question does this SQL statement answer?SELECT title, type, advanceFROM titlesWHERE (type = 'business' OR type = 'psychology')AND advance > 5000;

  47. Answer • Which psychology or business books have been given an advance greater than $5000?

  48. Exercise • Find all books that were published before Oct. 15, 1985 whose current revenue is less than twice the advance paid to the author.HINT: ytd_sales column represents number of books sold.

  49. Discussion • SELECT titleFROM titlesWHERE (pubdate < '98/10/15' )AND (ytd_sales * price) < (2 * advance);Parenthesis not necessary, but makes the statement easier to understand

  50. Ranges • Comparison operators > and < • exclusive range check • BETWEEN and NOT BETWEEN • inclusive range check

More Related