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