Professional Training

Prerequisites

The target audience for this course is all IT professionals, both business and systems
professionals. Among the specific groups for whom this course will be helpful are:
• Business and non-IT professionals
• Application designers and database developers
• Business Intelligence (BI) analysts and consumers
• Database administrators
• Web server administrators

Objectives

The first portion of this course considers the logical models upon which a relational database is based and the various configurations and environments in which you may work with a database. The next segment focuses on the actual SQL syntax for writing database queries. You will begin with the simplest of queries and then proceed onto moderately complex query scenarios. Finally, the course covers the DDL, DML and transaction control portions of the SQL language that allow one to create, maintain and manipulate application database objects and application data.
Since SQL is an industry standard language, many of the topics presented and many of the skills you will acquire will be applicable to other database platforms, such as Microsoft SQL Server, Ingres SQL, IBM DB2, the open-source databases MySQL and PostgreSQL, and others.

Course setup

Unless otherwise required by clients, the course is run using Visual Studio Code on an Azure training SQL database we provide. Participants will need to install Visual Studio Code (free).

Relational databases & data models

About data models
About the relational model
The electronics data model
About the relational DBMS

Selection & setup of the database interface

Considering available tools
Selecting the appropriate tool
Database connections
Setup querying tools

Using the database interface

About bind and substitution variables
Using Azure Studio, SQL Developer and other interfaces

Introduction to the SQL language

About the SQL language
Characteristics of SQL
Introducing SQL using SELECT
SQL rules theory

The SELECT statement

The SELECT statement
DISTINCT / UNIQUE keyword
Using alias names

Restricting results with the WHERE

About logical operators
Equality operator
Boolean operators
REGEXP_LIKE()
IN operator

Sorting data with the ORDER BY clause

About the ORDER BY clause
Multiple column sorts
Specify the sort sequence
About NULL values within sorts
Using column aliases

Pseudo columns, functions & top-N queries

ROWID pseudo column
ORA_ROWSCN pseudo column
ROWNUM pseudo column
About the built-in functions
SYSDATE
USER & UID
SESSIONTIMEZONE function
Using the DUAL table
Row limiting & top-N queries
FETCH FIRST x ROWS ONLY clause
OFFSET x ROWS clause
FETCH ... PERCENT clause
The WITH TIES option

Joining tables

About joins
INNER JOIN
Reflexive join
Non-key join
OUTER JOIN

Using the set operators

About the set operators
SQL set operator examples
UNION example
INTERSECT example
MINUS example
UNION ALL

Using sub-queries

Finding data with sub-queries
Standard sub-queries
Correlated sub-queries
The EXISTS operator

Aggregating data within groups

About summary groups
Find groups within the tables
Select data from the base tables
Select groups from the results

Use DDL to create & manage tables

CREATE TABLE statement
Column data types
NOT NULL
DEFAULT
DESCRIBE
ALTER TABLE statement
DROP TABLE statement
Table DDL using SQL Developer
ALTER USER statement
ALTER SESSION statement
NLS_LANGUAGE
NLS_DATE

Use DML to manipulate data

The INSERT statement
The DELETE statement
The UPDATE statement
About transactions
Transaction ROLLBACK
Transaction COMMIT
Transaction SAVEPOINT
The SET TRANSACTION statement
SET TRANSACTION READ ONLY statement rules