Oracle Database 12c R2: SQL Workshop I Ed 3 (D80194GC30)

This course is an introduction to SQL. It teaches students how to query or update data in a relational database management system (RDBMS). In this class, students learn the concepts of relational databases and the powerful SQL programming language. This course provides the essential SQL skills that allow developers to write queries against single and multiple tables, manipulate data in tables, and create database objects. A student can use SQL Developer to access an Oracle database or MySQL Workbench to access a MySQL database. They can also experience using SQL*Plus to access an Oracle database or MySQL command-line client to access a MySQL database.
 
Students can see SQL statements that are identical in both Oracle database and MySQL database, and also see the variations in output or syntax between the databases in some statements.
 
Target Audience
  • Functional Implementer
  • PL/SQL Developer
  • Application Developers
  • Database Administrators
  • System Analysts
  • Forms Developer
 
Course Objectives
  • Run complex queries to retrieve data from multiple tables
  • Run data manipulation language (DML) statements to update data in a database
  • Run data definition language (DDL) statements to create and manage schema objects
  • Identify the major components of Oracle Database and MySQL database
  • Retrieve row and column data from tables with the SELECT statement
  • Create reports of sorted and restricted data
  • Employ SQL functions to generate and retrieve customized data
 
Suggested Prerequisite
  • Familiarity with programming languages
  • Familiarity with data processing concepts and techniques
Show details
 
Course Topics
 
Introduction
  • Oracle Database 12c: Focus Areas
  • Introduction to Oracle Cloud
  • MySQL Enterprise Edition
  • High Scalability with MySQL
  • Relational Database Concept
  • Entity Relationship Model
  • How SQL Works
 
Retrieving Data using the SQL SELECT Statement
  • Concatenation Operator in Oracle
  • Concatenation Function in MySQL
  • Using Literal Character Strings in Oracle
  • Using Literal Character Strings in MySQL
  • Displaying Table Structure by Using Oracle SQL Developer
  • Displaying Table Structure by Using MySQL Workbench
  • Duplicate Rows
 
Restricting and Sorting Data
  • Using the WHERE Clause
  • Character Strings and Dates
  • Comparison Operators
  • Using the BETWEEN, IN and LIKE Operators
  • Using the AND, OR and NOT Operators
  • Using the ORDER BY Clause
  • SQL Row Limiting Clause
 
Using Single-Row Functions to Customize Output
  • Character Functions
  • Nesting Functions
  • Numeric Functions
  • Working with Dates
 
Using Conversion Functions and Conditional Expressions
  • Implicit Data Type Conversion
  • Using the TO_CHAR,TO_NUMBER and TO_DATE Functions
  • Using the CAST() function in Oracle
  • Explicit Data Type Conversion in MySQL
  • NVL Function (Oracle) and IFNULL() Function (MySQL
  • Using the NVL and NVL2 Function in Oracle
  • Using the IFNULL Function in MySQL
  • Conditional Expressions
 
Reporting Aggregated Data Using the Group Functions
  • Types of Group Functions
  • Group Functions and Null Values in Oracle
  • Group Functions and Null Values in MySQL
  • Using the DISTINCT Keyword
  • Creating Groups of Data
  • Restricting Group Results
  • Nesting Group Functions in Oracle
 
Displaying Data From Multiple Tables Using Joins
  • Creating Natural Joins
  • Using Table Aliases with the USING Clause in Oracle
  • Creating Joins with the ON Clause
  • Creating Three-Way Joins
  • Self-Joins Using the ON Clause
  • Retrieving Records with Nonequijoins
  • Cartesian Products
 
Using Subqueries to Solve Queries
  • Rules and Guidelines for Using Subqueries
  • Single-Row Subqueries
  • Using Group Functions in a Subquery
  • HAVING Clause with Subqueries
  • Multiple-Row Subqueries
  • Using the ANY Operator in Multiple-Row Subqueries
  • Using the ALL Operator in Multiple-Row Subqueries
  • Null Values in a Subquery
 
Using the SET Operators
  • Set Operator Rules
  • Using the UNION and UNION ALL Operators
  • Using the INTERSECT Operator
  • Using the MINUS Operator
  • Matching SELECT Statements in Oracle
  • Matching SELECT Statements in MySQL
  • Using the ORDER BY Clause in Set Operations in Oracle
  • Using the ORDER BY Clause with UNION in MySQL
 
Managing Tables using DML statements in Oracle and MySQL
  • Adding a New Row to a Table
  • Updating Rows in a Table
  • Removing a Row from a Table
  • Explicit Transaction Control Statements
  • Implicit Transaction Processing
  • Implementing Read Consistency
  • FOR UPDATE Clause in a SELECT Statement
 
Introduction to Data Definition Language in Oracle and MySQL
  • Database Objects
  • Creating Tables (Database)
  • Data Types
  • Including Constraints
  • Creating a Table Using a Subquery
  • Adding, Modifying and Dropping a Column