SQL

===
1. Data Definition Language(DDL):
=================================
> create
> alter
> alter - modify
> alter - add
> alter - rename
> alter - drop
> rename
> truncate
> drop

New commands:
=============
> recyclebin
> flashback
> purge

i)create:
==========
- to create a new object in database.
Ex: table,synonym,view,sequence,index,...etc

How to create a new table in oracle:

====================================
syntax:
=======
create table <tn>(<column name1> <datatype>[size],
<column name2> <datatype>[size],................);

Ex:
===
SQL> CREATE TABLE STUDENT(STID INT,SNAME CHAR(10),SFEE NUMBER(6,2));

How to view the structure of a table in oracle:

===============================================
syntax:
=======
desc <table name>; (describe)

Ex:
DESC STUDENT;

How to view all tables in oracle db:

====================================
syntax:
========
select * from tab;

ii) alter:
===========
- to modify the structure of a table.
- it again four types

a) alter - modify:
==================
- to change datatype from one datatype to another
datatypes and also change the size of datatype of
a particular column.

syntax:
=======
alter table <tn> modify <column name> <new datatype>[new size];

Ex:
SQL> ALTER TABLE STUDENT MODIFY SNAME VARCHAR2(20);

b) alter - add:
===============
- to add a new column to an existing table.

syntax:
=======
alter table <tn> add <new column name> <datatype>[size];

Ex:
SQL> ALTER TABLE STUDENT ADD SADDRESS VARCHAR2(10);

c) alter - rename:
==================
- to change a column name in a table.

syntax:
=======
alter table <tn> rename <column> <old column name>
to <new column name>;

Ex:
SQL> ALTER TABLE STUDENT RENAME COLUMN
SNAME TO STUDENTNAMES;

d) alter - drop:
================
- to drop / delete a column from a table.

syntax:
=======
alter table <tn> drop <column> <column name>;

Ex:
SQL> ALTER TABLE STUDENT DROP COLUMN SFEE;

iii) rename:
============
- to change a table name in database.

syntax:
=======
rename <old table name> to <new table name>;

Ex:
SQL> RENAME STUDENT TO SDETAILS;
SQL> RENAME SDETAILS TO STUDENT;

iv) truncate:
=============
 - to delete all rows from a table at a time.
- cannot delete a specific row from a table.
- it does not supports "where" clause condition.
- it was deleted rows but not columns.
- it is permanent rows deleted.

syntax:
=======
truncate table <table name>;

Ex:
SQL> TRUNCATE TABLE STUDENT;

V) drop:
========
- to drop / delete a table(rows & columns)
from database.

syntax:
=======
drop table <table name>;

Ex:
SQL> DROP TABLE STUDENT;

Note:
=====
- before oracle10g enterprise edition once we drop
a table then it was permanent but from oracle10g
enterprise edition once we drop a table it was
temporary.

New features in Oracle10g enterprise edition:

=============================================
i) recyclebin
ii) flashback
iii) purge

i) recyclebin:
==============
- it is pre-defined table.
- storing the information about dropped tables.

to view the structure of recyclebin:

====================================
syntax:
=======
desc recyclebin;

to view dropped tables information in recyclebin:

================================================
syntax:
========
select object_name,original_name from recyclebin;

OBJECT_NAME
ORIGINAL_NAME
------------------------------- -----------------------------------------
--------------------------------------------------
BIN$nUYdBPM7SBGOw48/662bmQ==$0 STUDENT

ii) flashback:
===============
- this command is used to restore dropped table
from recyclebin to oracle db.

syntax:
=======
flashback table <table name> to before drop;

Ex:
SQL> FLASHBACK TABLE STUDENT TO BEFORE DROP;

iii) purge:
===========
- this command is used to delete / drop a table
permanently.

to delete a specific table permanently from recyclebin:

=======================================================
syntax:
=======
purge table <table name>;

Ex:
purge table test1;

to delete all tables from recyclebin permanently:

=================================================
syntax:
=======
purge recyclebin;

to delete a table from database permanently:

============================================
syntax:
=======
drop table <table name> purge;

Ex:
SQL> DROP TABLE CUSTOMER PURGE;

ii) Data Manipulation Language(DML):

=====================================
i) insert
ii) update
iii) delete

New commands:
=============
i) insert all
ii) merge

i) insert:
==========
- to insert a new row data into a table.

syntax1:
=========
insert into <tn> values(v1,v2,v3,.....);

Ex:
SQL> INSERT INTO STUDENT VALUES(1,'SMITH',1500);

Note:
=====
no.of values and columns must be match.

syntax2:
========
insert into <tn>(<req.column names>)values(v1,v2...);

Ex:
SQL> INSERT INTO STUDENT(STID,SNAME,SFEE)VALUES(2,'ALLEN',2000);
SQL> INSERT INTO STUDENT(STID,SNAME)VALUES(3,'WARD');
SQL> INSERT INTO STUDENT(STID)VALUES(4);
SQL> INSERT INTO STUDENT(SNAME,SFEE,STID)VALUES('WARNER',4500,5);

How to insert multiple rows into a table:

=========================================
& : to insert values into columns dynamically.

syntax1:
=========
insert into <tn> values(&<column name1>,&<column name2>,.........);

Ex:
SQL> INSERT INTO STUDENT VALUES(&STID,'&SNAME',&SFEE);
Enter value for stid: 6
Enter value for sname: ADAMS
Enter value for sfee: 1100

SQL> / (to re-execute the last executed sql query in sqlplus editor)

Enter value for stid: 7

Enter value for sname: SCOTT
Enter value for sfee: 6300

syntax2:
========
insert into <tn>(req.column names)
values(&<col1>,&<col2>,........);

ex:
SQL> INSERT INTO STUDENT(STID,SFEE)VALUES(&STID,&SFEE);
Enter value for stid: 1
Enter value for sfee: 2500

SQL> /
.............................
.............................

How to insert NULLs into a table:

=================================
Ex:
SQL> INSERT INTO STUDENT VALUES(NULL,NULL,NULL);
SQL> INSERT INTO STUDENT(STID,SNAME,SFEE)VALUES(NULL,NULL,NULL);
2) UPDATE:
==========
- to update all rows data in a table at a time.
(or)
- to update a specific row data in a table by
using "where" condition.

syntax:
=======
update <tn> set <column name1>=<value1>,
<column name2>=<value2>,.....[where <condition>];

Ex:
UPDATE STUDENT SET SFEE=2300 WHERE STID=1;
UPDATE STUDENT SET SFEE=NULL WHERE STID=1;
UPDATE STUDENT SET SNAME='SMITH',SFEE=500 WHERE STID=2;
UPDATE STUDENT SET SNAME=NULL,SFEE=NULL WHERE STID=2;
UPDATE STUDENT SET SNAME='ADAMS';
UPDATE STUDENT SET SNAME=NULL;
UPDATE STUDENT SET SNAME='JONES' WHERE SNAME IS NULL;

Note:
=====
> when we assign NULL to a column ----> "=" operator
> when we compare NULL from a table ---> "is" operator

3) DELETE:
==========
- to delete a all rows data from a table at a time.
(or)
- to delete a specific row data from a table
by using "where" condition.

syntax:
=======
delete from <table name> [ where <condition> ];

Ex:
DELETE FROM STDUENT WHERE STID=1;
DELETE FROM STUDENT WHERE SFEE IS NULL;
DELETE FROM STDUENT;

delete VS truncate:
===================

delete truncate
====== ========
1. can delete a specific row. 1. not possible.
2. supports "where" condition. 2. not supports.
3. it is temporary deletion. 3. permanent deletion.
4. can restore deleted rows(data)4. not possible.
by using "rollback" command.
5. can delete rows in one by one 5. delete all rows at a time(like a page).
manner
6. execution speed is slow. 6. fast

3) DQL / DRL:
=============
i) select:
==========
- to retrive data from a table with complete rows
(or) a specific row by using "where" condition.

syntax:
=======
select * from <tn>;

Here, " * " ----- all columns.

Ex:
SQL> SELECT * FROM DEPT;
(OR)
SQL> SELECT DEPTNO,DNAME,LOC FROM DEPT;
SQL> SELECT DNAME,LOC FROM DEPT;
SQL> SELECT * FROM DEPT WHERE DEPTNO=30;
SQL> SELECT * FROM EMP WHERE COMM IS NULL;

CONCATENATION OPERATOR(||):
===========================
- TO ADD TWO STRING EXPRESSIONS.
SYNTAX:
=======
<STRING1>||<STRING2>

EX:
SQL> SELECT 'Mr.'||ENAME||' '||'IS WORKING AS A'||' '||JOB FROM EMP;

DISTINCT KEY:
=============
- to eliminate duplicate values from a column.

syntax:
=======
distinct <column name>

ex:
SQL> SELECT DISTINCT JOB FROM EMP;
SQL> SELECT DISTINCT DEPTNO FROM EMP ORDER BY DEPTNO;

Note:
======
- when we want to display a big data tables in
proper systematicall order in sqlplus editor then
we set the following two properties are

1) pagesize n:
==============
- to display no.of rows in a page.
- by default a page is having 14 rows.
- MAX LIMIT IS 50000 ROWS.
syntax:
=======
set pagesize n;

Here, " n " --- no.of rows

ex:
set pagesize 100;

2) lines n:
===========
- by default each line will occupied 80 bytes.
- is used to print no.of char's in no.of bytes.
- MAX LIMIT IS 32767 BYTES.

line1: .................(1 char=1byte)

100 rows:
each row no.of chars : 100 char's

...........................................(80b/c)
................(20c)

syntax:
=======
set lines n;

Here "n" ---- no.of bytes

Ex:
set lines 160;

How to create a new table from an existing table(old table):

===========================================================

syntax1: created a new table with copy of all rows & columns from the old table:
================================================================================
create table <new table name> as select * from <old table name>;

EX:
SQL> CREATE TABLE NEWDEPT AS SELECT * FROM DEPT;

syntax2: created a new table with only columns:

===============================================
create table <new table name> as select * from
<old table name> where <false condition>;

EX:
SQL> CREATE TABLE DEPTCOL AS SELECT * FROM DEPT
WHERE 1=2;

syntax3: created a new table with specific rows:

===============================================
create table <new table name> as select * from
<old table name> where <condition>;

ex:
SQL> CREATE TABLE EMPROWS AS SELECT * FROM EMP
WHERE DEPTNO=20;

syntax4: created a new table with specific COLUMNS:

===================================================
create table <new table name> as select * from
<old table name>;
EX:
SQL> CREATE TABLE EMPCOLS AS SELECT EMPNO,ENAME,
SAL FROM EMP;

How to copy data from one table to another table:

=================================================
syntax:
=======
insert into <destination table name> select * from <source table>;

RULE:
=====
1. no.of columns should be same in both tables.
2. order of columns and datatypes of column
must be match.

EX:
INSERT INTO DEMP SELECT * FROM SEMP;

i) insert all:
==============
- this command is used to insert rows into
multiple tables at a time but the rows must be
an existing table rows only.

insert : to insert new data into a single table.

insert all : to insert an existing table rows into multiple tables.

syntax:
=======
insert all into <tn1> values(<col1>,<col2>,...)
into <tn2> values(<col1>,<col2>,..............)
into <tn3> values(<col1>,<col2>,..............)
..............................................
..............................................
into <tn N> values(<col1>,<col2>,.............)
select * from <old table name>;

ex:
SQL> CREATE TABLE TEST1 AS SELECT * FROM DEPT WHERE 1=0;
SQL> CREATE TABLE TEST2 AS SELECT * FROM DEPT WHERE 1=0;
SQL> CREATE TABLE TEST3 AS SELECT * FROM DEPT WHERE 1=0;

SQL> INSERT ALL INTO TEST1 VALUES(DEPTNO,DNAME,LOC)

2 INTO TEST2 VALUES(DEPTNO,DNAME,LOC)
3 INTO TEST3 VALUES(DEPTNO,DNAME,LOC)
4 SELECT * FROM DEPT;

ALIAS NAMES:
============
- it is a temporary name creating for
columns & table.

i) column level alias name:

===========================
- created alias name for columns.

ii) table level alias name:

===========================
 - created alias name for table name.

syntax:
=======
select <column name1> <column alias name1>,
<column name2> <column alias name2>,.......
from <tn> <table alias name>;

ex:
SQL> SELECT DEPTNO X,DNAME Y,LOC Z FROM DEPT D;