C4Swimmers Newsletter  

(Tutorial) STRUCTURED QUERY LANGUAGE (SQL)

Submitted by guru on Tue, 2008-11-25 07:07
in

Tutorial : STRUCTURED QUERY LANGUAGE (SQL)

Introduction:

SQL is an ANSI (American National Standards Institution) standard for  accessing database system. SQL is a simple and powerful language used to  create, access, and manipulate data and structure in the databases. SQL is  like plain English, easy to write and understands. SQL statements are used  to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL  Server, Oracle, Sybase, etc.

FEATURES OF THE SQL:

  • SQL stands for Structured Query Language.

  • SQL allows you to access a database.

  • SQL is an ANSI standard.

  • SQL can execute queries against the database.

  • SQL can retrieve data from the database.

  • SQL can insert new records in the database.

  • SQL can delete records from the database.

  • SQL can update the records in the database.

  • SQL is easy to learn.

DATA TYPES THAT A CELL CAN HOLD:

Data Types:-

  • CHAR (Size):- This data type is used to store character strings values of fixed length. The size in brackets determines the number of characters the cell can hold. The maximum number of characters this data type can hold is 255 characters

  • VARCHAR (size) / VARCHAR2 (size):- This data type is used to store variable length alphanumeric data. The maximum this data type can hold is 2000 characters

  • NUMBER (P,S):- The NUMBER data type is used to store numbers. Numbers of virtually any magnitude maybe stored up to 38 digits. The precision (P), determines the maximum length of the data, whereas the scale (S), determines the number of places to the right of the decimal. If scale is omitted then the default is zero.If the values are stored with their original precision up to the maximum of 38 digits.

  • DATE:- This data type is used to represent date and time.

 

Categories of SQL statements:-

SQL is a simple and powerful language used to create, access, and manipulate data and structure in the database.
SQL statements into various categories, which are:

  1. Data Definition Language

  2. Data Manipulation Language

  3. Transaction Control Statements

  4. Data Control Language

  • DATA RETRIEVAL:- SELECT

  • DATA MANIPULATION LANGUAGE (DML):- INSERT, UPDATE, DELETE, MERGE

  • DATA DEFINITION LANGUAGE (DDL):- CREATE, ALTER, DROP , RENAME, TRUNCATE

  • TRANSACTION CONTROL:- COMMIT, ROLLBACK ,SAVEPOINT

  • DATA CONTROL LANGUAGE (DCL):- GRANT,REVOKE

DATA DESCRIPTION LANGUAGE (DDL) STATEMENTS

DDL statements are used to define, alter or drop database objects. The following table gives an overview about usage of DDL statements in ORACLE. The commands used are:
CREATE:- Used to create table having rows and columns
ALTER:- Used to alter the structure of the database object
DELETE:- Used to delete the database objects.
RENAME:- Used to rename the database objects.

DATA MANIPULATION LANGUAGE (DML) STATEMENTS

DML statements enable users to query or manipulate data in existing objects. DML statements are normally the most commonly used commands. The commands used are:
DELETE:- It removes rows from the database objects
INSERT:- It adds new rows of data to database objects.
SELECT:- It retrieves data from one or more tables.
UPDATE:- It changes column values in existing rows of database objects.

DATA CONTROL LANGUAGE (DCL) STAEMENTS

The SQL sentences used to control the behavior of objects are called data control statements. The commands used are:
GRANT:- Grant statements provide access to various tables.
REVOKE:- Revoke statement is used to deny the grant.

TRANSACTION CONTROL LANGUAGE (TCL) STATEMENTS

TCL statements manage the change made by DML statements and group DML statements into transactions. The SQL statements used to control various transactions are called transaction control statement.

NEXT