SQL / PLSQL

  • PL/SQL is a block structured language that enables developers to combine the power of SQL with procedural statements.
  • All the statements of a block are passed to oracle engine all at once which increases processing speed and decreases the traffic.

Syllabus

SQL:

Retrieving Data Using the SQL SELECT Statement

  • List the capabilities of SQL SELECT statements   
  • Execute a basic SELECT statement  
  • Describe how schema objects work

Restricting and Sorting Data  

  • Limit the rows that are retrieved by a query   
  • Sort the rows that are retrieved by a query

Using Single-Row Functions to Customize Output  

  • Describe various types of functions that are available in SQL   
  • Use character, number, and date functions in SELECT statements  
  • Describe the use of conversion functions

Reporting Aggregated Data Using the Group Functions  

  • Identify the available group functions
  • Describe the use of group functions  
  • Group data by using the GROUP BY clause  
  • Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables

  • Write SELECT statements to access data from more than one table using equijoins and nonequijoins  
  • Join a table to itself by using a self-join  
  • View data that generally does not meet a join condition by using outer joins  
  • Generate a Cartesian product of all rows from two or more tables

Using Subqueries to Solve Queries  

  • Define subqueries   
  • Describe the types of problems that subqueries can solve  
  • List the types of subqueries  

Write single-row and multiple-row subqueries

  • Using the Set Operators  
  • Describe set operators  
  • Use a set operator to combine multiple a single query  
  • Control the order of rows returned

Manipulating Data  

  • Describe each data manipulation language (DML) statement  
  • Insert rows into a table  
  • Update rows in a table  
  • Delete rows from a table  
  • Control transactions

Using DDL Statements to Create and Manage Tables  

  • Categorize the main database objects   
  • Review the table structure  
  • List the data types that are available for columns  
  • Create a simple table  
  • Explain how constraints are created at the time of table creation
  • Creating Other Schema Objects  
    • Create simple and complex views   
    • Retrieve data from views  
    • Create, maintain, and use sequences  
    • Create and maintain indexes  
    • Create private and public synonyms

 

Managing Objects with Data Dictionary Views

  • Use the data dictionary views to research data on your objects   
  • Query various data dictionary views
  • Controlling User Access  
  • Differentiate system privileges from object privileges   
  • Grant privileges on tables   
  • View privileges in the data dictionary   
  • Grant roles   
  • Distinguish between privileges and roles

Managing Schema Objects  

  • Add constraints   
  • Create indexes   
  • Create indexes using the CREATE TABLE statement   
  • Creating function-based indexes   
  • Drop columns and set column UNUSED  
  • Perform FLASHBACK operations  
  • Create and use external tables

 

Manipulating Large Data Sets  

  • Manipulate data using subqueries   
  • Describe the features of multitable INSERTs  
  • Use the following types of multitable INSERTs (Unconditional, Conditional and Pivot)  
  • Merge rows in a table  
  • Track the changes to data over a period of time

Generating Reports by Grouping Related Data  

  • Use the ROLLUP operation to produce subtotal values   
  • Use the CUBE operation to produce cross tabulation values   
  • Use the GROUPING function to identify the row values created by ROLLUP or CUBE   
  • Use GROUPING SETS to produce a single result set

Managing Data in Different Time Zones  

  • Use Various datetime functions

Retrieving Data Using Subqueries  

  • Write a multiple-column subquery   
  • Use scalar subqueries in SQL   
  • Solve problems with correlated subqueries   
  • Update and delete rows using correlated subqueries  
  • Use the EXISTS and NOT EXISTS operators  
  • Use the WITH clause

Hierarchical Retrieval  

  • Interpret the concept of a hierarchical query   
  • Create a tree-structured report   
  • Format hierarchical data   
  • Exclude branches from the tree structure

Regular Expression Support  

  • Using Meta Characters   
  • Regular Expression Functions  
  • Replacing Patterns  
  • Regular Expressions and Check Constraints

 

PLSQL

  • PL/SQL - Overview
  • PL/SQL - Environment
  • PL/SQL - Basic Syntax
  • PL/SQL - Data Types
  • PL/SQL - Variables
  • PL/SQL - Constants
  • PL/SQL - Operators
  • PL/SQL - Conditions
  • PL/SQL - Loops
  • PL/SQL - Strings
  • PL/SQL - Arrays
  • PL/SQL - Procedures
  • PL/SQL - Functions
  • PL/SQL - Cursors
  • PL/SQL - Records
  • PL/SQL - Exceptions
  • PL/SQL - Triggers
  • PL/SQL - Packages
  • PL/SQL - Collections
  • PL/SQL - Transactions
  • PL/SQL - Date & Time
  • PL/SQL - DBMS Output
  • PL/SQL - Object Oriented

 

 

For More Information

Feel Free to contact us and ask your queries.