SQL - Essentials

( Duration: 4 Days )

SQL - Essentials Training Course is designed to give Participants practical experience in using ANSI standard SQL. Some of the differences between different vendors’ of SQL implementations will also be covered.

By attending SQL - Essentials workshop, Participants will learn:

  • Creating SQL statements to query a database table
  • Writing SQL statements to retrieve data from multiple tables
  • Using standard aggregate functions
  • Implementing Subqueries

There are no formal pre-requisites for Introduction to SQL course, although an understanding of databases and exposure to any programming in general would be useful.

Anyone who needs to understand and use SQL to manipulate a database.




  • Describing the Life Cycle Development Phases
  • Defining a Database
  • Discussing the Theoretical Conceptual and Physical Aspects of a Relational Database
  • Describing how a Relational Database Management System is used to manage a Relational Database
  • Describing the oracle implementation of both the RDBMS and the Object Relational Database Management System (ORDBMS)
  • Describing how SQL is used in the Oracle product set

Writing a basic SQL statement

  • Describing the SQL select capabilities
  • Executing a basic Select statement with the mandatory clauses
  • Differentiating between SQL and iSQL*Plus commands

Restricting and Sorting data

  • Limiting the rows retrieved by a Query
  • Sorting the rows retrieved by a query

Single Row functions

  • Describing various types of functions available in SQL
  • Using a variety of character, number, and date functions in SELECT statements
  • Explaining what the conversion functions are and how they are used
  • Using control statements

Displaying data from multiple tables

  • Writing SELECT statements to access data from than one table
  • Describing the Cartesian product
  • Describing and using the different types of joins
  • Writing joins using the tips provided
  • Aggregating data by using group functions
  • Identifying the different group functions available
  • Explaining the use of group functions
  • Grouping data by using the GROUP BY clause
  • Filtering the groups using the HAVING clause

Writing Subqueries

  • Describing the types of problems that subqueries can solve
  • Describing subqueries
  • Listing the types of subqueries
  • Writing single-row and multiple-row
  • Describing and explaining the behavior of subqueries when NULL values are retrieved

Producing the readable output with iSQL*Plus

  • Producing queries that require an input variable
  • Customizing the iSQL*Plus environment
  • Producing more readable output
  • Creating and executing the script files

Manipulating the data

  • Describing each Data Manipulation Language (DML) command
  • Inserting rows into a table
  • Updating rows in a table
  • Deleting rows from a table
  • Merging rows from two or more tables
  • Controlling transactions
  • Describing the transaction processing
  • Describing the read consistency, implicit and explicit locking

Creating and managing tables

  • Describing the main database objects
  • Creating tables
  • Describing the Oracle data types
  • Altering table definitions
  • Dropping, Renaming, and Truncating tables

Including Constraints

  • Describing Constraints
  • Creating and Maintaining Constraints

Creating Views

  • Describing views and their uses
  • Creating a view
  • Retrieving data by means of a view
  • Inserting, Updating, and Deleting data through views
  • Dropping views
  • Altering the definition of a view
  • Inline views
  • Top ‘N’ Analysis

Other Database Objects

  • Creating, maintaining, and using Sequences
  • Creating and maintaining Indexes
  • Creating Private and Public Synonyms
  • Controlling user access
  • Understanding the concepts of Users, Roles and Privileges
  • Granting and revoking object privileges
  • Creating roles and granting privileges to Roles
  • Creating Synonyms for ease of table access

Using Set Operators

  • Describing the Set operators
  • Obeying the Set operators Rules and Guidelines
  • Using a Set operator to combine multiple queries into a single subquery
  • Controlling the order of rows returned

Oracle Single Row functions

  • Using DATETIME functions
  • Using the NVL2 function to handle NULL values

Enhancements to the GROUP BY clause

  • Using the ROLLUP as an extension to the GROUP BY clause to produce subtotal values
  • Using the CUBE as an extension to the GROUP BY clause to cross - product tabulation values
  • Using the GROUPING Function to Identify the Row Values Created by ROLLUP or CUBE Operators
  • Using GROUPING SETS to Produce a Single Result Set
  • That Is Equivalent to a UNION ALL Approach
  • Using the WITH Clause

Advanced Subqueries

  • Updating and deleting rows by using Correlated Subqueries
  • Writing a multicolumn subquery
  • Describing and explaining the behavior of Subqueries when NULL values are retrieved
  • Writing a subquery in a FROM clause
  • Describing the types of problems that can be solved with a correlated subquery
  • Describing a correlated subquery
  • Writing correlated Subqueries
  • Using the EXISTS and NOT EXISTS operators

Hierarchical Retrieval

  • Discussing the benefits of the Hierarchical Query
  • Ordering the rows retrieved by a query in hierarchical manner
  • Formatting hierarchical data so that it is easy to read
  • Excluding branches from the tree structure

Extensions to DML and DDL statements

  • Discussing multi-table inserts
  • Creating and using external tables
  • Naming the index and using the CREATE INDEX command at the time of creating Primary Key constraint

Writing scripts to generate scripts

  • Describing the types of problems that are solved by writing SQL scripts that generate other SQL scripts
  • Writing and executing scripts that generate scripts with commands to create and drop tables
  • Writing and executing a script that generates a script of INSERT INTO commands

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.