A 1 day course designed to follow on from the 2 day SQL Fundamentals course. It is aimed at staff who need to build small databases to support software development and testing, or for use with none critical applications. This course covers creating and modifying tables, indexes, views and sequences together with access control and transactions. The course requires access to an Oracle database the SQL*Plus command line interpreter and the SQL Developer GUI.
Prerequisites
The course is designed for staff with prior experience of SQL either with Oracle or another Relational Database System and are able to run queries, including multi-table joins, use where and order by clauses; and understand aggregate functions, group by and having clauses.
Learning Objectives
To introduce staff to the basic Data Definition Language (DDL) features of Oracle, specifically:
- Create, drop and alter tables
- Work with Views, Sequences and synonyms
- Use table indexes
- Work with transaction, commit and rollback semantics
- Understand the different isolation levels
- Grant and revoke user privileges
Delivery
A mix of theory, demonstrations and exercises, with approximately 50% of the time allocated to practical hands-on use of SQL.
Day 1 – Managing Database Objects
- Oracle system tables and data dictionary
- INSERT, UPDATE & DELETE
- INSERT FROM SELECT statement
- CASCADE DELETE tables
- CREATE and DROP TABLE statements
- CREATE and DROP views
- Using table indexes
- ALTER TABLE constraints and indexes
- INSERT FROM
- Create and use sequences
- Define Synonyms
- Transactions: and savepoints
- Using COMMIT and ROLLBACK
- Isolation levels and repeatable reads
- GRANT and REVOKE privileges