Relational Database concepts

·       Explaining the theoretical and physical aspects of a relational database

·       Relating clauses in SQL Select Statement to Components of an ERD

·       Explaining the relationship between a database and SQL

 

Retrieving Data using the SQL SELECT Statement

·       Using Column aliases

·       Using The SQL SELECT statement

·       Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword

·       Using Arithmetic expressions and NULL values in the SELECT statement

 

Restricting and Sorting Data

·       Applying Rules of precedence for operators in an expression

·       Limiting Rows Returned in a SQL Statement

·       Using Substitution Variables

·       Using the DEFINE and VERIFY commands

·       Sorting Data

 

Using Single-Row Functions to Customize Output

·       Manipulating strings with character functions in SQL SELECT and WHERE clauses

·       Performing arithmetic with date data

·       Manipulating numbers with the ROUND, TRUNC and MOD functions

·       Manipulating dates with the date function

 

Using Conversion Functions and Conditional Expressions

·       Applying the NVL, NULLIF, and COALESCE functions to data

·       Understanding implicit and explicit data type conversion

·       Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

·       Nesting multiple functions

 

Reporting Aggregated Data Using Group Functions

·       Restricting Group Results

·       Creating Groups of Data

·       Using Group Functions

 

Displaying Data from Multiple Tables

·       Using Self-joins

·       Using Various Types of Joins

·       Using Non equijoins

·       Using OUTER joins

·       Understanding and Using Cartesian Products

 

Using Subqueries to Solve Queries

·       Using Single Row Subqueries

·       Using Multiple Row Subqueries

·       Update and delete rows using correlated subqueries

 

Using SET Operators

·       Matching the SELECT statements

·       Using the ORDER BY clause in set operations

·       Using The INTERSECT operator

·       Using The MINUS operator

·       Using The UNION and UNION ALL operators

 

Managing Tables using DML statements

·       Managing Database Transactions

·       Controlling transactions

·       Perform Insert, Update and Delete operations

·       Performing multi table Inserts

·       Performing Merge statements

 

Managing Indexes Synonyms and Sequences

·       Managing Indexes

·       Managing Synonyms

·       Managing Sequences

 

Use DDL to manage tables and their relationships

·       Describing and Working with Tables

·       Describing and Working with Columns and Data Types

·       Creating tables

·       Dropping columns and setting column UNUSED

·       Truncating tables

·       Creating and using Temporary Tables

·       Creating and using external tables

·       Managing Constraints

 

Managing Views

·       Managing Views

 

Controlling User Access

·       Differentiating system privileges from object privileges

·       Granting privileges on tables

·       Distinguishing between granting privileges and roles

 

Managing Objects with Data Dictionary Views

·       Using data dictionary views

 

Managing Data in Different Time Zones

·       Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP

·       Working with INTERVAL data types