Basic SQL SELECT Statements Introduction to Oracle 9
Basic SQL SELECT Statements Introduction to Oracle 9 i: SQL 1
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 Oracle 9 i: SQL 2
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 Oracle 9 i: SQL 3
Chapter Objectives • Remove duplicate lists, using either the DISTINCT or UNIQUE keyword • Combine fields, literals, and other data • Format output Introduction to Oracle 9 i: SQL 4
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 Oracle 9 i: SQL 5
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 Oracle 9 i: SQL 6
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 Oracle 9 i: SQL 7
SELECT Statement Syntax Optional clauses and keywords are shown in brackets Introduction to Oracle 9 i: SQL 8
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 Oracle 9 i: SQL 9
Selecting All Data in a Table Substitute an asterisk for the column names in a SELECT clause Introduction to Oracle 9 i: SQL 10
Selecting One Column from a Table Enter column name in SELECT clause Introduction to Oracle 9 i: SQL 11
Selecting Multiple Columns from a Table Separate column names with a comma Introduction to Oracle 9 i: SQL 12
Operations Within the SELECT Statement • Column alias can be used for column headings • Perform arithmetic operations • Suppress duplicates • Concatenate data Introduction to Oracle 9 i: SQL 13
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 Oracle 9 i: SQL 14
Column Alias Example Introduction to Oracle 9 i: SQL 15
Arithmetic Operations • • Executed left to right Multiplication and division are solved first Addition and subtraction are solved last Override order with parentheses Introduction to Oracle 9 i: SQL 16
Example Arithmetic Operation with Column Alias Introduction to Oracle 9 i: SQL 17
Suppressing Duplicates Enter DISTINCT or UNIQUE after SELECT keyword Introduction to Oracle 9 i: SQL 18
Concatenation • Can combine data with string literal • Use concatenation operator, || • Allows use of column alias Introduction to Oracle 9 i: SQL 19
Concatenation Example Introduction to Oracle 9 i: SQL 20
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 Oracle 9 i: SQL 21
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 Oracle 9 i: SQL 22
Cartesian Join Example: Omitted Condition Introduction to Oracle 9 i: SQL 23
Cartesian Join Example: CROSS JOIN Keywords Introduction to Oracle 9 i: SQL 24
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 Oracle 9 i: SQL 25
Equality Join: WHERE Clause Example Introduction to Oracle 9 i: SQL 26
Equality Join: NATURAL JOIN Syntax: tablename NATURAL JOIN tablename Introduction to Oracle 9 i: SQL 27
Equality Join: JOIN…USING Syntax: tablename JOIN tablename USING (columnname) Introduction to Oracle 9 i: SQL 28
Equality Join: JOIN…ON Syntax: tablename JOIN tablename ON condition Introduction to Oracle 9 i: SQL 29
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 Oracle 9 i: SQL 30
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 Oracle 9 i: SQL 31
Non-Equality Join: WHERE Clause Example Introduction to Oracle 9 i: SQL 32
Non-Equality Join: JOIN…ON Example Introduction to Oracle 9 i: SQL 33
Self-Joins • Used to link a table to itself • Requires use of column qualifier Introduction to Oracle 9 i: SQL 34
Self-Join: WHERE Clause Example Introduction to Oracle 9 i: SQL 35
Self-Join: JOIN…ON Example Introduction to Oracle 9 i: SQL 36
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 Oracle 9 i: SQL 37
Outer Join: WHERE Clause Example Introduction to Oracle 9 i: SQL 38
Outer Join: OUTER JOIN Keyword Example Introduction to Oracle 9 i: SQL 39
Set Operators XX Used to combine the results of two or more SELECT statements Introduction to Oracle 9 i: SQL 40
Set Operator Example Introduction to Oracle 9 i: SQL 41
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 Oracle 9 i: SQL 42
Joining Three or More Tables: Example Introduction to Oracle 9 i: SQL 43
- Slides: 43