SQL INTRODUCTION

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.
Data Definition Language (DDL) : It is used to define new database objects

Create   Alter   Drop   Truncate Rename
MERGE Statement

Insert all

CREATE TABLE SYNTAX

Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);

Ex:SQL> create table student (no number (2), name varchar (10), marks number (3));

Alter

This can be used to add or remove columns and to modify the precision of the data type.

a) ADDING COLUMN

Syntax: alter table <table_name> add <col datatype>;

Ex: SQL> alter table student add sdob date;

b) REMOVING COLUMN

Syntax: alter table <table_name> drop <col datatype>;

Ex: SQL> alter table student drop column sdob;

c) INCREASING OR DECREASING PRECISION OF A COLUMN

Syntax: alter table <table_name> modify <col datatype>;

Ex: SQL> alter table student modify marks number(5);

 * To decrease precision the column should be empty.

d) MAKING COLUMN UNUSED

Syntax:

alter table <table_name> set unused column <col>;

Ex:

SQL> alter table student set unused column marks;

Even though the column is unused still it will occupy memory.

d) DROPPING UNUSED COLUMNS

Syntax:

alter table <table_name> drop unused columns;

Ex:

SQL> alter table student drop unused columns;

* You cannot drop individual unused columns of a table.

e) RENAMING COLUMN

Syntax:

alter table <table_name> rename column <old_col_name> to <new_col_name>;

Ex:

SQL> alter table student rename column marks to smarks;

DROP

This will be used to drop the database object;

Syntax:

Drop table <table_name>;

Ex:

SQL> drop table student;

TRUNCATE

This can be used to delete the entire table data permanently.

Syntax:

truncate table <table_name>;

Ex:

SQL> truncate table student;

RENAME

This will be used to rename the database object;

Syntax:

rename <old_table_name> to <new_table_name>;

Ex:

SQL> rename student to stud;

Data Manipulation Language (DML)

INSERT

This will be used to insert the records into table.

We have two methods to insert.

1      By value method

2      By address method

a) USING VALUE METHOD

Syntax:

insert into <table_name) values (value1, value2, value3 …. Valuen);

Ex:

            SQL> insert into student values (1, ’sudha’, 100);

            SQL> insert into student values (2, ’saketh’, 200);

To insert a new record again you have to type entire insert command, if there are lot of  records this will be difficult.

This will be avoided by using address method.

b) USING ADDRESS METHOD

 Syntax:

          insert into <table_name) values (&col1, &col2, &col3 …. &coln);

This will prompt you for the values but for every insert you have to use forward slash.

 Ex:

SQL> insert into student values (&no, '&name', &marks);

Enter value for no: 1

Enter value for name: Jagan

Enter value for marks: 300

old   1: insert into student values(&no, '&name', &marks)

new   1: insert into student values(1, 'Jagan', 300)

SQL> /

Enter value for no: 2

Enter value for name: Naren

Enter value for marks: 400

old   1: insert into student values(&no, '&name', &marks)

new   1: insert into student values(2, 'Naren', 400)

c) INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE METHOD

 Syntax:

 insert into <table_name)(col1, col2, col3 … Coln) values (value1, value2, value3 ….Valuen);

     Ex:

            SQL> insert into student (no, name) values (3, ’Ramesh’);

            SQL> insert into student (no, name) values (4, ’Madhu’);

d) INSERTING DATA INTO SPECIFIED COLUMNS USING ADDRESS METHOD

 Syntax: insert into <table_name)(col1, col2, col3 … coln) values (&col1, &col2 ….&coln);          This will prompt you for the values but for every insert you have to use forward slash.

     Ex:

            SQL> insert into student (no, name) values (&no, '&name');

Enter value for no: 5

Enter value for name: Visu

old   1:  insert into student (no, name) values(&no, '&name')

new   1:  insert into student (no, name) values(5, 'Visu')

SQL> /

Enter value for no: 6

Enter value for name: Rattu

old   1:  insert into student (no, name) values(&no, '&name')

new   1:  insert into student (no, name) values(6, 'Rattu')

UPDATE

This can be used to modify the table data.

Syntax:

Update <table_name> set <col1> = value1, <col2> = value2 where <condition>;

Ex:

SQL> update student set marks = 500;

If you are not specifying any condition this will update entire table.

SQL> update student set marks = 500 where no = 2;

SQL> update student set marks = 500, name = 'Venu' where no = 1;

DELETE

This can be used to delete the table data temporarily.

Syntax:

Delete <table_name> where <condition>;

Ex:

SQL> delete student;

If you are not specifying any condition this will delete entire table.

SQL> delete student where no = 2;

Data Retrieval Language (DRL)

SELECTING DATA

Syntax:

    Select * from <table_name>;              -- here * indicates all columns

Or

    Select col1, col2, … coln from <table_name>;

Ex:

    SQL> select * from student;

           NO NAME            MARKS

          ---  ------             --------

           1   Sudha             100

           2   Saketh             200

          1   Jagan              300

          2   Naren              400

         3   Ramesh

         4   Madhu

         5   Visu

         6   Rattu

    SQL> select no, name, marks from student;

        NO NAME            MARKS

        ---  ------             --------

         1   Sudha             100

         2   Saketh            200

         1   Jagan             300

         2   Naren             400

         3   Ramesh

         4   Madhu

         5   Visu

         6   Rattu

SQL> select no, name from student;

        NO NAME

        ---  -------

         1   Sudha

         2   Saketh

         1   Jagan

         2   Naren

         3   Ramesh

         4   Madhu

         5   Visu

         6   Rattu

Transaction Control Language (TCL)

COMMIT

This will be used to save the work.

Commit is of two types.

1     Implicit

2     Explicit

a) IMPLICIT

This will be issued by oracle internally in two situations.

1      When any DDL operation is performed.

2      When you are exiting from SQL * PLUS.

b) EXPLICIT

This will be issued by the user.

Syntax:

                Commit or commit work;

              * Whenever you committed then the transaction was completed.

USING ROLLBACK

This will undo the operation.

This will be applied in two methods.

1      Upto previous commit

2      Upto previous rollback

Syntax:

Roll or roll work;

Or

Rollback or rollback work;

* While process is going on, if suddenly power goes then oracle will rollback the transaction.

USING SAVEPOINT

You can use savepoints to rollback portions of your current set of transactions.

Syntax:

     Savepoint <savepoint_name>;

Ex:

     SQL> savepoint s1;

     SQL> insert into student values(1, ‘a’, 100);

     SQL> savepoint s2;

     SQL> insert into student values(2, ‘b’, 200);

     SQL> savepoint s3;

      SQL> insert into student values(3, ‘c’, 300);

     SQL> savepoint s4;

      SQL> insert into student values(4, ‘d’, 400);

     Before rollback

     SQL> select * from student;

        NO NAME      MARKS

        ---  -------     ----------

         1          a         100

         2          b         200

         3          c         300

         4          d         400

     SQL> rollback to savepoint s3;

                                    Or

     SQL> rollback to s3;

 This will rollback last two records.

     SQL> select * from student;

        NO NAME      MARKS

        ---  -------     ----------

         1          a         100

         2          b         200

Data Control Language (DCL)

DCL commands are used to granting and revoking the permissions.

GRANT

This is used to grant the privileges to other users.

Syntax:

  Grant <privileges> on <object_name> to <user_name> [with grant option];

Ex:

     SQL> grant select on student to sudha;              -- you can give individual privilege

      SQL> grant select, insert on student to sudha;    -- you can give set of privileges

     SQL> grant all on student to sudha;                    -- you can give all privileges

The sudha user has to use dot method to access the object.

     SQL> select * from saketh.student;

 The sudha user can not grant permission on student table to other users. To get this

type of option use the following.

     SQL> grant all on student to sudha with grant option;                             

     Now sudha user also grant permissions on student table.

REVOKE

This is used to revoke the privileges from the users to which you granted the privileges.

Syntax:

     Revoke <privileges> on <object_name> from <user_name>;

Ex:

SQL> revoke select on student form sudha;            -- you can revoke individual privilege

SQL> revoke select, insert on student from sudha;   -- you can revoke set of privileges

SQL> revoke all on student from sudha;                   -- you can revoke all privileges