SQL Programming

Course Objective:

1. 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
2. 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
3. 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
4. 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
5. 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
6. Substitution Variables
• The && Substitution Variable
• The DEFINE Command
• The VERIFY Command
7. 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
8. 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
9. 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
10. Set Operators
• The UNION operator
• The UNION ALL operator
• The INTERSECT operator
• The MINUS operator
• Guidelines when using SET operators
11. 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
12. 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
13. Advanced Subqueries
• Multi Column Sub queries
• Comparing Columns
• Comparison of Paired Data
• Scalar Expressions
• Correlated Subqueries
• The EXISTS Operator
• Correlated DML
• The WITH Clause
14. 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