Oracle SQL and PL/SQL

Instructor-Led 7 Agenda

No results found.

SQL Module 1 Lesson Id 1

SQL Module 1

SQL Module 2 Lesson Id 2

SQL Module 2

SQL Module 3 Lesson Id 3

SQL Module 3

SQL Module 4 Lesson Id 4

SQL Module 4

PL/SQL Module 5 Lesson Id 5

PL/SQL Module 5

PL/SQL Module 6 Lesson Id 6

PL/SQL Module 6

PL/SQL Module 7 Lesson Id 7

PL/SQL Module 7

Lesson Id	Title	Description
1	SQL Module 1	Database essentials: tables with their relationships, Primary Key, Foreign Key, normalisation<br />Selecting data using SELECT (DISTINCT) and operators (\|\|, +, -, *, /, %), from a table using FROM<br />Sorting data using ORDER BY<br />Filtering data using WHERE (AND, OR) and comparison operators (=, <, >, <=, >=, <>, IS, NOT, LIKE (%, _), BETWEEN, IN, NOT IN ... AND ... IS NOT NULL)<br />Aggregating and grouping data using aggregate functions (COUNT, SUM, AVG, MAX, MIN) and GROUP BY <br />Filtering aggregated data and groups using HAVING
2	SQL Module 2	Calculating data using functions for numbers, texts, dates, NULL and data type conversion<br />Joining tables using INNER JOINS, OUTER JOINS (LEFT, RIGHT, FULL), CROSS JOINS, self joins and the pre-ANSI 1992 Oracle way (+)<br />Further processing of query results using Subqueries and Correlated subqueries<br />Combining Query results using Set-operators: UNION (ALL), MINUS, INTERSECT
3	SQL Module 3	Performing advanced analytics using Analytical Functions:<br />Adding ranking to your query result using RANK, DENSE_RANK, ROW_NUMBER<br />Navigating through your query result using LAG, LEAD, FIRST_VALUE, LAST_VALUE <br />Calculating running totals and rolling averages using aggregate functions and the OVER clause <br />Pivoting result sets using PIVOT <br />Using variables in queries<br />Storing your queries in the database for later use: Views and Materialized Views
4	SQL Module 4	Creating, changing and deleting tables and other database objects using CREATE, ALTER, DROP<br />Copy a table using CREATE TABLE AS … query<br />Inserting, updating and deleting rows into/in/from database tables using INSERT, UPDATE, DELETE<br />Clearing a database table using TRUNCATE TABLE<br />Transaction Management
5	PL/SQL Module 5	PL/SQL introduction<br />Anonymous Blocks: DECLARE, BEGIN, EXCEPTION, END<br />Variables and Anchors<br />Program Structures: IF, CASE, LOOP <br />Cursors: implicit, explicit
6	PL/SQL Module 6	Stored Programs: Procedures, Functions<br />Packages: Package Specification, Package Body, Overloading, Describe, Scope<br />Exceptions<br />Nested Blocks<br />Triggers
7	PL/SQL Module 7	Records<br />Collections: Associative Arrays, Varrays, Nested Tables<br />Table Functions<br />Creating scripts based on table data