Oracle SQL For Data Analysis : Truly From Basics to Advanced

Lessons with examples about how to combine multiple conditions using AND operator in the SQL predicate clause

Lessons with examples about how to combine multiple conditions using OR operator in the SQL predicate clause

Combination of AND and OR operators is required to be handled according to the rule of precedence, which is the subject of this lesson.

Study of IN operator to list the rows when a common expression is compared with various different values with OR, a shorthand for multiple OR for common expression.

Listing rows based on a range of values criteria. Important are the fine points of BETWEEN operator explained with examples.

Use and properties of the DISTINCT clause used in SQL SELECT statement. Suppression of duplication of values in listing.

Use and properties of ORDER BY clause in SQL SELECT statement. To display the rows in ASCENDING and DESCENDING sorted order based on a column or combination of columns.

Concept of SQL SET Operators - UNION - MINUS - INTERSECTION - Explanation and use with examples - UNION & UNION ALL - UNION compatibility rules

Listing the rows based on NULL values in the rows by using IS NULL clause or IS NOT NULL clauses

SQL function NVL, its use, properties and case studies

SQL function NVL2, its use, properties and case studies

SQL function COALESCE, its use, properties and case studies

Understand the basic use of Sub-query and its need. Know basic syntax of Sub-query.

Understand about how to handle the situation when the Sub-query is supposed to return multiple values, with examples

Know about how to handle the situations when the sub-query has the possibility to return NULL values, with examples of workaround

Sub-queries returning sets of values and comparison of them as a set in the predicates of SQL statements, case study.

A very special type and complex form of Sub-Query. Use and properties of Correlated Subquery with examples

Use of EXISTS Boolean operator for Correlated Subqueries

Understand use & properties of ANY and ALL operators typically with Subqueries

Understand the basics of JOIN operation - EquiJoin - Joining the two relations based on equality of values in common columns.

Understand the use of Non-Equi type of join operation based on operators like between. Properties of Non-Equi joins

Understand the technique of table join with itself

Understand about why require OUTER JOIN operation. Use of special OUTER JOIN operator provided by Oracle. Properties of OUTER JOIN operator

Understand use of ANSI provided standardized OUTER JOIN clause with its various options. Advantages of using the OUTER JOIN clause as replacement of OUTER JOIN operator.

Understand the concept of Group Functions, different Group Functions, purpose of GROUP BY clause and HAVING clause

Actual use of Group Functions and GROUP BY clause and HAVING clause with query examples, syntax.

Certain SQL Group Function based tricky queries being frequently asked in interviews, with explanation, demonstration and case studies

Understand the use of two analytical extensions of GROUP BY clause to solve certain complex reporting problems of SubTotals and GranTotals in financial and other statements

Understand the use of some conversion functions or type casting functions TO_CHAR, TO_NUMBER

Continued study of conversion functions and some numeric built in functions with examples and problem solutions

Continued study of numeric functions and some important string functions, their way of  use and properties

Extension of study of string functions and some date functions with solutions to frequently asked tricky queries / problems in the interview

Extension of study of date functions with solutions to frequently asked tricky queries / problems in the interview

Conceptual explanation of the rules of database transactions, ACID Properties of transaction, Nature of database transaction

INSERT, UPDATE, DELETE statments, Syntax and Statement Form, Examples.

Isolation of transaction through locking, Optimistic and Pessimistic locking, How to request for the locks

Understand the concept of data consistent transaction, concept of READ ONLY transaction

Transaction Control Language statements consists of COMMIT, ROLLBACK and SAVEPOINT. Let us understand the proper use of these statements with examples

Similarities and Differences between DELETE, TRUNCATE and DROP TABLE commands. Answer to frequently asked question in the interview.