Advanced Oracle SQL Queries Free Tutorial

Welcome to our free Advanced Oracle SQL Queries tutorial. This tutorial is based on Webucator's Advanced Oracle SQL Queries course.

Contact Us or call 1-877-932-8228
  • google plus
Start Tutorial or choose from a lesson below

Lesson 1: Users and Schemas

When you interact with an Oracle database, you do so with a given identity that can take certain actions. This identity is represented by an Oracle user. The actions the user takes occur within a subsection of the database that can have database objects associated with it. This subsection is known as a schema. The Oracle HR Schema contains Human Resources data for a fictional company. The tables and data provide an area where you can experiment with Oracle Database features without concern of damaging production data. The tables and relationships are structured in a way that allows for the use of most of the features of SQL. In addition, most of the examples in Oracle's own documentation utilize data from the HR Schema.

Lesson 2: Pseudo Columns & Functions

Oracle SQL can do more than simply retrieve data from tables. There are features of the language which allow certain values that are not explicitly included in the database to be derived or calculated. This chapter will explore several of the features of the language which provide these capabilities.

Lesson 3: Using Subqueries

A subquery is a SQL SELECT statement that is contained within another SELECT statement. Subqueries can appear in various parts of a query, including the SELECT clause, the FROM clause, the WHERE clause and the HAVING clause. The innermost query is evaluated prior to queries that contain it. Performance problems can result if an "expensive" query is nested as an inner query.

Lesson 4: Joining Tables

Oracle SQL queries are not limited to selecting data from a single table. Several tables can be accessed within a query and the data aggregated into a single result set which is returned to the user. The relationship between two tables is known as a join and consists of a mapping of values between specified columns in each table. In this chapter you will learn about various types of joins and their application to data retrieval.

Lesson 5: Rollup & Cube

In this lesson, you will learn about enhanced group processing and using rollup and cube.

Lesson 6: Using Set Operators

Set operators are used to combine the result sets returned by two separate queries into a single result set. The set operators for SQL are MINUS, INTERSECT, UNION and UNION ALL.

Lesson 7: Conditional Processing

Many programming languages have the concept of conditional processing. Among the most common construct is an if/then structure. Oracle SQL does allow for conditional processing, but does not use if/then statements to accomplish it. Instead, the DECODE function and a CASE statement are provided.

Lesson 8: Scalar SQL Functions (Character)

SQL Functions return values based upon specified input. They provide functionality that is traditionally addressed in a procedural rather than a set-oriented programming paradigm. This lesson will provide an overview of functions included with Oracle that relate to character data.

Lesson 9: Scalar SQL Functions (Non-Character)

In this lesson you will learn about non-character SQL functions.

Lesson 10: SQL Data Manipulation Language

SQL Data Manipulation Language is also known as DML. It is used to manipulate data in the database. The ability to query data is a different kind of responsibility than the ability to change data. Because of this, a database user must have been granted appropriate security privileges to perform an INSERT, UPDATE, or DELETE on a given table. The target table is the table that is having rows added, changed or removed.

Try The Complete Course

In addition to the free course material here, our complete self-paced course includes:

  • Video Presentations from Expert Instructors
  • Exercises
  • Interactive Quizzes
  • Readings and Tasks
Learn More