PL/SQL Fundamentals Part 2

In the last session , we have seen a brief about relational database model and a table structure. Let us see the database maintenance language called SQL in this chapter.

SQL is English like data base query language initially developed by Donald D Chamberlin and Raymond Boyce to manipulate data in IBM’s database called System R. Foreseeing the scope of SQL, Oracle built their SQL based RDBMS and has done much modification to the structure of SQL. Later ANSI (American National Standards Institute) has approved SQL as the standard query language for Relational Databases.

The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sublanguage. The purpose of SQL is to provide an interface to a relational database such as Oracle Database, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC.

SQL provides statements for a variety of tasks, such as

1. Querying data
2. Inserting, updating, and deleting rows in a table
3. Creating, replacing, altering, and dropping objects
4. Controlling access to the database and its objects
5. Guaranteeing database consistency and integrity

Basic Elements of Oracle SQL

We will see the basic building blocks of SQL Statements. A detailed analysis of these will be included in the later sessions as and when the use of those is required. Those include

1. Datatypes
2. Literals
3. Format Models
4. Nulls
5. Comments
6. Database Objects
7. Schema Object Names

Datatypes

In the previous chapter, we have discussed about the datatypes. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another.

Literals

The term Literal refers to a fixed data value. For eg, from our table ‘Scott’, ‘Peter’ are character literals. All character literals should be enclosed in single quotation marks. This helps SQL to identify our data literal from Oracle Keywords and Reserved Words (We will talk about Reserved Words in a later stage). The values like 101, 102 (EMPID) are numeric literals which need not to be enclosed in single quotation marks.  Date related data is called Date Literals.

Format Models

A format model is a character literal that describes the format of datetime or numeric data stored in a character string. A format model does not change the internal representation of the value in the database. When you convert a character string into a date or number, a format model determines how Oracle Database interprets the string.  (More about format models will be discussed later in the data conversion lessons)

Nulls

If a column in a row has no value, then the column is said to be null, or to contain null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful. (Constraints will be discussed later).

Null values are not equal to zero and therefore do not use null to represent a value of zero.

Comments

Comments are used to denote the description of a particular SQL statement and are separated from its execution. It is not the part of an SQL Statement. These help developers to identify more about an object or a statement.

Database Objects

Oracle Database recognizes objects that are associated with a particular schema and objects that are not associated with a particular schema, as described in the sections that follow.

• Schema Objects

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:

• Clusters
• Constraints
• Database links
• Database triggers
• Dimensions
• External procedure libraries
• Index-organized tables
• Indexes
• Index types
• Java classes, Java resources, Java sources
• Materialized views
• Materialized view logs
• Object tables
• Object types
• Object views
• Operators
• Packages
• Sequences
• Stored functions, stored procedures
• Synonyms
• Tables
• Views

Schema Object Names and Qualifiers

Some schema objects are made up of parts that you can or must name, such as the columns in a table or view, index and table partitions and subpartitions, integrity constraints on a table, and objects that are stored within a package, including procedures and stored functions.

There are schema object naming conventions and rules which will be discussed later.

Next chapter we will begin with SQL Statements.