Database tables hold a large number of rows, which are often not sorted, in any specific order. This course will teach you how to selectively return only a subset of rows, sorted and customized. This course is one of a series in the Skillsoft learning path that cover the objectives for the Oracle Database 12c: SQL Fundamentals exam (1ZO-061). The exam is part of the Oracle Certified Associate certification.
Learning Objectives
Restricting and Sorting Data
- start the course
- describe the limiting of rows selected in a query and how to use the WHERE clause in a SQL statement
- use character strings and dates in a WHERE clause in a SQL statement
- use the comparison operators in a WHERE clause in a SQL statement
- use the BETWEEN and IN operators in a WHERE clause in a SQL statement
- use the LIKE operator and combine wildcard characters in a WHERE clause in a SQL statement
- use the NULL and logical operator conditions in a WHERE clause in a SQL statement
- use the AND, OR and NOT operators in a WHERE clause in a SQL statement
- define the rules of precedence in a WHERE clause in a SQL statement
- use the ORDER BY clause to sort the data returned by a SQL statement
- use the ROW_LIMITING_CLAUSE to limit the number of rows returned by a SQL statement
Substitution Variables
- describe the use of substitution variables in a SQL statement and use a single-ampersand substitution variable to provide a value in a SQL statement
- use substitution variables for column names, expressions and text in a SQL statement
- reuse a substitution variable by making use a double-ampersand substitution variable in a SQL statement
- use the DEFINE and VERIFY commands to create, assign, and verify a substitution variable when using SQL Developer
Practice: Substitution Variables with WHERE
- write a SQL statement using the WHERE clause and pass a substitution variable to the SQL statement
Single-row Functions
- identify the types of SQL functions in a SQL statement and describe single-row functions
- use character functions in a SQL statement
- use the case-conversion functions in a SQL statement
- use the character-manipulation functions in a SQL statement
- use the nesting functions in a SQL statement
- use the numeric ROUND and TRUNC functions in a SQL statement
- use the MOD function in a SQL statement
Using Date Functions
- work with dates and the RR date format in a SQL statement
- use SYSDATE, CURRENT_DATE and CURRENT_TIMESTAMP functions in a SQL statement
- use arithmetic operators with dates in a SQL statement
- describe and use date-manipulation functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY in a SQL statement
- use the numeric ROUND and TRUNC functions with dates
Practice: Character, Number, and Date
- write a SQL statement using the CHARACTER, NUMBER, and DATE functions