Basic SQL SELECT Statements - PowerPoint PPT Presentation

hank
basic sql select statements n.
Skip this Video
Loading SlideShow in 5 Seconds..
Basic SQL SELECT Statements PowerPoint Presentation
Download Presentation
Basic SQL SELECT Statements

play fullscreen
1 / 43
Download Presentation
Basic SQL SELECT Statements
97 Views
Download Presentation

Basic SQL SELECT Statements

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Basic SQL SELECT Statements Introduction to Oracle9i: SQL

  2. Chapter Objectives • Distinguish between an RDBMS and an ORDBMS • Identify keywords, mandatory clauses, and optional clauses in a SELECT statement • Select and view all columns of a table • Select and view one column of a table Introduction to Oracle9i: SQL

  3. Chapter Objectives • Display multiple columns of a table • Use a column alias to clarify the contents of a particular column • Perform basic arithmetic operations in the SELECT clause Introduction to Oracle9i: SQL

  4. Chapter Objectives • Remove duplicate lists, using either the DISTINCT or UNIQUE keyword • Combine fields, literals, and other data • Format output Introduction to Oracle9i: SQL

  5. Relational Database Management System (RDBMS) An RDBMS is the software program used to create the database and it allows you to enter, manipulate, and retrieve data Introduction to Oracle9i: SQL

  6. Object Relational Database Management System (ORDBMS) Same as an RDBMS except it can be used to reference objects such as maps and object fields Introduction to Oracle9i: SQL

  7. SELECT Statement Syntax • SELECT statements are used to retrieve data from the database • Syntax gives the basic structure, or rules, for a command Introduction to Oracle9i: SQL

  8. SELECT Statement Syntax Optional clauses and keywords are shown in brackets Introduction to Oracle9i: SQL

  9. SELECT Statement Syntax • SELECT and FROM clauses are required • SELECT clause identifies column(s) • FROM clause identifies table(s) • Each clause begins with a keyword Introduction to Oracle9i: SQL

  10. Selecting All Data in a Table Substitute an asterisk for the column names in a SELECT clause Introduction to Oracle9i: SQL

  11. Selecting One Column from a Table Enter column name in SELECT clause Introduction to Oracle9i: SQL

  12. Selecting Multiple Columns from a Table Separate column names with a comma Introduction to Oracle9i: SQL

  13. Operations Within the SELECT Statement • Column alias can be used for column headings • Perform arithmetic operations • Suppress duplicates • Concatenate data Introduction to Oracle9i: SQL

  14. Column Alias • List after column heading • AS keyword is optional • Enclose in double quotation marks: • If it contains blank space(s) • If it contains special symbol(s) • To retain case Introduction to Oracle9i: SQL

  15. Column Alias Example Introduction to Oracle9i: SQL

  16. Arithmetic Operations • Executed left to right • Multiplication and division are solved first • Addition and subtraction are solved last • Override order with parentheses Introduction to Oracle9i: SQL

  17. Example Arithmetic Operation with Column Alias Introduction to Oracle9i: SQL

  18. Suppressing Duplicates Enter DISTINCT or UNIQUE after SELECT keyword Introduction to Oracle9i: SQL

  19. Concatenation • Can combine data with string literal • Use concatenation operator, || • Allows use of column alias Introduction to Oracle9i: SQL

  20. Concatenation Example Introduction to Oracle9i: SQL

  21. Purpose of Joins • Joins are used to link tables and reconstruct data in a relational database • Joins can be created through: • Conditions in a WHERE clause • Use of JOIN keywords in FROM clause Introduction to Oracle9i: SQL

  22. Cartesian Join • Created by omitting joining condition in the WHERE clause or through CROSS JOIN keywords in the FROM clause • Results in every possible row combination (m * n) Introduction to Oracle9i: SQL

  23. Cartesian Join Example:Omitted Condition Introduction to Oracle9i: SQL

  24. Cartesian Join Example:CROSS JOIN Keywords Introduction to Oracle9i: SQL

  25. Equality Join • Links rows through equivalent data that exists in both tables • Created by: • Creating equivalency condition in the WHERE clause • Using NATURAL JOIN, JOIN…USING, or JOIN…ON keywords in the FROM clause Introduction to Oracle9i: SQL

  26. Equality Join: WHERE Clause Example Introduction to Oracle9i: SQL

  27. Equality Join: NATURAL JOIN Syntax: tablename NATURAL JOIN tablename Introduction to Oracle9i: SQL

  28. Equality Join: JOIN…USING Syntax: tablename JOIN tablename USING (columnname) Introduction to Oracle9i: SQL

  29. Equality Join: JOIN…ON Syntax: tablename JOIN tablename ON condition Introduction to Oracle9i: SQL

  30. JOIN Keyword Overview • Use NATURAL JOIN when tables have one column in common • Use JOIN…USING when tables have more than one column in common • Use JOIN…ON when a condition is needed to specify a relationship other than equivalency • Using JOIN keyword frees the WHERE clause for exclusive use in restricting rows Introduction to Oracle9i: SQL

  31. Non-Equality Joins • In WHERE clause, use any comparison operator other than equal sign • In FROM clause, use JOIN…ON keywords with non-equivalent condition Introduction to Oracle9i: SQL

  32. Non-Equality Join: WHERE Clause Example Introduction to Oracle9i: SQL

  33. Non-Equality Join: JOIN…ON Example Introduction to Oracle9i: SQL

  34. Self-Joins • Used to link a table to itself • Requires use of column qualifier Introduction to Oracle9i: SQL

  35. Self-Join: WHERE Clause Example Introduction to Oracle9i: SQL

  36. Self-Join: JOIN…ON Example Introduction to Oracle9i: SQL

  37. Outer Joins // • Use to include rows that do not have a match in the other table • In WHERE clause, include outer join operator (+) next to table with missing rows to add NULL rows • In FROM clause, use FULL, LEFT, or RIGHT with OUTER JOIN keywords Introduction to Oracle9i: SQL

  38. Outer Join: WHERE Clause Example Introduction to Oracle9i: SQL

  39. Outer Join: OUTER JOIN Keyword Example Introduction to Oracle9i: SQL

  40. Set Operators XX Used to combine the results of two or more SELECT statements Introduction to Oracle9i: SQL

  41. Set Operator Example Introduction to Oracle9i: SQL

  42. Joining Three or More Tables • Same procedure as joining two tables • Will always results in one less join than the number of tables being joined Introduction to Oracle9i: SQL

  43. Joining Three or More Tables: Example Introduction to Oracle9i: SQL