After completing this week’s section you will learn about the basic concepts on SQL, SQL *Plus and PL/SQL. Please use discussion board if you have any questions, we will help you with more details explanation. If you do not understand and practice in the order we provide it will be difficult for you to learn Oracle.

Section 1: Using SQL *Plus to communicate with Oracle Databases

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Server or Client installation. It has a command-line user interface, and more recently, a web-based user interface called iSQL*Plus.
On Windows platforms, context menu options accessed through the right mouse button enable local files to be sent to the iSQL*Plus user interface.

SQL*Plus has its own commands and environment, and it provides access to the Oracle RDBMS. It allows you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:

1. enter SQL*Plus commands to configure the SQL*Plus environment
2. enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks
3. format, perform calculations on, store, and print from query results
4. interact with an end user
5. startup and shutdown a database
6. connect to a database
7. define variables
8. capture errors
9. list column definitions for any table
10. perform database administration

This chapter introduces you to SQL*Plus, covering the following topics:

• What is SQL*Plus
• SQL*Plus Installation
• Who Can Use SQL*Plus
• How Can I Learn SQL*Plus
• How to Use the SQL*Plus Guide
• Oracle9i Sample Tables and SQL*Plus

Introducing SQL*Plus

This chapter provides introductory information to help you get started withSQL*Plus. Specific topics discussed are:

• Overview
• Basic Concepts
• Obtaining Online Help

Using SQL*Plus

This chapter explains how to start and use SQL*Plus from both the command-line interface and the graphical user interface, and describes the graphical user interface menu options. It also describes how to use and configure the iSQL*Plus Extension for Windows.

Specific topics discussed are:

• Using the Command-line Interface
• Using the Graphical User Interface
• Exiting SQL*Plus
• iSQL*Plus Extension for Windows

Introduction to SQL - Types of SQL Commands, Writing Simple SELECT Statements

1. Simple SELECT statements
2. Logical Operators and Aggregation
3. JOINs and VIEWs
4. Sub Queries and Schema

Download simple tutorials which helps you understand the topics we are trying to cover in this section.

Simple SELECT statements Download Tutorial

You can review the following sections in detail after you complete the tutorial from the download link above.

• Database Models
• Relational Databases
• Relational Data Structure
• Domain and Integrity Constraints
• Structure of a Table
  • CAR
  • DRIVER
  • Relationship between CAR and DRIVER
  • Example Data
  • Columns or Attributes
• Primary Keys
• SQL Basics
• Simple SELECT
• Comments
• SELECT filters
• Comparisons
  • Dates
  • BETWEEN
  • NULL
  • LIKE
  • HAVING

PL/SQL

PL/SQL is structured into blocks and can use conditional statements, loops and branches to control program flow. Variables can be scoped so that they are only visible within the block where they are defined.

PL/SQL is a database-orientated programming language that extends Oracle SQL with procedural capabilities. We will review in this lab the fundamental features of the language and learn how to integrate it with SQL to help solving database problems.

PL/SQL programs are organized in functions, procedures and packages. There is a limited support for object-oriented programming. PL/SQL is based on the ADA programming language.

PL/SQL blocks come in three types,

1. Anonymous Procedure: An anonymous procedure is an unnamed procedure, it can't be called. It is placed where it is to be run, normally attached to a database trigger or application event.
2. Named Procedure: A named procedure may be called, it may accept inbound parameters but won't explicitly return any value.
3. Named Function: A named function may also be called, it may accept inbound parameters and will always return a value.

Next Page: Introduction to SQL