Skip Ribbon Commands
Skip to main content

ORA11g001v1 - Oracle Database 11g: SQL Fundamentals I

Price:

Duration: 2 Days

Audience:

Level:

Technology:

Delivery Method:

Software Assurance Value:

Microsoft CPE:

Course Information

Course Description

​This course is a common starting point in the Sideris Oracle database curriculum for administrators, developers and business users. The objective of this course is to provide an introduction to the SQL database language within the context of an Oracle database, based upon the latest features available.

Course Objectives

​Specific subject areas to be explored and objectives to be reached are:


A summation of relational database principles and an introduction to the Oracle relational database server.

Using SQL Developer and other available SQL interfaces.

Writing simple SQL queries which isolate exactly the data desired, and then format and sort the data as needed.

Writing moderately complex SQL queries using various join techniques.

Supplement SQL code with references to pseudo columns and system functions.

Summarizing, grouping and combining data to obtain more meaningful query results and to allow one to draw conclusions and make business decisions based upon the data processed.

Advanced query techniques such as set operations, sub-queries and summary functions, allowing one to solve some of the more complex theoretical query challenges.

Creating and maintaining database tables using the SQL Data Definition Language (DDL).

Managing data within tables using the SQL Data Manipulation Language (DML).

Taking advantage of the latest features found within the database such as the use of regular expressions, support for international data, time zones and other topics.

Course Audience

​The target audience for this course is all Oracle professionals, both business and systems professionals. Among the specific groups for whom this course will be helpful are:

  1. End-Users and Analysts
  2. Database administrators
  3. Business users and non-technical senior end users

Course Outline

​RELATIONAL DATABASES & SQL


ABOUT RELATIONAL DATABASES

ELEMENTS OF SQL

CHOOSING A SQL & PL/SQL INTERFACE


ABOUT DATABASE CONNECTIONS

ABOUT BIND VARIABLES

USING SQL DEVELOPER

USING SQL*PLUS

USING APPLICATION EXPRESS

BUILDING A SELECT STATEMENT


ABOUT THE SELECT STATEMENT

USING ALIAS NAMES

RESTRICTING DATA WITH THE WHERE CLAUSE


ABOUT LOGICAL OPERATORS

EQUALITY OPERATOR

BOOLEAN OPERATORS

NULL & BETWEEN OPERATORS

IS [NOT] NULL Operator

[NOT] BETWEEN Operator

FINDING TEXT STRINGS

[NOT] LIKE Operator

REGEXP_LIKE()

IN OPERATOR

SORTING DATA WITH THE ORDER BY CLAUSE


ABOUT THE ORDER BY CLAUSE

MULTIPLE COLUMN SORTS

SPECIFYING THE SORT SEQUENCE

ABOUT NULL VALUES WITHIN SORTS

USING COLUMN ALIASES

PSEUDO COLUMNS & FUNCTIONS


USING ROWID

USING ROWNUM

USING THE FUNCTIONS

SYSDATE

USER & UID

USING THE DUAL TABLE

SESSIONTIMEZONE FUNCTION

JOINING TABLES


ABOUT JOINS

INNER JOIN

REFLEXIVE JOIN

NON-KEY JOIN

OUTER JOIN

USING THE SET OPERATORS


ABOUT THE SET OPERATORS

SET OPERATOR EXAMPLES

SUMMARY FUNCTIONS


ABOUT SUMMARY FUNCTIONS

SUMMARY FUNCTIONS WITH DISTINCT

USING SUB-QUERIES


ABOUT SUB-QUERIES

STANDARD SUB-QUERIES

CORRELATED SUB-QUERIES

AGGREGATING DATA WITHIN GROUPS


ABOUT SUMMARY GROUPS

FINDING GROUPS WITHIN THE BASE TABLES

SELECTING DATA FROM THE BASE TABLES

SELECTING GROUPS FROM THE RESULT TABLE

BUILD SIMPLE SQL*PLUS REPORTS


FORMAT COLUMN OUTPUT WITH COLUMN

DEFINING REPORT BREAKS WITH BREAK

PRODUCE SUBTOTALS WITH COMPUTE

USE DATA DEFINITION LANGUAGE TO CREATE & MANAGE TABLES


CREATE TABLE STATEMENT

NOT NULL

DEFAULT

ALTER TABLE STATEMENT

DROP TABLE STATEMENT

SUPPORTIVE STATEMENTS

DESCRIBE

RENAME

USE DATA MANIPULATION LANGUAGE TO MANIPULATE DATA


ABOUT THE INSERT STATEMENT

ABOUT THE DELETE STATEMENT

ABOUT THE UPDATE STATEMENT

ABOUT TRANSACTIONS

ROLLBACK

COMMIT

SAVEPOINT

SET TRANSACTION

TRUNCATE TABLE

COMPLEX TABLE REFERENCES

APPENDIX A

REFERENCE INFORMATION

SQL DDL STATEMENT REFERENCE

SQL DML STATEMENT REFERENCE

SQL TRANSACTION CONTROL STATEMENT REFERENCE

SQL OPERATORS REFERENCE

Course Prerequisites

​No mandatory prerequisites exist for this course. The following prerequisites are recommended:

A-162, Relational Database Design Concepts

Course Schedule
This course is not scheduled yet.