D64252GC10, Oracle Database: Introduction to SQL

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

Plāni

Datums, laiks	Vieta	Valoda
20.maijs - 24.maijs, 2019	RĪGA, Tallinas 4	Latviski	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