Oracle Database PL SQL Fundamentals

Duration: 
2 days
Codes: 
SQL,PL

Overview

This Oracle Database: SQL and PL/SQL Fundamentals training delivers the fundamentals of SQL and PL/SQL along with the benefits of the programming languages using Oracle Database technology. You'll explore the concepts of relational databases.

Audience

Each participant will leave with a recognised certificate

Course Outline

  • Introduction
  • Overview of Oracle Database 12c 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
  • 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
  • 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
  • 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
  • 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
  • Aggregated Data Using the Group Functions
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • Introduction to PL/SQL
  • PL/SQL Overview
  • List the benefits of PL/SQL Subprograms
  • Overview of the Types of PL/SQL blocks
  • Create a Simple Anonymous Block
  • Generate the Output from a PL/SQL Block
  • PL/SQL Identifiers
  • List the different Types of Identifiers in a PL/SQL subprogram
  • Use of variables to store data
  • Scalar Data Types
  • %TYPE Attribute
  • Bind Variables
  • Sequences in PL/SQL Expressions
  • Write Executable Statements
  • Basic PL/SQL Block Syntax Guidelines
  • How to comment code?
  • SQL Functions in PL/SQL
  • Data Type Conversion
  • N

Related Courses

 

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

ITILv3, RESILIA, PRINCE2, PRINCE2 Agile, AgileSHIFT, MSP, M_o_R, P3M3, P3O, MoP, MoV courses on this page are offered by QA, ATO of AXELOS Limited. ITIL, RESILIA, PRINCE2, PRINCE2 Agile, AgileSHIFT, MSP, M_o_R, P3M3, P3O,MoP, MoV are registered trademarks of AXELOS Limited. All rights reserved.

Upcoming Dates

  • GREEN This class is Guaranteed To Run.
  • SPVC - Self-Paced Virtual Class.
  • Click a Date to Enroll.
Course Location Days Cost Date
Yorkshire Leeds5 1023 £1023 2019-09-23
Yorkshire Leeds5 1023 £1023 2019-09-23
Edinburgh Edinburgh5 1023 £1023 2019-09-30
London London5 1023 £1023 2019-09-30
Edinburgh Edinburgh5 1023 £1023 2019-09-30
London London5 1023 £1023 2019-09-30
Bristol Bristol2 1023 £1023 2019-10-03
Devon Exeter2 1023 £1023 2019-10-03
Midlands Birmingham2 1023 £1023 2019-10-03
Edinburgh Edinburgh2 1023 £1023 2019-10-03
Midlands Birmingham5 1023 £1023 2019-10-07
Manchester Manchester5 1023 £1023 2019-10-07
Manchester Manchester2 1023 £1023 2019-10-07
Manchester Manchester5 1023 £1023 2019-10-07
Cheshire Chester5 1023 £1023 2019-10-07
London London2 1023 £1023 2019-10-07
Bristol Bristol5 1023 £1023 2019-10-07
London London2 1023 £1023 2019-10-07
Midlands Birmingham5 1023 £1023 2019-10-14
Midlands Birmingham5 1023 £1023 2019-10-14
London London5 1023 £1023 2019-10-28
London London5 1023 £1023 2019-10-28
London London2 1023 £1023 2019-11-04
London London2 1023 £1023 2019-11-04
Bristol Bristol5 1023 £1023 2019-11-11
Bristol Bristol5 1023 £1023 2019-11-11
Yorkshire Leeds5 1023 £1023 2019-11-11
Yorkshire Leeds5 1023 £1023 2019-11-11
Manchester Manchester5 1023 £1023 2019-11-18
Online Online5 1023 £1023 2019-11-18
London London5 1023 £1023 2019-11-18
Cheshire Chester5 1023 £1023 2019-11-18
Manchester Manchester2 1023 £1023 2019-11-21
London London5 1023 £1023 2019-11-25
London London5 1023 £1023 2019-11-25
Edinburgh Edinburgh5 1023 £1023 2019-11-25
Edinburgh Edinburgh5 1023 £1023 2019-11-25
Cheshire Chester5 1023 £1023 2019-12-02
Bristol Bristol5 1023 £1023 2019-12-02
Manchester Manchester5 1023 £1023 2019-12-02
Midlands Birmingham5 1023 £1023 2019-12-02
Manchester Manchester5 1023 £1023 2019-12-02
Devon Exeter2 1023 £1023 2019-12-05
Midlands Birmingham2 1023 £1023 2019-12-05
Edinburgh Edinburgh2 1023 £1023 2019-12-05
Bristol Bristol2 1023 £1023 2019-12-05
Midlands Birmingham5 1023 £1023 2019-12-09
Midlands Birmingham5 1023 £1023 2019-12-09
Glasgow Glasgow2 1023 £1023 2019-12-09
London London5 1023 £1023 2020-01-06
London London5 1023 £1023 2020-01-06
Yorkshire Leeds5 1023 £1023 2020-01-13