Sql Data Analysis:( Oracle, Sql, Pl/Sql, Toad, Sqlplus)

Mastering SQL Data Analysis: From Oracle Foundations to Advanced PL/SQL Techniques

What you'll learn

Install Oracle Database and configure the environment for optimal use.

Execute basic to advanced SQL queries to retrieve data from the Oracle Database.

Use the SELECT, FROM, WHERE, ORDER BY, and DISTINCT clauses effectively in queries.

Understand and apply various SQL functions, including aggregate functions for data analysis.

Employ JOIN operations to extract and combine data from multiple tables.

Utilize Oracle’s SET operators for advanced data comparison and analysis.

Filter and sort data effectively using a variety of SQL clauses and operators.

Learn to write and execute PL/SQL blocks for procedural data manipulation.

Develop PL/SQL stored procedures and functions for reusable code and efficient database operations.

Requirements

Basic Computer Literacy: Familiarity with operating a computer, managing files and folders, and a general understanding of operating systems (Windows, Linux, or macOS).

Fundamental Understanding of Databases: A basic grasp of what databases are and how they are used. Knowledge of what tables, rows, and columns represent in a database context.

Introductory SQL Knowledge: While not mandatory, having an introductory level of SQL knowledge, such as understanding what a SELECT statement does, can be beneficial.

Oracle Database Software: Access to Oracle Database software for practice. This could be through Oracle's free editions or through a provided environment if part of an institutional learning package.

Software Installation: Ability to install software on your computer, as the course will require installing Oracle Database, SQL Developer, TOAD, and potentially other tools.

Eagerness to Learn: A strong willingness to learn and experiment with SQL and PL/SQL coding, database management, and data analysis techniques.

Analytical Thinking Skills: Ability to think logically and analytically, as solving database problems and writing queries require a structured approach.

Description

Dive into the world of Oracle SQL and PL/SQL with our comprehensive course, designed to take you from beginner to expert. This course is meticulously structured over eleven sections, starting with the essentials of Oracle Database setup, configuration with TOAD and SQL Developer, and advancing through to complex data analysis using PL/SQL. Whether you’re looking to enhance your data management skills, leverage analytical functions for in-depth insights, or understand the intricacies of SQL data manipulation, this course offers a deep dive into the powerful capabilities of Oracle databases.From installing Oracle and creating your first database user to mastering advanced analytic functions and PL/SQL programming, you'll gain hands-on experience with real-world applications. Learn to navigate Oracle's rich set of features, including querying data with SQL, extracting data from multiple tables, aggregating data using aggregate functions, and performing sophisticated data analysis with set operators. Our course also covers essential topics like filtering data with SQL, analyzing data with single row functions, and leveraging the full power of PL/SQL for database management and automation.Each section is packed with detailed lectures, from understanding the basics of what Oracle is to advanced PL/SQL stored procedures creation and execution. You’ll unlock sample HR schemas, connect to Oracle with TNSNames, and explore a variety of SQL clauses and functions to filter, sort, and analyze data. With practical examples and clear explanations, you'll learn how to write queries for joins, understand aggregate functions, and utilize analytic and single row functions to draw insights from data.Our course is enriched with practical examples using the Oracle HR Schema, providing a hands-on approach to learning. By the end of this journey, you will have acquired the skills to effectively analyze data, manage databases, and develop robust SQL queries and PL/SQL programs for real-world applications.

Overview

Section 1: Oracle Database Setup

Lecture 1 Introduction

Lecture 2 What is Oracle

Lecture 3 Oracle installation requirements

Lecture 4 Download Oracle

Lecture 5 Install Oracle

Lecture 6 Connect to Oracle with SQLPlus

Lecture 7 Create a new database user

Lecture 8 Create a table

Lecture 9 How to stop and start Oracle database server

Section 2: Oracle Configuration with TOAD and SQL Developer

Lecture 10 What is SQL Developer

Lecture 11 Download SQL Developer

Lecture 12 Connect SQL Developer to Oracle

Lecture 13 Download sample schemas

Lecture 14 Unlock sample HR Schema account

Lecture 15 Connect sample schema account to Oracle

Lecture 16 Unlock sample schema tables

Lecture 17 What is TNS Names

Lecture 18 Creating TNSNames

Lecture 19 Connect to oracle with TNSNames

Lecture 20 What is TOAD

Lecture 21 Install TOAD

Lecture 22 Connect TOAD to Oracle

Section 3: Query data with SQL

Lecture 23 Using FROM clause

Lecture 24 Using WHERE Clause for filtering data

Lecture 25 Using ORDER BY Clause

Lecture 26 Using DISTINCT

Lecture 27 Using aliases

Lecture 28 Using Sub-Query

Lecture 29 Restricting and Sorting Data

Lecture 30 Using GROUP BY and HAVING Clause

Lecture 31 Overview of SQL

Section 4: Extracting data from multiple tables

Lecture 32 Introduction to Joins

Lecture 33 Writing query for a SELF Join

Lecture 34 Writing query for an OUTER Join

Lecture 35 Writing query for a CARTESIAN Join

Lecture 36 Writing query for a NON EQUI Join

Lecture 37 Writing query for an EQUI Join

Lecture 38 Overview of Oracle SQL Joins

Section 5: Aggregating Data using Aggregate Functions

Lecture 39 What are aggregate functions

Lecture 40 Writing query for AVG aggregate

Lecture 41 Writing query for COUNT aggregate

Lecture 42 Writing query for MIN aggregate

Lecture 43 Writing query for MAX aggregate

Lecture 44 Overview of Aggregate Functions

Section 6: Data Analysis using SET Operators

Lecture 45 What are SET Operators

Lecture 46 Union SET Operator

Lecture 47 Minus SET Operator

Lecture 48 Intersect SET Operator

Lecture 49 Overview of SET Operators

Section 7: Filtering data in SQL

Lecture 50 Filtering data with OR Operator

Lecture 51 Filtering data with AND Operator

Lecture 52 Filtering data with NOT Operator

Lecture 53 Filtering data with ANY Operator

Lecture 54 Filtering data with IN Operator

Lecture 55 Filtering data with ANY Operator

Lecture 56 Filtering data with LIKE Operator

Lecture 57 Filtering data with NULL Values

Lecture 58 Filtering data with ISNULL

Lecture 59 Overview of Filtering data with SQL

Section 8: Data Analysis using Analytic Functions

Lecture 60 Introduction To Analytic Functions

Lecture 61 How To Write Analytic Functions

Lecture 62 Partition By Clause

Lecture 63 Order By Clause

Lecture 64 Row number Functions

Lecture 65 Rank Function

Lecture 66 Dense Rank Function

Lecture 67 Lead Function

Lecture 68 Lag Function

Lecture 69 First and Last Functions

Lecture 70 ListAgg Function

Lecture 71 Overview of Analytic functions

Section 9: Analysing data with Single Row Functions

Lecture 72 single row

Lecture 73 Character Functions

Lecture 74 Conversion Functions

Lecture 75 Date Functions

Lecture 76 Numeric Functions

Lecture 77 Overview of single functions

Section 10: PL/SQL Essentials

Lecture 78 What is PL/SQL

Lecture 79 PL/SQL Block Structure

Lecture 80 Execute a PL/SQL Anonymous Block using SQLPlus

Lecture 81 Execute a PL/SQL Anonymous Block using Oracle SQL Developer

Lecture 82 Execute a PL/SQL Anonymous Block using TOAD

Lecture 83 PL/SQL Variables

Lecture 84 PL/SQL Variable Declaration

Lecture 85 PL/SQL Variable Assignment

Lecture 86 PL/SQL Variable Anchors

Lecture 87 Executing PL/SQL Variable Assignment using SQLPlus

Lecture 88 Executing PL/SQL Variable Assignment using Oracle SQL Developer

Lecture 89 Executing PL/SQL Variable Assignment using TOAD

Lecture 90 Initializing Variables in PL/SQL using SQLPlus

Lecture 91 Initializing Variables in PL/SQL using SQL Developer

Lecture 92 Initializing Variables in PL/SQL using TOAD

Lecture 93 PL/SQL Literals

Lecture 94 Variable Scopes in PL/SQL using SQLPlus

Lecture 95 Variable Scopes in PL/SQL using SQL Developer

Lecture 96 Variable Scopes in PL/SQL using TOAD

Lecture 97 Introduction to PL/SQL Functions

Lecture 98 Creating Functions

Lecture 99 Creating PL/SQL Functions using SQLPlus

Lecture 100 Creating PL/SQL Functions using SQL Developer

Lecture 101 Creating PL/SQL Functions using TOAD

Lecture 102 What are PL/SQL Stored Procedures

Lecture 103 How to create stored procedure using SQLPlus

Lecture 104 How to create stored procedure using SQL Developer

Lecture 105 How to create stored procedure using TOAD

Lecture 106 How to execute or call a stored procedure using SQLPlus

Lecture 107 How to execute or call a stored procedure using SQL Developer

Lecture 108 How to execute or call a stored procedure using TOAD

Lecture 109 PL/SQL Data Types

Lecture 110 PL/SQL IF Statements

Lecture 111 PL/SQL IF Statements example using SQLPlus

Lecture 112 PL/SQL IF Statements example using SQL Developer

Lecture 113 PL/SQL IF Statements example using TOAD

Lecture 114 Overview of PL/SQL

Section 11: More Data Analysis with Oracle SQL

Lecture 115 Analyze employee count total

Lecture 116 Generate hire date report

Lecture 117 Analyze unique salaries

Students and Recent Graduates,IT Professionals,Career Changers,Database Enthusiasts and Hobbyists,Beginners to Oracle SQL