Atpakaļ

D64252GC10, Oracle Database: Introduction to SQL

Kursa ilgums, akadēmiskās stundas:40
Kursa cena, EUR (bez PVN):1640,00

Plāni

Datums, laiksVietaValoda
20.maijs - 24.maijs, 2019 RĪGA, Tallinas 4Latviski
Pieteikties

 

Apraksts nav pieejams latviešu valodā

Course target

  • Identify the major structural components of the Oracle Database 12c
  • Create reports of aggregated data
  • Write SELECT statements that include queries
  • Retrieve row and column data from tables
  • Run data manipulation statements (DML) in Oracle Database 12c
  • Create tables to store data
  • Utilize views to display data
  • Control database access to specific objects
  • Manage schema objects
  • Display data from multiple tables using the ANSI SQL 99 JOIN syntax
  • Manage objects with data dictionary views
  • Write multiple-column sub-queries
  • Employ SQL functions to retrieve customized data
  • Use scalar and correlated sub-queries
  • Create reports of sorted and restricted data

This Oracle Database: Introduction to SQL training helps you write subqueries, combine multiple queries into a single
query using SET operators and report aggregated data using group functions. Learn this and more through hands-on
exercises. 

Note: This course is a combination of Oracle Database: SQL Workshop I and Oracle Database: SQL Workshop II
courses.

Audience

  • Application Developers
  • Business Analysts
  • Data Warehouse Administrator
  • Developer
  • Forms Developer
  • PL/SQL Developer
  • System Analysts

After course students will be able to

  • Understand the basic concepts of relational databases ensure refined code by developers.
  • Create reports of sorted and restricted data.
  • Run data manipulation statements (DML).
  • Control database access to specific objects.
  • Manage schema objects.
  • Manage objects with data dictionary views.
  • Retrieve row and column data from tables.
  • Control privileges at the object and system level.
  • Create indexes and constraints; alter existing schema objects.
  • Create and query external tables.

Prerequisites

Required Prerequisites

  • Familiarity with data processing concepts and techniques
  • Data processing

Course contents

1. Introduction

  • Course Objectives, Course Agenda and Appendixes Used in this Course
  • Overview of Oracle Database 12c and Related Products
  • Overview of relational database management concepts and terminologies
  • Introduction to SQL and its development environments
  • What is Oracle SQL Developer?
  • Starting SQL*Plus from Oracle SQL Developer
  • The Human Resource (HR) Schema
  • Tables used in the Course

2. Retrieving Data using the SQL SELECT Statement

  • Capabilities of the SELECT statement
  • Arithmetic expressions and NULL values in the SELECT statement
  • Column aliases
  • Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
  • Use of the DESCRIBE command

3. Restricting and Sorting Data

  • Limiting the Rows
  • Rules of precedence for operators in an expression
  • Substitution Variables
  • Using the DEFINE and VERIFY command

4. Using Single-Row Functions to Customize Output

  • Describe the differences between single row and multiple row functions
  • Manipulate strings with character function in the SELECT and WHERE clauses
  • Manipulate numbers with the ROUND, TRUNC and MOD functions
  • Perform arithmetic with date data
  • Manipulate dates with the date functions

5. Using Conversion Functions and Conditional Expressions

  • Describe implicit and explicit data type conversion
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nest multiple functions
  • Apply the NVL, NULLIF, and COALESCE functions to data
  • Use conditional IF THEN ELSE logic in a SELECT statement

6. Reporting Aggregated Data Using the Group Functions

  • Group Functions
  • Creating Groups of Data
  • Restricting Group Results

7. Displaying Data from Multiple Tables Using Joins

  • Introduction to JOINS
  • Types of Joins
  • Natural join
  • Self-join
  • Non equijoins
  • OUTER join

8. Using Subqueries to Solve Queries

  • Introduction to Subqueries
  • Single Row Subqueries
  • Multiple Row Subqueries

9. Using the SET Operators

  • Set Operators
  • UNION and UNION ALL operator
  • INTERSECT operator
  • MINUS operator
  • Matching the SELECT statements
  • Using ORDER BY clause in set operations

10. Managing Tables using DML statements

  • Data Manipulation Language
  • Database Transactions

11. Introduction to Data Definition Language

  • Data Definition Language

12. Introduction to Data Dictionary Views

  • Introduction to Data Dictionary
  • Describe the Data Dictionary Structure
  • Using the Data Dictionary views
  • Querying the Data Dictionary Views

13. Creating Sequences, Synonyms, Indexes

  • Overview of sequences
  • Overview of synonyms
  • Overview of indexes

14. Creating Views

  • Overview of views

15. Managing Schema Objects

  • Managing constraints
  • Creating and using temporary tables
  • Creating and using external tables

16. Retrieving Data by Using Subqueries

  • Retrieving Data by Using a Subquery as Source
  • Working with Multiple-Column subqueries
  • Using Scalar subqueries in SQL
  • Correlated Subqueries
  • Working with the WITH clause

17. Manipulating Data by Using Subqueries

  • Using Subqueries to Manipulate Data
  • Inserting by Using a Subquery as a Target
  • Using the WITH CHECK OPTION Keyword on DML Statements
  • Using Correlated Subqueries to Update and Delete rows

18. Controlling User Access

  • System privileges
  • Creating a role
  • Object privileges
  • Revoking object privileges

19. Manipulating Data

  • Overview of the Explicit Default Feature
  • Using multitable INSERTs
  • Using the MERGE statement
  • Performing flashback operations
  • Tracking Changes in Data

20. Managing Data in Different Time Zones

  • Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP
  • Working with INTERVAL data types