About SQL , SQL * PLUS in Details

Introduction to SQL

SQL stands for Structured Query Language.

     SQL provides an interface to RDBMS.

     SQL was developed by IBM in the 1970s for use in System R [System R is a database system built as a research project at IBM's San Jose Research Laboratory beginning in 1974. System R was a seminal project: it was the first implementation of SQL, which has since become the standard relational data query language. It was also the first system to demonstrate that a relational database management system could provide good transaction processing performance.], and is a de facto standard [A de facto standard is a custom, convention, product, or system that has achieved a dominant position by public acceptance or market forces], as well as an ISO and ANSI standard.

     When an SQL statement is entered, it is stored in a part of memory called the SQL buffer and remains there until a new SQL statement is entered.

     SQL also cover DML (Data Manipulation language) for insert, update and delete and DDL (Data Definition  Language) is a set of SQL commands used to create, modify and delete database structures but not data .

Characteristics of RDBMS

     RDBMS model represents all the data in database as simple row and column format.

     In RDBMS rows of table are called “TUPLES” and columns of table are called as “ATTRIBUTES”.

Features of SQL

     SQL can be used by a range of users, including those with little or no programming experience.

     It is a non procedural language.

     It reduces the amount of time required for creating and maintaining systems. It is an English-like language.

Rules for SQL

     SQL starts with a verb (i.e. a SQL action word). Example: SELECT statements. This verb may have additional adjectives. Example: FROM

     Each verb is followed by number of clauses. Example: FROM, WHERE, HAVING.

     A space separates clauses. Example: DROP TABLE EMP;

     A ‘;’ is used to end SQL statements

     Comments may be enclosed between /* and */ symbols and may be multi line. Single line comments may be prefixed with a – symbol.

     Character and date literals must be enclosed within single quotes.

Introduction to SQL *PLUS

     SQL *PLUS recognizes SQL statements and sends them to the Server.

     SQL *PLUS is the Oracle proprietary interface for executing SQL statements.

     SQL *PLUS does not allow manipulating of values in the database.

     SQL *PLUS is entered one line at a time, not stored the SQL buffer.

     SQL *PLUS does not require termination character executes commands immediately.

     SQL *PLUS is an Oracle tool that recognizes and submits SQL statements to the Oracle 9i Server for execution. It contains its own command language.

Features of SQL*PLUS

     SQL *PLUS accepts instant entry of statements.

     It accepts SQL input from files.        

     It provides a line editor for modifying SQL statements.

     It controls environmental settings.

     If formats query results into basic reports.

     It accesses local and remote databases.

SQL V/S SQL *PLUS

SQL	SQL *PLUS
SQL is a language for communicating with the Oracle Server to access data.	SQL *PLUS recognizes SQL statements and sends them to the Server.
SQL is based on American National Standards Institute (ANSI) standard.	SQL *PLUS is the Oracle proprietary interface for executing SQL statements.
SQL manipulates data and table definitions in the database.	SQL *PLUS does not allow manipulating of values in the database.
SQL is entered into the SQL buffer on one or more lines.	SQL *PLUS is entered one line at a time, not stored the SQL buffer.
SQL does not have a continuation character.	SQL *PLUS uses a dash (-) as a continuation character, if the command is longer than one line.
It cannot be abbreviated.	It can be abbreviated.
SQL uses a termination character to execute commands immediately.	SQL *PLUS does not require termination character executes commands immediately.
SQL uses functions to perform some formatting.	SQL *PLUS uses commands to format data.

Component of SQL

     DDL (Data definition Language) – It is a set of SQL commands used to create, modify and delete database structures but not data. These commands are normally not used by a general user, who should be accessing the database via an application. They are normally used by the DBA to a limited amount, a database designer or application developer. These statements are immediate i.e. they are not susceptible to ROLLBACK commands. It should also be noted that if several DML statements for example updated are executed then issuing any DDL command would COMMIT all the updated as every DDL command implicitly issues a COMMIT command to the database. Anybody using DDL must have the CREATE object privilege and a Tablespace area in which to create objects.

     DML (Data Manipulation Language)-It is the area of SQL that allows changing data within the database. 

     DCL (Data Control Language) – It is the component of SQL statement that control access to data and to the database. Occasionally DCL statements are grouped with DML statements.

     DQL (Data Query Language) – It is the component of SQL statement that allows getting data from the database. It includes the SELECT statement. This command is the heart of SQL. It allows getting the data out of the database and perform operations on it.

O DDL : Data Definition Language

Examples:

     CREATE : To create objects in the database.

     ALTER : Alter the structure of the database.

     DROP : Delete objects from the database.

     TRUNCATE : Remove all records from a table, including   

                               all spaces allocated for the records removed.

     COMMENT : Add comments to the data dictionary.

     GRANT : Gives user’s access privileges to database.

     REVOKE : Withdraw access privileges given with the             

O DML: Data Manipulation Language

Examples:

     INSERT Insert data into a table

     UPDATE Updates existing data within a table

     DELETE Deletes all records from a table, the space for the records remain

O DCL: Data Control Language

Example:

     COMMIT Save work done

     SAVEPOINT Identify a point in a transaction to which you can later roll back

     ROLLBACK Restore database to original since the last COMMIT

     SET TRANSACTION Change transaction options like what rollback segment to use

     GRANT / REVOKE Grant or take back permissions to or form the oracle users

O DQL: Data Query Language

Examples:

     SELECT Retrieve data for the a database

Relationship

There are four types of relationship.

1.One to One  (1:1)

2. One to Many (1:M)

3. Many to One (M:1)

4. Many to Many (M:M)

One to One (1:1)

     A one to one relationship is established by joining tables. It means a record in a table has only one record in another table.

     One manager manage only one branch.

One to Many (1:M)

     One to many relationship means when two tables are related through a common field and one record of a table is related to one more record of another table.

     One branch holds many accounts.

Many to One (M:1)

     In many to one relationship there are records of a table is related to one record of another table.

     Many accounts are held by only one branch.