Category: Databases and SQL
This course covers all the skills needed to use Oracle Database in professional work. Structured knowledge of SQL can be an asset to business analysts, report writers, application developers in the RAD environments (Oracle Forms, Oracle Fusion and even Microsoft Access), programmers and enterprise application architects.
This four-day intensive course covers, among other things, the full requirements for the 1Z0-071 Oracle Database SQL exam, which when passed grants you an Oracle Database SQL Certified Associate certificate.
This course covers work at the user level. It does not focus on performance or implementation aspects.
Duration
4 days
Agenda
- Introduction to databases:
- basic concepts relating to databases
- relational data model
- transactional databases.
- A few words about Oracle:
- DBMS architecture,
- versions and licenses,
- available tools,
- work environment configuration.
- Create simple SQL queries:
- displaying data retrieved from the database,
- sorting and limiting the number of rows returned,
- using alternative column names – aliases,
- basic mathematical operations and string concatenation,
- data filtering,
- substitution of variables during query execution,
- NULL value and its handling in database,
- elimination of repetitive rows – DISTINCT clause.
- Extended data selection:
- logical operators of conjunction and negation of conditions,
- comparison operators (>, >=, =, <, <=, <>, !=),
- range operator (BETWEEN),
- membership operator (IN),
- pattern operator (LIKE).
- scalar functions
- date and time operations,
- operations on character types,
- numeric functions,
- conditional functions, logical operators (CASE, DECODE, COALESCE, NVL, NVL2),
- implicit type conversion,
- functions relating to type conversion: TO_CHAR, TO_DATE, TO_NUMBER,
- use of functions in all places of the query
- Data aggregation
- grouping functions,
- filtering rows after aggregation.
- Set operations
- UNION, UNION ALL, MINUS, INTERSECT operators,
- control of the order of rows returned by operations on sets.
- Joining data sets:
- available join criteria, equijoin types, theta-join, natural joins
- Oracle and ANSI SQL syntax
- cross joins,
- external joins,
- selfjoin type,
- antijoin type.
- Nested queries (subqueries)
- scope of subqueries,
- scalar subqueries,
- multiline subqueries,
- correlated and uncorrelated subqueries.
- DML (Data Manipulation Language) commands and transactions
- inserting rows,
- deleting data from the database,
- changing data in the database,
- the scope of constraints when performing DML operations
- Transaction Management
- commit, rollback transactions, save points,
- locks on concurrent execution of operations,
- deadlocks,
- UNDO space principle and FLASHBACK operations.
- DDL (Data Definition Language) commands
- creating, deleting and modifying tables, column types,
- constraints, primary and foreign keys,
- creating simple and complex views,
- schemas in a database,
- other database objects: indexes, synonyms (public and private), sequences.
- Database design
- basic information about standardisation,
- normalization and efficiency,
- selecting appropriate data types
- good practices of creating and naming objects
Audience and prerequisites
Course participants do not need to have any prior specialist knowledge, but should have no problems with basic computer skills. It may be advantageous, but it is not necessary to have good knowledge of Excel or to have some programming skills.
Certificates
Course participants receive completion certificates signed by ALX.
