SQL -- Structured Query Language

SQL -- Structured Query Language.....................................

Data -- > collection of Numbers, Alphabets and special characters...

Collection of records is called a table…

Collection of tables is called a database...

Ex: student table holding 2 records….

std no	Name	Sex	age	add
1	Ashok	M	30	India
2	Ankul	M	30	US

SQL --- Structured Query Language…

Language used to query the retrieve data base in a structured manner..

Inorder to connect to data base we can use different tools like..

SQL plus, Toad, SQL developer…

Inorder to connect to any data base using above tool..

Please enter

User name: apps

Password: apps

Database: VIS.

DATA TYPES:

Data Type	Syntax	Explanation (if applicable)
NUMBER(p,s)	p-- precision           S -- scale	This data type is used to hold the numbers only
DATE	DATE	This data type is used to hold the date values
CHAR(n)	N –length	This data type is used to hold the character and number values
VARCHAR(n)	N-length	This data type is used to hold the alpha number character values
VARCHAR2(n)	N-length	This data type is used to hold the alpha number character values which holds up to max 4000 bytes
BOOLEAN	BOOLEAN	It is an plsql data type and return true or false
BLOB	BLOB	It is an data type which holds the binary large objects
CLOB	CLOB	It is an data type which holds the character large objects
LONG	LONG	This data type is used to hold the alpha number character values which holds up to 2 Gigabytes

Difference between char, varchar, varchar2…

Ex: column_name char(10)

If value ‘TEST’ is inserted in to the above column as the size of the data is 4 but data type size restricted to column is 10.

So, the value is occupied on this column is whole size of 10.

Ex: column_name varchar (10)

If value ‘TEST 10’ is inserted in to the above column as the size of the data is 7 including null spaces but data type size restricted to column is 10.

So, the value is occupied on this column is whole size of 7.

Ex: column_name varchar2(10)

If value ‘TEST 10’ is inserted in to the above column as the size of the data is 7 including null spaces but data type size restricted to column is 10.

So, the value is occupied on this column is whole size of 6 which excludes null spaces.

SQL is divided in to 5 different languages…

Sql is case insensitive and every sql statement should be terminated by semi colon(;).

DQL – Data Query Language

This language is used to query the data base tables..

We can query the data base table using SELECT command.

Ex: select * from emp;

        It shows the all records from the table.

Ex: select empno,ename,sal from emp;

        It shows the empno,ename,sal details from the emp table.

DDL – Data Definition Language.

This language acts only on structure of the table not on the data of the table.

1)CREATE – using create command we can create the table structure…

Syntax: CREATE TABLE TABLE_NAME

             (COLUMN_NAME1 DATATYPE(SIZE)

              , COLUMN_NAME2 DATATYPE(SIZE)

             , COLUMN_NAME3 DATATYPE(SIZE));

Ex: create table xx_student

 ( sno    number

 ,sname  varchar2(30)

 ,sex    char(1)

 ,add    varchar2(100));

How to view the structure of the table…

We can do that using Desc keyword..

Ex: DESC xx_student;

Rules to be followed when creating the table

 a) Always table name should start with character.

 Ex:   create table 1xx_student

( sno    number

 ,sname  varchar2(30)

 ,sex    char(1)

 ,add    varchar2(100));

    O/P:  When creating the above table it shows error as it is starting with  other then character.

b) Size of the table name should not exceed more then 30 characters.

        create table xx_student0123456789012345678900

( sno    number

 ,sname  varchar2(30)

 ,sex    char(1)

 ,add    varchar2(100));

   O/P: When creating the above table it shows error bcz the size of the table name is more then 30

            Characters.

c) Every table name in oracle database should be unique

d)Any table name which is being created should not hold the null spaces..

EX: create table xx_student stg

( sno    number

 ,sname  varchar2(30)

 ,sex    char(1)

 ,add    varchar2(100));

O/P: It shows error when creating the above table as the table name containing spaces.

2) ALTER – Using alter command we can add or drop or modify a column name of the table.

Syntax:

 To add a column       :  Alter table table_name add(column_name datatype(size));

 To drop a column     :  Alter table table_name drop(column_name);

 To modify a column :  Altert table table_name modify(column_name datatype(size));

Ex:       alter table xx_student add(Marks number);

alter table xx_student add(test number);

alter table xx_student drop(test);

alter table xx_student modify(add1 varchar(200));

3) DROP  -- Using Drop command we can drop the structure of the table.

 Syntax : Drop table table_name

Ex: Drop table xx_student;

4)Rename – Using this command we can rename the table name.

      

 Syntax : rename table_name to new_table_name;

Ex: rename xx_student to xx_student_stg

5)Truncate: This is used to delete all the data from the table but the structure remains same…

Syntax : Truncate table table_name

Ex: Truncate table xx_student_stg;

DML – Data Manipulation Language…

 This language is used to act on the data in the table but not on the structure of the table.

1)INSERT – Using this command we can insert the records in to the table…

Syntax : insert into  Table_name(col2,col2,clo3)

                                              Values(val1,val2,val3);

Ex1: 

1)Insert into xx_student_stg(sno,sname,sex,add1)

            values(4,'Sandeep','M','Atlanta');

2)Insert into xx_student_stg(sno,sname)

            values(5,'Sandeep');

3)Insert into xx_student_stg(sex,sno,sname)

            values('M',6,'Sandeep');

4)Insert into xx_student_stg

            values(7,'Sandeep','M','Atlanta');

5)Insert into xx_student_stg

            values(&sno,&sname,&sex,&add1);

2)UPDATE – This is used to update any record in the table

Syntax:   update table_name

                Set col1 =val1,col2 =val2

                Where col1 = some value – where condition is an optional…

Ex:         update xx_student_stg

set age = 30;  -- updates for all the records

update xx_student_stg

set age = 31,add1 = 'Atlanta,America'

where sname = 'Sandeep'; -- updates age and add1 only for the sandeep snames

update xx_student_stg

set age = 31,add1 = 'florida,America'

where sname = 'janosh';  -- updates age and add1 only for the janosh snames

                                                                                                    

3)Delete – Using this command we can delete the data from the table

syntax:   Delete from xx_student_stg

               where condition is optional…

Ex:   Delete from xx_student_stg

         Where sname = ‘Sandeep’; --deletes only rows whose ename is sandeep

       Delete from xx_student_stg;  -- deletes all the rows

Very important : Difference between drop and truncate and delete…

Delete	Drop	Truncate
Where condition can be used as it is dml command	Where condition cannot be used as it is ddl command	Where condition cannot be used as it is ddl command
Deletes the data from the table but we need to use commit inorder to delete the data permanently form the table	drops the table and data permanently from the data base.No need to use commit because it is an ddl command . It is autocommit.	deletes the data  permanently  from the table but structure remains same.No need to use commit because it is an ddl command . It is autocommit.