OVERVIEW
In this course students with previous ANSI SQL experience are introduced to the Oracle Database 11g environment, SQL Developer, the SQL language and extensions to the ANSI SQL standard. The course provides students with an overview of the basic elements of SQL. Students learn about the SELECT statement, pseudonyms, and different types of operators, conditions, expressions, and functions in SQL. Students also
learn how to query from tables using joins and other advanced subqueries. Students learn to create and manage database objects such as tables, views, indexes, sequences, and synonyms. In addition, you learn how to control user access to the database and how to create roles and grant privileges.
DURATION: 2 Days
OBJECTIVES
- Describe the Oracle database environment
- Use various features of SQL*Developer
- Describe and work with the basic elements of Oracle SQL
- Combine tables using joins and subqueries
- Create and manage tables, synonyms, sequences, indexes, and views
- Implement database security by granting privileges and creating roles
INTENDED AUDIENCE
This course is designed for people who will be retrieving data from a relational database using SQL with an Oracle 11g focus. This includes Administrators, Application Developers, Database Administrators, Forms Developer, PL/SQL Developer, System Analysts, Technical Consultant.
PREREQUISITES
Basic understanding of Relational Databases, and working knowledge of SQL is required.
FORMAT/MATERIALS
This course includes a combination of lecture, discussion and numerous computer workshops. Students will receive an extensive course manual covering SQL, which will be a valuable reference after class.
CONTENT
SQL DEVELOPER
- List the key features of Oracle SQL Developer
- Install Oracle SQL Developer
- Identify the menu items of Oracle SQL Developer
- Create a database connection
- Manage database objects
- Use SQL Worksheet
- Save and Run SQL scripts
- Create and save reports
REVIEW SELECT BASICS
- Selected column list
- Where clause for row selection
- Group by for summary
- Having for group selection
- Order by for sorting
ADVANCED RETRIEVALS
- Joining Tables
- Join condition syntax
- Alias Names
- Joining a table to itself
- The Outer Join (LEFT, RIGHT, FULL)
- Partition Outer Join
- NATURAL JOIN
- Subqueries
- Single-valued subqueries
- Multi-valued subqueries
- ALL and ANY parameters
- Correlated subqueries
- EXISTS parameter
- Differences
- Unions
DATA MANIPULATION
- Updating a Single Row
- Updating Multiple Rows
- Inserting a Single Row
- Inserting Multiple Rows
- Multi-Table INSERT
- Deleting a Single Row
- Deleting Multiple Rows
- Truncating rows
- MERGE
- COMMITs and ROLLBACKs
CREATING AND MANAGING OBJECTS
- Create and maintain tables by using the CREATE, ALTER, DROP, RENAME, and TRUNCATE statements
- Use the data dictionary to view and maintain information about tables
- Create and maintain integrity constraints
- Discuss constraint states
- Create and maintain a view
- Retrieve, insert, update, and delete data through a view
- Create, maintain, and use sequences
- Create and maintain indexes
CONTROLLING USER ACCESS
- Discuss the concepts of users, roles, and privileges
- Create users
- Create roles
- Grant and revoke object privileges
- Create and access database links