ORACLE SQL QUERIES FOR DEVELOPERS AND ADMINISTRATORS

Oracle is a Relational Data Base Management System (R.D.B.M.S).

SQL (Sequel): Structure Query Language is defined with following languages-

DDL - DATA DEFINITION LANGUAGE

Create

Alter

Drop

Truncate

DML - DATA MANIPULATION LANGUAGE

Insert

Update

Delete

DRL - DATA RETRIVAL LANGUAGE

Select

TCL - TRANSACTION CONTROL LANGUAGE

Commit

Roll Back

Save Point

SCL - SESSION CONTROL LANGUAGE

Alter Session

Set Role

SYCL - SYSTEM CONROL LANGUAGE

Alter System

DCL - DATA CONTROL LANGUAGE

Grant

Revoke

Sql Prompt : SQL>

UserName : Scott

Password : Tiger

HostString : Oracle

To clear the screen : cl scr (This commands comes under SQL *PLUS)

Clear device

Shift + Del + Enter

Table : It is a basic unit of storage of data or information in the form of rows and columns.

Data Types: To enter specific values into specified fields we require data types- Oracle8i data types are –

Char Min is 1 Max is 2000 bytes

Varchar2 Min is 1 Max is 4000 bytes

Number(S, P) S->0 to 38 digits P-> -84 to 127 digits

Date 1 Jan 4712 BC ---- 31 Dec 9999AD

Raw Min is 1 Max is 2000bytes

Long Min is 1 Max is 2GB

Long Raw Min is 1 Max is 2GB

LOB’S_____________

|→ CLOB - Character Lob Max is 4GB |

|→ BLOB - Binary Lob Max is 4GB

|→ Bile - Binary File O/S dependent

UDT’S - User Defined Data Types

Or ADT’S Abstract Data Types

|→Objects →Types (These or almost same as Structures in C)

|→Collections

|→VArrays (Arrays and Multi dimensional arrays in C)

|→Nested Tables

Sql Terminator is - ;

Date format is - DD-MON-YYYY

To create a table in Oracle:

Create Table table_name (column_name1 datatype1, column_name2 datatype2…………);

+ Enter Key

Output

Table Created (If Success)

In case of error

Sql/> Ed

This edits the note pad and we can change the sql statement which was already written and save and exit will display the new sql statement to the sql prompt……To execute the query

Sql/> /

This will execute the previous query with pupations…..

Conditions to give a Table Name:

1. First letter should be an alphabet

2. Max of 30 characters

3. No space in the middle

4. No special character allowed except $ and _

5. Oracle reserved words cannot be used as table names.

Note: Max Number of columns in a table can be up to 1000.

Example:

Sql/> Create table emp(eno number(4),ename varchar2(20),Sal number(8,2),dob date);

Table Created.

Sql/>

To see all the Tables Name that has been created in a user:

Sql/> select * from tab;

This displays all the table names that has been created

To desc the table with all data types

Sql/> desc table_name;

This describes the complete table with their column names and data types. This commands comes under SQL *PLUS

To insert a Record into the table.

Except for number data types we have to give single quotes to all values which are to be inserted.

Sql/> Insert into table_name1 values (1001,’Raja’, 6000,’12-sep-1979’);

1 row created.

If u want to insert multiple rows---

Sql/> Insert into table_name1 values (&no,’&name’, &no,’&date’);

This asks as follows-

Enter the no: “Enter the first column value”

Enter the name: “Enter the second column value”

Enter the no: “Enter the third column value”

Enter the date: “Enter the forth column value”

1 row created.

To insert values to only some particular columns

Sql/> Insert into emp(eno,ename) values (100,’gita’);

1 row inserted.

Again if u want to insert one more record just do…

Sql/> /

This do the same as above….to insert the record.

/ - To execute the previous command.

To display records in the table:

Select statement is used to display records in the Table.

Ex:

Sql>Select * from table_name;

NChar: National Character Set….It supports 32 languages and Oracle is NChar Language.

If u want to change the format of Date this can be done as follows-

Sql/> Alter Session set NLS_DATE_FORMAT = ‘DD/MON/YYYY’;

This changes the format of the date

Sql/> column Sal format 99999.99

This changes the format of the salary with two decimal points

To Delete the records from the table:

Sql/> Delete from table_name;

Number of rows deleted

Sql/> Delete table_name;

Number of rows deleted

To Rename the table

Sql/> rename OldName to NewName;

To Delete the table

Sql/> Drop table table_name;

To add a column to a table

Sql/> Alter table table_name add ( column_name data_type);

Sql/> Alter table emp add( Deptno number(4));

To modify a particular column data type for a table

Sql/> Alter table table_name modify( Column_name data)type);

Sql/> Alter table emp modify(ename varchar2(25));

Note: To reduce the size of a column or to change the data type of the column that particular column must not have any information.

To Delete particular column from a table:

Sql/> Alter table table_name drop column_name;

Sql/> Alter table table_name drop ( column_name1, column_name2….);

( To delete more than one particular column give directly the names with out any specification of the column and we cannot drop all the columns in a table at least one column must be present).

Sql/> select * from emp;

This display all the records from the table emp;

Sql/> select eno,ename from emp;

This display all the records ( only two columns) from the table emp;

Sql/>Select *from emp where sal>6000;

This display all the records whose sal is greater than 6000.

Relational Operators:

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

<> not equal to (!=)

= equal to

Logical Operators:

AND

BETWEEN…………AND

Sql/> Select *from emp where sal>6000 and deptno=10;

This display all the records in the emp table whose salary is greater than 6000 and he belongs to deptno 10.

Sql/> Select * from emp where sal>=3000 and sal<=9000;

This display all the records whose sal is between 3000 and 9000. This can also be written as

Sql/> Select * from emp where sal between 3000 and 9000;

Sql/>Select * from emp where deptno=10 or deptno=20;

This display all the records who belong to deptno 10 or 20.

Sql/>Select *from emp where deptno in(10,20,30);

This display all the records who belong to deptno 10,20,30.

Sql/>Select * from emp where deptno no in (10,20,30);

This display all the records who does not belong to deptno 10,20,30.

Sql/> Select * from emp where sal not between 300 and 2500;

This display all the records who sal is not in between 300 and 2500.

Sql/> Select *from emp where ename=’chaitanya’;

This display the record with the ename chaitanya.

Note : “Where” retrieve the information …Oracle is Case Sensitive.

Sql/> Select *from emp where ename like’chaitanya’;

Like is used for exact comparisons.

Char is used only for fixed length field and downward compatibility where as varchar2 is variant .

% - This symbol is used for wild character in oracle

_ - This symbol is also used as wild character in oracle.

Sql/> select * from emp where ename like ‘C%’;

To display all the records starting with C in their ename.

Sql/> Select * from emp where ename like ‘C%A’;

To display all the records starting with C and ending with A in their ename.

Sql/> Select *from emp where ename like ‘C__A’;

To display all the records starting with C and ending with A and in between only two characters in their ename.

Sql/>Select * from emp where ename not like’C%’;

To display all the records that are not starting with C in their ename.

Some of the commands that can be used are

Not in

Not between

Not like

Sql/>Delete from emp where sal=6000;

This deletes all the record with sal 6000.

*** Note: All the conditions applied for select are applicable for delete and update also.

Sql/> update emp set sal=6000;

This update all the records with sal as 6000.

Sql/> update emp set sal=sal+100;

This update all the records with increment of 100 in their sal;

Sql/>update emp set sal=sal+100 where sal>6000;

This updates all the records whose Sal is greater than 6000 with an increment of 100 in their Sal.

To display all the records whose Sal is NULL.

Sql/> Select * from emp where sal is NULL;

COMMIT, ROLLBACK, AND SAVEPOINT :

These are transaction control language

Sql> Commit;

To save up to date commands,

Note:

All DDL commands are auto command.

(Create, Alter, Drop, Truncate, Grant, Revoke)

Sql>Rollback;

it undo to the last previous commit

Commit;

sql> s->15;

sql> insert 2 rec;

sql> Save point A;

sql> delete 3 rec

sql>Save point B;

sql>insert 4 rec;

sql>save point c;

sql>del 3 rec;

sql>s->16 rec;

sql rollback to save point b;

sql>s->14;

sql>roll back to save point c (Error)

Note: When commit command is given all save point before given will be flushed.

GRANT and REVOKE:

These are used to grant or revoke privileges

GRANT:

sql>Grant all on emp to ov9292

Privileges:

-Insert

-Update

-Delete

-Select

-Alter

-Index

-Reference

Sql> Grant select, insert on emp to ov9292

to give some privileges

Sql> Grant all on emp to ov9292, ov9393;

Sql> Grant all on emp to public;

Privilege given not only to existing but also to the future login name also.

Sql> Grant all on emp to ov9292 with Grant option

Sql> Select * from ov9165.emp;

Sql> Insert into ov9165.emp values();

sql>Grant all on ov9165.Emp to ov9798

sql>Select * from ov9265.emp;

REVOKE:

Sql> Revoke all on emp from ov9292;

Sql> Revoke delete on emp from ov9292;

Sql> Select * from USER_TAB_PRIVS_MADE

To see whom you have given the privileges..

Sql>Select * from USER_TAB_PRIVS_RECD

To see the PRIVILEGES GRANTED TO YOU

TRUNCATE:

Sql>Truncate table emp;

Here, all the information is lost and table is empty but it will not delete.

Duplicating a Table:

Sql> Create table employee as select * from emp;

-It Creates table Employee with the same structure and information as emp these tables are known as pseudo table.

Sql> Create table employee as select * from emp where 1=2;

It create a table employee with same structure as emp table with out any information.

Note: Here any false condition can be given.

To Change name of column:

Sql> Create table employee (Eno,Ename,Sal) as select empno,ename,sal from emp;

Sql> Drop table emp;

Sql>Rename Employee to emp;

Sql> Desc Emp

Output:

Eno

Ename

Sal

Sql> Select Distinct (sal) from emp;

Note: We can pass only one parameter for distinct Command

Sql> Select * from emp order by sal

-Sorting in default Ascending Order

Sql> Select * from emp order by sal desc;

-Sorting in Descending order.

Sql> Select * from emp order by sal, Empno;

-First sort will be done on sal, If two sal are equal then sorting will be done by empno.

Sql> Select * from emp order by sal, empno desc;

Sal – Ascending order

Empo – Descending order

Sql> Select * from emp order by sal Desc, Empno desc;

Sal – Descending order

Empno – Descending order

Sql> Select * from emp order by sal desc, empno;

Sal – Descending order

Empno – Ascending order

Functions:

Single Row Functions:

● Char

● Date

● Numeric

● Conversions(conv)

● Misc

Group Functions

CHARACTER FUNCTIONS:

Sql> Select upper (‘New’) from dual;

-Displays given string in Upper Case (NEW)

Sql> Select upper(ename) from emp;

-Displays all enames of emp in upper case.

Sql> Select lower (‘Test) from dual;

-Displays given string in Lower Case (test)

Sql> Select initcap(‘ORACLE’) from dual;

-Displays given String with Initial Caps (Oracle)

Sql> select lpad(‘Oracle’,8,’*’) from dual;

Lpad

**Oracle

Sql> select rpad(‘Oracle’,8,’*’) from dual;

Rpad

Oracle**

Sql> Select Ascii(‘A’) from dual;

- Displays Ascii Value of Character ‘A’ (American Standard Code for Information Interchange)

Sql> Select chr(65) from dual;

- Displays Character value for given Ascii number

Sql> Select replace (‘Jack and Jill’,’J’,’B’) from dual;

- Replace the given character with the new character

Back and Bill

SQL> SELECT REPLACE('JACK AND JILL','JA','BC') FROM DUAL;

REPLACE('JACK

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

BCCK AND JILL

SQL> SELECT TRANSLATE('JACK AND JILL','J','B') FROM DUAL;

TRANSLATE('JA

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

BACK AND BILL

SQL> SELECT TRANSLATE('JACK AND JILL','JA','BC') FROM DUAL;

TRANSLATE('JA

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

BCCK CND BILL

SQL> SELECT LENGTH('JACK AND JILL') FROM DUAL;

-Gives the length of the given String.

LENGTH('JACKANDJILL')

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

SQL> SELECT REVERSE('RAMA') FROM DUAL;

REVE

---------

AMAR

SQL> SELECT SUBSTR('RAMARAO',3,4) FROM DUAL;

SUBS

----------

MARA

SQL> SELECT SUBSTR('VENKATA RAO',3) FROM DUAL;

SUBSTR

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

NKATA RAO

SQL> SELECT SUBSTR('VENKATA RAO',-3,3) FROM DUAL;

SUB

------

RAO

SQL> SELECT INSTR('RAMA KRISHNA PRASAD','A',3,2) FROM DUAL;

-Returns the position of the given Character in the given String.

INSTR('RAMAKRISHNAPRASAD','A',3,2)

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

SQL> SELECT INSTR('RAMAKRISHNA PRASAD','A',3,2) FROM DUAL;

INSTR('RAMAKRISHNAPRASAD','A',3,2)

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

SQL> SELECT LTRIM('VENKATA RAO','V') FROM DUAL;

LTRIM

-----------

ENKATA RAO

SQL> SELECT LTRIM('VENKATA RAO','VE') FROM DUAL;

LTRIM

-----------

NKATA RAO

SQL> SELECT LTRIM('RAJARAO','ARJ') FROM DUAL;

SQL> SELECT RTRIM('RAJA RAO','O') FROM DUAL;

RTRIM

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

RAJA RA

DATE FUNCTIONS:

SQL> SELECT SYSDATE FROM DUAL;

-Returns Current System Date.

SYSDATE

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

09-DEC-05

SQL> SELECT SYSDATE+3 FROM DUAL;

SYSDATE+3

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

12-DEC-05

SQL> SELECT SYSDATE-3 FROM DUAL;

SYSDATE-3

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

06-DEC-05

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;

LAST_DAY

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

31-DEC-05

SQL> SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;

ADD_MONTH

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

09-FEB-06

SQL> SELECT ADD_MONTHS(SYSDATE,-2) FROM DUAL;

ADD_MONTH

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

09-OCT-05

SQL> SELECT NEXT_DAY(SYSDATE,'FRI') FROM DUAL;

NEXT_DAY

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

16-DEC-05

SQL> SELECT NEXT_DAY(SYSDATE,'SUN') FROM DUAL;

NEXT_DAY

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

11-DEC-05

SQL> SELECT MONTHS_BETWEEN(SYSDATE,'5-MAR-2005') FROM DUAL;

MONTHS_BETWEEN(SYSDATE,'5-MAR-2005')

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

9.1547805

SQL> SELECT MONTHS_BETWEEN(SYSDATE,'5-MAR-2006') FROM DUAL;

MONTHS_BETWEEN(SYSDATE,'5-MAR-2006')

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

-2.845209

SQL> SELECT MONTHS_BETWEEN(SYSDATE,'09-JAN-2006') FROM DUAL;

MONTHS_BETWEEN(SYSDATE,'09-JAN-2006')

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

-1

SQL> SELECT MONTHS_BETWEEN(SYSDATE,'09-NOV-2005') FROM DUAL;

MONTHS_BETWEEN(SYSDATE,'09-NOV-2005')

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

NUMERICAL FUNCTIONS:

SQL> SELECT SQRT(64) FROM DUAL;

SQRT(64)

---------

SQL> SELECT MOD(7,2) FROM DUAL;

MOD(7,2)

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

SQL> SELECT POWER(3,2) FROM DUAL;

POWER(3,2)

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

SQL> SELECT SIGN(123) FROM DUAL;

SIGN(123)

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

SQL> SELECT SIGN(-123) FROM DUAL;

SIGN(-123)

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

-1

SQL> SELECT LOG(10,100) FROM DUAL;

-Returns the value of Log ₁₀100

LOG(10,100)

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

SQL> SELECT LOG(10,2) FROM DUAL;

-Returns the value of Log₁₀2

LOG(10,2)

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

.30103

SQL> SELECT EXP(2) FROM DUAL;

- Returns the value of e²

EXP(2)

---------

7.3890561

SQL> SELECT LN(2) FROM DUAL;

● Returns the value of Log_e 2

●

LN(2)

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

.69314718

SQL> SELECT SIN(45) FROM DUAL;

SIN(45)

-----------

.85090352

SQL> SELECT COS(45) FROM DUAL;

COS(45)

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

.52532199

SQL> SELECT TAN(45) FROM DUAL;

TAN(45)

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

1.6197752

SQL> SELECT SINH(45) FROM DUAL;

SINH(45)

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

1.747E+19

SQL> SELECT COSH(45) FROM DUAL;

COSH(45)

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

1.747E+19

SQL> SELECT TANH(45) FROM DUAL;

TANH(45)

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

SQL> SELECT CEIL(123.45) FROM DUAL;

CEIL(123.45)

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

124

SQL> SELECT FLOOR(123.45) FROM DUAL;

FLOOR(123.45)

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

123

CONVERSIONS:

SQL> SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;

----

SQL> SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL;

TO_

-------

DEC

SQL> SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY BC') FROM DUAL;

TO_CHAR(SYSDA

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

09-12-2005 AD

SQL> SELECT TO_CHAR(SYSDATE,'HH') FROM DUAL;

----

SQL> SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;

----

SQL> SELECT TO_CHAR(SYSDATE,'HH24:MI:SS AM') FROM DUAL;

TO_CHAR(SYS

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

19:19:53 PM

SQL> SELECT * FROM SCOTT.EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> SELECT ROUND(101,-2) FROM DUAL;

ROUND(101,-2)

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

100

SQL> SELECT TRUNC(26.12,-1) FROM DUAL;

TRUNC(26.12,-1)

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

SQL> SELECT ROUND(26.52,-1) FROM DUAL;

ROUND(26.52,-1)

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

SQL> SELECT ROUND(26.27,-1) FROM DUAL;

ROUND(26.27,-1)

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

MISC FUNCTIONS:

SQL> SELECT USER FROM DUAL;

USER

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

CHAITANYA

SQL> SHOW USER

USER is "CHAITANYA"

SQL> SELECT UID FROM DUAL;

UID

---------

SQL> SELECT * FROM ALL_USERS;

USERNAME USER_ID CREATED

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

SYS 0 01-MAR-99

SYSTEM 5 01-MAR-99

OUTLN 11 01-MAR-99

DBSNMP 20 01-MAR-99

MTSSYS 28 01-MAR-99

AURORA$ORB$UNAUTHENTICATED 25 01-MAR-99

SCOTT 26 01-MAR-99

DEMO 27 01-MAR-99

ORDSYS 30 01-MAR-99

ORDPLUGINS 31 01-MAR-99

MDSYS 32 01-MAR-99

CTXSYS 35 01-MAR-99

PRASAD 37 08-MAR-05

13 rows selected.

SQL> SET PAUSE ON

SQL> SELECT VSIZE('JIM CARRY') FROM DUAL;

SQL> SELECT NVL(COMM,200) FROM SCOTT.EMP;

NVL(COMM,200)

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

200

300

500

200

1400

200

14 rows selected.

SQL> SELECT MAX(SAL) FROM EMP;

-Returns the Maximum value from that column.

● MIN() -- Returns Minimum Value

● AVG() -- Average

● SUM() -- Sum

● STDDEV() -- Standard Deviation

● VARIANCE() – Variance

SQL> SELECT COUNT(*) FROM EMP;

● This will display the number of values.

COUNT(*)

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

SQL>SELECT COUNT(SAL) FROM EMP;

COUNT(SAL)

----------

SQL>SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO;

DEPTNO MAX(SAL)

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

10 50000

20 3000

30 2850

SQL>SELECT DEPTNO,COUNT(*),MAX(SAL) FROM EMP GROUP BY DEPTNO;

DEPTNO COUNT(*) MAX(SAL)

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

10 4 50000

20 5 3000

30 6 2850

SQL>SELECT DEPTNO, MAX(SAL), COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>5;

DEPTNO MAX(SAL) COUNT(*)

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

30 2850 6

SET OPERATIONS:

● UNION

● UNION ALL

● INTERSECT

● MINUS

UNION: It gives all the values from both queries with out duplicates.

SQL> SELECT EMPNO FROM EMP1

UNION

SELECT EMPNO FROM EMP2;

UNION ALL: It gives all the values from both queries including duplicates.

SQL> SELECT EMPNO FROM EMP1

UNION ALL

SELECT EMPNO FROM EMP2

INTERSECT: It gives the values which are common from both the queries.

SQL> SELECT EMPNO FROM EMP1

UNION

SELECT EMPNO FROM EMP2;

MINUS: It gives all the values from first query except common values in the second query

SQL> SELECT EMPNO FROM EMP1

MINUS

SELECT EMPNO FROM EMP2;

JOINS:

SIMPLE JOINS

-EQUI JOINS

-NON EQUI JOINS

OUTER JOINS

SELF JOINS

SIMPLE JOIN:

SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME

FROM EMP, DEPT;

The symbol ‘=’ is known as Equi Join Other than this symbol (>, <, <>, ect) are known as Non Equi Join

OUTER JOIN:

This is used to retrieve information from both matching records and non matching records.

SQL> SELECT EMP.EMPNO, EMP.ENAME, EMP.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO (+);

SELF JOIN:

The tables which pointed to it self are known as Self Joined.

SQL> SELECT A.EMPNO, A.SAL, B.EMPNO, B.SAL FROM EMP A,

EMP B WHERE A.EMPNO=B.EMPNO AND A.SAL>B.SAL;

SUB-QUERIES:

Two queries combined to form sub queries

SQL> SELECT * FROM EMP WHERE SAL= (SELECT MAX (SAL) FROM EMP);

To find the Second maximum salary:

SQL> SELECT MAX(SAL) FROM EMP WHERE

SAL<(SELECT MAX(SAL) FROM EMP);

Nested Sub-Queries:

SQL> SELECT * FROM EMP WHERE

SAL=(SELECT MAX(SAL) FROM EMP WHERE

SAL<(SELECT MAX(SAL) FROM EMP));

Note:

1. If the number of ‘Select’ statements are one then it is a query

2. If the number of ‘Select’ statements are two it is called sub query.

3. If the number of ‘Select’ statements are more than two then it is called a nested sub query.

4. The maximum number of ‘Select’ statements in a nested sub query are 255.

Co-Related Sub Query:

The number of times the parent query executes that number of times the child query also executes known as correlated sub query.

To display the information of employee who are earning more than average sal of the respective departments.

SQL> SELECT * FROM EMP A

WHERE SAL>(SELECT AVG(SAL) FROM EMP

WHERE DEPTNO=A.DEPTNO);

CONSTRAINTS:

Constraints are some conditions to be imposed on a table.

DOMAIN INTEGRITY CONSTRAINTS

● NOT NULL

● CHECK

ENTITY INTEGRITY CONSTRAINTS

● UNIQUE

● PRIMARY KEY

REFERENCIAL INTEGRITY CONSTRAINTS

● FOREIGN KEY (Or) REFERENCIAL

A constraints can be given on a table is two levels

1. Column Level

2. Table Level

Column Level:

If we want to given single constraint for single column we have to go for column level.

Table Level:

If we want to give single constraint for one column or more than one column we have to go for table level.

NOT NULL:

SQL> Create table emp

( Empno number(5) constraint nay not null,

Ename varchar2(15),

Sal number(14,2));

SQL> Create table emp

(Empno number (5) Not Null,

Ename varchar2 (15),

Sal number (14, 2));

Note: A NOT NULL Constraint cannot be given in table level.

SQL> Alter table emp modify (Ename Varchar2 (20) Constraint Nk3 Not Null);

The Existing column must not have any null value (Ename) for above Example.

UNIQUE CONSTRAINT (Column Level):

It doesn’t allow you to enter any duplicates.

Sql> Create Table Emp

( Empno Number(4) Constraint Uk1 UNIQUE,

Ename Varchar2 (30),

Sal Number (14, 2));

Sql> Create Table Emp

( Empno Number(4) Constraint Uk1 UNIQUE,

Ename Varchar2 (20) Constraint Uk2 UNIQUE,

Sal Number (14,2));

● It will check individually

UNIQUE CONSTRAINT (Table Level):

1) Sql> Create Table Emp

(Empno Number (4),

Ename Varchar(20),

Sal Number (14, 2),

Constraint Uk3 Unique (Empno));

- In above example there is a Unique Constraint defined on the field Empno. i.e, No duplication is allowed on this field.(Only one constraint is defined)

2) Sql> Create Table Emp

( Empno Number(4),

Ename Varchar2 (20),

Sal Number (14, 2),

Constraint Uk3 Unique (Empno),

Constraint Uk4 Unique (Ename));

- In the above example there are two Unique Constraint Defined on the fields Empno and Ename. i.e. We cannot duplicate the values of both the fields.

3) Sql> Create Table Emp

(Empno Number (4),

Ename Varchar2 (20),

Sal Number (14, 2),

Constraint Uk3 Unique (Empno, Ename));

● In above example there is a single unique constraint defined on both the fields (i.e. Empno and Ename). This is also known as Composite Unique Constraint means together they cannot be duplicated.

COMPOSITE UNIQUE CONSTRAINT:

The maximum number of columns you can specify in a COMPOSITE UNIQUE CONSTRAINT is 32 in Oracle 8 and 16 in Oracle7.

By default every column has null Constraint so it can be used in table level.

Sql> Alter table Emp Add Constraint Uk3 Unique (Empno);

Note: In above example the existing column must not have any duplicate values.

CHECK CONSTRAINT (Column Level):

Sql> Create Table Emp

(Empno Number (4),

Ename Varchar2 (20),

Comm Number (8, 2),

Sal Number (14, 2) Constraint Ck Check(Sal>1500));

- In the above example there is a Check Constraint defined on the ‘Sal’ field to check that it is grater than 1500.

CHECK CONSTRAINT (Table Level):

Sql> Create table Emp

(Empno Number (4),

Ename Varchar2 (30),

Sal Number (14, 2),

Constraint Ck Check (Sal>1500));

● This is similar to above Example.

Sql> Create table emp

(Empno Number (4),

Ename Varchar2 (20),

Comm Number (8,2),

Sal Number (14, 2),

Constraint Ck Check (Sal > Comm));

- In above example there is a Check condition Sal>Comm it can only be possible in table level but, not in the column level. (i.e. Comparison between two columns).

Sql> Alter Table Emp Add Constraint Ck3 Check (Sal between 3000 and 9000);

PRIMARY KEY CONSTRAINT:

It is a combination of Unique and Not Null Constraint.

Column Level:

Sql> Create Table Dept

(Deptno Number (4) Constraint Pk Primary Key,

Dname Varchar2 (20),

Loc Varchar2 (30));

Note: A Table can have only one PRIMARY KEY Constraint

Table Level:

Sql> Create Table Dept

(Deptno Number (4),

Dname Varchar2 (20),

Loc Varchar2 (30),

Constraint Pk1 Primary Key (Deptno));

Sql> Create Table Dept

(Deptno Number (4),

Dname Varchar2 (30),

Loc Varcha2 (30),

Constraint Pk2 Primary Key (Deptno,Dname));

Note: This is known as Composite Primary Key Constraint. In this case it check for the uniqueness in the combined columns and Not Null for individual Columns.

1. A table can have only one primary key constraint

2. We can give a simple primary key constraint for more than one column then it is called as composite primary key constraint.

3. The maximum number of columns you can specify in a composite primary constraint are

1. Oracle 8 – 32

2. Oracle 7 – 16.

Sql> Alter table dept Add Constraint Pk2 Primary Key(Deptno);

Note: The existing must not have null or duplicate values.

REFERENTIAL INTEGRITY:

This is used to provide link between Master and Child Tables

Parent Table(Master) Child Table

DEPT

Deptno

DName

Loc

EMP

Empno

Ename

Deptno

<- Parent Key / Primary Key (Reference)

Child Key / Foreign Key ->

Note: The parent key column in the parent table should have either a primary key constraint or a unique constraint.

For Example: In the department table the deptno column should have either a primary key constraint or unique constraint.

Column Level:

Sql> Create table Emp

(Empno Number (4),

Ename Varchar2 (30),

Deptno Number (2),

Constraint FK references Dept (Deptno));

Sql> Create table Emp

(Empno Number (4),

Ename Varchar2 (30),

Deptno Number (2) Constraint Fk1 References Dept (Deptno) on delete Cascade);

Note: In the above example if the master table row is deleted then all child table rows corresponding references to that row will be deleted by using “On Deleted Cascade”.

i.e. The use of “On Delete Cascade” is if we delete a master record the corresponding child records automatically deleted.

Table Level:

Sql> Create table emp

(Empno Number (4),

Ename Varchar2 (30),

Deptno Number (3),

Constraint Fk1 Foreign Key (Deptno) References Dept (Deptno));

Sql> Alter table emp add constraint Fk foreign key (Deptno) references Dept (Deptno);

Note: What ever the value of deptno in emp table, it should be there in deptno of dept table.

Sql> Create table emp

(Empno Number (4) constraint Nk Not Null initially deferred deferrable);

If we place ‘INITIALLY DEFERRED DEFERRABLE’ in the query then it will not check every time, the constraints only check at commit. If the entry is wrong it automatically rollback.

The following Queries are used to see the Constraints:

SQL>select constraint_name, constraint_type from user_constraints;

CONSTRAINT_NAME C

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

PK_DEPT P

PK_EMP P

FK_DEPTNO R

SYS_C001257 P

Sql> Select * from user_cons_columns;

Note: If column violates the constraint we can enforce the constraint as follows.

Sql> Alter table EMP enforce constraint <Constraint-name>;

Sql> Alter table EMP modify Constraint PK disable/enable/drop;

Sql> Alter table EMP Disable / Enable / Drop Constraint PK;

Disable: It will disable the constraint

Enable: It will again invokes the constraint.

Drop: Completely deletes the constraint.

DATA BASE OBJECTS:

Any object which is stored in data base either directly or indirectly is called as data base object.

View: (An imaginary object)

A view doe’s not occupy any table space.

1. Sql>Create view ABC as Select * from Emp;

2. Sql> Create view ABC as select eno, ename from emp;

3. Sql> Creat view ABC as select * from emp where deptno=10;

4. Sql> Create view ABC as select * from emp where deptno=10 with check option;

5. Sql> Create view ABC as select emp.empno,emp.ename,dept.dno,dept.dname from emp,dept where emp.dno=dept.dno;

Note: If a view is based on joins or set operators or group function those type of views are neither insertable nor updatable nor deletable.(DMlL commands will not work)

6. Sql> Create view ABC (Dno,msal) as (Select Deptno, ,max(Sal) from emp group by deptno);

7. Sql> Create view ABC as select * from emp with read only;

Force View:

Sql> Create force view ABC as select * from custom;

Sql> create table custom(Custid number(4), custname varchar2(20));

To work the view:

Sql> Alter view ABC compile; (Before 7.1)

SYNONYM: (Alias Name)

Owner name is masked

- Private Synonym (User can create)

- Public Synonym (DBA)

Sql> Create Synonym KLM for Emp;

Note: We must use user name

Sql> Create public synonym KLM for emp;

If we grant table KML to any user he can directly use the table as “select * from KML;”

INDEX:

Sql> Create index ind on emp (Empno);

Sql> Select * from emp where empno=7900;

It will take less time to execute where you create index. Maximum of 5 index will give better performance.

Sql> Create index Ind on emp(Empno,Ename);

Maximum of 32 fields can be indexed

Sql> Create index ind on emp(Empno) local;

Where if the table is created based on partition. it will create the same values to index.

Local Partition Index:

Sql> Create index ind on emp(Empno) global partition by range(Empno)

(Partition s1 values less than (2000),

Partition s2 values less than (5000),

Partition s3 values less than (Maxvalue));

Global is a key word which is used if the table is not a partition table and if we want to create index table as partition.

Global Partition Index:

Note: If the table is partitioned on two columns and if the index is given on first column it is called as prefixed partition index.

SEQUENCE:

Create sequence

Start with 1

Increment by 2

Maxvalue 20

Cycle

Cache 5;

Sql> Select seq.nextval from dual;

Sql> Select seq.currval from dual.

Sysdate, nextval, currval from pseudo columns.

Sql> Insert into cust values(Seq.nextval, ‘&Con’);

Number is taken from the sequence in this case.

Sql> Select view_name from user_views;

Sql> Select synonym_name from user_synonyms;

Sql> Select index_name from user_indexes;

Sql> Select Sequence_name from user_sequences;

Sql> Drop Sequence <Seq_name>;

Sql> Drop Index <Ind>;

Sql> Drop View <aol>;

Sql> Drop Synonym <syn>;

Index Organised Tables:

Sql> Create table emp(Empno number(4) primary key) organization index;

1. These types of table’s doesnot have rowid.

2. Information retrieval is faster compared to ordinary tables.

3. This type of table should have primary key constraints.

User Defined Types:

UDT’s (Abstract Data Types (ADT’s))

- Objects -> Types

- Collections

-> Varrays

-> Nested Tables

Types:

A type does not occupy any table space. Type is similar to structure in C-Language.

Sql> Create type dept_ty as object (Deptno Number(2), Dname varchar2(30))

Sql> Create table Employee(Empno number(4), ename varchar2(30), dept_info dept_ty);

Sql> Insert into emp values(1,’Abc’,Dept_ty(20,’Acc’));

Sql> Select * from emp;

Sql> Select a.dept_info.deptno from Employee A;

Sql> Select eno, a.dept_info.deptno, a.dept_info.dname from emp a;

To See the types created:

Sql> Select type_name from user_types;

Note:

1. The columns in the types are called attributes.

2. These can be any no. of attributes in a single type.

3. Table dependent types cannot be dropped.

Sql> Drop type dept_ty;

Sql> Drop type Dept_ty force;

The columns of the table related to dept_ty also deleted and it will not create again even the type is again created.

Varrays: (Varying Arrays)

Set of elements of similar data types is called as Varray.

Sql> Create type books_var as varray(5) of varchar2(30);

Sql> Create table STUD (Studno number(4), Books Books_Varr);

Sql> Insert into Stud values(1001,books_varr(‘c’,’c++’,’java’,’vb’,’.net’));

Sql> select * from stud;

Updating is also possible.

Nested Tables:

A table inside a table is called nested table.

Sql> Create type books_ty as object (Bookno number(4), Author varchar2(20));

Sql> Create type books_nt as table of books_ty;

Sql> Create table student(Studno number(4), Books Books_nt)

Nested table books

Store as books_tab;

Sql> Insert into student values (100,books_nt(books_ty(101,’jack’),books_ty(102,’jill’),books_ty(103,’John’));

Sql> Select * from student;

Sql> Select * from the(select books from student where studno=1001); (Flattened Queries)

Sql> Insert into the (Select books from student where studno=1001) values(104,’jim’);

Sql> Update the (Select books from student where studno=1001) set author_name=’Johmmy’ where bookno=102;

Sql> Delete from the(Select books from student where studno=1001) where bookno=104;

Object Views:

1. Create type sal_ty as object (sal number(4), comm. Number(4));

2. Create view ABC(Empno,Salinfo) as (select empno,sal_ty(sal,comm.) from emp);

Object Tables:

Sql> Create type dept_ty as object (Deptno number(4), Dname varchar2(20));

Sql> Create table dept of dept_ty;

Sql> Desc dept

Sql> Insert into dept values(10,’all’);

Sql> Select * from dept;

Sql> Select ref(A) from dept A;

-Address of the record (entered as pointer in c).

Sql> Create table emp(Empno number(4), ename varchar2(30), dept_info ref dept_ty);

Sql> Insert into Emp Select 1001,’Rani’,ref(A) from Dept A where deptno=10;

Sql> Select deref(A deptinfo) from emp A;

SQL *PLUS

Sql> Set numwidth 4 // To change the number width

Set feedback off/on //To display number of rows

Set space 3 // To leave 3 spaces between columns

Set Underline *

Set heading off/on

Set pagesize 18

Set sqlterminator *

Set define #

Set verify off/on

Set Sqlprompt OracleSql>

Set autocommit on/off

Set editfile sqlOracle

Set Time on

Set timing on/off This depends on server spec

SetTtitle ‘Oracle Corp.’ [on/off]

Set Btitle ‘End of Report’ [on/off]

THANK YOU FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ON BLOG https://chaitanyaoracledba.blogspot.com/

Saturday, July 25, 2020

ORACLE SQL QUERIES FOR DEVELOPERS AND ADMINISTRATORS

To display records in the table:

To Delete the table

To modify a particular column data type for a table

ITIL Process

Blog Archive

Categories