+ Basic to intermediate level
+ Basic to intermediate level
This course will give those with basic Oracle SQL skills the advanced SQL skills necessary to design and code complex queries against Oracle databases to generate the REPORTS that management really wants. You will learn to use many advanced SQL coding techniques such as coding analytic functions for business intelligence reporting and decision support queries, using partition outer join to "densify" data, the Oracle12 MODEL clause ("spreadsheet-like capability directly from the database"), Oracle12 regular expressions for pattern matching, using the extended aggregate functions CUBE and ROLLUP, coding SET operations such as INTERSECT, using subqueries to increase SQL performance and more
This course will benefit participants in the following areas:
• Improve understanding of data concepts
• Understand database management systems
• Understanding database normalization
• Understanding data structures and Data dictionary
• Usage of database queries and functions.
• Building and understanding database logic.
• Developing problem solving mentality.
This course will benefit participants in the following areas:
• Improve understanding of data concepts
• Understand database management systems
• Understanding database normalization
• Understanding data structures and Data dictionary
• Usage of database queries and functions.
• Building and understanding database logic.
• Developing problem solving mentality.
PART 1 – DATA QUERY LANGUAGE
SECTION 1 SIMPLE SELECT STATEMENTS ( REVIEW)
Retrieving Data Using the SQL SELECT Statement
List the capabilities of SQL SELECT statements
Execute a basic SELECT statement
Restricting and Sorting Data
Limit the rows that are retrieved by a query
Sort the rows that are retrieved by a query
Use character, number, and date functions in SELECT statements
Describe the use of conversion functions
Reporting Aggregated Data Using the 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
SECTION 2 ADVANCED SELECT STATEMENTS
SECTION 2.1 ADVANCED GROUP CLAUSES
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
EXAMPLES AND ASSIGNMENT
• SECTION 2.2 ADVANCED JOINS
Write SELECT statements to access data from more than one table using nonequijoins
Write SELECT statements to access data from 3 or more tables.
Join a table to itself by using a self-join
View data that generally does not meet a join condition by using outer joins
EXAMPLES AND ASSIGNMENT
• SECTION 3 SUBQUERIES
Using Subqueries to Solve Queries
Define subqueries
Describe the types of problems that subqueries can solve
Write single-row and multiple-row subqueries
Retrieving Data Using Subqueries
Write a multiple-column subquery
Use scalar subqueries in SQL
Solve problems with correlated subqueries
Use the EXISTS and NOT EXISTS operators
Use the WITH clause
EXAMPLES AND ASSIGNMENT
• SECTION 4 ADVANCED FUNCTIONS
Using the DECODE and CASE functions
Using Analytical functions
Using Regular Expression Functions
EXAMPLES AND ASSIGNMENT
• SECTION 5 SET OPERATORS
Describe set operators
Use a set operator to combine multiple queries into single query
Control the order of rows returned
EXAMPLES AND ASSIGNMENT
• PART 2 DATA MANIPULATION LANGUAGE
SECTION 1 - Manipulating Data (REVIEW )
Describe each data manipulation language (DML) statement
Insert rows into a table
Update rows in a table
Delete rows from a table
• SECTION 2 - TRANSACTIONS
Control transactions
• SECTION 3 - ADVANCED DML
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
• PART 3 DATA DEFINITION LANGUAGE
SECTION 1 REVIEW
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
• SECTION 2 CREATING OTHER SCHEMA OBJECTS
Create simple and complex views
Retrieve data from views
Create, maintain, and use sequences
• SECTION 3 MANAGING OBJECTS WITH DATA DICTIONARY VIEWS
Use the data dictionary views to research data on your objects
Query various data dictionary views
PART 1 – DATA QUERY LANGUAGE
SECTION 1 SIMPLE SELECT STATEMENTS ( REVIEW)
Retrieving Data Using the SQL SELECT Statement
List the capabilities of SQL SELECT statements
Execute a basic SELECT statement
Restricting and Sorting Data
Limit the rows that are retrieved by a query
Sort the rows that are retrieved by a query
Use character, number, and date functions in SELECT statements
Describe the use of conversion functions
Reporting Aggregated Data Using the 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
SECTION 2 ADVANCED SELECT STATEMENTS
SECTION 2.1 ADVANCED GROUP CLAUSES
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
EXAMPLES AND ASSIGNMENT
• SECTION 2.2 ADVANCED JOINS
Write SELECT statements to access data from more than one table using nonequijoins
Write SELECT statements to access data from 3 or more tables.
Join a table to itself by using a self-join
View data that generally does not meet a join condition by using outer joins
EXAMPLES AND ASSIGNMENT
• SECTION 3 SUBQUERIES
Using Subqueries to Solve Queries
Define subqueries
Describe the types of problems that subqueries can solve
Write single-row and multiple-row subqueries
Retrieving Data Using Subqueries
Write a multiple-column subquery
Use scalar subqueries in SQL
Solve problems with correlated subqueries
Use the EXISTS and NOT EXISTS operators
Use the WITH clause
EXAMPLES AND ASSIGNMENT
• SECTION 4 ADVANCED FUNCTIONS
Using the DECODE and CASE functions
Using Analytical functions
Using Regular Expression Functions
EXAMPLES AND ASSIGNMENT
• SECTION 5 SET OPERATORS
Describe set operators
Use a set operator to combine multiple queries into single query
Control the order of rows returned
EXAMPLES AND ASSIGNMENT
• PART 2 DATA MANIPULATION LANGUAGE
SECTION 1 - Manipulating Data (REVIEW )
Describe each data manipulation language (DML) statement
Insert rows into a table
Update rows in a table
Delete rows from a table
• SECTION 2 - TRANSACTIONS
Control transactions
• SECTION 3 - ADVANCED DML
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
• PART 3 DATA DEFINITION LANGUAGE
SECTION 1 REVIEW
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
• SECTION 2 CREATING OTHER SCHEMA OBJECTS
Create simple and complex views
Retrieve data from views
Create, maintain, and use sequences
• SECTION 3 MANAGING OBJECTS WITH DATA DICTIONARY VIEWS
Use the data dictionary views to research data on your objects
Query various data dictionary views
info@rhythexconsultingghana.com
No. 9 Samora Machel Road Asylum
Down, Accra – Ghana
Tel: +23332231305, +233243301313
info@rhythexconsulting.com
39A Sura Mogaji Street, Off Coker Road, Ilupeju, Lagos, Nigeria
Tel: +234 (0) 816 436 2696
info@rhythexconsulting.com
Plot 624, James Agwu Onoja Street,
Block 1, Flat 2, Wuye District, Abuja.
Tel: 08179444443, 08038553466