Information Technology

Oracle SQL and PL/SQL

7 Days 56 Hrs 7 Lessons

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

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

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

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

Interested in course?

Course Type: Instructor Led

course