Introduction SQL and PL/SQL

This document contains content table for the book Introduction to SQL and PL/SQL.

This document contains preface to the book Introduction to SQL and PL/SQL.

Database Management System. A Database Management is a collection of interrelated data and a set of programs to access this data. Collection of interrelated data is known as Database. Example - Mark sheet & Student Records. The main objective of a DBMS is to provide a convenient environment to retrieve and store database information. Database systems can support single user or multi-user environment. While, on one hand, DBMS allows only one person to access the database at any given time like MS-Access & Fox Pro, on the other, RDBMS allows multiple users simultaneous access to the database like Oracle.

Oracle Corporation was founded in 1977 in Redwood, California. They introduced the first Relational Database Management System based on the IBM System/R model and the first database management system utilizing IBM’s Structured Query Language (SQL) technology. Today, the Oracle DBMS is supported on over 80 different operating environments, ranging from IBM mainframes, DEC VAX minicomputers, UNIX-based minicomputers, Windows NT and several proprietary hardware-operating system platforms, and is clearly the world’s largest RDBMS vendor.

The ALTER command is used to modify the structure of a table and UPDATE command is used to modify the records or field of a table.  So using alter command we can add new columns, modify existing columns, and add or drop integrity constraints.

SQL *Plus provides some special inbuilt functions to perform operations using the DML commands. A function takes one or more arguments and returns a value. There are mainly two types of functions. 1. Single row functions (Scalar Functions) 2. Group Functions (Aggregate Functions)

Normalization theory was first described by E. F. Codd and is used in practice to aid in database design. The overall goals of the normalization process are to– • Arrange data so that it can be represented in tabular form where each row-column position contains a single data element (no repeating groups). • Ensure that data elements are associated with the correct keys, and thereby minimize data redundancy. Normalization involves a series of steps that change the structure of the various records that make up a database by placing the data into a series of different forms called first normal form, second normal form, and so on

Foreign key represents relationships between two or more tables. A foreign key is a column or a group of columns whose values are derived from the primary key or unique key of any other table. The table in which the foreign key is defined is called a foreign table. The table that defines the primary or unique key and  is referred by the foreign key is called master table. The master table can be referred in the foreign key definition by using the REFERENCES. If the name of the table is not specified, by default, Oracle refers the primary key in the master table.

PL/SQL stands for Procedural Language/SQL, superset of Structural Query Language (SQL). PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. PL/ SQL is a block-structured language. That is, the basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested sub-blocks. Each logical block corresponds to a problem or sub problem to be solved. PL/SQL supports the divide-and-conquer approach to problem solving called stepwise refinement

When we execute a SQL statement from PL/SQL, the Oracle RDBMS assigns a private work area for that statement. This work area contains information about the SQL statement and the set of data returned or affected by that statement. The PL/SQL cursor is a mechanism by which we can name that work area and manipulate the information within it.

An assertion is a predicate expressing a condition that we wish the database always to satisfy. Domain constraints and referential-integrity constraints are special forms of assertions. They are easily tested and apply to a wide range of database applications.

Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called procedures and functions. Generally, we use a procedure to perform an action and a function to compute a value.

This document contains index.