Scribd
Upload
32 views

Day 1 Introduction To SQL Constructs: Exercise - Investigate The EMP Database

This document outlines a 5-day training course on SQL and PL/SQL. Day 1 covers basic SQL statements, the Oracle EMP database, and using SQL*Plus. Day 2 focuses on joining tables, subqueries, and advanced SQL operators. Day 3 is about SQL tuning, optimization, and performance. Day 4 introduces PL/SQL including variables, blocks, exceptions, and control structures. Day 5 covers PL/SQL tables, dynamic SQL, triggers, stored procedures, functions, packages, and bulk operations. The course includes exercises for participants to apply the concepts learned each day.

Uploaded by

c1190222
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
32 views

Day 1 Introduction To SQL Constructs: Exercise - Investigate The EMP Database

This document outlines a 5-day training course on SQL and PL/SQL. Day 1 covers basic SQL statements, the Oracle EMP database, and using SQL*Plus. Day 2 focuses on joining tables, subqueries, and advanced SQL operators. Day 3 is about SQL tuning, optimization, and performance. Day 4 introduces PL/SQL including variables, blocks, exceptions, and control structures. Day 5 covers PL/SQL tables, dynamic SQL, triggers, stored procedures, functions, packages, and bulk operations. The course includes exercises for participants to apply the concepts learned each day.

Uploaded by

c1190222
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 8

Day 1

Introduction to SQL constructs

Review of Basic SQL statements


Select
Project
Join
Describing Oracle tables
Exercise investigate the EMP database

Restricting row returns


Where clause
Exercise write a sample query in the emp database
Complex Boolean logic in SQL
ROWID restrictions
Exercise write a sample query in the emp database

Using SQL*Plus

Creating basic reports


Exercise format a simple query in the emp database

Using the set commands


Adjusting line output
Setting pagesize and linesize
Echo

Feedback
Verify
termout
Exercise set the SQL*plus environment

Column wrapping
Exercise add column statements

Creating breaks and summaries


Exercise add breaks and summaries

Adding prompts to queries


Exercise parameterize a SQL*Plus script

DAY 2
Joining Oracle tables

Equi-join
Exercise write an equi-join

Outer join
Exercise write an outer join

Hiding joins by creating views


Exercise create a view of a join

Using IN, NOT IN, EXISTS and NOT EXISTS

Subqueries
Exercise write a subquery

Correlated subquery

Non-correlated subqueries

Advanced SQL operators

Between operator

IN and NOT In operators

Sub-queries

EXISTS clause

Using wildcards in queries (LIKE operator)

Aggregation in SQL

Count(*)

Sum

Avg

Min and max

Using the group by clause

SQL access methods

Review of Basic joining methods

Merge join

Hash Join

Nested Loop join

Advanced SQL operators

Between operator

DAY 3

SQL Tuning

Introduction to rule-based optimization

Introduction to cost-based optimization

Collecting table and index statistics

Using column histograms

Changing the default optimizer modes

Using TKPROF

Using SQL*Trace

SQL reusability within the library cache

Table high-water mark

Table striping and table partitions

Using EXPLAIN PLAN

Interpreting EXPLAIN PLAN Output

Using indexes to improve performance

Identifying full-table scans

Re-writing SQL queries

Using hints to improve SQL performance

Using parallel query to improve performance

Tuning sub-queries

DAY 4
PL/SQL Section
1. Basics of PL/SQL

PL/SQL architecture

PL/SQL and SQL*Plus

PL/SQL Basics
Variables
Constants
Datatypes
Error messages user_errors and show errors

PL/SQL wrapper utility

2. PL/SQL structures

Simple blocks

Control structures

PL/SQL records

Recognizing the Basic PL/SQL Block and Its Sections

Describing the Significance of Variables in PL/SQL

Distinguishing Between PL/SQL and Non-PL/SQL Variables

Declaring Variables and Constants

Executing a PL/SQL Block

3. Error checking exception handling

Defining exceptions

Using the when others clause

Ensuring complete error checking

Passing error messages to calling routine

4. Boolean logic in PL/SQL

Identifying the Uses and Types of Control Structures

Constructing an IF Statement

Constructing and Identifying Different Loop Statements

Controlling Block Flow Using Nested Loops and Labels

Using Logic Tables

If-then-else structure

Testing for numbers characters and Booleans

5. Cursors in PL/SQL

Cursor basics

Using a cursor for a multi-row SQL query

6. Iteration in PL/SQL

For loop

While loop

DAY 5
7. PL/SQL tables

Defining PL/SQL tables

Reasons to use PL/SQL tables

Populating a PL/SQL table

Retrieving from a PL/SQL table

8. Dynamic SQL in PL/SQL

Introduction to the dbms_sql package

Creating a dynamic SQL statement

9. Nested blocks in PL/SQL

Creating nested blocks

Understanding scope in nested blocks

10. Triggers in PL/SQL

Triggers and database events

Defining a trigger

Timing a trigger

Enabling and disabling a trigger

11. Stored procedures, functions and packages

Basics of stored procedures

Basics of functions

Basics of packages

Defining stored procedures & functions

Function and stored procedures prototypes

Passing arguments to functions and stored procedures

Recompiling functions and stored procedures

Pinning packages in the SGA with dbms_shared_pool.keep

Package forward declaration

Package dependency

Package overloading

Listing package information

12. Bulking in PL/SQL

Bulk queries

Bulk DML (forall statement)

Using cursor attributes

Analyzing impact of bilk operations

You might also like