Basic SQL SELECT Statements

1
Basic SQL SELECT Statements
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

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

4
Chapter Objectives

• Remove duplicate lists, using either the DISTINCT
  or UNIQUE keyword
• Combine fields, literals, and other data
• Format output

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

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

7
SELECT Statement Syntax

• SELECT statements are used to retrieve data from
  the database
• Syntax gives the basic structure, or rules, for a
  command

8
SELECT Statement Syntax

• Optional clauses and keywords are shown in
  brackets

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

10
Selecting All Data in a Table

• Substitute an asterisk for the column names in a
  SELECT clause

11
Selecting One Column from a Table

• Enter column name in SELECT clause

12
Selecting Multiple Columns from a Table

• Separate column names with a comma

13
Operations Within the SELECT Statement

• Column alias can be used for column headings
• Perform arithmetic operations
• Suppress duplicates
• Concatenate data

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

15
Column Alias Example
16
Arithmetic Operations

• Executed left to right
• Multiplication and division are solved first
• Addition and subtraction are solved last
• Override order with parentheses

17
Example Arithmetic Operation with Column Alias
18
Suppressing Duplicates

• Enter DISTINCT or UNIQUE after SELECT keyword

19
Concatenation

• Can combine data with string literal
• Use concatenation operator,
• Allows use of column alias

20
Concatenation Example
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

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)

23
Cartesian Join ExampleOmitted Condition
24
Cartesian Join ExampleCROSS JOIN Keywords
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, JOINUSING, or JOINON
  keywords in the FROM clause

26
Equality Join WHERE Clause Example
27
Equality Join NATURAL JOIN

• Syntax tablename NATURAL JOIN tablename

28
Equality Join JOINUSING

• Syntax tablename JOIN tablename USING
  (columnname)

29
Equality Join JOINON

• Syntax tablename JOIN tablename ON condition

30
JOIN Keyword Overview

• Use NATURAL JOIN when tables have one column in
  common
• Use JOINUSING when tables have more than one
  column in common
• Use JOINON 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

31
Non-Equality Joins

• In WHERE clause, use any comparison operator
  other than equal sign
• In FROM clause, use JOINON keywords with
  non-equivalent condition

32
Non-Equality Join WHERE Clause Example
33
Non-Equality Join JOINON Example
34
Self-Joins

• Used to link a table to itself
• Requires use of column qualifier

35
Self-Join WHERE Clause Example
36
Self-Join JOINON Example
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

38
Outer Join WHERE Clause Example
39
Outer Join OUTER JOIN Keyword Example
40
Set Operators XX

• Used to combine the results of two or more
  SELECT statements

41
Set Operator Example
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

43
Joining Three or More Tables Example