Oracle Sql From Scratch

Introduction Preview 02:42

Watch this lecture to understand what is covered in this section and get any pertinent recommendations.

How to take the course without installing any program Preview 05:34

You must watch it if you plan to take the course from your internet browser, without installing a local database.

Setting Up and using Apex to take the course on the browser Preview 04:40

Using Live SQL to take the course on the browser Preview 07:27

Downloading and installing Oracle Database Express Edition 18c Preview 12:15

Watch this lecture for instructions on how to download and install the Oracle Database software, which you will use to start learning SQL.

Taking the Course on a Mac or Windows "Home" Computer Preview 09:40

This lesson explains the options you have to take the course using a Mac or a Windows "Home" edition computer. It was added because the Oracle database cannot be installed directly on a computer running Mac OS.X (now macOS) or Windows Home..

Downloading and installing Oracle Sql Developer Preview 04:34

Watch this lecture for instructions on how to download and install Oracle SQL Developer

Oracle Sql Developer Basics Preview 07:47

Watch this lecture for the very basics on how to use Oracle SQL Developer.  You can safely skip this lesson if you already took my "Oracle SQL Developer : Tips and Tricks" course.

Introduction Preview 00:44

Watch this lecture to understand what is covered in this section and get any pertinent recommendations.  This section covers the basic concepts about databases, which are really vital to learn SQL correctly.

Basic Database Concepts Preview 07:29

This lecture covers the most basic database concepts, which you really need to know before starting to learn Oracle SQL

What is SQL? Preview 04:01

This is a short lecture that introduces you to the SQL language

Basic Oracle Database Concepts and Architecture Preview 05:19

This lesson contains a high level overview of the Oracle Database architecture and some other basic Oracle concepts.

The Multitenant Architecture Preview 05:10

In this lesson you will learn about the "multitenant" architechture, which was introduced in Oracle 12c, and is now included in the Express Edition 18c.

Basic Oracle Database Peculiarities Preview 04:07

This lecture explains the most basic and important peculiarities you need to know about Oracle databases.

Section Recap (BC) Preview 02:34

Introduction to retrieving information from an Oracle database. Preview 01:03

Watch this lecture to understand what is covered in this section and get any pertinent recommendations.

Creating the first Test Tables Preview 07:15

This lecture guides you through the process of creating the tables you will use to practice the contents of the first practical lectures.

Basic Oracle Datatypes Preview 05:54

In this lecture you will learn about the most commonly used data types in Oracle databases.

The SELECT Statement in its Simplest Form Preview 07:49

In this lecture you will be introduced to the SELECT statement.

Practice Challenge: Basic SELECT Statement Preview 00:18

Column and Table Aliases in Oracle Preview 04:02

In this lecture you will learn why aliases are important and how to use them.

Section Quiz (RIFD)

Congratulations on finishing the section!

Now, take this quiz before moving to the next lesson ;)

Solution to the practice challenges in this section (RIFD) Preview 01:01

Section Recap (RIFD) Preview 02:11

Introduction to filtering and sorting results from Oracle SQL queries. Preview 00:47

Watch this lecture to understand what is covered in this section and get any pertinent recommendations.

Filtering Results (Introducing the WHERE Clause) Preview 05:18

Here you will start learning how to filter the results of you queries by using the WHERE clause

Practice Challenge: Filtering Results Preview 00:11

More complex WHERE Conditions Preview 06:26

Here you will learn how to use more complex conditions to filter the results of your Oracle SQL queries.

Practice Challenge: More complex WHERE Conditions Preview 00:22

Solution to the More Complex Conditions Challenge Preview 04:36

Watch this video to see how I solved the practice challenge from the "More Complex WHERE Conditions" lesson.

Filtering Duplicate Rows (DISTINCT Clause) Preview 05:22

In this lecture you will learn how and when to use the DISTINCT clause

Ordering the Results (ORDER BY Clause) Part 1 Preview 06:12

This lecture teaches you how to order the results of your queries by using the ORDER BY clause in Oracle.

Ordering the Results (ORDER BY Clause) Part 2 Preview 05:26

Understanding and Handling NULLs Preview 06:02

A very important lesson that covers the concept of NULL and how to handle it in Oracle.

Practice Challenge: Understanding and Handling NULLs Preview 00:24

Understanding and Handling NULLs Part 2 Preview 04:18

NULL-handling Functions in Oracle SQL Preview 09:10

Practice Challenge: NULL-handling Functions in Oracle SQL Preview 00:26

NULL-handling Functions in Oracle SQL Part 2 Preview 06:46

Practice Challenge: NULL-handling Functions in Oracle SQL Part 2 Preview 01:01

Section Quiz (FSR)

Congratulations!  You know everything you need to know about filtering and sorting results!

To confirm that the previous statement is true, go ahead and take this quiz.

Solution to the practice challenges in this section (FSR) Preview 05:02

Section Recap (FSR) Preview 04:08

Introduction to the use of Operators in Oracle SQL. Preview 00:42

Watch this lecture to understand what is covered in this section and get any pertinent recommendations. In this section you will learn everything about conditions and operators.

Comparison Operators (=,<,>,ANY,ALL) Preview 04:00

Here you will learn how the comparison operators work in Oracle SQL

SQL Operators (LIKE, IN, BETWEEN, IS NULL) Preview 09:34

Here you will learn how the SQL operators work in Oracle SQL

Practice Challenge: SQL Operators Preview 00:38

Logical Operators (AND, OR, NOT) Preview 08:30

Here you will learn how the logical operators work in Oracle SQL

Practice Challenge: Logical Operators Preview 00:17

Substitution Variables Preview 06:10

In this lecture you will learn how to use substitution variables in tools like SQL Developer or SQL*Plus to create generic or parameterized SQL statements.

Practice Challenge: Substitution Variables Preview 00:57

Section Quiz (O)

Congratulations on your progress!

Take this quiz before moving to the Group Operations section.

Solution to the practice challenges in this section (O) Preview 05:51

Section Recap (O) Preview 02:34

Introduction to Group Operations in Oracle SQL. Preview 00:47

Watch this lecture to understand what is covered in this section and get any pertinent recommendations. This section covers the very important topic of "Group Operations".

Aggregate Functions (MIN, MAX, SUM, COUNT, AVG) Preview 07:38

In this lecture you will be introduced to group operations and will learn how to use aggregate functions in Oracle SQL.

Practice Challenge: Aggregate Functions Preview 00:16

Grouping Rows (The GROUP BY Clause) Preview 08:23

Here you will learn how to group rows by using the GROUP BY clause.  A very important topic.

Practice Challenge: Grouping Rows Preview 00:10

Filtering Group Results (HAVING Clause) Preview 05:43

Here you will learn how to filter the results of grouped queries by using the HAVING clause

Practice Challenge: Filtering Group Results Preview 00:17

Section Quiz (GO)

Great to see you continue making progress!

Here is the quiz for the Group Operations section.  Enjoy!

Solution to the practice challenges in this section (GO) Preview 02:03

Section Recap (GO) Preview 01:53

Introduction to Subqueries in Oracle SQL. Preview 00:57

Watch this lecture to understand what is covered in this section and get any pertinent recommendations. This section covers the very interesting topic of "Subqueries".

Understanding Subqueries Preview 11:20

In this lecture you will be introduced to subqueries and will understand what they are and how they work in Oracle SQL

Practice Challenge: Subqueries Preview 00:15

Common Questions About Subqueries Preview 10:27

Based on the feedback from some of you, I decided to add this new lesson to answer a couple of common questions about subqueries. Enjoy it!

Inline Views Preview 02:09

Here you will learn about the type of subqueries that are known as "inline views"

Practice Challenge: Inline Views Preview 00:11

Subquery Factoring (The WITH Clause) Preview 09:07

Here you will learn about the subquery factoring clause in Oracle SQL.  This is a very important and powerful clause.

Practice Challenge: Subquery Factoring Preview 00:20

Top-N Queries (And Intro to ANALYTIC Functions) Preview 10:32

Watch this lecture to understand how to write TOP-N queries correctly, using different methods.  Here you will also be introduced to the amazing world of Analytic Functions in Oracle SQL

Practice Challenge: Top-N Queries Preview 00:24

The Row Limiting Clause Preview 08:10

In this lesson you will learn about the ROW LIMITING clause, which was introduced in Oracle 12c, and is now available in the express edition version 18c.

Practice Challenge: The Row Limiting Clause Preview 00:32

Section Quiz (S)

Let's see how much you learnt about subqueries!

Here is a short quiz for you.  Enjoy!

Solution to the practice challenges in this section (S) Preview 05:57

Section Recap (S) Preview 04:01

Introduction to Single Row Functions in Oracle SQL. Preview 00:56

Watch this lecture to understand what is covered in this section and get any pertinent recommendations. This section covers Single Rows Functions, Case Expressions and Date Arithmetic.

What is a Function? Preview 03:34

In this lecture you will be introduced to single-row functions

Text Functions Preview 14:33

Here you will learn how and when to use the most common text functions available in Oracle.

Practice Challenge: Text Functions Preview 00:53

Numeric Functions Preview 04:19

Here you will learn how and when to use the most common numeric functions available in Oracle.

Practice Challenge: Numeric Functions Preview 00:18

Date Functions Preview 10:35

Here you will learn how and when to use the most common date functions available in Oracle.

Practice Challenge: Date Functions Preview 00:35

Conversion Functions Part 1 Preview 11:13

In this and the next lecture you will learn about conversion functions, which are very, very important.

Conversion Functions Part 2 Preview 07:32

Practice Challenge: Conversion Functions Preview 01:17

Implicit Conversions Are Evil! Preview 12:01

This is a very important lesson which is an experience tip in itself.  Implicit conversions are very dangerous, but this lesson will teach what you need to know about them, and how to avoid them.

The DECODE Function and CASE Expressions Preview 09:40

In this lecture you will learn 2 different methods to implement IF-THEN-ELSE logic in Oracle SQL statements

Practice Challenge: The DECODE Function and CASE Expressions Preview 00:58

Date Arithmetic Preview 04:30

Here you will learn how to make date calculations in Oracle SQL without using functions.

Practice Challenge: Date Arithmetic Preview 00:22

Section Quiz (F)

Let's confirm that you are ready to move out of the Functions section, by taking this short quiz.

Ready?

Solution to the practice challenges in this section (F) Preview 13:47

Section Recap (F) Preview 09:04

Introduction Preview 01:10

Watch this lecture to understand what is covered in this section and get any pertinent recommendations. This section focuses on a specific type of problems in which you need to transpose rows into columns or columns into rows, and teaches you the 2 basic methods we have to solve them.

Solving the Problem with the Traditional Method Preview 06:00

In this lecture you will understand the problem and will learn how to solve it using the traditional method, which includes the use of CASE expressions.

Pivot Preview 06:37

Watch this lecture to understand how to use the awesome Pivot feature of Oracle SQL

Practice Challenge: Pivot Preview 00:10

Unpivot Preview 08:31

Watch this lecture to understand how to use the awesome Unpivot feature of Oracle SQL

Practice Challenge: Unpivot Preview 00:24

Section Quiz (TRC)

Let’s see how much you learned in this section!

Solution to the practice challenges in this section (TRC) Preview 02:22

Section Recap (TRC) Preview 04:17

Introduction to the Analytic Functions Section Preview 01:27

Watch this lecture to understand what is covered in this section and get any pertinent recommendations. This section covers the wonderful topic of "Analytic Functions" in Oracle SQL.

Analytic Functions Explained Preview 05:51

A very important lesson that will help you understand how Analytic Functions work in Oracle
SQL

The Partition Clause Preview 03:58

This lesson covers the Partition sub-clause, of the Analytic Clause.

Practice Challenge: The Partition Clause Preview 00:19

The Analytic Order By Clause Preview 03:00

This lesson covers the Order By sub-clause, of the Analytic Clause.

The Windowing Clause Preview 10:11

This lesson covers the Windowing sub-clause, of the Analytic Clause.

Practice Challenge: The Windowing Clause Preview 00:12

Solution to the Windowing Clause Practice Challenge Preview 05:07

Here I solve the problem presented as practice challenge in the Windowing Clause lesson, for you to check your solution and complement your learning.

Analytic COUNT, SUM, MIN and MAX Preview 03:56

Here you will find specific examples and recommendations about COUNT, SUM, MIN and MAX, when they are used as analytic functions

Practice Challenge: Common Analytic Functions Preview 00:15

Ranking Functions Preview 07:08

A very interesting lesson about the ranking analytic functions

Practice Challenge: Ranking Functions Preview 00:26

The LISTAGG Function Preview 03:29

A short but very interesting lesson about the LISTAGG analytic function

Practice Challenge: The LISTAGG Function Preview 00:15

The LAG and LEAD Functions Preview 04:43

Here you will learn how and when to use the awesome LEAD and LAG analytic functions

Practice Challenge: The LAG and LEAD Functions Preview 00:29

The FIRST and LAST Functions Preview 09:16

Here you will learn how and when to use the FIRST and LAST functions, which can be used either as aggregate or analytic functions, but are not known for most people.

Practice Challenge: The FIRST and LAST Functions Preview 00:10

The FIRST_VALUE and LAST_VALUE Functions Preview 06:38

Here you will learn how to use the FIRST_VALUE and LAST_VALUE analytic functions.

Practice Challenge: The FIRST_VALUE and LAST_VALUE Functions Preview 00:24

Section Quiz (AF)

You completed this ultra-important section, and I'm sure you enjoyed working on the practice challenges.

Now, it is time for you to evaluate your progress by taking the section's quiz.

Enjoy!

Solution to the practice challenges in this section (AF) Preview 09:22

Section Recap (AF) Preview 08:42

Introduction to Set Operators in Oracle SQL. Preview 01:39

Here you will be introduced to SET operators and will receive some general recommendations about how to use them

The UNION and UNION ALL Operators Preview 09:31

A lesson that covers the UNION and UNION ALL operators in detail

Practice Challenge: The UNION and UNION ALL Operators Preview 00:24

The INTERSECT Operator Preview 02:29

A lesson that covers the INTERSECT operator in detail

Practice Challenge: The INTERSECT Operator Preview 00:22

The MINUS Operator Preview 02:42

Here you will learn how to use the MINUS set operator

Practice Challenge: The MINUS Operator Preview 00:21

Combining Set Operators in the Same Query Preview 03:15

A very important lesson on how to use more than on set operator in a single query, correctly

Practice Challenge: Combining Set Operators in the Same Query Preview 00:15

Section Quiz (SO)

How much did you learn about SET operators?

Let's find out!

Solution to the practice challenges in this section (SE) Preview 03:23

Section Recap (SO) Preview 02:19

Introduction to SQL Joins. Preview 08:03

The introduction to this very interesting and most likely long awaited section about querying more than one table in the same statement.  An initial explanation of what a JOIN is.

Practice Challenge: Introduction to SQL Joins Preview 00:06

Table Joins Explained Preview 08:53

This lecture includes an explanation of the JOIN concept.
In this lesson no code is shown, as we will see how to actually write joins in the following lessons.

A more Complex Test Schema Preview 06:22

In this lecture you will create a more complex database schema that will allow you to write more complex queries

Practice Challenge: Querying a more Complex Test Schema Preview 00:06

Inner Joins Preview 08:08

A very important lesson about the INNER type of joins

Practice Challenge: Inner Joins Preview 00:22

Other Types of Joins (Equijoins, Non Equijoins, Self, Cross and Outer Joins) Preview 05:57

An explanation of the other types of joins that exist in Oracle SQL

Practice Challenge: Other Types of Joins Preview 00:24

Outer Joins in Detail Preview 12:17

A very important and interesting lesson about the OUTER types of joins

Section Quiz (SDMT)

Are you a master of joins?

You probably are, or will be.  Show your knowledge!

Solution to the practice challenges in this section (SDMT) Preview 04:31

Section Recap (SDMT) Preview 05:13

Introduction Preview 00:42

Watch this lecture to understand what is covered in this section and get  any pertinent recommendations. This section covers the interesting topic  of "Hierarchical Queries" in Oracle SQL. 

What is a Hierarchical Query? Preview 03:41

In this lesson you will learn what "hierarchical data" and "hierarchical queries" are, which will prepare you to start learning the techniques and features you can use to solve problems that involve this kind of data.

The Hierarchical Query Clause Preview 10:58

In this lesson you will start learning how to work with hierarchical data using the Hierarchical Query clause in Oracle SQL.

Practice Challenge: The Hierarchical Query Clause Preview 00:18

Hierarchical Pseudo-Columns Preview 03:25

This lesson covers some very useful pseudo-columns that are available when you work with the hierarchical query clause.

Practice Challenge: Hierarchical Pseudo-Columns Preview 00:14

Other Hierarchical Operators and Functions Preview 05:06

Some hierarchical operators were already covered in a previous lesson, but here you will lean about some other operators and some functions that can be really helpful when working with hierarchical data in Oracle.

Practice Challenge: Hierarchical Operators and Functions Preview 00:31

Understanding The Execution of Hierarchical Queries Preview 12:55

This lesson explains the order in which things happen when you execute a hierarchical query, which is very important because understanding how things work will allow you to approach problems in the correct way.

Practice Challenge: Understanding The Execution of Hierarchical Queries Preview 00:15

Sorting The Results of Hierarchical Queries Preview 03:48

Sorting the results of a hierarchical query might require some special treatment.  Here you will learn how Oracle orders the results of hierarchical queries by default, and how to do it correctly if you want to preserve the hierarchical nature of your results.

Practice Challenge: Sorting The Results of Hierarchical Queries Preview 00:19

Section Quiz (HQ)

Ready for a hierarchical quiz?
Let's do it!

Solution to the practice challenges in this section (HQ) Preview 03:45

Section Recap (HQ) Preview 04:16

SELECT statement extended practice Preview 07:49

Here you have a lot of problems with different difficulty levels to help you practice what you have learned until this point.  You are expected to not continue to the next section until you are able to solve them all.

You will enjoy this practice session!

Introduction to Data Modification Preview 00:54

Watch this lecture to understand what is covered in this section and get  any pertinent recommendations. This section covers the statements used insert, modify and delete data from the Oracle database.

Database Transactions Preview 11:59

This lecture introduces you to the section where you will learn how to UPDATE, DELETE and INSERT data into your database tables.  Here you will learn what a database transaction is and how it works in Oracle databases.

The INSERT Statement Preview 11:52

Here you will  learn how to insert new rows into a database table

Practice Challenge: The INSERT Statement Preview 00:44

The UPDATE Statement Preview 14:23

Here you will  learn how to modify the existing data in a database table

Practice Challenge: The UPDATE Statement Preview 00:31

The DELETE Statement Preview 05:38

Here you will  learn how to delete information from database tables

Practice Challenge: The DELETE Statement Preview 00:19

The MERGE Statement (Part 1) Preview 11:31

In this and the following lecture you are going to learn about the MERGE statement, which can help you simplify certain types of operations and to make them more efficient.

The MERGE Statement (Part 2) Preview 13:06

This is the second part of the MERGE statement.

Practice Challenge: The MERGE Statement Preview 00:43

Section Quiz (CCDT)

Check your understanding on this section's topic by solving this quiz.

Enjoy!

Solution to the practice challenges in this section (CCDT) Preview 08:01

Section Recap (CCDT) Preview 05:10

Introduction to DDL in the Oracle database. Preview 01:26

Watch this lecture to understand what is covered in this section and get any pertinent recommendations

Creating, and Dropping Tables Preview 15:21

In this lecture you will learn how to create and drop database tables, which is a very important topic in your SQL learning process

Practice Challenge: Creating, and Dropping Tables Preview 01:14

Changing the Definition of Tables Preview 04:30

Here you will learn how to change the definition of tables that already exist in the database.  This is important because this is a very common scenario when you work for a company, as an Oracle SQL developer

Practice Challenge: Changing the Definition of Tables Preview 00:34

Adding, Dropping and Disabling Constraints Preview 08:46

Here you will learn how to define, drop and disable the different types of constraints that exist in Oracle

Practice Challenge: Constraints Preview 00:32

Sequences Preview 10:46

Watch this lecture to learn how to create, drop and use database sequences, which is another type of object that is widely used in companies.

Practice Challenge: Sequences Preview 00:41

Indexes (Part 1) Preview 09:33

Here you will start learning about the fascinating world of indexes

Indexes (Part 2) Preview 07:44

In this part of the lesson you will complete the basic understanding and knowledge you need to
have about indexes in Oracle databases

Practice Challenge: Indexes Preview 00:56

Views (Part 1) Preview 08:31

Watch this lesson to start learning about database views.  Learn how they are and how to create and use them

Practice Challenge: Views 1 Preview 00:35

Views (Part 2) Preview 06:58

In the second part of the views lesson you complete the basic knowledge you need to have about views in Oracle databases

Practice Challenge: Views 2 Preview 00:15

Section Quiz (DDL)

Ready for the last quiz of the course?

Check you DDL knowledge.

Ready, set, go!

Solution to the practice challenges in this section (DDL) Preview 14:41

Section Recap (DDL) Preview 11:11

Introduction (CUA) Preview 01:23

User Accounts Basics Preview 11:13

Practice Challenge: User Accounts Preview 00:37

User Privileges Part 1 Preview 08:14

User Privileges Part 2 Preview 06:39

Practice Challenge: User Privileges Preview 00:33

User Roles Preview 10:15

Practice Challenge: User Roles Preview 00:54

Related Data Dictionary Views Preview 06:31

Synonyms Preview 07:52

Practice Challenge: Synonyms Preview 00:33

Solution to the practice challenges in this section (CUA) Preview 06:53

Section Recap (CUA) Preview 09:04

Bonus Preview 00:38

Conclusion Preview 02:12

At this point the course has come to an end, which is in reality just a beginning for you.  Watch this lecture for my recommendations on what to do after taking the course to make sure you shine to your full potential.