Database Design, Tools & Techniques with Oracle DB

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

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

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 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

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 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 Indexes Synonyms and Sequences

• Managing Indexes
• Managing Synonyms
• Managing Sequences

Managing Views

• Managing Views
• Using data dictionary views to manage objects

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

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

Reporting Aggregated Data Using Group Functions

• Restricting Group Results
• Creating Groups of Data
• Using Group Functions

Using Subqueries to Solve Queries

• Using Single Row Subqueries
• Using Multiple Row Subqueries
• Update and delete rows using correlated subqueries

Managing Tables using DML statements

• Managing Database Transactions
• Controlling transactions
• Perform Insert, Update and Delete operations
• Performing multi table Inserts
• Performing Merge statements

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

Controlling User Access

• Differentiating system privileges from object privileges
• Granting privileges on tables
• Distinguishing between granting privileges and roles

Managing Data in Different Time Zones

• Working with CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• Working with INTERVAL data types