Basic SQL SELECT Statements

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