Course Content - SQL Basic & Advance Training:

Oracle Database : SQL Basics

• Introduction - More
  • Overview of Oracle Database 11g and related products
  • Overview of relational database management concepts and terminologies
  • Introduction to SQL and its development environments
  • The HR schema and the tables used in this course
  • Oracle Database documentation and additional resources
• Retrieve Data using the SQL SELECT Statement - More
  • List the capabilities of SQL SELECT statements
  • Generate a report of data from the output of a basic SELECT statement
  • Use arithmetic expressions and NULL values in the SELECT statement
  • Invoke Column aliases
  • Concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
  • Display the table structure using the DESCRIBE command
• Restricted and Sorted Data - More
  • Write queries with a WHERE clause to limit the output retrieved
  • Describe the comparison operators and logical operators
  • Describe the rules of precedence for comparison and logical operators
  • Usage of character string literals in the WHERE clause
  • Write queries with an ORDER BY clause
  • Sort the output in descending and ascending order
  • Substitution Variables
• Usage of Single-Row Functions to Customize Output - More
  • List the differences between single row and multiple row functions
  • Manipulate strings using character functions
  • Manipulate numbers with the ROUND, TRUNC, and MOD functions
  • Perform arithmetic with date data
  • Manipulate dates with the DATE functions
• Conversion Functions and Conditional Expressions - More
  • Describe implicit and explicit data type conversion
  • Describe the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nesting multiple functions
  • Apply the NVL, NULLIF, and COALESCE functions to data
  • Usage of conditional IF THEN ELSE logic in a SELECT statement



Oracle Database : SQL Advance

• Aggregated Data Using the Group Functions - More
  • Usage of the aggregation functions in SELECT statements to produce meaningful reports
  • Describe the AVG, SUM, MIN, and MAX function
  • How to handle Null Values in a group function?
  • Divide the data in groups by using the GROUP BY clause
  • Exclude groups of date by using the HAVING clause
• Display Data from Multiple Tables - More
  • Write SELECT statements to access data from more than one table
  • Join Tables Using SQL:1999 Syntax
  • View data that does not meet a join condition by using outer joins
  • Join a table to itself by using a self join
  • Create Cross Joins
• Usage of Subqueries to Solve Queries - More
  • Use a Subquery to Solve a Problem
  • Single-Row Subqueries
  • Group Functions in a Subquery
  • Multiple-Row Subqueries
  • Use the ANY and ALL Operator in Multiple-Row Subqueries
  • Use the EXISTS Operator
• SET Operators - More
  • Describe the SET operators
  • Use a SET operator to combine multiple queries into a single query
  • Describe the UNION, UNION ALL, INTERSECT, and MINUS Operators
  • Use the ORDER BY Clause in Set Operations
• Data Manipulation - More
  • Add New Rows to a Table
  • Change the Data in a Table
  • Use the DELETE and TRUNCATE Statements
  • How to save and discard changes with the COMMIT and ROLLBACK statements
  • Implement Read Consistency
  • Describe the FOR UPDATE Clause
• DDL Statements to Create and Manage Tables - More
  • Categorize Database Objects
  • Create Tables
  • Describe the data types
  • Understand Constraints
  • Create a table using a subquery
  • How to alter a table?
  • How to drop a table?
• Other Schema Objects - More
  • Create, modify, and retrieve data from a view
  • Perform Data manipulation language (DML) operations on a view
  • How to drop a view?
  • Create, use, and modify a sequence
  • Create and drop indexes
  • Create and drop synonyms
• Few Important Actions - More
  • Identify features of an Entity Relationship (ER) model
  • Identify features of Relational Database Management System (RDBMS)
  • Identify features of single-row functions
  • Match the character functions with their uses
  • Match the number functions with their purpose
  • Identify the use of natural joins to retrieve data from multiple tables
  • Identify the use of an equi-join to retrieve data from multiple tables
  • Identify the use of self-join to retrieve data from a table
  • Identify the code for using the INSERT statement
  • Identify the code for using the UPDATE statement
  • Identify the code for using the DELETE statement
  • Identify tasks to be performed for granting system privileges
  • Identify tasks to be performed for granting object privileges
  • Identify syntax for performing various tasks for maintaining columns
  • Identify the code for retrieving data hierarchically from a table
  • Identify the code for formatting hierarchical reports