INF3707
NOTES
,Oracle 12c: SQL 2-1
Chapter 2
Basic SQL SELECT Statements
At a Glance
Instructor’s Notes
♦ Chapter Overview
♦ Chapter Objectives
♦ Instructor Notes
♦ Troubleshooting Tips
♦ Quick Quizzes
♦ Discussion Questions
♦ Key Terms
,Oracle 12c: SQL 2-2
Chapter Overview
The purpose of this chapter is to learn the basic SELECT statement used to retrieve data from a
database table. The students will learn to use the SELECT clause to retrieve all columns, one
column, and multiple columns from a table specified in the FROM clause. In addition, students
learn how to perform simple arithmetic operations and concatenation in the SELECT clause.
Students will need to execute the JLDB_Build.sql script file. Each student should be assigned a
different user name so the generated tables will be located in a different schema for each student.
Each student must be granted sufficient privileges to execute statements to create tables and
execute queries.
Chapter Objectives
After completing this chapter, you should be able to do the following:
♦ 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
♦ 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
Instructor Notes
SELECT Statement Syntax
Every SELECT statement is required to have a SELECT and FROM clause. Each statement
clause begins with a keyword. The SELECT clause is used to identify the column or columns to
be retrieved from a table. The name of the table is identified in the FROM clause. The structure
of the SELECT statement is depicted in Figure 2-2. This chapter only addresses the SELECT and
FROM clauses. Later chapters will address all of the remaining clauses.
, Oracle 12c: SQL 2-3
Quick Quiz
1. How do you retrieve all columns from a table without listing the column names?
ANSWER: Use an asterisk in the SELECT clause
2. How do you separate multiple columns listed in a SELECT statement?
ANSWER: Use commas
3. Are keywords case sensitive?
ANSWER: No
4. Are column names case sensitive?
ANSWER: No
5. Where do you identify the table containing the specified columns?
ANSWER: In the FROM clause
Troubleshooting Tip Demonstrate that SQL is not case sensitive in terms of the
command keywords or column/table names when a command is
executed. Also demonstrate the various methods of executing an
SQL statement.
Troubleshooting Tip Identify the two different Oracle SQL tool interfaces: the client
SQL*Plus and SQL Developer. Appendix B introduces these
interfaces.
Selecting All Data in a Table
The asterisk is used to represent all columns in a table. The asterisk is not a wildcard in the
traditional sense. It has a specific use in SQL commands. If the asterisk is used in the SELECT
clause, the clause can contain no other column references.
Selecting One Column from a Table
To select a specific column from a table, list the name of the column after the SELECT keyword.
Note that the column name can be entered in uppercase, lowercase, or mixed case. However, the
column name is displayed in uppercase characters by default.
NOTES
,Oracle 12c: SQL 2-1
Chapter 2
Basic SQL SELECT Statements
At a Glance
Instructor’s Notes
♦ Chapter Overview
♦ Chapter Objectives
♦ Instructor Notes
♦ Troubleshooting Tips
♦ Quick Quizzes
♦ Discussion Questions
♦ Key Terms
,Oracle 12c: SQL 2-2
Chapter Overview
The purpose of this chapter is to learn the basic SELECT statement used to retrieve data from a
database table. The students will learn to use the SELECT clause to retrieve all columns, one
column, and multiple columns from a table specified in the FROM clause. In addition, students
learn how to perform simple arithmetic operations and concatenation in the SELECT clause.
Students will need to execute the JLDB_Build.sql script file. Each student should be assigned a
different user name so the generated tables will be located in a different schema for each student.
Each student must be granted sufficient privileges to execute statements to create tables and
execute queries.
Chapter Objectives
After completing this chapter, you should be able to do the following:
♦ 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
♦ 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
Instructor Notes
SELECT Statement Syntax
Every SELECT statement is required to have a SELECT and FROM clause. Each statement
clause begins with a keyword. The SELECT clause is used to identify the column or columns to
be retrieved from a table. The name of the table is identified in the FROM clause. The structure
of the SELECT statement is depicted in Figure 2-2. This chapter only addresses the SELECT and
FROM clauses. Later chapters will address all of the remaining clauses.
, Oracle 12c: SQL 2-3
Quick Quiz
1. How do you retrieve all columns from a table without listing the column names?
ANSWER: Use an asterisk in the SELECT clause
2. How do you separate multiple columns listed in a SELECT statement?
ANSWER: Use commas
3. Are keywords case sensitive?
ANSWER: No
4. Are column names case sensitive?
ANSWER: No
5. Where do you identify the table containing the specified columns?
ANSWER: In the FROM clause
Troubleshooting Tip Demonstrate that SQL is not case sensitive in terms of the
command keywords or column/table names when a command is
executed. Also demonstrate the various methods of executing an
SQL statement.
Troubleshooting Tip Identify the two different Oracle SQL tool interfaces: the client
SQL*Plus and SQL Developer. Appendix B introduces these
interfaces.
Selecting All Data in a Table
The asterisk is used to represent all columns in a table. The asterisk is not a wildcard in the
traditional sense. It has a specific use in SQL commands. If the asterisk is used in the SELECT
clause, the clause can contain no other column references.
Selecting One Column from a Table
To select a specific column from a table, list the name of the column after the SELECT keyword.
Note that the column name can be entered in uppercase, lowercase, or mixed case. However, the
column name is displayed in uppercase characters by default.
