SQL Tutorial-- Basics

SQL refers to "Structured Query Language"

·         It is an Structured Query Language because it follows ANSI, ISO standards.

·         It is an editor which helps the user to communicate with the Oracle Server.

·         User communicates with the help of queries. A query is a request / question / command submitted to oracle server to perform operations over DB.

·         SQL is developed by IBM, olden days it was called as “Sequel”.

·         It is the language common for all RDBMS software’s like Oracle, SQL Server, MySQL.

·         Apart from SQL, we also have QBE and QUEL which are used to communicate with the RDBMS server.

-          QBE refers to Query by Example. Used in MS-ACCESS.

-          QUEL refers to Query Language.

Depending on the operations, SQL is categorized into following sub languages:

1.       DML     ( Data Manipulation Language )

2.      DDL      ( Data Definition Language )

3.       DRL      ( Data Retrieval Language )

4.      TCL       ( Transaction Control Language )

5.      DCL       ( Data Control Language )

Data Definition Language (DDL):

-          DDL is a set of instructions to perform operations over Data Definition.

-          Data Definition is also called as Metadata. (data about data)

-          Commands in DDL are:

(i)                 CREATE            

(ii)               ALTER

(iii)             DROP

(iv)              TRUNCATE

(v)                RENAME

 CREATE :

Syntax:

Create table <table_name>

(

                                <column_name>             <data_type> (size),

                                <column_name>             <data_type> (size),

                                <column_name>             <data_type> (size),

                                --

                                --

);            

               

Ex:

                Create table Employee

(

                EmpNo                 Number(4),

                EmpName          varchar2(20),

                Salary                   Number(7,2),

                Desg                     varchar2(20),

                DeptNo                Number(2)

);

Scenarios:

                                 1.       Create a table from the existing table ?

                                 CREATE TABLE EMP_TARGET

                                 AS

                                SELECT * FROM EMP;

                                It creates a new table with name EMP_TARGET. It will create the table along with 

                                the data.

                               2.      Copy the table definition but not the data ?

                               CREATE TABLE EMP_TARGET

                               AS

                               SELECT * FROM EMP WHERE 1=2;

ALTER :

-          It is used to modify data definition of a table.

-          Using alter command :

1.       Add columns

2.      Drop columns

3.       Rename a column

4.      Modify a column

a.       Increasing or decreasing size

b.      Changing data type

c.       Changing Null to Not Null

d.      Changing Not Null to Null

1.       Adding columns using Alter ?

Syntax:

                ALTER TABLE <table_ name>

                                ADD ( column_name datatype(size));

Ex:

Alter Table Employee

                Add ( DOB Date, Gender CHAR(1));

2.      Dropping a column using Alter ?

Syntax:

                ALTER TABLE <table_ name>

                                DROP ( column_name datatype(size));

Ex:

Alter Table Employee

                DROP ( DOB, GENDER);

               

3.       Rename a column using Alter ?

Syntax:

                ALTER TABLE <table_ name>

                                RENAME COLUMN <old_name>  to  <new_name>;

Ex:

Alter Table Employee

                RENAME  column  HireDate   to   DateOfJoin;

4.      Modifying a column using Alter ?

a)      Increasing or decreasing the column size.

Syntax:

                ALTER TABLE <table_ name>

                                MODIFY ( column_name datatype(size)…);

Ex:

Alter Table Employee

                                MODIFY ( ename    varchar2(20));

b)      Changing datatype.

Ex:

Alter Table Employee

                                MODIFY ( COMM    varchar2(20));

c)      Changing from NULL to NOT NULL.

Ex:

Alter Table Employee

                                MODIFY ( ename    NOT NULL);

d)      Changing from NOT NULL to NULL.

Ex:

Alter Table Employee

                                MODIFY ( ename    NULL);

DROP :

Syntax:

                DROP TABLE <tableName>

Ex:

                DROP TABLE EMP;

-          Prior to 10g, we don’t have the option of restoring.

-          From 10g, we can even restore the Dropped tables. This is possible with the concept of “Flashback”.

-          In 10g, when the table is dropped, it is moved to recyclebin in Oracle server.

-          To view  the contents of the Recyclebin, execute the following commands:

Select * from recyclebin;

OR

Show recyclebin;

-          To restore the tables from Recyclebin, execute the following commands:

FLASHBACK table EMP to before DROP;

                This restores the table back along with the data.

-          To delete the table permanently,  execute the following commands:

PURGE   TABLE   EMP;

               

                This deletes the table permanently. Hence FLASHBACK is not possible in this case.

-          To empty Recycle bin, execute the following commands:

DROP   TABLE    EMP   PURGE;

SQL stands for Structured Query Language. and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Nonprocedural language, as compared to the procedural or third generation languages (3GLs) such as COBOL and C. SQL was developed by IBM in the 1970s.
The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Each time, ANSI added new features and incorporated new commands and capabilities into the language.
SQL is a simple, yet powerful, language used to create, access, and manipulate data and structure in the database.

SQL Statements categories: DDL - Data Definition Language.

DDL is used to define, alter, or drop database objects and their privileges. DDL statements will implicitly perform a commit.

DDL Statements:

Create	It is used to create objects(tables, views) in the database.
Alter	It is used to alter the structure of the database objects.
Drop	delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints ).
Truncate	remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark.)
Grant	assigning privileges

DML - Data Manipulation Language.

DML is used to access, create, modify or delete data in the structures of the database.

DML Statements:

Select	Select data from the database
Insert	It is used to insert data into a table
Update	It is used to update existing data within a table
Delete	It removes rows from the table.

DCL - Data Control Language

Following are the examples of Data control Statements.

DCL Statements:

Commit	It will end the current transaction making the changes permanent and visible to all users..
Savepoint	It will identify a point(named SAVEPOINT) in a transaction to which you can later roll back
Rollback	It will undo all the changes made by the current transaction.
Set- Transaction	It is used to define the properties of a transaction