SQL Introduction

2 days

Audience

Prerequisites

Developers or end-users who need to know how to write SQL queries for Oracle databases.

·           Experience with Windows operating system.

·           Knowledge of Relational Database technology. No SQL experience required.

Description

This course will introduce the student to Relational Database design techniques, including Entity Relationship Modeling, Normalization, and Data Integrity concepts. Students will learn how to use Oracle’s SQL*Plus to create database tables and execute queries through the use of SQL’s Data Definition Language (DDL) and Data Manipulation Language (DML).

There are several hands-on labs in this course that will provide the student with the opportunity to practice and perfect the knowledge of Oracle that they will gain during this course.

Outline/Table of Contents

Database Management Systems - An Overview

·           Data Models – The Evolution of Data Storage    

·           More on the Relational Model

·           Structured Query Language

·           Using SQL*Plus        

·           Data Management    

Creating Database Tables using Data Definition Language (DDL)

·           Introduction               

·           DDL : CREATE TABLE           

·           Most Commonly Used Data Types in Oracle       

·           Use of NULL              

·           Adding Constraints  

·           DDL : DROP TABLE

·      EXERCISE – Create Table Definitions             

·           DML : INSERT, UPDATE, DELETE       

·      EXERCISE – Insert Rows  

Basic Data Manipulation (DML)

·           Introduction

·           Basic SELECT Statement

·           WHERE Clause

·           WHERE Clause Comparison Operators

·           Specific Column Selection

·           Derived Columns

·      EXERCISE - Limiting Rows and Columns

·           ORDER BY Clause

·           Expanding on the WHERE Clause

·      Boolean Operators

·      BETWEEN Operator           

·      IN Operator          

·      LIKE Pattern-Matching Operator     

·      EXERCISE - Expanding on the WHERE Clause

·           Negative Logic in the WHERE Clause  

·           NULLS in the WHERE Clause

·           Types of Built-In Functions

·           Aggregate Functions

·         SUM

·         AVG

·      MIN and MAX

·      Impact of NULLs

·      Summary – Impact of NULLS

·      EXERCISE - Aggregate Functions

·           GROUP BY Clause

·           HAVING Clause

·           EXERCISE – GROUP BY and HAVING

·           Other Functions

·      DATE/TIME Functions

·      Working with Dates

·      Transformation Functions

·      Numeric Functions

·      String Manipulation Functions

·      DECODE

·      EXERCISE – Other Functions           

·                 Summary               

Advanced Data Manipulation (DML)

·           Table Joins 

·           Inner Joins 

·      EXERCISE – Inner Joins    

·           Outer Joins 

·           Subqueries 

·      Single Values From Subqueries        

·      Lists of Values From Subqueries      

·      Subqueries That Return Multiple Columns    

·      EXERCISE - Subqueries     

·           Correlated Subqueries              

·      Using Correlated Subqueries to Check Data Integrity            

·           UNION        

·      Guidelines for UNION        

·      EXERCISE - UNIONS         

·           Using SELECT With INSERT