Oracle Database 12c – Introduction to SQL: Working with Joins and Subqueries

Functional Implementer, Forms Developer, System Analysts, Application Developers, PL/SQL Developer and learners looking to obtain the Oracle Database 12c Administrator Certified Associated designation or to become an OPN Certified Specialist.

Prerequisite
None

Expected Duration
112 minutes

Description
Database normalization is good practice when building a relational database to minimize data redundancy. In this course, you will learn how to join tables together to view information across multiple tables, how to write subqueries, as well as the more advanced features of the SELECT statements used to obtain values based on an unknown conditional value. This course is one of a series in the SkillSoft learning path that cover the objectives for the Oracle Database 12c: SQL Fundamentals exam (1ZO-061). The exam is part of the Oracle Certified Associate certification.

Objective

Multiple Tables and Joins

  • start the course
  • describe how to obtain data from multiple tables using JOINS in a SQL statement
  • join tables automatically based on columns with matching data types and names to retrieve data in a SQL statement
  • use the USING clause to specify columns used for an equijoin to retrieve records in a SQL statement
  • use aliases with the USING clause and use prefixes to join multiple tables in a SQL statement
  • use the ON clause when you specify join conditions in the WHERE clause in a SQL statement
  • using a three-way join to join three tables in a SQL statement
  • use the AND clause or the WHERE clause to apply additional conditions to a join in a SQL statement
  • use a self-join to join a table to itself in a SQL statement
  • use a join condition containing a non-equijoin in a SQL statement

Multiple Tables and Outer Joins

  • use an OUTER join to return records with no direct match in a SQL statement
  • describe the differences between an INNER and OUTER join
  • use a left outer join in a SQL statement
  • use a right outer join in a SQL statement
  • use a full outer join in a SQL statement
  • describe the generation of a Cartesian product returned by a SQL statement
  • create cross joins in a SQL statement

Using Subqueries to Solve a Problem

  • use a subquery to solve a problem in a SQL statement
  • describe the rules, guidelines, and types of subqueries used in a SQL statement

Single-row Subqueries

  • use a single-row subquery to return one row in a SQL statement
  • display data from a main query using a group function in a subquery to return a single row in a SQL statement
  • use a subquery in the HAVING clause of a SQL statement
  • identify common errors that occur when an incorrect number of rows is returned by a single-row subquery in a SQL statement

Multiple-row Subqueries

  • describe multiple-row subqueries in a SQL statement
  • use the IN, ANY, and ALL operators in multiple-row subqueries in a SQL statement
  • use a multiple-column subquery to return more than one column to the outer query in a SQL statement
  • describe NULL values in a subquery and use the NOT IN operator in a SQL statement

Practice: Multiple Tables and Joins

  • display data from multiple tables using joins in a SQL statement

Practice: Using Subqueries to Solve a Problem

  • combine two queries by making use of a subquery in a SQL statement

MONTHLY SUBSCRIPTION

$129/month
 

ANNUAL SUBSCRIPTION

$1295/year

Multi-license discounts available for Annual and Monthly subscriptions.