Oracle 12c: SQL

Chapter 2
Basic SQL SELECT Statements

©2016. Cengage Learning. All rights reserved.

 Objectives
• Create the initial database
• 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
• Display multiple columns of a table

©2016. Cengage Learning. All rights reserved. 2

 Objectives (continued)
• Use a column alias to clarify the contents of a
particular column
• Perform basic arithmetic operations in the
SELECT clause
• Remove duplicate lists using either the
DISTINCT or UNIQUE keyword
• Use concatenation to combine fields, literals,
and other data

©2016. Cengage Learning. All rights reserved. 3

 Create the JustLee Database

• Use the provided script to create the database so

you can follow the chapter examples
• Verify table contents using the DESCRIBE
command.
• SQL Developer:
1. From the menu, click File, Open and navigate to the JLDB_Build.sql
file.
2. Click the file, and then click the Open button. You should see the
script statements in the work area.
3. Click the Run Script button above the work area (refer to Figure 2-2)
to execute the statements. You might be prompted to select a
connection

©2016. Cengage Learning. All rights reserved. 4

 SELECT Statement Syntax

• SELECT statements are used to retrieve data from

the database
• A SELECT statement is referred to as a query
• Syntax gives the basic structure, or rules, for a
command
• Optional clauses and keywords are shown in brackets

©2016. Cengage Learning. All rights reserved. 5

 SELECT Statement Syntax
(continued)

©2016. Cengage Learning. All rights reserved. 6

 SELECT Statement Syntax
(continued)
• SELECT and FROM clauses are required
• SELECT clause identifies column(s)
• FROM clause identifies table(s)
• Each clause begins with a keyword

©2016. Cengage Learning. All rights reserved. 7

 Selecting All Data in a Table
• Substitute an asterisk for the column names
in a SELECT clause

©2016. Cengage Learning. All rights reserved. 8

 Selecting One Column from a Table
• Enter column name in SELECT clause

©2016. Cengage Learning. All rights reserved. 9

 Selecting Multiple Columns from a
Table
• Separate column names with a comma

©2016. Cengage Learning. All rights reserved. 10

 Operations within the SELECT
Statement
• Column alias can be used for column headings
• Perform arithmetic operations
• Suppress duplicates
• Concatenate data

©2016. Cengage Learning. All rights reserved. 11

 Using Column Aliases

• List the alias after the 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

©2016. Cengage Learning. All rights reserved. 12

 Column Alias Example

©2016. Cengage Learning. All rights reserved. 13

 Using Arithmetic Operations

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

©2016. Cengage Learning. All rights reserved. 14

 Example Arithmetic Operation
with Column Alias

©2016. Cengage Learning. All rights reserved. 15

 NULL Values

©2016. Cengage Learning. All rights reserved. 16

 Using DISTINCT and UNIQUE
• Enter DISTINCT or UNIQUE after SELECT keyword to
suppress duplicates

©2016. Cengage Learning. All rights reserved. 17

 Using Concatenation

• You can combine data with a string literal

• Use the concatenation operator, ||
• It allows the use of column aliases

©2016. Cengage Learning. All rights reserved. 18

 Concatenation Example

©2016. Cengage Learning. All rights reserved. 19

 Summary
• A basic query in Oracle 12c SQL includes the SELECT
and FROM clauses, the only mandatory clauses in a
SELECT statement
• To view all columns in the table, specify an asterisk (*)
or list all of the column names individually in the
SELECT clause
• To display a specific column or set of columns, list the
column names in the SELECT clause (in the order in
which you want them to appear)
• When listing column names in the SELECT clause, a
comma must separate column names

©2016. Cengage Learning. All rights reserved. 21

 Summary (continued)
• A column alias can be used to clarify the contents of a
particular column; if the alias contains spaces or special
symbols, or if you want to display the column with any
lowercase letters, you must enclose the column alias in
double quotation marks (" ")
• Indicate the table name following the FROM keyword
• Basic arithmetic operations can be performed in the
SELECT clause
• NULL values indicate an absence of a value

©2016. Cengage Learning. All rights reserved. 22

 Summary (continued)
• To remove duplicate listings, include either the
DISTINCT or UNIQUE keyword
• To specify which table contains the desired
columns, you must list the name of the table
after the keyword FROM
• Use vertical bars (||) to combine, or concatenate,
fields, literals, and other data

©2016. Cengage Learning. All rights reserved. 23