SQL


Basic SQL SELECT Statements

  • Basic SELECT Statement
  • Selecting All Columns
  • Selecting Specific Columns
  • Writing SQL Statements
  • Arithmetic Expressions
  • Using Arithmetic Operators
  • Operator Precedence
  • Using Parentheses
  • Defining a Null Value
  • Defining a Column Alias
  • Concatenation Operator
  • Using the Concatenation Operator
  • Duplicate Rows
  • Eliminating Duplicate Rows

Restricting and Sorting Data

  • Limiting Rows Using a Selection
  • Limiting the Rows Selected
  • Using the WHERE Clause
  • Character Strings and Dates
  • Comparison Conditions
  • Using Comparison Conditions
  • Other Comparison Conditions
  • Using the BETWEEN Condition
  • Using the IN Condition
  • Using the LIKE Condition
  • Using the NULL Conditions
  • Using the AND Operator
  • Using the OR Operator
  • Using the NOT Operator
  • Rules of Precedence
  • ORDER BY Clause
  • Sorting in Descending Order
  • Sorting by Multiple Columns

Single-Row Functions

  • SQL Functions
  • Number Functions
  • Using the ROUND Function
  • Using the TRUNC Function
  • Using the MOD Function
  • Arithmetic with Dates
  • Using Arithmetic Operators with Dates
  • Date Functions
  • Using Date Functions
  • Implicit Data Type Conversion
  • Explicit Data Type Conversion
  • Using the TO_CHAR Function with Dates
  • Using the TO_NUMBER and TO_DATE Functions
  • RR Date Format
  • NVL Function
  • Using the NVL ,NVL2,NULLIF,COALESCE Function
  • The CASE Expression
  • Using the CASE Expression
  • The DECODE Function

Displaying Data from Multiple Tables

  • Obtaining Data from Multiple Tables
  • Cartesian Products
  • Types of Joins
  • Joining Tables Using Oracle Syntax
  • What is an Equijoin?
  • Retrieving Records with Equijoins
  • Additional Search Conditions Using the AND Operator 4-10
  • Joining More than Two Tables
  • Non-Equijoins
  • Outer Joins
  • Outer Joins Syntax
  • Using Outer Joins
  • Self Joins
  • Joining a Table to Itself

5 Aggregating Data Using Group Functions

  • What Are Group Functions?
  • Types of Group Functions
  • Group Functions Syntax
  • Using the avg, sum, min, max, count, distinct Functions
  • The GROUP BY Clause Syntax
  • Using the GROUP BY Clause
  • Grouping by More Than One Column
  • Using the HAVING Clause

6 Subqueries

  • Subquery
  • The HAVING Clause with Subqueries
  • Multiple-Row Subqueries
  • Using the ANY Operator in Multiple-Row Subqueries
  • Using the ALL Operator in Multiple-Row Subqueries

7 Manipulating Data

  • Data Manipulation Language
  • The INSERT Statement Syntax
  • Inserting New Rows
  • Inserting Rows with Null Values
  • Inserting Special Values
  • Inserting Specific Date Values
  • Creating a Script
  • Copying Rows from Another Table
  • The UPDATE Statement Syntax
  • The DELETE Statement
  • Using a Subquery in an INSERT Statement
  • Using the WITH CHECK OPTION Keyword on DML Statements
  • The MERGE Statement
  • The MERGE Statement Syntax
  • Merging Rows
  • Database Transactions
  • COMMIT and ROLLBACK Statements
  • Controlling Transactions
  • Rolling Back Changes to a Marker
  • Implicit Transaction Processing
  • Data Before COMMIT or ROLLBACK

8 Creating and Managing Tables

  • Database Objects 9-3
  • Naming Rules
  • The CREATE TABLE Statement
  • Referencing Another User’s Tables
  • The DEFAULT Option
  • Creating Tables
  • Tables in the Oracle Database
  • Querying the Data Dictionary
  • Data Types
  • Creating a Table by Using a Subquery
  • Creating a Table by Using a Subquery
  • The ALTER TABLE Statement
  • Adding a Column
  • Modifying a Column
  • Dropping a Column
  • Dropping a Table
  • Changing the Name of an Object
  • Truncating a Table
  • Adding Comments to a Table

9 Including Constraints

  • What are Constraints?
  • Constraint Guidelines
  • Defining Constraints
  • The NOT NULL Constraint
  • The UNIQUE Constraint
  • The PRIMARY KEY Constraint
  • The FOREIGN KEY Constraint
  • FOREIGN KEY Constraint Keywords
  • The CHECK Constraint
  • Adding a Constraint Syntax
  • Adding a Constraint
  • Dropping a Constraint
  • Disabling Constraints
  • Enabling Constraints
  • Cascading Constraints

10 Creating Views

  • Database Objects
  • What is a View?
  • Simple Views and Complex Views
  • Creating a View
  • Retrieving Data from a View
  • Querying a View
  • Modifying a View
  • Creating a Complex View
  • Rules for Performing DML Operations on a View
  • Using the WITH CHECK OPTION Clause
  • Denying DML Operations
  • Removing a View
  • Inline Views
  • Top-N Analysis

11 Other Database Objects

  • What is a Sequence?
  • The CREATE SEQUENCE Statement Syntax
  • reating a Sequence
  • Confirming Sequences
  • NEXTVAL and CURRVAL Pseudocolumns
  • Using a Sequence
  • Modifying a Sequence
  • Guidelines for Modifying a Sequence
  • Removing a Sequence
  • What is an Index?
  • How Are Indexes Created?
  • Creating an Index
  • When to Create an Index
  • When Not to Create an Index
  • Function-Based Indexes
  • Removing an Index
  • Synonyms
  • Creating and Removing Synonyms

12 Controlling User Access

  • Objectives
  • Controlling User Access
  • Privileges
  • System Privileges
  • Creating Users