SQL COMMANDS

CREATE COMMANDS

Create Table

CREATE TABEL EMP(empno number, ename varchar2(30), hiredate date);

CREATE TABLE scott.emp

(empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL CONSTRAINT upper_ename CHECK (ename = UPPER(ename)),job VARCHAR2(9),mgr NUMBER CONSTRAINT fk_mgr REFERENCES scott.emp(empno),hiredate DATE DEFAULT SYSDATE, sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal > 500),comm  NUMBER(9,0)   DEFAULT NULL,          deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL CONSTRAINT fk_deptno   REFERENCES scott.dept(deptno))PCTFREE 5 PCTUSED 75;

Display the Constraints in Table:

DESC USER_CONSTRAINTS;

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMPLOYEES';

Storage Example

CREATE TABLE salgrade(grade  NUMBER  CONSTRAINT pk_salgrade RIMARY KEY                     USING INDEX TABLESPACE users_a,losal NUMBER,hisal  NUMBER ) TABLESPACE human_resource STORAGE (INITIAL  6144 NEXT  6144 MINEXTENTS 1        MAXEXTENTS 5 );

PARALLEL Example

CREATE TABLE emp_dept PARALLEL AS SELECT * FROM scott.emp WHERE deptno = 10;

NOPARALLEL Example

CREATE TABLE emp_dept AS SELECT * FROM scott.emp WHERE deptno = 10;

Partitioned Table Example

CREATE TABLE stock_xactions (stock_symbol CHAR(5),stock_series CHAR(1),       num_shares NUMBER(10),price NUMBER(5,2),trade_date DATE) STORAGE INITIAL 100K NEXT 50K) LOGGING PARTITION BY RANGE (trade_date)    (PARTITION sx1992 VALUES LESS THAN (TO_DATE('01-JAN-1993','DD-MON-YYYY'))TABLESPACE ts0 NOLOGGING,PARTITION sx1993 VALUES LESS THAN TO_DATE('01-JAN-1994','DD-MON-YYYY'))TABLESPACE ts1,PARTITION sx1994 VALUES LESS THAN (TO_DATE('01-JAN-1995','DD-MON-YYYY'))TABLESPACE ts2);

Object Table Examples

Consider object type DEPT_T:

CREATE TYPE dept_t AS OBJECT(dname VARCHAR2(100),address VARCHAR2(200) );

Object table DEPT holds department objects of type DEPT_T:

CREATE TABLE dept OF dept_t;

CREATE OR REPLACE TYPE salesrep_t AS OBJECT(repId NUMBER,repName VARCHAR2(64));

CREATE TABLE salesreps OF salesrep_t;

REF Example

The following example creates object type DEPT_T and object table DEPT to store instances of all departments. A table with a scoped REF is then created.

CREATE TYPE dept_t AS OBJECT (dname   VARCHAR2(100),address VARCHAR2(200) );

CREATE TABLE dept OF dept_t;

CREATE TABLE emp(ename VARCHAR2(100),enumber NUMBER,edept REF dept_t SCOPE IS dept );

The following statement creates a table with a REF column which has a referential constraint defined on it:

CREATE TABLE emp(ename VARCHAR2(100),enumber NUMBER,edept REF dept_t REFERENCES dept);

Create Table Using Select Statements:

CREATE TABLE EMP1 AS SELECT * FROM SCOTT.EMP;

CREATE TABLE DEPTNO AS SELECT * FROM SCOTT.DEPT ORDER BY DEPTNO ASC;

Constraints on Type Columns Example

1.CREATE TYPE address AS OBJECT(hno NUMBER,street VARCHAR2(40),city VARCHAR2(20),zip VARCHAR2(5),phone VARCHAR2(10) );

2. CREATE TYPE person AS OBJECT(name VARCHAR2(40),dateofbirth DATE,    homeaddress address,manager REF person );

3.CREATE TABLE persons OF person(homeaddress NOT NULL UNIQUE (homeaddress.phone),CHECK (homeaddress.zip IS NOT NULL),CHECK (homeaddress.city <> 'San Francisco') );

Create Index

An indexis a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. A partitioned index consists of partitions containing an entry for each value that appears in the indexed column(s) of the table. A function-based indexis an index on expressions. It enables you to construct queries that evaluate the value returned by an expression, which in turn may include functions (built-in or user-defined).

PARALLEL Example

The following statement creates an index using 10 parallel execution servers, 5 to scan SCOTT.EMP and another 5 to populate the EMP_IDX index:

CREATE INDEX emp_idx ON scott.emp (ename) PARALLEL 5;

COMPRESS Example

To create an index with the COMPRESS clause, you might issue the following statement:

CREATE INDEX emp_idx2 ON emp(job, ename) COMPRESS 1;

Cluster Index Example

To create an index for the EMPLOYEE cluster, issue the following statement:

CREATE INDEX ic_emp ON CLUSTER employee;

No index columns are specified, because the index is automatically built on all the columns of the cluster key. For cluster indexes, all rows are indexed.

Partitioned Tables And Indexes

Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways:

We can devided the rows of a single table into multiple parts,Dividing the table's data in this manner is called "Partitioning the table". Partitioning is useful for very large tables and it is used for..

Improve the Qurey performance,it may be easier to load and delete the data in the partitions.

There are two type of prtitions

Range:

CREATE TABLE EMP1(ENO NUMBER PRIMARY KEY,NAME VARCHAR2(20),SAL NUMBER(8,2),

DEPTNO NUMBER REFERENCES DEPT(DEPTNO))

PARTITION BY RANGE(DEPTNO)

(PARTITION PART1 VALUES LESS THAN(20),

PARTITION PART2 VALUES LESS THAN(30));

Hash:

CREATE TABLE EMP1(ENO NUMBER PRIMARY KEY,NAME VARCHAR2(20),SAL NUMBER(8,2),

DEPTNO NUMBER REFERENCES DEPT(DEPTNO))

PARTITION BY HASH(DEPTNO)

PARTITIONS 2;

Composite Partitioning Tables

Composite partitioning allows range partitions to be hash subpartitioned on a different key. The greater number of partitions increases the possiblities for parallelism and reduces the chances of contention. The following example will range partition the table on invoice_date and subpartitioned these on the invoice_no giving a totol of 32 subpartitions:

CREATE TABLE invoices (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL,

comments VARCHAR2(500)) PARTITION BY RANGE (invoice_date) SUBPARTITION BY HASH (invoice_no) SUBPARTITIONS 8

(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),

PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),

PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),

PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY'));

Partitioning Indexes

There are two basic types of partitioned index:

Local - All index entries in a single partition will correspond to a single table partition (equipartitioned). They are created with the LOCAL keyword and support partition independance. Equipartioning allows oracle to be more efficient whilst devising query plans.
Global - Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and do not support partition independance. Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equipartitioned, but Oracle will not take advantage of this structure.

Both types of indexes can be subdivided further:

Prefixed - The partition key is the leftmost column(s) of the index. Probing this type of index is less costly. If a query specifies the partition key in the where clause partition pruning is possible, that is, not all partitions will be searched.
Non-Prefixed - Does not support partition pruning, but is effective in accessing data that spans multiple partitions. Often used for indexing a column that is not the tables partition key, when you would like the index to be partitioned on the same key as the underlying table.

Local Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning are examples of local prefixed indexes:

CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL(PARTITION invoices_q1 TABLESPACE users,

PARTITION invoices_q2 TABLESPACE users,

PARTITION invoices_q3 TABLESPACE users,

PARTITION invoices_q4 TABLESPACE users);

Global Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE, the followning examples is of a global prefixed index:

CREATE INDEX invoices_idx ON invoices (invoice_date) GLOBAL PARTITION BY RANGE (invoice_date)

(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,

PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,

PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,

PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE users);

Create Procedures

A procedureis a group of PL/SQL statements that you can call by name.

Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.

CREATE TABEL EMP (EMPNO NUMBER, ENAME VARCHAR2 (30);

USING SELECT STATEMENT:

CREATE OR REPLACE PROCEDURE Get_Ename(Eno IN EMP.EMPNO%TYPE,

NAME OUT EMP.ENAME%ROWTYPE) AS

BEGIN

SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=Eno;

END;

/ (OR)

CREATE OR REPLACE PROCEDURE Get_Ename(Eno IN number,

NAME OUT varcahr2) AS

BEGIN

SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=Eno;

END;/

Create Variable: VARIABLE X VARCHAR2(30);

EXEC GET_ENAME(7788, :X)

PRINT X;

USING DELETE STATEMENT:

CREATE OR REPLACE PROCEDURE fire_emp1(Emp_id NUMBER) AS

   BEGIN

      DELETE FROM Emp_tab WHERE Empno = Emp_id;

   END;

USING UPDATE STATEMENT:

CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS

   BEGIN

      UPDATE accounts

      SET balance = balance + amount

      WHERE account_id = acc_no;

   END;

Create Functions

A stored function (also called a user function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called.

CREATE FUNCTION get_bal(acc_no IN NUMBER)

   RETURN NUMBER

   IS acc_bal NUMBER(11,2);

   BEGIN

      SELECT balance

      INTO acc_bal

      FROM accounts

      WHERE account_id = acc_no;

      RETURN(acc_bal);

    END;

Create Sequence

A sequenceis a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

CREATE SEQUENCE eseq INCREMENT BY 10;

CREATE SEQUENCE empno start with 1 increment by 1 maxvalue 100 minvalue 1 cycle cache 5;

Select empno.nextval from dual;

Select empno.currval from dual;

Create Schema

To create multiple tables and views and perform multiple grants in a single transaction.

To execute a CREATE SCHEMA statement, Oracle executes each included statement. If all statements execute successfully, Oracle commits the transaction. If any statement results in an error, Oracle rolls back all the statements.

Create Synonym

A synonym is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object,

CREATE SYNONYM market FOR scott.market_research;

CREATE PUBLIC SYNONYM emp FOR scott.emp@sales;

A synonym may have the same name as the base table, provided the base table is contained in another schema.

Create Tablespace

A tablespace is an allocation of space in the database that can contain schema objects. When you create a tablespace, it is initially a read-write tablespace.

This statement creates a tablespace named TABSPACE_2 with one datafile:

A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application's objects to simplify some administrative operations.

CREATE TABLESPACE tabspace_2

DATAFILE 'diska:tabspace_file2.dat' SIZE 20M

DEFAULT STORAGE (INITIAL 10K NEXT 50K    MINEXTENTS 1 MAXEXTENTS 999)

Create Cluster

A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Oracle stores together all the rows (from all the tables) that share the same cluster key. They can be created to increase the performance of data retrieval.

Clustered Tables Related data stored together more efficiently

Non Clustered Tables Related data stored differently and taking up more space.

CREATE CLUSTER personnel (department_number  NUMBER(2)) SIZE 512     STORAGE (INITIAL 100K NEXT 50K);

Adding Tables to a Cluster

The following statements add the EMP and DEPT tables to the cluster:

CREATE TABLE emp (empno  NUMBER   PRIMARY KEY, ename  VARCHAR2(10)  NOT NULL                              CHECK (ename = UPPER(ename)), job   VARCHAR2(9), mgr NUMBER REFERENCES scott.emp(empno), hiredate  DATE  CHECK (hiredate < TO_DATE ('08-14-1998', 'MM-DD-YYYY')),      sal   NUMBER(10,2)  CHECK (sal > 500), comm  NUMBER(9,0)  DEFAULT NULL, deptno NUMBER(2)  NOT NULL ) CLUSTER personnel (deptno);

CREATE TABLE dept (deptno  NUMBER(2),dname   VARCHAR2(9),loc  VARCHAR2(9))     CLUSTER personnel (deptno);

Create Trigger

A database trigger is

A stored PL/SQL block associated with a table, a schema, or the database
An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java

Oracle automatically executes a trigger when specified conditions occur.

Example:

CREATE TABLE departments(ID NUMBER(10) NOT NULL,DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD(CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

CREATE OR REPLACE TRIGGER dept_bir BEFORE INSERT ON departments

FOR EACH ROW

WHEN (new.id IS NULL)

BEGIN

  SELECT dept_seq.NEXTVAL   INTO   :new.id   FROM   dual;

END;

CREATE OR REPLACE TRIGGER dept_bir

BEFORE INSERT ON departments

FOR EACH ROW

BEGIN

  SELECT dept_seq.NEXTVAL

  INTO   :new.id

  FROM   dual;

END;

CREATE OR REPLACE TRIGGER dept_bir

BEFORE INSERT ON departments

FOR EACH ROW

BEGIN

  IF new.id IS NOT NULL THEN

    RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified');

  ELSE

    SELECT dept_seq.NEXTVAL

    INTO   :new.id

    FROM   dual;

  END IF;

END;

Create User

CREATE USER sidney IDENTIFIED BY welcome DEFAULT TABLESPACE cases_ts     QUOTA 10M ON cases_ts QUOTA 5M ON temp_ts QUOTA 5M ON system PROFILE engineer PASSWORD EXPIRE;

CREATE USER ops$george IDENTIFIED EXTERNALLY DEFAULT TABLESPACE accs_ts     TEMPORARY TABLESPACE temp_ts QUOTA UNLIMITED ON accs_ts QUOTA UNLIMITED ON temp_ts;

Create View

To define a view, a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.

CREATE VIEW dept20 AS SELECT ename, sal*12 annual_salary FROM emp WHERE deptno = 20;

CREATE VIEW clerk (id_number, person, department, position)  AS SELECT empno, ename, deptno, job  FROM emp  WHERE job = 'CLERK'  WITH CHECK OPTION CONSTRAINT wco;

CREATE VIEW EMP_DTLS AS

SELECT E.EMPNO,E.ENAME,D.DNAME,E.SAL+NVL(COMM,0) "TOTALSAL" FROM EMP E,DEPT D WHERE D.DEPTNO=E.DEPTNO;

Join View Example

CREATE VIEW ed AS  SELECT e.empno, e.ename, d.deptno, d.loc  FROM emp e, dept d  WHERE e.deptno = d.deptno

View created.

SELECT column_name, updatable  FROM user_updatable_columns  WHERE table_name = 'ED';

Read-Only View Example

CREATE VIEW clerk (id_number, person, department, position)  AS SELECT empno, ename, deptno, job FROM emp  WHERE job = 'CLERK'    WITH READ ONLY;

Create Materialized View

For data warehousing purposes, a materialized view definition can include an aggregation (SUM, COUNT(x), COUNT(*), COUNT(DISTINCT x), AVG, VARIANCE, STDDEV, MIN, and MAX) and any number of joins. Such materialized views can be used in query rewrite, an optimization technique that transforms a user request written in terms of master tables into a semantically equivalent request that includes one or more materialized view. In a data warehousing environment, all detail tables must be local.

CREATE MATERIALIZED VIEW mv1 REFRESH FAST ON COMMIT

   AS SELECT t.month, p.prod_name, SUM(f.sales) AS sum_sales

      FROM time t, product p, fact f

      WHERE f.curDate = t.curDate AND f.item = p.item

      GROUP BY t.month, p.prod_name

   BUILD IMMEDIATE;

DROP MATERIALIZED VIEW LOG ON parts;

ALTER Commands:

Alter Cluster

The following statement alters the CUSTOMER cluster in the schema SCOTT:

ALTER CLUSTER scott.customer SIZE 512 STORAGE (MAXEXTENTS 25);

ALTER CLUSTER scott.customer  DEALLOCATE UNUSED KEEP 30 K;

ADD Constraints:

ALTER TABLE EMP ADD CONSTRAINTS PK_EMPNO PRIMARY KEY(EMPNO);

ALTER TABLE EMP ADD CONSTRAINTS FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);

Alter Index

ALTER INDEX scott.customer INITRANS 5 STORAGE (NEXT 100K);

Alter Procedure

ALTER PROCEDURE henry.close_acct COMPILE;

Alter Sequence

ALTER SEQUENCE eseq  MAXVALUE 1500;

Alter Table

Add Column Example

ALTER TABLE emp ADD(thriftplan NUMBER(7,2),loancode CHAR(1) NOT NULL);

Modify Column Examples

ALTER TABLE emp MODIFY (thriftplan NUMBER(9,2));

DISABLE Example

ALTER TABLE customers DISABLE UNIQUE (areaco, phoneno) CASCADE;

DROP PRIMARY KEY Example

ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS;

ALTER TABLE t1 DROP (pk, fk, c1);

Drop Constraint Examples

ALTER TABLE dept DROP PRIMARY KEY CASCADE;

ALTER TABLE dept DROP UNIQUE (dname);

Alter Tablespace

ALTER TABLESPACE accounting BEGIN BACKUP;

ALTER TABLESPACE accounting END BACKUP;

Alter Trigger

ALTER TRIGGER reorder DISABLE;

ALTER TRIGGER reorder ENABLE;

Alter View

ALTER VIEW customer_view COMPILE;

DELETE COMMANDS

DELETE FROM temp_assign;

DELETE FROM emp WHERE JOB = 'SALESMAN'AND COMM < 100;

DELETE FROM (select * from emp)WHERE JOB = 'SALESMAN'AND COMM < 100;

DELETE FROM emp WHERE job = 'SALESMAN' AND COMM < 100 RETURNING sal INTO:1;

Delete Table

DROP TABLE test_data;

RENAME dept TO emp_dept;

CREATE TABLE temporary (newname, col2, col3)AS SELECT oldname, col2, col3 FROM static;

DROP TABLE static;

RENAME temporary TO static;

Drop Columns

Logical Delete

On large tables the process of physically removing a column can be very time and resource consuming. For this reason you may decide to logically delete it:

ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name SET UNUSED (column_name1, column_name2);

Physical Delete

To physically drop a column you can use one of the following syntaxes, depending on whether you wish to drop a single or multiple columns:

ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP (column_name1, column_name2);

Delete Cluster

DROP CLUSTER geography INCLUDING TABLES   CASCADE CONSTRAINTS;

Delete Function

DROP FUNCTION riddley.new_acct;

Delete Index

DROP INDEX monolith;

Delete Procedure

DROP PROCEDURE kerner.transfer

Delete Tablespace

DROP TABLESPACE TableSpaceName INCLUDING CONTENTS CASCADE CONSTRAINTS;

Delete Trigger

DROP TRIGGER ruth.reorder;

Delete User

DROP USER bradley CASCADE;

Delete View

DROP VIEW view_data;

SELECT AND SUBQUERIES

SELECT * FROM EMP;

SELECT ‘SELECT * FROM ‘||TABLE_NAME||’;’ FROM USER_TABLES;

NULL Function

SELECT EMPNO, ENAME,SAL,COMM FROM EMP WHERE COMM IS NULL;

SELECT EMPNO,ENAME,SAL,COMM FROM EMP WHERE COMM IS NOT NULL;

SELECT SAL+NVL(COMM,0) FROM EMP;

GROUP BY Examples

SELECT * FROM emp WHERE deptno = 30;

SELECT ename, job, sal, deptno FROM emp WHERE NOT (job = 'SALESMAN' AND deptno = 30);

SELECT a.deptno "Department",a.num_emp/b.total_count "%Employees",  a.sal_sum/b.total_sal "%Salary"  FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum FROM scott.emp GROUP BY deptno) a,(SELECT COUNT(*) total_count, SUM(sal) total_sal FROM scott.emp) b ;

SELECT COUNT(*) * 100 FROM emp SAMPLE BLOCK (1);

SELECT deptno, MIN(sal), MAX (sal) FROM emp GROUP BY deptno;

SELECT deptno, MIN(sal), MAX (sal)FROM emp WHERE job = 'CLERK'     GROUP BY deptno;

SELECT EMP_NO,COUNT(EMP_NO) FROM EMP_ATTENDANCE_DTLS WHERE

SUBSTR(TO_CHAR(ENTRY_DT,'DD/MM/YY'),4,5)='03/05' GROUP BY EMP_NO

Display the Top or Bottom level rows

SELECT LEVEL, MAX(SAL) FROM EMP WHERE LEVEL= 1 OR LEVEL=2 CONNECT BY PRIOR SAL > SAL GROUP BY LEVEL

SELECT LEVEL, MIN(SAL) FROM EMP WHERE LEVEL=2 OR LEVEL =3 CONNECT BY PRIOR SAL < SAL GROUP BY LEVEL

SELECT EMPNO,ENAME,SAL FROM EMP A WHERE 3>=(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE B.SAL>=A.SAL) ORDER BY SAL DESC;

SELECT EMPNO,ENAME,SAL FROM EMP A WHERE 3>=(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE A.SAL>=B.SAL) ORDER BY SAL DESC;

SELECT EMPNO,ENAME,SAL FROM (SELECT * FROM EMP ORDER BY SAL DESC)

WHERE ROWNUM<=3;

SELECT EMPNO,ENAME,SAL FROM (SELECT * FROM EMP ORDER BY SAL)

WHERE ROWNUM<=3;

Having Examples

SELECT deptno, MIN(sal), MAX(sal)FROM emp WHERE job = 'CLERK'     GROUP BY deptno  HAVING MIN(sal) < 1000;

ORDER BY Examples

SELECT * FROM emp WHERE job = 'SALESMAN' ORDER BY comm DESC;

SELECT ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;

SELECT ename, deptno, sal FROM emp  ORDER BY 2 ASC, 3 DESC;

FOR UPDATE Examples

SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE;

SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE OF emp.sal;

Table Collection Examples

CREATE TYPE ProjectType AS OBJECT(pno   NUMBER, pname CHAR(31),budget NUMBER);

CREATE TYPE ProjectSet AS TABLE OF ProjectType;

Sub query Examples

SELECT ename, deptno FROM emp WHERE deptno =(SELECT deptno FROM emp  WHERE ename = 'TAYLOR');

UPDATE emp SET sal = sal * 1.1 WHERE empno NOT IN (SELECT empno FROM bonus);

CREATE TABLE newdept (deptno, dname, loc) AS SELECT deptno, dname, loc FROM dept;

SELECT * FROM EMP_DTLS WHERE TOTALSAL= (SELECT MAX(TOTALSAL) FROM EMP_DTLS);

SELECT * FROM EMP_DTLS WHERE TOTALSAL >(SELECT TOTALSAL FROM EMP_DTLS WHERE EMPNO = 7369);

SELECT * FROM EMP_DTLS WHERE TOTALSAL <(SELECT TOTALSAL FROM EMP_DTLS

WHERE EMPNO=7839) ORDER BY TOTALSAL;

Equijoin Examples

This equijoin returns the name and job of each employee and the number and name of the department in which the employee works:

SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno;

SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND job = 'CLERK';

Ex:

SELECT L.EMP_LEAVE_ID,L.EMP_NO,L.LEAVE_REASON,M.NAME,M.SURNAME FROM EMP_LEAVE_DTLS L,MST_EMPLOYEES M WHERE L.EMP_NO=M.EMP_NO AND L.EMP_NO=1421;

Self Join Example

The following query uses a self join to return the name of each employee along with the name of the employee's manager:

SELECT e1.ename||' works for '||e2.ename "Employees and their Managers"     FROM emp e1, emp e2   WHERE e1.mgr = e2.empno;

Outer Join Examples

This query uses an outer join to extend the results of the Equijoin example above:

SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno;

SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK';

SELECT orderno, custno, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE"     FROM orders;

The following outer join returns all customers and the dates they placed orders. The (+) operator ensures that customers who placed no orders are also returned:

SELECT custname, TO_CHAR (orderdate, 'MON-DD-YYYY') "ORDERDATE" FROM customers, orders WHERE customers.custno = orders.custno (+)

Inner Join

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE

E.DEPTNO=D.DEPTNO

In Oracle 9i

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E inner join DEPT D on

E.DEPTNO=D.DEPTNO

Left Outer Join

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE

E.DEPTNO(+)=D.DEPTNO

In Oracle 9i

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E left outer join DEPT D on

E.DEPTNO)=D.DEPTNO

Right Outer Join

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE

E.DEPTNO=D.DEPTNO(+)

In Oracle 9i

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E right outer join DEPT D on

E.DEPTNO)=D.DEPTNO

Full Outer Join

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE

E.DEPTNO(+)=D.DEPTNO

UNION

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE

E.DEPTNO=D.DEPTNO(+);

In Oracle 9i

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME FROM EMP E full outer join DEPT D on

E.DEPTNO)=D.DEPTNO

DISTINCT Example:

SELECT EMPNO,ENAME "Employee Name",SAL,JOB FROM EMP;

SELECT ENAME||JOB AS "EMP INFO" FROM EMP;

SELECT DISTINCT DEPTNO,JOB FROM EMP;

SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO IN(10,20);

SELECT EMPNO,ENAME,SAL FROM EMP WHERE ENAME LIKE'S%';

SELECT EMPNO,ENAME,SAL FROM EMP WHERE ENAME LIKE'%T';

SELECT EMPNO,ENAME,SAL FROM EMP WHERE JOB NOT IN('MANAGER','PRESIDENT');

SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL;

CHARACTER FUNCTIONS:

SELECT SUBSTR('SQL COMMANDS',1,3) FROM DUAL;

SELECT LOWER('ORACLE') FROM DUAL;

SELECT LENGTH('ORACLE') FORM DUAL;

SELECT INITCAP('ORACLE') FROM DUAL;

SELECT CONCAT(ENAME,JOB) FROM EMP;

SELECT LPAD(SAL,11,'*') FROM EMP;  SELECT RPAD(SAL,11,'*') FROM EMP;

NUMBER FUNCTIONS:

SELECT ROUND(2356.2345,2),ROUND(2541.012,0),ROUND(3698.235,-1) FROM DUAL;

SELECT TRUNC(2356.2345,2),TRUNC(2541.012,0),TRUNC(3698.235,-1) FROM DUAL;

SELECT EMPNO,ENAME,SAL,MOD(SAL,2000) FROM EMP WHERE EMPNO=7566;

DATE FUNCTIONS:

SELECT SYSDATE FROM DUAL;

SELECT HIREDATE,HIREDATE+7,HIREDATE-7,SYSDATE-HIREDATE FROM EMP WHERE HIREDATE LIKE'%JUN%';

SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP WHERE EMPNO=7566;

SELECT HIREDATE,ADD_MONTHS(HIREDATE,3),ADD_MONTHS(HIREDATE,-3) FROM EMP WHERE DEPTNO=20;

SELECT HIREDATE,NEXT_DAY(HIREDATE,'FRIDAY'),NEXT_DAY(HIREDATE,6) FROM EMP WHERE DEPTNO=10;

SELECT SYSDATE,NEXT_DAY(SYSDATE,'FRIDAY'),NEXT_DAY(SYSDATE,2) FROM DUAL;

CANVERTION FUNCTIONS:

SELECT TO_CHAR(SYSDATE,'DAY,DDTH MONTH YYYY') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'HH:MI:SS') FROM DUAL;

SELECT TO_CHAR(SAL,'$9,0000') FROM EMP;

SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>TO_NUMBER('1500');

SELECT * FROM EMP WHERE SUBSTR(HIREDATE,4,3)='JAN';

SELECT * FROM EMP WHERE SUBSTR(TO_CHAR(HIREDATE,'DD/MM/YY'),4,5)='01/82';

SELECT * FROM EMP WHERE SUBSTR(TO_CHAR(HIREDATE,'DD/MM/YYYY'),4,2)='01';

GENERAL FUNCTIONS:

SELECT USER FROM DUAL;

SELECT VSIZE('HELLO') FROM DUAL;

SELECT ENAME,JOB,SAL,DECODE(JOB,'CLERK',1.10*SAL,'MANAGER',2*SAL)"RECEIVED SALARY" FROM EMP;

SELECT DECODE(COMM,NULL,'NOT AVAILABLE',COMM) FROM EMP;

SELECT HIREDATE,DECODE(SUBSTR(HIREDATE,4,3),'DEC','EMPLOYEE JOINED IN DECEMBER','FEB','EMPLOYEE JOINED IN FEBRUARY',

HIREDATE) FROM EMP

SET OPARATORS:

UNION:

SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=10

                              UNION

SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=30;

UNION ALL:

SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=10

                         UNION ALL

SELECT JOB,DEPTNO FROM EMP WHERE DEPTNO=30;

INTERSECT:

SELECT JOB FROM EMP WHERE DEPTNO=10

                       INTERSECT

SELECT JOB FROM EMP WHERE DEPTNO=30;

MINUS:

SELECT JOB FROM EMP WHERE DEPTNO=10

                             MINUS

SELECT JOB FROM EMP WHERE DEPTNO=30;

TRUNCATE

TRUNCATE TABLE emp PRESERVE SNAPSHOT LOG;

TRUNCATE TABLE stock;

UPDATE

UPDATE emp SET comm = NULL WHERE job = 'TRAINEE';

UPDATE emp SET job = 'MANAGER', sal = sal + 1000, deptno = 20 WHERE ename = 'JONES';

INSERT

INSERT INTO dept VALUES (50, 'PRODUCTION', 'SAN FRANCISCO');

INSERT INTO emp (empno, ename, job, sal, comm, deptno)VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);

INSERT INTO (SELECT empno, ename, job, sal, comm, deptno FROM emp)   VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);

Sub Query Example

INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal OR job IN ('PRESIDENT', 'MANAGER');

INSERT INTO scott.accounts@sales (acc_no, acc_name) VALUES (5001, 'BOWER');

Sequence Example

INSERT INTO emp VALUES  (empseq.nextval, 'LEWIS', 'CLERK',  7902, SYSDATE, 1200, NULL, 20);

GRANT PRIVILEGES AND ROLES

GRANT CREATE SESSION TO richard;

GRANT CREATE TABLE TO travel_agent;

GRANT READ ON DIRECTORY bfile_dir1 TO scott WITH GRANT OPTION;

GRANT ALL ON bonus TO jones WITH GRANT OPTION;

GRANT SELECT, UPDATE ON golf_handicap TO PUBLIC;

GRANT REFERENCES (empno), UPDATE (empno, sal, comm)ON scott.emp TO blake;

REVOKE PRIVILEGES AND ROLES

REVOKE DROP ANY TABLE FROM bill, mary;

REVOKE CREATE TABLESPACE FROM controller;

create TABLE : This is a DDL command in SQL that creates a new table in a database.

create TABLE table-name (column-name data-type [size] NOT NULL/DEFAULT default-value] CHECK (column-name 0) UNIQUE (column-name) PRIMARY KEY (column-name) FOREIGN KEY (column-name) REFERENCES table-name)

ALTER TABLE : This is a DDL command in SQL that modifies the structure of an existing table.
Syntax : ALTER TABLE table-name ADD (column-name data-type [size] [NOT NULL DEFAULT]...)
primary key definition / foreign key definition
DROP PRIMARY KEY / DROP FOREIGN KEY)
DROP TABLE : This is DDL command in SQL that deletes the an existing table. Once you delete a table, all data contained in it is lost and cannot be recovered. The storage space used by this table is also released.
Syntax : DROP TABLE table-name

Interacting with a Database :

SELECT : This is a DML command in SQL that retrieves data from the database in the form of query results. The command supports the following keywords and clauses :
FROM This keyword specifies the name of the table.
* This keyword selects all the columns of the table.
WHERE This keyword gives the search condition that specifies the data to be retrieved.
AND This operator is used to combine two or more search conditions.
ORDER BY This keyword sorts the query result on one or more columns.
GROUP BY This keyword groups the query result and lets you generate summary result for each group.
NULL values This value indicates that the data is not present.
Subquery This is the query that is place inside the main query. It passes its query result to the main query.
INSERT : This is a DML command in SQL that you use to add data in rows of a table.
SYNTAX : INSERT INTO table-name (column-names) VALUES (constant/NULL)
UPDATE : This is a DML command in SQL that you use to change data on rows of a table.
Syntax : UPDATE table-name SET column-name-value WHERE condition
DELETE : This is a DML command in SQL that removes one or more rows of data from a table.
Syntax : DELETE FROM table-name WHERE condition.
End-user's View of a Database :
Views : Views are relations that are derived from one or more source tables. Views have the following features:
Views let you restrict the access todata so that end-users see data relevant tothem.
Views do not physically exist in the database and only their definition is stored by an RDBMS.
An RDBMS accesses the source tables for data tobe retrieved from a view.
Any changes that users make to views do not reflect in the source tables if the view has been created using a Join condition.
Views created WITH CHECK OPTION allows for an added measure of security in a view. For example, the user will not be able to insert or update a row that could not be selected by the view-with check option prevents this from happening.
create VIEW : A view can be created using the create VIEW command.
Syntax : create VIEW view-name (column-names) AS query.
Retrieving Data from a View : Once you createa view, you can retrieve data from it using the SELECT command, just as you do for a table.
Restricting Access to a Database :

GRANT : This is a DCL command in SQL that you use to grant a specific set of authorities to one or more users.
Syntax : GRANT (SQL command) (column-names) ON table-name to user-name.
REVOKE : This is a DCL command in SQL that you use to take away a specific set of authorities from one or more users.
Syntax : REVOKE (SQL command) ON table-name touser-name

Introduction

Materialized viewsare stored summaries of queries containing precomputed results. Materialized views greatly improve data warehouse query processing.The existence of a materialized view is transparent to SQL applications, so a DBA can create or drop materialized views at any time without affecting the validity of SQL applications.

Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution time and storing these results in the database. The query optimizer can make use of materialized views by automatically recognizing when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views. Rewriting queries to use materialized views rather than detail relations results in a significant performance gain.

Materialized views can be refreshed automatically whenever the data is changed in the underlying tables. The refresh method can be incremental (fast refresh) or complete. Incremental method re-populates only the changed data. Complete method truncates and rebuilds the view. The refresh process can be enabled by adding the REFRESH clause while creating the materialized view. You can suppress the refresh process for the entire life of the view.

Example 1 :

The following statement creates and populates a materialized view SALES_BY_MONTH_BY_STATE. The materialized view will be populated with data as soon as the statement executes successfully, and subsequent refreshes will be accomplished by reexecuting the materialized view's query.

CREATE MATERIALIZED VIEW sales_by_month_by_state
TABLESPACE my_ts
PARALLEL (10)
ENABLE QUERY REWRITE
BUILD IMMEDIATE
REFRESH COMPLETE AS
SELECT t.month, g.state, SUM(sales) AS sum_sales
FROM fact f, time t, geog g
WHERE f.cur_date = t.cur_date
AND f.city_id = g.city_id
GROUP BY month, state;

The fact table stores the actual data about the sales by month and state, time table has the day,month, year dimenstions and geog stores the state names. This view is built and populated with data immediately.

Lets asssume that you asked oracle to calculate the same values in a query and if query_rewrite is allowed on your server, the optimizer will automatically use the values in the above precomputed view.

NOTES:

1. Set QUERY_REWRITE_ENABLE=TRUE in init.ora

2. Set JOB_QUEUE_PROCESSES=1 ( 1 or more based on your requirements).

RESTRICTIONS:

1. Materialized views consume storage space. Be aware to provide storage parameters according to the data it holds.

2. There cannot be any set functions like UNION, MINUS in the underlying query for materialized views.

Syntax for Drop the users:

If user BRADLEY's schema contains no objects, you can drop BRADLEY by issuing the statement:

DROP USER bradley;

If BRADLEY's schema contains objects, you must use the CASCADE clause to drop BRADLEY and the objects:

DROP USER bradley CASCADE;

Partitions

Clustered and Non-clustered Tables

PL/SQL

It Procedural Language, we can execute a block of statements at a time.

Structure of PL/SQL Program:

It contains Three Sections

DECLAR

Declaration of Variable, constraints and cursors;

BEGIN

Execution of SQL and PL/SQL Statements;

EXCEPTION

Exception Handlers;

END;

There are two types of PL/SQL blocs.

1. Anonymous Block: According to Oracle server it any PL/SQL block is not having any specific name is called “Anonymous Block”.

2. Name Block: According to Oracle server it any PL/SQL block is having specific name is called “Name Block”.

Datatypes: What ever datatypes SQL supports, the same datatypes PL/SQL support. And addition it support BOOLEN datatype.

CHAR it is fixed length the range is 32,767.

VARCHAR2 the range is 32,767.

CREATING A FILE: SQL> ED <FILENAME> EX: ED ADDTION

EXECUT THE FILE : SQL> @<FILENAME> EX: @ ADDTION.

DISPLAY THE OUTPUT: SET SERVEROUTPUT ON;

Program for Adding Two Numbers:

DECLARE

A NUMBER :=&A;

B NUMBER :=&B;

C NUMBER;

BEGIN

C:=A+B;

DBMS.OUTPUT.PUT_LINE(‘SUM=’||C);

END;

DBMS.OUTPUT: It is a predefine Package is used to display the output on the screen.

PUT_LINE: It is a procedure defined inside of DBMS.OUTPUT package.

Finding the Length of String?

DECLARE

A NUMBER:=&A;

B NUMBER;

BEGIN

B:=LENGTH(A);

DBMS.OUTPUT.PUT_LINE(‘Length of’|| A ||‘is’|| B);

END;

Find the First and Last dist of String

DECLARE

A VARCHAR2(32767):='&A';

F CHAR;

L CHAR;

BEGIN

F:=SUBSTR(A,1,1);

L:=SUBSTR(A,-1);

DBMS_OUTPUT.PUT_LINE('First Char is'||F);

DBMS_OUTPUT.PUT_LINE('Last Char is'||L);

END;

Flow of PL/SQL Program:

1. SEQUENCIAL.

2. CONDITIONAL/SELECTIVE.

3. LOOPING/ ITERATION.

CONTROL STATEMENTS:

IF STATEMENT:

Syntax: IF CONDITION1 THEN

Statement to Execute;

ELSE

Statement to Execute;

END IF;

IF CONDITION1 THEN

Statement to Execute;

ELSIF CONDITION2 THEN

Statement to Execute;

ELSIF CONDITION3 THEN

Statement to Execute;

ELSE

Statement to Execute;

END IF;

Example:

DECLARE

A NUMBER:=&A;

B NUMBER:=&B;

C NUMBER:=&C;

D NUMBER;

BEGIN

IF A=1 THEN

D:=B+C;

ELSIF A=2 THEN

D:=B-C;

ELSE

DBMS_OUTPUT.PUT_LINE('INVALID OPTION');

END IF;

DBMS_OUTPUT.PUT_LINE('SUM '||D);

END;

Loops: If we want to execute the same statement number of time the we will use loops.

There are three types of loops.

1. Simple Loop: In simple loop if the condition is false then execute the given statement.

Syntax:

LOOP

EXIT WHEN CONDITION;

Statement to Execute;

END LOOP;

Example:

DECLARE

I NUMBER:=1;

BEGIN

LOOP

EXIT WHEN I>5;

DBMS_OUTPUT.PUT(' '||I);

I:=I+1;

END LOOP;

DBMS_OUTPUT.NEW_LINE();

END;

2. While Loop: If the condition is true then it will execute the given statement.

Syntax:

WHILE CONDITION LOOP

Statement to Execute;

END LOOP;

Example:

DECLARE

I NUMBER :=&I;

BEGIN

WHILE I<=5 LOOP

DBMS_OUTPUT.PUT(' '||I);

I:=I+1;

END LOOP;

DBMS_OUTPUT.NEW_LINE();

END;

3. For Loop: Variable using For Loop is not require to DECLARE, No need of Increment and Variable using by the For Loop is Not editable.

Syntax:

FOR Variable in EXPRESSION1..EXPRESSION2 LOOP

Statement to Execute;

END LOOP;

Example:

BEGIN

FOR I IN 1..5 LOOP

DBMS_OUTPUT.PUT(' '||I);

END LOOP;

DBMS_OUTPUT.NEW_LINE();

END;

Print the given String in Reveres Order.

DECLARE

A VARCHAR2(32767):='&A';

--B VARCHAR2(32767)

C CHAR;

BEGIN

FOR I IN 1..LENGTH(A) LOOP

C:=SUBSTR(A,-I,1);

DBMS_OUTPUT.PUT(C);

END LOOP;

DBMS_OUTPUT.NEW_LINE();

END;

Find the given String is Palindrome or Not.

DECLARE

A VARCHAR2(32767):='&A';

B VARCHAR2(32767);

C CHAR;

BEGIN

FOR I IN 1..LENGTH(A) LOOP

C:=SUBSTR(A,-I,1);

B:=B||C;

END LOOP;

IF A=B THEN

DBMS_OUTPUT.PUT_LINE(A||' IS PALINDROME');

ELSE

DBMS_OUTPUT.PUT_LINE(A||' IS NOT PALINDROME');

END IF;

END;

Find the given Number is Amstrong Number or not.

Amstrong No: It sum of cube of all the digits=Amstrong No

Ex: 1³+5³+3³=153.

DECLARE

A NUMBER :=&A;

C NUMBER:=0;

BEGIN

FOR I IN 1..LENGTH(A) LOOP

C:=C+POWER(SUBSTR(A,I,1),3);

END LOOP;

IF A=C THEN

DBMS_OUTPUT.PUT_LINE(A ||' IS AMSTRONG NUMBER');

ELSE

DBMS_OUTPUT.PUT_LINE(A ||' IS NOT AMSTRONG NUMBER');

END IF;

END;

Accessing data from the Tables using PL/SQL:

DECLARE

ENO NUMBER:=&ENO;

N VARCHAR2(32767);

S NUMBER;

J VARCHAR2(32767);

BEGIN

SELECT ENAME,SAL,JOB INTO N,S,J FROM EMP WHERE EMP.EMPNO=ENO;

DBMS_OUTPUT.PUT_LINE('Employee Name is '||N);

DBMS_OUTPUT.PUT_LINE('Employee Salary is '||S);

DBMS_OUTPUT.PUT_LINE('Employee job is '||J);

END;

Exception Handle:

In PL/SQL we have two types of errors one is ‘Syntax Errors’ which are encountered during the Compilation. Second one is ‘Runtime Errors’ which are encountered during the Runtime.

If the PL/SQL block is not compiled successfully, whatever transactions we done in the block, that will Rollback automatically.

Syntax:

Exception

When No_Data_Found Then

DBMS_OUTPUT.PUT_LINE(‘Record Not Existed..’);

Exception is used to handle runtime errors in PL/SQL program. It is two types

1. Predefine Exception

a. Define

b. Un define

2. User Define Exception.

Define Exception: It is the exception which is having Error Number with a specific Name.

Un Define Exception: It is the exception which is having Error Number without having any name.

Predefine Exception are

1. NO_DATA_FOUND

2. TOO_MANY_ROWS

3. DUP_VAL_ON_INDEX

4. VALUE_ERROR

5. INVALID_NUMBER

6. ZERO_DEVIDE

7. CURSOR_ALREADY_OPEN

8. INVALID_CURSOR

9. PROGRAM_ERROR

10. STORAGE_ERROR

11. LOGIN_DEFINE.

Example:

DECLARE

A NUMBER:=&A;

N EMP.ENAME%TYPE;

J EMP.JOB%TYPE;

S NUMBER;

INC NUMBER;

EXCESS_SALARY EXCEPTION;

BEGIN

SELECT ENAME,JOB,SAL INTO N,J,S FROM EMP WHERE EMPNO=A;

DBMS_OUTPUT.PUT_LINE('Employee Old Salary='||S);

IF S>=800 OR S<=1500 THEN

INC:=500;

ELSIF S>1500 OR S<=3000 THEN

INC:=1000;

ELSE

INC:=1000;

END IF;

UPDATE EMP SET SAL=SAL+INC WHERE EMPNO=A;

DBMS_OUTPUT.PUT_LINE('Employee Number='||A);

DBMS_OUTPUT.PUT_LINE('Employee Name='||N);

DBMS_OUTPUT.PUT_LINE('Employee Job='||J);

DBMS_OUTPUT.PUT_LINE('Employee Incremented Salary='||S);

IF S+INC>5500 THEN

RAISE EXCESS_SALARY;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Employee Number Not Exist');

WHEN EXCESS_SALARY THEN

UPDATE EMP SET SAL=5500 WHERE EMPNO=A;

DBMS_OUTPUT.PUT_LINE('EXCESS SALARY');

END;

CURSOR: It is a work area that can store the executed SQL Statement result data. It is used to handle the multiple records in PL/SQL program. It is two types

1. Implicit Cursor: System can open a cursor for internal processes such type of cursors are called ‘Implicit cursors’.

2. Explicit Cursor: Users can open a cursor for accessing the data, that type of user define cursors are called ‘Explicit Cursors’.

Handle the Cursors: There are three ways to handle the cursors.

1. Open the cursor using ‘open<cursor name>’

2. Fetch data from cursor using ‘fetch <cursor name>’

3. Close the cursor using ‘close <cursor name>’

Attributes of Cursor:

1. %ISOPEN: It returns true if the cursor is open else it returns false

2. %FOUND: It returns true if the record fetch from the workspace else it returns false.

3. %NOTFOUND: It returns true if the record not fetch from the workspace else it returns true.

4. %ROWCOUNT: It returns the count of rows.

Example:

DECLARE

CURSOR C IS SELECT * FROM EMP;

E C%ROWTYPE;

TOT NUMBER:=0;

S NUMBER;

BEGIN

OPEN C;

DBMS_OUTPUT.PUT_LINE(RPAD('EMPNO',15,'-')||RPAD('NAME',20,'-')||RPAD('JOB',15,'-')||'SALARY');

DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));

LOOP

FETCH C INTO E;

EXIT WHEN C%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(RPAD(E.EMPNO,15,'-')||RPAD(E.ENAME,20,'-')||RPAD(E.JOB,15,'-')||E.SAL);

S:=E.SAL;

--DBMS_OUTPUT.PUT_LINE(S);

TOT:=TOT+S;

END LOOP;

DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));

DBMS_OUTPUT.PUT_LINE('Total Number of Rows='||C%rowcount);

DBMS_OUTPUT.PUT_LINE('Total Salary='||TOT);

DBMS_OUTPUT.PUT_LINE('Average Salary='||ROUND(TOT/C%ROWCOUNT,2));

CLOSE C;

END;

Using For Loop:

DECLARE

CURSOR C IS SELECT * FROM EMP;

E C%ROWTYPE;

TOT NUMBER:=0;

CTR NUMBER:=0;

BEGIN

DBMS_OUTPUT.PUT_LINE(RPAD('EMPNO',15,'-')||RPAD('NAME',20,'-')||RPAD('JOB',15,'-')||'SALARY');

DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));

FOR E IN C LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(E.EMPNO,15,'-')||RPAD(E.ENAME,20,'-')||RPAD(E.JOB,15,'-')||E.SAL);

TOT:=TOT+NVL(E.SAL,0);

CTR:=C%ROWCOUNT;

END LOOP;

DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));

DBMS_OUTPUT.PUT_LINE('Total Number of Rows='||CTR);

DBMS_OUTPUT.PUT_LINE('Total Salary='||TOT);

DBMS_OUTPUT.PUT_LINE('Average Salary='||ROUND(TOT/CTR,2));

END;

PARAMETRIC CURSOR:

DECLARE

A NUMBER:=&DEPTNO;

CURSOR C(DNO NUMBER) IS SELECT * FROM EMP WHERE DEPTNO=DNO;

E C%ROWTYPE;

TOT NUMBER:=0;

CTR NUMBER;

BEGIN

OPEN C(A);

DBMS_OUTPUT.PUT_LINE(RPAD('EMPNO',15,' ')||RPAD('NAME',20,' ')||RPAD('JOB',15,' ')||'SALARY');

DBMS_OUTPUT.PUT_LINE(RPAD('_',60,'_'));

IF C%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20000,'Record Not Found');

END IF;

CLOSE C;

FOR E IN C(A) LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(E.EMPNO,15,' ')||RPAD(E.ENAME,20,' ')||RPAD(E.JOB,15,' ')||E.SAL);

TOT:=TOT+NVL(E.SAL,0);

CTR:=C%ROWCOUNT;

END LOOP;

DBMS_OUTPUT.PUT_LINE(LPAD('_',60,'_'));

DBMS_OUTPUT.PUT_LINE('Number of Rows='||CTR);

DBMS_OUTPUT.PUT_LINE('Totla Salary='||TOT);

DBMS_OUTPUT.PUT_LINE('Average Salary='||ROUND(TOT/CTR,2));

END;

PROCEDURES & FUNCTIOS:

Procedure: A procedure is a group of PL/SQL statements that you can call by name. It may or may not return a value.

Syntax:

CREATE OR REPLACE PROCEDURE <PROCEDURE NAME>(parameter datatype) IS/AS

LOCAL DECLARATION;

BEGIN

EXECUTED STATEMENTS;

EXCEPTION

EXCEPTION HANDLER;

END;

Execution of Procedure: EXEC <Procedure Name> (Input values);

Procedure divided into 2 parts

1. Procedure Specifications: Before IS/AS is called procedure specification.

2. Procedure Body: After IS/AS is call body.

Once the procedure is created successfully it will crate object code that will store in the server. This object code is called Pseudo Code.

There are three types of Parameters in Procedure. IN, OUT, INOUT.

IN parameter is readable but not writable. It’s constant.

OUT parameter is writable not readable.

INOUT parameter is both readable and writable.

CREATE OR REPLACE PROCEDURE EMP_DTLS(ENO IN NUMBER,NAME OUT VARCHAR2,SALARY OUT NUMBER,D_NAME OUT VARCHAR2) IS

BEGIN

SELECT E.ENAME,E.SAL,D.DNAME INTO NAME,SALARY,D_NAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND EMPNO=ENO;

DBMS_OUTPUT.PUT_LINE(RPAD('NAME',15,' ')||RPAD('SALARY',15,' ')||'Department');

DBMS_OUTPUT.PUT_LINE(LPAD('-',50,'-'));

DBMS_OUTPUT.PUT_LINE(RPAD(NAME,15,' ')||RPAD(SALARY,15,' ')||D_NAME);

END;

DECLAR VARIABLES;

SQL> VAR N VARCHAR2(30);

SQL> VAR S NUMBER;

SQL> VAR D VARCHAR2(30);

SQL> EXEC EMP_DTLS(7369,:N,:S,:D)

Functions: It should return a single value the advantage of function is used to Reusability, Readable:-Decreasing the coding.

Modularity: - Dividing the program into number of modules

Easy to Maintain.

Syntax: CREATE OR REPLACE FUNCTION <function name>( Parameter Name Datatype) RETURN Datatype IS/AS

DECLARATION STATEMENTS;

BEGIN

EXECTION STATEMENTS;

EXCEPTION

EXCEPTION HANDLERS;

END;

EXAMPLES:

CREATE OR REPLACE FUNCTION PRIM(A NUMBER) RETURN VARCHAR2 IS

BEGIN

FOR I IN 2..A-1 LOOP

IF MOD(A,I)=0 THEN

RETURN 'IT IS NOT PRIM NUMBER';

EXIT;

END IF;

END LOOP;

RETURN 'IT IS PRIM NUMBER';

END;

EXEC: SELECT PRIM(23) FROM DUAL;

Finding the Employee Experience in EMP TABLE:

CREATE OR REPLACE FUNCTION EXPE(A NUMBER) RETURN NUMBER IS

X NUMBER;

BEGIN

SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12,0) INTO X FROM EMP WHERE EMPNO=A;

RETURN X;

END;

EXEC: SELECT EXPE(EMPNO) FROM EMP;

SELECT * FROM EMP WHERE EXPE(EMPNO)=23;

Finding the Month from the Given Date:

CREATE OR REPLACE FUNCTION MTH(A DATE) RETURN NUMBER IS

BEGIN

RETURN TO_CHAR(A,'MM');

END;

EXEC: SELECT MTH(SYSDATE) FROM DUAL;

SELECT MTH(HIREDATE) FROM DUAL;

SELECT * FROM EMP WHERE MTH(HIREDATE)=3;

Knowledge Base Articles On Oracle Technology and Cloud Computing

Tags

Saturday, November 19, 2016

SQL Commands

Create Table

PARALLEL Example

Partitioned Table Example

Object Table Examples

REF Example

Create Table Using Select Statements:

CREATE TABLE EMP1 AS SELECT * FROM SCOTT.EMP;

CREATE TABLE DEPTNO AS SELECT * FROM SCOTT.DEPT ORDER BY DEPTNO ASC;

Constraints on Type Columns Example

Create Index

PARALLEL Example

COMPRESS Example

Cluster Index Example

Partitioned Tables And Indexes

We can devided the rows of a single table into multiple parts,Dividing the table's data in this manner is called "Partitioning the table". Partitioning is useful for very large tables and it is used for..

Improve the Qurey performance,it may be easier to load and delete the data in the partitions.

There are two type of prtitions

Range:

CREATE TABLE EMP1(ENO NUMBER PRIMARY KEY,NAME VARCHAR2(20),SAL NUMBER(8,2),

DEPTNO NUMBER REFERENCES DEPT(DEPTNO))

PARTITION BY RANGE(DEPTNO)

(PARTITION PART1 VALUES LESS THAN(20),

PARTITION PART2 VALUES LESS THAN(30));

Hash:

CREATE TABLE EMP1(ENO NUMBER PRIMARY KEY,NAME VARCHAR2(20),SAL NUMBER(8,2),

DEPTNO NUMBER REFERENCES DEPT(DEPTNO))

PARTITION BY HASH(DEPTNO)

PARTITIONS 2;

Composite Partitioning Tables

Partitioning Indexes

Local Prefixed Indexes

Global Prefixed Indexes

Create Procedures

Create Functions

Create Sequence

Create Schema

Create Synonym

Create Tablespace

Create Cluster

Adding Tables to a Cluster

Create Trigger

Create User

Create View

Create Materialized View

Alter Cluster

ADD Constraints:

ALTER TABLE EMP ADD CONSTRAINTS PK_EMPNO PRIMARY KEY(EMPNO);

ALTER TABLE EMP ADD CONSTRAINTS FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);

Alter Index

Alter Procedure

Alter Sequence

Alter Table

Drop Constraint Examples

Alter Tablespace

Alter Trigger

Alter View

DELETE COMMANDS

Delete Table

Drop Columns

Physical Delete

Delete Cluster

Delete Function

Delete Index

Delete Procedure

Delete Tablespace

Delete Trigger

Delete User

Delete View

NULL Function

GROUP BY Examples

SELECT EMP_NO,COUNT(EMP_NO) FROM EMP_ATTENDANCE_DTLS WHERE

SUBSTR(TO_CHAR(ENTRY_DT,'DD/MM/YY'),4,5)='03/05' GROUP BY EMP_NO

Display the Top or Bottom level rows

SELECT LEVEL, MAX(SAL) FROM EMP WHERE LEVEL= 1 OR LEVEL=2 CONNECT BY PRIOR SAL > SAL GROUP BY LEVEL

SELECT LEVEL, MIN(SAL) FROM EMP WHERE LEVEL=2 OR LEVEL =3 CONNECT BY PRIOR SAL < SAL GROUP BY LEVEL

SELECT EMPNO,ENAME,SAL FROM EMP A WHERE 3>=(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE B.SAL>=A.SAL) ORDER BY SAL DESC;