Study guide
2.1 SQL SELECT Fundamentals & Projection
Oracle Database SQL Certified Associate 1Z0-071
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Relational Database Concepts & Logical Modeling
1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals
2 Chapter 2: Retrieving Data with SQL SELECT
2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection
3 Chapter 3: Restricting and Sorting Query Data
3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives
4 Chapter 4: Customizing Output with Single-Row Functions
4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions
5 Chapter 5: Conversion Functions and Conditional Expressions
5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE
6 Chapter 6: Reporting Aggregated Data with Group Functions
6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause
7 Chapter 7: Displaying Data from Multiple Tables (Joins)
7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL
8 Chapter 8: Solving Queries with Subqueries
8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps
9 Chapter 9: Set Operators
9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules
10 Chapter 10: Managing Tables with DML & Transaction Control
10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT
11 Chapter 11: Managing Tables and Relationships with DDL
11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables
12 Chapter 12: Views
12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints
13 Chapter 13: Indexes, Synonyms, and Sequences
13.1 Sequences 13.2 Indexes 13.3 Synonyms
14 Chapter 14: Controlling User Access & Privileges
14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management
15 Chapter 15: Oracle Data Dictionary Views
15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments
16 Chapter 16: Managing Data in Different Time Zones
16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment
2.1 SQL SELECT Fundamentals & Projection
Key Takeaways Explain This Topic AI
Projection specifies which columns to retrieve from a table, selection restricts which rows are returned, and joining links related data across multiple tables.
The SELECT and FROM clauses are the two mandatory clauses in an Oracle SQL query to retrieve data from a database table or view.
Arithmetic expressions (+, -, *, /) follow standard operator precedence, where multiplication and division take precedence over addition and subtraction unless parentheses override evaluation order.
Column aliases rename output headers; aliases default to uppercase unless enclosed in double quotes ("..."), which preserve case sensitivity, spaces, and special characters.
Single quotes ('...') define string literals, whereas double quotes ("...") define database object identifiers and column aliases; mixing them up triggers syntax error ORA-00923.
Last updated: August 2026
SQL SELECT Fundamentals & Projection
The SELECT statement is the fundamental Data Query Language (DQL) command in Oracle SQL. It empowers database developers and analysts to query data stored in relational database tables, views, and materialized views. Understanding how Oracle processes query syntax, projects specific attributes, evaluates mathematical operations, and formats column headers provides the bedrock for mastering the Oracle Database SQL Certified Associate (1Z0-071) examination.
1. Relational Query Capabilities: Projection, Selection, and Joining
Dr. E.F. Codd defined three core relational operations that a relational query language must support. The Oracle SQL SELECT statement implements all three: +-----------------------------------------------------------------------------+
- | RELATIONAL QUERY CAPABILITIES IN SQL |
- | |
- | 1. PROJECTION (Vertical Subset) |
- | SELECT employee_id, last_name, salary FROM employees; |
- | +-------------+-------------+----------+ |
- | | EMPLOYEE_ID | LAST_NAME | SALARY | <-- Only specified columns |
- | +-------------+-------------+----------+ |
- | |
- | 2. SELECTION / RESTRICTION (Horizontal Subset) |
- | SELECT * FROM employees WHERE department_id = 60; |
- | +-------------+-------------+----------+ |
- | | 103 | Hunold | 9000 | <-- Only matching rows |
- | | 104 | Ernst | 6000 | |
- | +-------------+-------------+----------+ |
- | |
- | 3. JOINING (Combining Tables) |
- | SELECT e.last_name, d.department_name |
- | FROM employees e JOIN departments d ON e.department_id = d.department_id;|
- | +-------------+-----------------------+ |
- | | King | Executive | <-- Data from linked tables |
- | +-------------+-----------------------+ |
- +-----------------------------------------------------------------------------+
Capability Relational Action Primary SQL Mechanism
Projection Chooses a vertical subset of columns from a table, ignoring all other columns. SELECT column1, column2
Selection (Restriction) Chooses a horizontal subset of rows based on specified search criteria. WHERE condition
Joining Connects and retrieves data stored across multiple tables through related keys. FROM table1 JOIN table2 ON ...
2. Basic SELECT Syntax and Clause Requirements
In Oracle SQL, the simplest valid query requires at least two mandatory clauses: the SELECT clause and the FROM clause. SELECT [DISTINCT | ALL] { * | column | expression [AS alias], ... }
FROM schema_name.table_name;
SELECT Clause: Identifies the column list or calculated expressions to be projected into the output.
FROM Clause: Identifies the database table, view, inline view, or synonym containing the projected columns.
Querying All Columns ( * )
To retrieve every column in the exact sequence in which the columns were defined when the table was created (or altered), use the asterisk wildcard character ( * ): SELECT *
FROM employees;
Note
While SELECT * is convenient for ad-hoc querying, writing production queries with explicitly named columns is an industry best practice. Explicit projection reduces network bandwidth overhead and prevents application failure if table schema structures change.
Querying Specific Columns (Projection)
To project specific attributes, separate each column name with a comma: SELECT employee_id, first_name, last_name, email, hire_date
- FROM employees;
- Simulated Result Set: EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL HIRE_DATE
- ---------- -------------------- ------------------------- ------------------------- ---------
- 100 Steven King SKING 17-JUN-03
- 101 Neena Kochhar NKOCHHAR 21-SEP-05
- 102 Lex De Haan LDEHAAN 13-JAN-01
- 3. Arithmetic Expressions and Operator Precedence
You can perform mathematical operations on projected numeric and date columns using standard arithmetic operators.
- Operator Description Supported Datatypes
- + Addition NUMBER , DATE , TIMESTAMP , INTERVAL
- Subtraction NUMBER , DATE , TIMESTAMP , INTERVAL
- * Multiplication NUMBER
- / Division NUMBER
- Operator Precedence Hierarchy
Oracle evaluates arithmetic operators based on strict precedence rules:
Parentheses () : Expressions inside parentheses are evaluated first, from the innermost level to the outermost level.
Multiplication and Division ( * , / ): Evaluated second. If multiple * and / operators occur together, Oracle evaluates them from left to right .
Addition and Subtraction ( + , - ): Evaluated last. If multiple + and - operators occur together, Oracle evaluates them from left to right . -- Expression 1: Multiplication takes precedence over addition
- SELECT last_name, salary, 12 * salary + 100
- FROM employees;
- - Expression 2: Parentheses override default precedence
- SELECT last_name, salary, 12 * (salary + 100)
- FROM employees;
Execution Breakdown:
Suppose salary is 5000 :
In Expression 1: 12 * 5000 = 60000 , then 60000 + 100 = 60100 .
In Expression 2: 5000 + 100 = 5100 , then 12 * 5100 = 61200 . +-----------------------------------------------------------------------------+
- | ARITHMETIC PRECEDENCE COMPARISON |
- | |
- | Query 1: 12 * salary + 100 |
- | Step 1: [12 * 5000] = 60000 (Multiplication First) |
- | Step 2: [60000 + 100] = 60100 (Addition Second) |
- | |
- | Query 2: 12 * (salary + 100) |
- | Step 1: (5000 + 100) = 5100 (Parentheses First) |
- | Step 2: [12 * 5100] = 61200 (Multiplication Second) |
- +-----------------------------------------------------------------------------+
- 4. Default Column Headings & Formatting Behavior
When Oracle SQL outputs the results of a query, default formatting rules apply to the column headings:
Default Text Case: Column headings are displayed in ALL UPPERCASE letters.
Default Alignment:
Character and Date column values/headings are left-aligned .
Numeric column values/headings are right-aligned .
Calculated Headers: If an expression is projected without an alias, Oracle displays the raw expression string in uppercase (e.g., SALARY*12 ).
5. Column Aliases: Rules, Syntax, and Options
A column alias renames a column heading in the query result set. Aliases are essential for making calculated expressions readable and giving business-friendly titles to database fields.
- Basic Column Alias Syntax: -- Method A: Space separator (standard SQL)
- SELECT last_name name, salary * 12 annual_salary
- FROM employees;
- - Method B: Using optional 'AS' keyword (recommended for readability)
- SELECT last_name AS name, salary * 12 AS annual_salary
- FROM employees;
Tip
The AS keyword is optional between the column name/expression and its alias. However, using AS makes the code significantly easier to read and maintain.
Double-Quoted Aliases: Preserving Case, Spaces, and Special Symbols
By default, Oracle converts all unquoted column aliases to uppercase in the result set. To override this default, you must enclose the alias in double quotation marks ( "..." ).
Double quotation marks are mandatory when an alias:
Contains spaces (e.g., "Annual Salary" ).
Requires exact mixed-case or lowercase formatting (e.g., "Monthly Pay" ).
Contains special characters or punctuation (e.g., "Total ($)" , "Employee #" ).
- Matches a reserved SQL keyword (e.g., "SELECT" , "FROM" , "GROUP" ). SELECT last_name AS "Employee Surname",
- salary AS "Monthly",
- salary * 12 AS "Annual Salary ($)",
- department_id AS "Dept #"
- FROM employees;
- Result Set Display: Employee Surname Monthly Annual Salary ($) Dept #
- ------------------------ --------- ----------------- ----------
- King 24000 288000 90
- Kochhar 17000 204000 90
- De Haan 17000 204000 90
- 6. Critical 1Z0-071 Exam Traps: Quotes and Keywords
- Trap 1: Single Quotes vs. Double Quotes
This is one of the most frequently tested syntax traps on the 1Z0-071 exam:
Single quotes ( '...' ): Used exclusively for character string and date literals (data values).
Double quotes ( "..." ): Used exclusively for database object identifiers, column aliases, and case-sensitive schema names. -- INVALID: Using single quotes for an alias triggers ORA-00923
- SELECT last_name AS 'Employee Name' FROM employees;
- - ORA-00923: FROM keyword not found where expected
- - VALID: Double quotes must be used for aliases
- SELECT last_name AS "Employee Name" FROM employees;
- Trap 2: The AS Keyword in Table Aliases vs. Column Aliases
Column Aliases: The AS keyword is optional and valid ( SELECT salary AS sal FROM employees ).
Table Aliases: The AS keyword is STRICTLY INVALID in Oracle SQL ( FROM employees AS e fails with ORA-00933: SQL command not properly ended ).
Trap 3: Missing Commas and Accidental Aliasing
If you omit a comma between two column names, Oracle does not report a syntax error; instead, it treats the second column name as an alias for the first column! -- Intent: Retrieve first_name AND last_name
- - Actual execution: Retrieves first_name, renaming the column header to LAST_NAME
- SELECT first_name last_name
- FROM employees;
- Trap 4: Trailing Commas in the SELECT List
Placing a comma after the final expression before the FROM keyword is invalid: -- INVALID: Trailing comma before FROM
- SELECT employee_id, last_name, salary,
- FROM employees;
- - ORA-00936: missing expression