SQL Course Layout

What is RDBMS & Backend Testing?

• What is a database application?
• Why should we test databases?
• Back end vs. front end testing
• Testing application vs. testing RDBMS
• Writing and setting up database tests
• Using SQL* Plus
• Introduction to Oracle PL/SQL
• Introduction to MySQL
• How to use Toad in for backend testing
• Use of MS Excel for data validation

Database Data Access

• SELECT Statements
• Specific Data Retrieval using WHERE Clause
• Row Identifiers and Numbers
• Performing Arithmetic
• DISTINCT Values
• Value Comparison
• SQL Operators: LIKE, IN, BETWEEN
• ORDER BY Clause
• Join Conditions and Join Types

 

Database Functions

• Single Row Functions
• Aggregate Functions
• Grouping Rows using GROUP BY and HAVING Clause
• Storing and Processing DATE and TIME
• Using Timestamp and Time Intervals

Subqueries and Advanced Queries

• Single Row Subqueries
• Multiple Row Subqueries
• Correlated Subqueries
• Nested Subqueries
• Using SET Operators
• TRANSALTE() Function
• DECODE() Function
• CASE Expression
• Hierarchical Queries

Data Manipulation Language (DML)

• Adding Rows using INSERT Statement
• Modifying Rows using UPDATE Statement
• The RETURNING Clause
• Removing Rows using DELETE Statement
• Data Integrity – Primary & Foreign key
• Merging Rows using MERGE
• Database Transactions – COMMIT, ROLLBACK

Data Definition Language (DDL)

• Tables – Create, Alter, Rename, Truncate, Drop
• Sequences – Create, Retrieve, Use, Modify, Drop
• Index – Create, Retrieve, Modify, Drop
• Views – Create, Use, Modify, Drop

Users, Privileges, Roles

• Users – Create, Change Password, Delete
• System Privileges
• Object Privileges
• Roles – Create, Drop
• Data Control Language (DCL) – Grant, Revoke