Net lo Java: 2013

demobld.sql

--
-- Copyright (c) Oracle Corporation 1988, 1999. All Rights Reserved.
--
-- NAME
--    demobld.sql
--
-- DESCRIPTION
--   This script creates the SQL*Plus demonstration tables in the
--   current schema. It should be STARTed by each user wishing to
--   access the tables. To remove the tables use the demodrop.sql
--   script.
--
-- USAGE
--       SQL> START demobld.sql
--
--

SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF

DROP TABLE BONUS;

CREATE TABLE BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM NUMBER);

DROP TABLE EMP;

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH', 'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN', 'SALESMAN', 7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN', 7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES', 'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN', 7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE', 'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK', 'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT', 'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN', 7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, NULL, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS', 'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES', 'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

DROP TABLE DEPT;

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

DROP TABLE SALGRADE;

CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;

SET TERMOUT ON

PROMPT Demonstration table build is complete.

**Components of SQL**
DQL/DRL
Select
DML
Insert
Update
Delete
DDL
Create
Alter
Drop
Truncate
Rename
DCL
Grant
Revoke
TCL
Commit
Rollback
Savepoint

Data Retrieval/Query Language(DRL/DQL)

Data Retrieval Language:

Data Retrieval Language(DRL) or Data Query Language(DQL) is the ability to search for and locate ingormation that has been stored. Data Retrival is a sub language of SQL which is used to query or retrieve the data from the existing tables with in the database. This language contains One command.

i) SELECT

i) SELECT:

This command is used to retrieve the data from the existing table. Using this command we can retrieve all records and also we can retrieve some specific records in the table(Using where clause).

Syntax:

select [<column name>,....] [*] from <table name>

where [Condition];

Note: Here * represents all columns.

EX:

SQL>select * From emp;

SQL>select empno,ename,job from emp;

SQL>select unique job from emp;

SQL>select distinct job from emp

where deptno=30;

WHERE Clause:

This clause is used to check the condition, based on the condition.
We can retrieve some specific records.
We can delete some specific records.
We can modify some specific records.
In SQL data available in the table is case sensitive.

EX:

SQL>select empno,ename,job,deptno from emp

where job='SALESMAN';

SQL>select * from emp

where job='SALESMAN' and

deptno=10;

SQL>select * from emp

where sal>3000;

SQL>select * from emp

where sal>1500 and sal<5000;

SQL>select empno,ename from emp

where deptno in(10,20);

SELECT

SELECT:

This command is used to retrieve the data from the existing table. Using this command we can retrieve all records and also we can retrieve some specific records in the table(Using where clause).

Syntax:

select [<column name>,....] [*] from <table name>

where [Condition];

Note: Here * represents all columns.

EX:

SQL>select * From emp;

SQL>select empno,ename,job from emp;

SQL>select unique job from emp;

SQL>select distinct job from emp

where deptno=30;

WHERE Clause:

This clause is used to check the condition, based on the condition.
We can retrieve some specific records.
We can delete some specific records.
We can modify some specific records.
In SQL data available in the table is case sensitive.

EX:

SQL>select empno,ename,job,deptno from emp

where job='SALESMAN';

SQL>select * from emp

where job='SALESMAN' and

deptno=10;

SQL>select * from emp

where sal>3000;

SQL>select * from emp

where sal>1500 and sal<5000;

SQL>select empno,ename from emp

where deptno in(10,20);

Data Manipulation Language(DML)

Data Manipulation Language:

Data manipulation language(DML) is a core part of SQL. When you want to add, update, or delete data in the database, you execute a DML statement.
A collection of DML statements that form a logical unit of work is called a transaction.
The DML in SQL which contain four commands those are

i) INSERT

ii) UPDATE

iii) DELETE

i) INSERT:

This command is used to insert the records in the table using this command we can insert the data into the table in two methods.

i) Explicit method.

ii) Implicit method.

i)Explicit Method:

In this method user need to enter all the values into the columns without left any column data.

Syntax:

insert into <table name> values(value1,value2,....,valuen);

EX:

SQL>insert into emp values(1001,'SCOTT',10);

SQL>insert into employee('1002,'netlojava@netlojava.com');

Note:

We can use && in front of any column. By placing like this the use is – “It takes particular column values as default for remaining all values.

Syntax to insert the records into the table using i nsertion operation:

insert into <table name> values(&column1,&column2,.....,&column_n);

EX:

SQL>insert into emp values(&empno,'&ename','&job',&sal);

SQL>insert into employee values(&empno,'&email',&mobileno);

Implicit method:

This method we can enter the values at required columns in the table.

Syntax:

insert into <table name>(column1,column2,.....,column_n);

EX:

SQL>insert into emp(empno,ename) values(1003,'MILLER');

SQL>insert into employee (email,mobileno) values ('netlojava@netlojava' ,9876543210);

Syntax to insert record using & Symbol:

insert into <table name>(column1,column2,.....,column_n) values (&column1,&column2,.....,&column_n);

EX:

SQL>insert into emp(empno,job) values(&empno,'&job') values (1004,'MANAGER');

SQL>insert into employe(ename,sal,deptno) values('&ename',&sal,&deptno);

ii) UPDATE:

Update is used to modify the existing values in Table or in the base table of View.

The Table must be in the Own schema.

UPDATE object privilege should be available.

Syntax:

update <table name> set <column name>=<value>[,<column name2>,...]

[where condition];

EX:

SQL>update emp set deptno=20 where empno='1002';

SQL>update employee set sal=5000 where empno='1003';

SQL>update emp set sal=3000,comm=NULL

where job='CLERK';

Updating Two Columns with a Sub Query:

SQL>update emp set job=(select job from emp

where empno=1004),

sal=(select job from emp

where empno=1005),

where empno=1002;

SQL>update emp set deptno=(select deptno from emp

where empno=1001)

where job=(select job from emp where empno=1002);

SQL>update emp set (job,deptno)=(select job,deptno

from dept where empno=1003)

where empno=1002;

Updating Rows based on Another Table:

use sub queries in UPDATE statements to update rows in a table based on values from another table.

SQL>update emp set deptno=(select deptno from dept

where dname='ACCOUNTING')

where deptno=(select deptno from dept

where dname=-'SALES');

Appling default Values:

It is used to UPDATE a value in a column with DEFAULT value set in the constraints.

SQL>update emp set sal=DEFAULT where ename='FORD';

SQL>update emp set hiredate=DEFAULT where empno=1003;

Returning Clause:

The returning clause is introduced from 8.0.

It is used to return a value from a aggregate function.

This feature is introduced in grid technology.

The clause can be specified for table and materialized views and for views with a single base table.

Restrictions:

Each express must be a simple expression or a single set aggregate function.

It cannot be specified for multiple table insert.

It cannot be specified upon a view upon which a instead of trigger is defined.

SQL>VAR sumsal number;

SQL>update emp set sal=sal*0.25

where deptno=10

returning sum(sal) into :sumsal;

SQL>var bsal number;

SQL>var bname varchar;

SQL>update emp set sal=sal+sal*0.25

where empno=1004

returning ename,sal into :bname,:bsal;

SQL>print :bname :bsal;

iii) DELETE:

This command is used to delete the records from the existing table. Using this command we can delete all the records and also we can delete specific records

from the table.

To DELETE rows from a table, the table must be in the USERS SCHEMA.

DELETE any table system privilege allows to DELETE rows from any TABLE or PARTITION TABLE or from the base table of any VIEW.

Syntax:

delete [FROM] <table name>

[WHERE Condition];

Ex:

SQL>delete from emp;

SQL>delete from emp empno;

SQL>delete from emp where empno in(7566,7900);

SQL>delete from emp where ename='SCOTT';

SQL>delete from emp where job='SALESMAN' and

hiredate<Sysdate;

SQL>var vsal number;

SQL>delete from emp where job='SALESMAN' and

hiredate<Sysdate

returning SUM(sal) into :vsal;

SQL>print :vsal;

SQL>delete from emp where ename='SCOTT' or ename='KING';

INSERT

INSERT:

This command is used to insert the records in the table using this command we can insert the data into the table in two methods.

i) Explicit method.

ii) Implicit method.

i)Explicit Method:

In this method user need to enter all the values into the columns without left any column data.

Syntax:

insert into <table name> values(value1,value2,....,valuen);

EX:

SQL>insert into emp values(1001,'SCOTT',10);

SQL>insert into employee('1002,'netlojava@netlojava.com');

Note:

We can use && in front of any column. By placing like this the use is – “It takes particular column values as default for remaining all values.

Syntax to insert the records into the table using i nsertion operation:

insert into <table name> values(&column1,&column2,.....,&column_n);

EX:

SQL>insert into emp values(&empno,'&ename','&job',&sal);

SQL>insert into employee values(&empno,'&email',&mobileno);

Implicit method:

This method we can enter the values at required columns in the table.

Syntax:

insert into <table name>(column1,column2,.....,column_n);

EX:

SQL>insert into emp(empno,ename) values(1003,'MILLER');

SQL>insert into employee (email,mobileno) values ('netlojava@netlojava' ,9876543210);

Syntax to insert record using & Symbol:

insert into <table name>(column1,column2,.....,column_n) values (&column1,&column2,.....,&column_n);

EX:

SQL>insert into emp(empno,job) values(&empno,'&job') values (1004,'MANAGER');

SQL>insert into employe(ename,sal,deptno) values('&ename',&sal,&deptno);

UPDATE

UPDATE:

Update is used to modify the existing values in Table or in the base table of View.

The Table must be in the Own schema.

UPDATE object privilege should be available.

Syntax:

update <table name> set <column name>=<value>[,<column name2>,...]

[where condition];

EX:

SQL>update emp set deptno=20 where empno='1002';

SQL>update employee set sal=5000 where empno='1003';

SQL>update emp set sal=3000,comm=NULL

where job='CLERK';

Updating Two Columns with a Sub Query:

SQL>update emp set job=(select job from emp

where empno=1004),

sal=(select job from emp

where empno=1005),

where empno=1002;

SQL>update emp set deptno=(select deptno from emp

where empno=1001)

where job=(select job from emp where empno=1002);

SQL>update emp set (job,deptno)=(select job,deptno

from dept where empno=1003)

where empno=1002;

Updating Rows based on Another Table:

use sub queries in UPDATE statements to update rows in a table based on values from another table.

SQL>update emp set deptno=(select deptno from dept

where dname='ACCOUNTING')

where deptno=(select deptno from dept

where dname=-'SALES');

Appling default Values:

It is used to UPDATE a value in a column with DEFAULT value set in the constraints.

SQL>update emp set sal=DEFAULT where ename='FORD';

SQL>update emp set hiredate=DEFAULT where empno=1003;

Returning Clause:

The returning clause is introduced from 8.0.

It is used to return a value from a aggregate function.

This feature is introduced in grid technology.

The clause can be specified for table and materialized views and for views with a single base table.

Restrictions:

Each express must be a simple expression or a single set aggregate function.

It cannot be specified for multiple table insert.

It cannot be specified upon a view upon which a instead of trigger is defined.

SQL>VAR sumsal number;

SQL>update emp set sal=sal*0.25

where deptno=10

returning sum(sal) into :sumsal;

SQL>var bsal number;

SQL>var bname varchar;

SQL>update emp set sal=sal+sal*0.25

where empno=1004

returning ename,sal into :bname,:bsal;

SQL>print :bname :bsal;

DELETE

DELETE:

This command is used to delete the records from the existing table. Using this command we can delete all the records and also we can delete specific records
from the table.
To DELETE rows from a table, the table must be in the USERS SCHEMA.
DELETE any table system privilege allows to DELETE rows from any TABLE or PARTITION TABLE or from the base table of any VIEW.

Syntax:

delete [FROM] <table name>

[WHERE Condition];

Ex:

SQL>delete from emp;

SQL>delete from emp empno;

SQL>delete from emp where empno in(7566,7900);

SQL>delete from emp where ename='SCOTT';

SQL>delete from emp where job='SALESMAN' and

hiredate<Sysdate;

SQL>var vsal number;

SQL>delete from emp where job='SALESMAN' and

hiredate<Sysdate

returning SUM(sal) into :vsal;

SQL>print :vsal;

SQL>delete from emp where ename='SCOTT' or ename='KING';

Data Definition Language(DDL)

Data definition Language:

This is the first sub language in SQL which is used to define the database objects such as table, view, synonym, index and sequence. This language contains five commands those are

i) CREATE.

ii) ALTER.

iii) DROP.

iv) TRUNCATE.

v) RENAME.

i) CREATE:

This command is used to creating the database objects such as Table,View, Synonym, Trigger, Stored, Procedure etc..,

Syntax: create table <table name>

(COLUMN1 datatype(Size),

COLUMN2 datatype(Size),

COLUMNN datatype(size));

Ex:

SQL>create table emp(empno varhcar(10),

ename varchar2(10),

sal number(7,2));

Rules for Creating a table:

Table name must be unique in schema.
The table name should begin with a letter ad can be 1-30 characters long.
Table name should start with alphabets which contains minimum 1 and maximum 30 characters
it should not allows any spaces or any special characters such as except _, #, $ and 0 to 9.
A table can have minimum 1 column maximum thousand columns.
A table can have 0 no of records and maximum n no of records up to hard disk capacity.
Oracle reserved keywords and words should not be used column names or table names.
•Names are not case sensitive.

ii) ALTER:

This command is used to modify the structure of the table. Using this command we can perform four different operations. This command contains four subprograms those are

ALTER- a) Modify

b) ADD

c) RENAME

d) DROP

a) Alter- Modify:

Guidelines for Modifying Columns:

This command is used to increase or decrease the size of the data type and also we can use the data type from all data type to new data type.

Syntax for Modifying Single Column:

ALTER TABLE <Table name> MODIFY (<Column name> data type(size)

EX:

SQL> alter table emp modify ename varchar2(12);

SQL> alter table emp modify(empno number(6));

Syntax for modify more than one column:

ALTER TABLE <Table name> MODIFY (COLUMN1 data type(size), COLUMN2 data type(size)…….,COLUMNN data type(size));

EX:

SQL> alter table emp modify(empno number(6),ename varchar2(12));

SQL> alter table emp modify(ename varchar2(12),deptno number(6));

Guidelines for Modifying Columns:

We can increase the width or precision of a numeric column.
We can decrease the width of a column if the column contains only NULL values and if the table has no rows.
We can change the data type if the column contains NULL's.
We can converts a CHER column to the VARCHAR2 data type or convert a VARCHAR2 COLUMN to the CHAR data type if the column contains NULL values or if the size is not changed.

Alter - ADD:

This command is used to add a new column to the existing table.

Syntax for add a single column:

alter table<Table name>add column name datatype(size);

EX:

SQL> alter table emp add deptno number(4);

SQL> alter table employee add(email varchar2(25));

Syntax to add more than one column:

alter table<table name> add (column1 datatype(size), column2 datatype(size)…..,columnn datatype(size));

EX:

SQL> alter table emp add( mobileno number(10), address1 varchar2(30));

SQL> alter table employee add(mobileno number(10),address2 varchar2(30));

Note:

When we add a new column to a table the new column is always added to the end of the table only.

Alter- RENAME:

This command is used to change the column name from old column name to new column name.

Guidlines for RENAME a Columns:

A column can renamed in table by using ALTER RENAME.
The Feature is enabled from oracle 9.2 onwards.
We can rename only one column at a time.

Syntax:

alter table <table name> rename COLUMN<old_column_name> to <new_column_name).

EX:

SQL>alter table employe rename column sal to salary;

SQL>alter table emp rename column ename to employe_name;

Syntax for change table name:

ALTER TABLE<old table name> RENAME TO <New table name>

EX:

SQL>alter table emp rename to employee;

SQL>alter table employee rename to emp;

Alter – DROP:

This command is used to remove the column from the existing table.

Guidlines to Drop a Column:

The column may or may not contain data.
We can drop more than one column at a time.
The table must have at least one column remaining in it after it is altered.
Once a column is droaped it cannot be recovered.

Syntax for Drop a Column:

alter table <table_name> drop column <column_name>;

EX:

SQL>alter table emp drop column empno;

SQL>alter table employee drop column email;

Syntax to drop more than one column:

alter table <table name> drop(<column name1>,<column name2>,....);

Ex:

SQL>alter table emp drop(empno,ename,deptno);

SQL>alter table emp employee(

Note:

Here we can’t drop all columns.

DROP:

This command is used to drop entire data from database.
It removes the definition of the oracle table.
The command not only drops the table and along with the associated INDEXES also.

Guidelines to Drop Table:

The data is totally deleted from the table.
Any VIEWS and SYNONYAMS will remain but are dept in invalid state.
Any pending transactions are committed.
Only the create or owner of the table or a USER with DROP any table privilege can remove a table from Database.
The DROP Table statement once executed is irreversible.

Syntax:

drop table <table name> [CASCADE CONSTRAINTS];

Ex:

SQL> drop table emp;

SQL> drop table employee purge;

SQL> drop table temp CASCADE CONSTRAINTS;

TRUNCATE:

This command is use delete records permanently from the existing table.
It is used to remove all rows from a table and to release the STORAGE SPACE used by the specific TABLE.
The TRUNCATE TABLE will not facilitate for ROLLBACK.
To TRUNCATE a Table must be the WONER of the Table.

Syntax:

Truncate table <table name>

Ex:

SQL>truncate table emp;

SQL>truncate table employee;

RENAME:

This command is used to change the table name from old table name to new table name.

Guidelines for RENAME Columns:

The RENAME command can be used to change the name of TABLE, VIEW, SEQUENCE, SYNONYM.
To RENAME the OBJECT we must be the OWNER of the OBJECT.

Syntax:

RENAME <old table name> to <new table name>;

EX:

SQL>rename emp to employee;

SQL>rename employee to emp;

SQL

**Components of SQL**
SQL/DRL
Select
DML
Insert
Update
Delete
DDL
Create
Alter
Drop
Truncate
Rename
DCL
Grant
Revoke
TCL
Commit
Rollback
Savepoint

Pages