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