Course Objective:
- Introduction to RDBMS
- Overview of Data Modelling
- Implementation in an RDBMS:
- Entities and Tables
- Attributes and Columns
- Tuples and Rows
- Relationships and Keys
- Describe SQL as a Language
- Overview Of Oracle's Main Architectural components
- Describe how Oracle implements SQL and allows users to Interact with the Data
- SQL Plus and SQL Developer Tools
- using the SqlPlus Interface
- SQL commands versus SqlPlus commands
- Using scripts with SqlPlus
- Connecting via SQL Developer
- Opening an SQL Developer worksheet
- Running command in SQL Developer
- Running scripts in SQL Developer
- Writing SELECT statements
- Define and explain the terms: projection, selection and join
- The Basic SQL SELECT statement
- Selecting single and multiple columns
- Statements containing arithmetic operators
- Operator Precedence
- Using Literal strings
- Quotes and the quote operator
- The use of column aliases
- The concatenation operator
- The Null character; its definition and usage
- Interpreting Nulls in various expressions
- Restricting and Sorting Data
- Limiting Rows During a Selection
- Using the WHERE Clause
- Explain the main Comparison Operators
- Using the LIKE Operator to Compare Literal Values
- Explain the Logical Operators AND, OR, NOT
- Using Multiple Conditions in the WHERE clause
- Describe the Rules of Precedence
- The ORDER BY Clause
- Accessing Data From Multiple Tables
- Joining Multiple Tables
- Selecting Across Multiple Tables (FROM Clause)
- Restrictions Across Multiple Tables (WHERE Clause)
- Cartesian Joins
- Natural Joins
- Inner Joins
- Out Joins; Left, Right and Full
- Self Joins
- The Use of Aliases to Simplify Statements
- The USING clause
- The ON clause
- Substitution Variables
- The && Substitution Variable
- The DEFINE Command
- The VERIFY Command
- Using SQL Functions
- Explain Single Row Functions
- Case and Character Manipulation Functions
- Numeric Manipulation Functions; MOD, ROUND and TRUNC
- Working with Dates
- Date Formatting and Conversion Functions
- Date Manipulation Functions
- Arithmetical Operation on Dates
- Data Type Conversion, Explicit and Implicit
- Conditional Operators; CASE, DECODE
- Group By clause and Aggregate Functions
- Types of Group Functions
- The AVG, SUM, MAX, MIN, and COUNT Functions
- The use of the DISTINCT Keyword in group Functions
- How Nulls are handled in Group Functions
- The GROUP BY Clause
- Group Data by multiple columns
- Illegal Queries with Group Functions
- Restricting result with the HAVING Clause
- Using Subqueries
- Why use a subquery?
- Where to place a subquery in a statement
- Single Row and Multiple Row subqueries
- Using Subquery Operators
- Sub queries and Grouping Functions
- Restrictions on Subqueries
- Handling Null in Subqueries
- Set Operators
- The UNION operator
- The UNION ALL operator
- The INTERSECT operator
- The MINUS operator
- Guidelines when using SET operators
- Data Manipulation
- Inserting Data; the INSERT command
- Inserting Date and Time Values
- Inserting Null Values
- Inserting Data via a Select Statement
- Changing Data: the UPDATE command
- Updating Rows in a Table
- Updating Rows based on a Select Statement
- Updating a Single Column
- Updating Multiple Columns
- Deleting Rows from a Table
- The DELETE Statement
- Deleting Rows based on a Select Statement
- Deleting All Rows in a Table
- The TRUNCATE Command
- Transactions
- What is a Transaction?
- Controlling Transactions with Transaction Control Statements
- The COMMIT Command
- The ROLLBACK Command
- The SAVEPOINT Command
- Implicit Transaction Control Statements
- Viewing Committed and Uncommitted Data
- Advanced Subqueries
- Multi Column Sub queries
- Comparing Columns
- Comparison of Paired Data
- Scalar Expressions
- Correlated Subqueries
- The EXISTS Operator
- Correlated DML
- The WITH Clause
- Accessing Meta-Data via Data Dictionary Views
- Types of Dictionary Views Available
- How to Find the Relevant Dictionary Views
- List Commonly Used Dictionary Views
- Writing Queries to Gather Schema Information
- Adding Comments to Objects with the COMMENT command
