Oracle Database Free Courses with Certificate offer
Databases for Developers: Foundations
Table of Contents
Databases for Developers: Foundations
Learn SQL in this FREE 12-part boot camp. It will help you get started with Oracle Database and SQL.
The course is a series of videos to teach you database concepts, interactive SQL tutorials, and quizzes to reinforce the ideas.
What You’ll Learn
| Module 1: Tables Learn about the types of table available in Oracle Database, their uses, and how to use create table. |
| Module 2: Columns and Data Types When defining columns in a table, you need to choose a data type for them. This class you about the common SQL data types and their uses. |
| Module 3: Data Modeling Deciding where to store what is one of the fundamental decisions you make when building a database. This class gives an overview of the things you need to consider when designing your tables. |
| Module 4: Tables, Columns and Modeling Review A series of quizzes to recap the material from modules 1-3. |
| Module 5: Select and Where Learn how to use a select statement to get you rows from your database. And filter these data using a where clause. |
| Module 6: Joins This module teaches you about the different types SQL join: inner, left and right outer, full, and cross. |
| Module 7: Aggregates and Group By This class covers how you can summarize your data using aggregate functions and group by. |
| Module 8: Select, Joins and Group By Review A chance to recap and catch up on modules 5-7 covering the basics of SQL queries: select, joins and group by. |
| Module 9: Insert and Commit Learn how to use insert to loads data into your database tables. And save and undo your changes with commit and rollback. |
| Module 10: Update and Transactions Master the basics of changing values in your tables with the update statement. This module also covers the concept of a transaction: a single, logical unit of work. |
| Module 11: Delete and Truncate Learn how to remove data from your database using delete and truncate. This class covers the differences between these operations and when you should use one or the other. |
| Module 12: Insert, Update and Delete Review A final set of quizzes to check you’ve understood the material on insert, update, delete, and truncate. Complete the course to get your certificate! |
Free App development 30 Days of Flutter course
Databases for Developers: Next Level
Databases for Developers: Next Level
A FREE 10-part tutorial to help you improve your Oracle SQL skills.
This course continues where Databases for Developers: Foundations left off, taking you further on your SQL journey.
The class consists of a series of videos introducing concepts, interactive coding sections, and quizzes to test your understanding.
The first module is now open. Join us for SQL-awesomeness! 🙂
What You’ll Learn
| Module 1: Null Null is a placeholder for missing or not applicable information. But it introduces a third state to true/false logic: unknown. This module shows you the correct way to query null values. |
| Module 2: Subqueries A subquery is a query buried in another SQL statement. These come in several varieties. This module explains their differences and how to use them. |
| Module 3: Order By and Top-N To guarantee your results are sorted in a particular way, you must use an order by clause. This module shows you how this works. It goes on to explain how to filter results to show the first N rows of a sorted data set. |
| Module 4: Analytic Functions Aggregate functions summarize data, so you get one row per group. But sometimes you need to see the individual rows in the group alongside the totals. To do this you need analytic functions. But that’s not all. Analytic functions enable you to define sliding windows over your rows. So you can compute running totals with ease. This module gives an overview of how to do this. |
| Module 5: Pivot and Unpivot Sometimes you need to summarize data by week or month. But with the dates as columns instead of rows! So you need to convert the row values into column headings. Or the reverse. This module shows you how to do this with the pivot and unpivot clauses. |
| Module 6: Union, Minus and Intersect You can combine rows from two tables to get all the rows, only those in common or those in one table not in the other. This module shows you how to do this with the set operators union, intersect and minus. |
| Module 7: Hierarchical Queries Often a table stores rows with a parent-child relationship. For example, managers to their employees, folders on your computer, and family trees. This module shows you how to display parents and children next to each other using SQL. |
| Module 8: Merge Sometimes when saving a record, you want to add it to the database if it doesn’t exist. But if it does, update the existing values instead. So you need an update-or-insert operation. Luckily there’s one statement that does it all: merge. This module shows you how. |
| Module 9: Read Consistency If someone saves changes to a table you’re querying, what happens? At what point do you see their updates? How do you ensure you get a consistent view of the data? This module explains how Oracle Database’s read consistency model works and discusses the implications for your code. |
| Module 10: Course Review A recap of the course material and a live Q&A for you to put your questions to the experts! There are no videos or tutorials in this module. Just a series of quizzes to check you’ve understood all the course material. |
Get Started with PL/SQL Table Functions
Get Started with PL/SQL Table Functions
Table functions are functions that act like tables in a SELECT statement: you can query columns and rows from the array returned by the function!
Table functions are a great way to programmatically construct data sets and also play a key role in data warehouses that need to perform multiple transformations of data within a single SQL statement.
This class starts with table function fundamentals, then explores streaming table functions, and finishes up with a look at pipelined table functions.
Before starting this class, you should be comfortable with writing SQL SELECT statements and working with PL/SQL collections (primarily nested tables).
When the course begins, you will find links at the bottom of this page to help you do just that.
What You’ll Learn
| Module 1: Overview and Fundamentals of Table Functions Table functions can be very simple, and very complicated. We start with the most basic example: a function that returns an array of scalar values. Watch the video, take the LiveSQL tutorial, and then take some quizzes to reinforce your knowledge. |
| Module 2: Returning Multiple Columns from a Table Function Suppose you want your table function to return multiple columns, just like most relational tables. Then what do you do? Well, you create an object type, an array of those object types, and construct data as needed to populate that array. Sound complicated? Not really! Watch our video, read a blog post, then finish up with quizzes to drive the points home. |
| Module 3: Streaming Table Functions A common usage of table functions is to stream data directly from one process or transformation to the next process without intermediate staging. Hence, a table function used in this way is called a streaming table function. This technique is most often used in data warehouses. Streaming table functions usually accept as parameters result sets (rows and columns returned by SELECTs), which then requires the use of the CURSOR expression. Watch our video, dive into more details with a blog post, and then answer some quizzes to be sure you’ve absorbed the material! |
| Module 4: Pipelined Table Functions Pipelined table functions are a specialized variation of table functions that can only be used in a SELECT statement (not within a PL/SQL block natively) and allow results to be “piped” directly back to that SELECT statement for immediate use (even before the function has finished all its work!). Pipelined table functions can offer a big performance boost and greatly reduce PGA consumption. Learn all about them from our video, blog post and quizzes! |
Analytic SQL for Developers
A FREE 6 module boot camp to help you become an expert with Oracle Analytic SQL functions.
The course is organized around a series of videos that teach you the concepts and syntax behind analytic functions.
Following each video, take a set of quizzes to reinforce what you’ve learned. Got questions? Log them on the AskTOM site with the tag #AnalyticClass in the subject, and Connor McDonald of the answer team will help you out.
You can also ask questions about specific quizzes here at the Dev Gym, once you’ve submitted your answers.
What You’ll Learn
| Module 1: Introduction An introduction to the analytic SQL syntax and writing your first queries to rank/sequence data in a result set. |
| Module 2: Predicates and Partitioning Using analytic functions as a predicate (eg, “Show me the top 5 highest salary earners”) requires care to ensure the results are logically consistent. The Partitioning clause in Analytics (not to be confused with table partitioning) is the means via which analytic expressions can be applied to logical groupings of data within a result set. |
| Module 3: Windows Perhaps the most powerful construct in Analytic SQL, and the most complicated to understand, is the windowing clause. This define how broadly the set of rows and/or values that will be acted upon for an analytic expression |
| Module 4: More on Windows, and the LAG/LEAD functions Windows can be dynamic, have automatic defaults, and the presence of nulls can alter how a window expression will be applied. The LAG/LEAD functions are perhaps the most commonly used Analytic functions, making queries such as “compare today’s sales to yesterday’s sales” easy to code without unnecessary self-joins. |
| Module 5: Problem Solving with Analytics Now that the syntax components and function usage have been covered, we move on to applying those skills to solving specific problems using analytic functions. |
| Module 6: Additional features and wrap up There are several other features that fall under the umbrella of Analytical SQL within the Oracle Data Warehousing Documentation set. The course will wrap up by covering these more esoteric features to complete your knowledge base on Analytics. |
CRUD operations using cx_Oracle and Python
CRUD operations using cx_Oracle and Python
Learn how to use the cx_Oracle driver in your Python-based applications to perform basic CRUD (Create, Read, Update and Delete) operations in Oracle Database.
Each module in this class starts with a video and blog post, and concludes with quizzes to reinforce your knowledge of the topic.
What You’ll Learn
| Module 1: Connections and Cursors In this module, you will learn how to create a connection to Oracle Database. We will also explore some basic functionality of the cursor object in cx_Oracle. |
| Module 2: Create Rows (INSERT) using cx_Oracle In this module, you will learn how to insert rows into tables through the cx_Oracle driver. We start with a video, then invite you to explore a blog post, and finish up with some quizzes to drive home the lessons. |
| Module 3: Retrieve Rows (SELECT) with cx_Oracle It’s important to know how to get data into Oracle Database from your Python programs. But just as critical is getting data back out with SELECT statements. We show you the way with a video and blog post. Then you can get to verify your new-found knowledge with a handful of quizzes. |
| Module 4: Update Rows with cx_Oracle You may work with some tables that are “insert-only” – that is, data is inserted and never changed. But for most tables, you will need to insert rows and then change values of columns in those rows. For that you need the UPDATE statement and a driver to execute that statement for you. cx_Oracle to the rescue! So pop some popcorn and watch Blaine’s video. Then catch up on the details in his blog post and prove to the world you’re an expert by answering our quizzes. |
| Module 5: Delete Rows with cx_Oracle Data generally is forever, but individual rows? Not always. Sometimes they must be deleted. Learn the ins-and-outs of removing rows with the cx_Oracle driver. You can probably guess the drill: watch a video; read a blog post, then take some quizzes to validate your newfound knowledge. |
Nine Good-to-Knows for PL/SQL Error Management
Nine Good-to-Knows for PL/SQL Error Management
This class covers key features and guidelines for managing errors in your PL/SQL-based applications.
It is built around a blog and a video playlist created by Steven Feuerstein – links to both are in each module of the class, but may also be found in the list of resources for this class (see bottom of page).
What You’ll Learn
| Module 1: Handling Exceptions Raised in Declaration Section #1 in Steven Feuerstein’s list of Nine Good-to-Knows for PL/SQL Error Management, a blog post first on the list of exercises for this workout. So what territory does an exception section actually handle, anyway? What happens if you raise an exception in the declaration section? You might be surprised, but you don’t WANT to be surprised. To take away the mystery, read the blog post, watch a short video, and then it’s quiz time! |
| Module 2: Impact of Exceptions on Transactions #2 in Steven Feuerstein’s list of Nine Good-to-Knows for PL/SQL Error Management, a blog post first on the list of exercises for this workout. Lots of developers think that whenever there is an exception, all uncommitted changes are rolled back. Not necessarily so! Make sure you are crystal clear on this topic: read the blog post, watch a short video, and then it’s quiz time! Any non-query DML statements that complete successfully in your session are not rolled back when an exception occurs – either directly in PL/SQL or propagated out from the SQL engine. You still have the option of either committing or rolling back yourself. |
| Module 3: Hard-Coding Error Numbers #3 in Steven Feuerstein’s list of Nine Good-to-Knows for PL/SQL Error Management, a blog post first on the list of exercises for this workout. Some errors have named exceptions, but lots don’t. So do you hard-code error numbers in your code? That doesn’t sound good. To find out about alternatives, read the blog post, watch a short video, and then it’s quiz time! |
| Module 4: Propagation of Exceptions (or Not) #4 in Steven Feuerstein’s list of Nine Good-to-Knows for PL/SQL Error Management blog post. Let’s explore how exceptions propagate out through enclosing blocks. Read the blog post, watch a short video, then it’s quiz time! URL for blog post: http://stevenfeuersteinonplsql.blogspot.com/2016/03/nine-good-to-knows-about-plsql-error.html |
| Module 5: Important Data to Log With Errors #5 in Steven Feuerstein’s list of Nine Good-to-Knows for PL/SQL Error Management, a blog post first on the list of exercises for this workout. Whenever you handle an exception and record the error in your log, you need to grab some key data. Which data? To find out…read the blog post, watch a short video, and then it’s quiz time! |
| Module 6: Logging and Re-Raising Exceptions #6 in Steven Feuerstein’s list of Nine Good-to-Knows for PL/SQL Error Management, a blog post first on the list of exercises for this workout. Let’s explore the impact of re-raising an exception for error handling and logging. Read the blog post, watch a short video, then it’s quiz time! |
| Module 7: WHEN OTHERS THEN NULL #7 in Steven Feuerstein’s list of Nine Good-to-Knows for PL/SQL Error Management, a blog post first on the list of exercises for this workout. When things go wrong, it’s better to know than stick your head in the sand. So I’ll show you the PL/SQL code for of head-in-sand, and then discuss how to avoid it. Read the blog post, watch a short video, then dive into some quizzes to make sure you’ve got it down. |
| Module 8: Suppress Row-level SQL Errors #8 in Steven Feuerstein’s list of Nine Good-to-Knows for PL/SQL Error Management, a blog post first on the list of exercises for this workout. Usually it’s all-rows or no-rows for a SQL DML statement, but you can change that! Read the blog post, watch a short video, then it’s quiz time! |
| Module 9: Application Specific Error Messages #9 in Steven Feuerstein’s list of Nine Good-to-Knows for PL/SQL Error Management blog post. Let’s explore what you need to do to send an application-specific error message back to your users (what? They don’t like reading “ORA-01403: no data found”?). Read the blog post, watch a short video, then it’s quiz time! URL for blog post: http://stevenfeuersteinonplsql.blogspot.com/2016/03/nine-good-to-knows-about-plsql-error.html |
FAQ’S
How much does this cost?
Nothing. Nada. Nil. Zilch. That’s right, it’s 100% FREE!
I missed the start. Can I still join?
Yes! You can join Databases for Developers at any time
When does the course end?
The modules have no fixed end date. Once you’re registered you can take open classes whenever you want
What will I learn on this course?
This course will teach you the basics of SQL and working with Oracle Database
How much time will it take?
We estimate that this will take up to 30 minutes per week.
What if I fall behind?
This is a self-paced course. Once registered, you have lifetime access to these materials to review at any time. So you can complete it at your leisure. But to get the best value from this course we recommend that you complete each class in the week it’s released.
What is the format of this course?
The course is a combination of:
- Videos designed to teach you database concepts.
- Coding tutorials for you to practice your skills.
- Quizzes to deepen your knowledge.
Do I need access to an Oracle Database?
No! This is a fully online course. The materials are designed so you can complete the course without a database.Are there free resources where can I practice what I’ve learned?Yes! Oracle has the following free offerings:
- Always free cloud services. These allows you to create two free-forever Oracle Autonomous Databases.
- Oracle Database XE. A fully-featured free verison you can download and install on your computer.
- Live SQL. This is a free, browser-based tool you can use to write SQL.
Will I get a certificate for completing this course?
Yes! If you complete all the exercises then we’ll send you a certificate of your achivement.
I already have a good working knowledge of SQL and Oracle Database. Is this course right for me?
This course is aimed at beginners. It assumes no prior knowledge of SQL or Oracle Database. If you’re an advanced user there may be little new for you. But it never hurts to brush up on the basics, right? 😉
I’m confident with the basics, have you got anything more advanced?
Yes! If you want to go beyond the basics, take Databases for Developers: Next Level or you can learn about SQL performance tuning in Databases for Developers: Performance
Will this course help me pass Oracle Certification exams?
Databases for Developers is not officially aligned with Oracle University curriculum. But this course along with Next Level do cover most of the material for the Oracle Database SQL exam, 1Z0-071
I still have unanswered questions. Where can I ask them?
You can reach out to Chris Saxon