SQL/ Oracle

Introduction TO DBMS

  • Creating Database
  • Creating Tables in the database
  • Writing SQL Statements
  • Creating DML Queries
  • Insert Queries with all fields and without all fields
  • Select Query
  • Using Where clause
  • Joining conditions using and & or , not operators
  • Using Alias Name

Restricting and Sorting Data

  • Using the BETWEEN Condition
  • Using the IN Condition
  • Using the LIKE Condition
  • Using the NULL Conditions
  • Rules of Precedence
  • ORDER BY Clause
  • Sorting in Descending Order
  • Sorting by Column Alias
  • Sorting by Multiple Columns

SQL Functions

  • SQL Aggregate Function like sum, Avg Count etc
  • Number Functions
  • Using the ROUND Function
  • Using the TRUNC Function
  • Using the MOD Function
  • Working with Dates
  • Date Functions
  • Using Date Functions

Displaying Data from Multiple Tables

  • Obtaining Data from Multiple Tables
  • Cartesian Products
  • Generating a Cartesian Product
  • Types of Joins
  • Joining Tables Using Oracle Syntax
  • What is an Equijoin?
  • Retrieving Records with Equijoins
  • Additional Search Conditions Using the AND Operator
  • Qualifying Ambiguous Column Names
  • Using Table Aliases
  • Joining More than Two Tables
  • Non-Equijoins
  • Retrieving Records with Non-Equijoins
  • Outer Joins Outer Joins Syntax
  • Using Outer Joins
  • Self Joins
  • Joining a Table to Itself
  • Joining Tables Using SQL: 1999 Syntax
  • Creating Cross Joins
  • Creating Natural Joins
  • Retrieving Records with Natural Joins
  • Creating Joins with the USING Clause
  • Retrieving Records with the USING Clause
  • Creating Joins with the ON Clause
  • Retrieving Records with the ON Clause
  • Creating Three-Way Joins with the ON Clause
  • INNER Versus OUTER Joins
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
  • Additional Conditions

Subqueries

  • Objectives
  • Using a Subquery to Solve a Problem
  • Subquery Syntax
  • Using a Subquery
  • Guidelines for Using Subqueries
  • Types of Subqueries
  • Single-Row Subqueries
  • Executing Single-Row Subqueries
  • Using Group Functions in a Subquery
  • The HAVING Clause with Subqueries
  • What is Wrong with this Statement?
  • Will this Statement Return Rows?
  • Multiple-Row Subqueries
  • Using the ANY Operator in Multiple-Row Subqueries
  • Using the ALL Operator in Multiple-Row Subqueries
  • Null Values in a Subquery

Manipulating Data

  • Data Manipulation Language
  • Adding a New Row to a Table
  • The INSERT Statement Syntax 8-5
  • Inserting New Rows
  • Inserting Rows with Null Values
  • Inserting Special Values
  • Inserting Specific Date Values
  • Creating a Script
  • Copying Rows from Another Table
  • Changing Data in a Table
  • The UPDATE Statement Syntax
  • Updating Rows in a Table
  • Updating Two Columns with a Subquery
  • Updating Rows Based on Another Table
  • Updating Rows: Integrity Constraint Error
  • Removing a Row from a Table
  • The DELETE Statement
  • Deleting Rows from a Table
  • Deleting Rows Based on Another Table
  • Deleting Rows: Integrity Constraint Error
  • Using a Subquery in an INSERT Statement
  • Using the WITH CHECK OPTION Keyword on DML Statements
  • Overview of the Explicit Default Feature
  • Using Explicit Default Values
  • The MERGE Statement
  • The MERGE Statement Syntax
  • Merging Rows
  • Database Transactions
  • Advantages of COMMIT and ROLLBACK Statements
  • Controlling Transactions
  • Rolling Back Changes to a Marker
  • Implicit Transaction Processing
  • State of the Data Before COMMIT or ROLLBACK
  • State of the Data after COMMIT
  • Committing Data
  • State of the Data After ROLLBACK
  • Statement-Level Rollback
  • Read Consistency
  • Implementation of Read Consistency
  • Locking
  • Implicit Locking
  • Read Consistency Example

Creating and Managing Tables

  • Database Objects
  • 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 9-10
  • Data Types
  • DateTime Data Types
  • TIMESTAMP WITH TIME ZONE Data Type
  • TIMESTAMP WITH LOCAL TIME Data Type
  • INTERVAL YEAR TO MONTH Data Type
  • INTERVAL DAY TO SECOND Data Type
  • Creating a Table by Using a Subquery Syntax
  • Creating a Table by Using a Subquery
  • The ALTER TABLE Statement
  • Adding a Column
  • Modifying a Column
  • Dropping a Column
  • The SET UNUSED Option
  • Dropping a Table
  • Changing the Name of an Object
  • Truncating a Table
  • Adding Comments to a Table