Oracle Tips and Tricks--SQL-I

[debasisdas]

This thread contains some useful tips in oracle 9i/10g which the forum members might find useful.
I would like to share my knowledge with members by adding some new contents also in days to come.

Following is the list of PSEUDO-COLUMNS IN ORACLE

USER
SYSDATE
CURRVAL
NEXTVAL
LEVEL
ROWID
ROWNUM
CONNECT_BY_ISLEAF
CONNECT_BY_ISCYCLE
VERSIONS_STARTSCN
VERSIONS_STARTTIME
VERSIONS_ENDSCN
VERSIONS_ENDTIME
VERSIONS_XID
VERSIONS_OPERATION

Note :--The last eight are only supported in Oracle version 10 onwards.

[debasisdas]

This is the list of Oracle (SQL) supported data types.

DATA TYPES
========================
NUMBER(P,S)
P-1 TO 38
S- -84 TO 127
-----------------------------------
CHAR(S)----2000 BYTES

VARCHAR(S)
VARCHAR2(S)
S-4000 BYTES

LONG ---UPTO 2GB

BINARY_FLOAT-------32 BIT FLOATING POINT
BINARY_DOUBLE-------64 BIT FLOATING POINT

RAW---BINARY DATA 2000 BYTES
LONG RAW ----2GB


LOB
BLOB,CLOB,NCLOB,BFILE

TEMPORAL DATA TYPE IN ORACLE
=============================
DATE type:-
I.DATE
TIMESTAMP type:-
I.TIMESTAMP
II.TIMESTAMP WITH TIME ZONE
III.TIME STAMP WITH LOCAL TIME ZONE.
INTERVAL type:-
I.INTERVAL YEAR TO MONTH
II.INTERVAL DAY TO SECOND.

INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. This datatype is useful for representing the difference between two datetime values when only the year and month values are significant.

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. This datatype is useful for representing the precise difference between two datetime values.

CREATE TABLE TIMETABLE
(
ID            NUMBER(38),
DT        DATE,
TR_TS         TIMESTAMP(6),
TR_TS_TZ        TIMESTAMP(3) WITH TIME ZONE,
TR_TS_LTZ       TIMESTAMP(3) WITH LOCAL TIME ZONE,
TR_DURATION     INTERVAL YEAR(2) TO MONTH,
TR_D2S          INTERVAL DAY(3) TO SECOND(6)
);
 

DATE:-DATE DATA TYPE HOLDS DATE AS WELL AS TIME INFORMATION.INTERNAL TO DATABASE ,A DATE IS A FIXED LENGTH,7 BYTE FIELD.THE 7 BYTES REPRESENTS
CENTURY,YEAR,MONTH,DATE,HOUR,MINUTE,SECOND.

TIMESTAMP:-A TIMESTAMP INCLUDES ALL THE COMPONENTS OF DATE DATA TYPE PLUS FRACTIONAL SECONDS.

TIMESTAMP WITH TIME ZONE:-IT EXTENDS THE TIME STAMP DATATYPE TO INCLUDE A TIMEZONE DISPLACEMENT.THE TIME ZONE DISPLACEMENT IS THE TIME DIFFERENCE IN HOURS AND MINUTES BETWEEN LOCAL TIME ZONE AND UTC.

TIMESTAMP WITH LOCAL TIME ZONE:-ITS VARIANT OF THE TIME STAMP WITH TIME ZONE DATA TYPE.IN THIS CASE THE TIME ZONE DISPLACEMENT IS NOT STORED AS PART OF THE COLUMN DATA.THE DATA STORED IN THE DATABSE IS NORMALISED TO THE TIME ZONE OF THE DATABASE.THE ORIGINAL TIMEZONE IS NOT PRESERVED.AT THE TIME OF RETRIVING THE DATA ORACLE RETURNS IN THE TIME ZONE OF THE USER SESSION.


NOTE:----Oracle does not support the data type BOOLEAN in SQL.

[debasisdas]

Some of the SQL: SELECT Statement
--------------------------------------------------------------------------------

The SELECT statement allows you to retrieve records from one or more tables in your database.

The syntax for the SELECT statement is:

SELECT columns[,cloumn....] FROM table [WHERE condition];


Example #1
Let's take a look at how to select all fields from a table.

SELECT * FROM suppliers WHERE city = 'NewDelhi';

In the above example, i've used * to signify that i wish to view all fields from the suppliers table where the supplier resides in NewDelhi.

Example #2
You can also choose to select individual fields as opposed to all fields in the table.

For example:

SELECT name, city, state FROM suppliers WHERE supplier_id >= 1000;
 

This select statement would return all name, city, and state values from the suppliers table where the supplier_id value is greater than or equals to 1000.



Example #3
You can also use the select statement to retrieve fields from multiple tables.

SELECT orders.order_id, suppliers.name FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;
 

The result set would display the order_id and suppier name fields where the supplier_id value existed in both the suppliers and orders table.

Note:--The above query contains Join.

Example #4

Selecting a few random records in a table in Oracle
Oracle has the sample keyword that can be used in select statements like shown below:

select * from inventory sample(40);

This statement will select approximatly 40 percent of the data in the table.
This feature requires the sample scan option.

SQL: DISTINCT Clause
--------------------------------------------------------------------------------
The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements.

The syntax for the DISTINCT clause is:

SELECT DISTINCT columns FROM tables [WHERE condition];

Example #1
Let's take a look at a very simple example.

SELECT DISTINCT JOB FROM EMP;

This SQL statement would return all unique jobs from the emp table.

Example #2

The DISTINCT clause can be used with more than one field.

For example:

SELECT DISTINCT city, state FROM suppliers;

This select statement would return each unique city and state combination. In this case, the distinct applies to each field listed after the DISTINCT keyword.

Example #3
The DISTINCT clause can be used with COUNT() also.

For example:

SELECT count(DISTINCT job) FROM emp;

This select statement would return the total number sof distinct jobs i.e.--it will count each jobs only once irrespective of the number of occurrances in the entire table.

[debasisdas]

SQL: WHERE Clause
=================
The WHERE clause allows you to filter the results from an SQL statement - select, insert, update, or delete statement.

Example #1

SELECT * FROM suppliers WHERE supplier_name = 'HCL';

In this first example, i've used the WHERE clause to filter the results from the suppliers table. The SQL statement above would return all rows from the suppliers table where the supplier_name is HCL. Because the * is used in the select, all fields from the suppliers table would appear in the result set.


Example #2

SELECT supplier_id FROM suppliers WHERE supplier_name = 'HCL'
or supplier_city = 'HYDERABAD';
 

We can define a WHERE clause with multiple conditions. This SQL statement would return all supplier_id values where the supplier_name is HCL or the supplier_city is HYDERABAD.


Example #3

SELECT suppliers.suppler_name, orders.order_id FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_city = 'BANGLORE';
 

We can also use the WHERE clause to join multiple tables together in a single SQL statement. This SQL statement would return all supplier names and order_ids where there is a matching record in the suppliers and orders tables based on supplier_id, and where the supplier_city is BANGLORE.

[debasisdas]

SQL: "AND" Condition
=================
The AND condition allows you to create an SQL statement based on 2 or more conditions being satisfied. It can be used in any valid SQL statement - select, insert, update, or delete. The command will execute only if both the conditions satisfies.

The syntax for the AND condition is:

SELECT columns FROM tables WHERE column1 = 'value1' and column2 = 'value2';

The AND condition requires that each condition be must be met for the record to be included in the result set. In this case, column1 has to equal 'value1' and column2 has to equal 'value2'.


Example #1

The first example that we'll take a look at involves a very simple example using the AND condition.

SELECT * FROM suppliers WHERE city = 'NewDelhi' and type = 'PC Manufacturer';
 

This would return all suppliers that reside in NewDelhi and are PC Manufacturers. Because the * is used in the select, all fields from the supplier table would appear in the result set.

Example #2
The following example checks for any duplicacy. To find out if there is any employee who belings to two differnt departments.

 select * from emp where deptno=10 and deptno=20;
 

The above query returns if there is any duplicate entry or else no records.

Example #3
Next example demonstrates how the AND condition can be used to "join" multiple tables in an SQL statement.

SELECT orders.order_id, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'SPA';
 

This would return all rows where the supplier_name is SPA. And the suppliers and orders tables are joined on supplier_id. You will notice that all of the fields are prefixed with the table names (ie: orders.order_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the suppliers and orders tables.

In this case, the result set would only display the order_id and supplier_name fields (as listed in the first part of the select statement.).

Note : if any condition fails the statment will not execute.

[debasisdas]

SQL: "OR" Condition
--------------------------------------------------------------------------------
The OR condition allows you to create an SQL statement where records are returned when any one of the conditions is satisfied. It can be used in any valid SQL statement - select, insert, update, or delete.

The syntax for the OR condition is:

SELECT columns FROM tables WHERE column1 = 'value1' or column2= 'value2';

The OR condition requires that any of the conditions must satisfy for the record to be included in the result set. In this case, column1 has to equal 'value1' OR column2 has to equal 'value2'.

Example #1
The first example that we'll take a look at involves a very simple example using the OR condition.

SELECT * FROM suppliers WHERE city = 'NewDelhi' or city = 'Bombay';

This would return all suppliers that reside in either NewDelhi or Bombay. Because the * is used in the select, all fields from the suppliers table would appear in the result set.


Example #2
The next example takes a look at three conditions. If any of these conditions is met, the record will be included in the result set.

For example:

SELECT supplier_id FROM suppliers WHERE name = 'HCL' or name = 'INFY' or name = 'SPA';
 

Example #3
The next example uses ORING on two different fields.

select * from emp where job='MANAGER' OR SAL >=3000;
 

The above query returns records where either job is MANAGER or sal >= 3000;

[debasisdas]

SQL: Combining the "AND" and "OR" Conditions
--------------------------------------------------------------------------------
The AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement - select, insert, update, or delete.

When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition.

Example #1
The first example that we'll take a look at an example that combines the AND and OR conditions.

SELECT *
FROM suppliers
WHERE (city = 'Kolkota' and name = 'IBM')
or (city = 'Hyderabad');
 

This would return all suppliers that reside in Kolkota whose name is IBM and all suppliers that reside in Hyderabad. The brackets determine what order the AND and OR conditions are evaluated in.


Example #2
The next example takes a look at a more complex statement.

For example:

SELECT supplier_id FROM suppliers WHERE (name = 'SPA')
or (name = 'HP' and city = 'Cuttack')
or (name = 'DAS' and status = 'Active' and city = 'Pune');
 

This SQL statement would return all supplier_id values where the supplier's name is SPA or the name is HP and the city is Cuttack or the name is DAS, the status is Active, and the city is Pune.

Example #3
The next example takes a look at swapping the position of OR and AND .

select * from emp where deptno=10 and job='MANAGER' OR SAL >2000;
select * from emp where deptno=10 OR job='MANAGER' AND SAL >2000
 

Ckeck U get different output for both the queries.
Note--Since AND enjoys higher precedence over OR ,if in a astatment both the logical operators are included AND executes first.To overcome this use brackets.()


Try brackets n see the difference

[debasisdas]

List of Character / String Functions in Oracle
===================================
Ascii
AsciiStr
Chr
Compose
Concat
Concat using ||
Convert
Decompose
Dump
Initcap
Instr
Length
Lower
Lpad
Ltrim
Replace
Rpad
Rtrim
Soundex
Substr
Translate
Trim
Upper
VSize

Some of the useful Mathematical Functions in oracle

Abs
Acos
Asin
Atan
Atan2
Avg
Bin_To_Num
BitAnd
Ceil
Corr
Cos
Cosh
Count
Covar_pop
Covar_samp
Cume_Dist
Dense_Rank
Exp
Extract
Floor
Greatest
Least
Ln
Log
Max
Median
Min
Mod
Power Tanh
Rank
Remainder
Round (dates)
Round (numbers)
Sign
Sin
Sinh
Sqrt
StdDev
Sum
Tan
Trunc (dates)
Trunc (numbers)
Var_pop
Var_samp
Variance

[debasisdas]

Useful date functions
===================
Add_Months
Current_Date
Current_Timestamp
DbTimeZone
From_Tz
Last_Day
LocalTimestamp
Months_Between
New_Time
Next_Day
Round
SessionTimeZone
Sysdate Tz_Offset
SysTimestamp
Trunc

[debasisdas]

Conversion Functions in Oracle (To Convert between different data types)
================================================== ====
Bin_To_Num
Cast
CharToRowid
From_Tz
HexToRaw
NumToDSInterval
NumToYMInterval
To_Char
To_Clob
To_Date
To_DSInterval
To_Lob
To_Multi_Byte
To_NClob
To_Number
To_Single_Byte
To_Timestamp
To_Timestamp_Tz
To_YMInterval
TO_BINARY_FLOAT
TO_BINARY_DOUBLE

Some of the Miscelenous functions used in oracle
========================================
BFilename
Cardinality
Case Statement
Coalesce
Decode
Group_ID
NANVL
NVL
NVL2
Sys_Context
Uid
User
UserEnv

[debasisdas]

List of commonly used Oracle System Tables with Description
================================================
ALL_ARGUMENTS ---------->Arguments in object accessible to the user
ALL_CATALOG --------->All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS---------> Comments on columns of accessible tables and views
ALL_CONSTRAINTS ------------->Constraint definitions on accessible tables
ALL_CONS_COLUMNS --------->Information about accessible columns in constraint definitions
ALL_DB_LINKS ---------->Database links accessible to the user
ALL_ERRORS ----------->Current errors on stored objects that user is allowed to create
ALL_INDEXES --------->Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS -------------->COLUMNs comprising INDEXes on accessible tables.
ALL_LOBS ---------->Description of LOBs contained in tables accessible to the user
ALL_OBJECTS ---------->Objects accessible to the user
ALL_OBJECT_TABLES ---------->Description of all object tables accessible to the user .
ALL_SEQUENCES ---------->Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS ---------->Snapshots the user can access
ALL_SOURCE ---------->Current source on stored objects that user is allowed to create
ALL_SYNONYMS ---------->All synonyms accessible to the user
ALL_TABLES ---------->Description of relational tables accessible to the user
ALL_TAB_COLUMNS ---------->Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS ---------->Columns of user's tables, views and clusters
ALL_TAB_COMMENTS ---------->Comments on tables and views accessible to the user
ALL_TRIGGERS ---------->Triggers accessible to the current user
ALL_TRIGGER_COLS ---------->Column usage in user's triggers or in triggers on user's tables
ALL_TYPES ---------->Description of types accessible to the user
ALL_UPDATABLE_COLUMNS ---------->Description of all updatable columns
ALL_USERS ---------->Information about all users of the database
ALL_VIEWS ---------->Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL ---------->Database compatible parameter set via init.ora
DBA_DB_LINKS ---------->All database links in the database
DBA_ERRORS ---------->Current errors on all stored objects in the database
DBA_OBJECTS ---------->All objects in the database
DBA_ROLES ---------->All Roles which exist in the database
DBA_ROLE_PRIVS ---------->Roles granted to users and roles
DBA_SOURCE ---------->Source of all stored objects in the database
DBA_TABLESPACES ---------->Description of all tablespaces
DBA_TAB_PRIVS ---------->All grants on objects in the database
DBA_TRIGGERS ---------->All triggers in the database
DBA_TS_QUOTAS ---------->Tablespace quotas for all users
DBA_USERS ---------->Information about all users of the database
DBA_VIEWS----------> Description of all views in the database
DICTIONARY ---------->Description of data dictionary tables and views
DICT_COLUMNS ---------->Description of columns in data dictionary tables and views
GLOBAL_NAME ---------->global database name
NLS_DATABASE_PARAMETERS ---------->Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS ---------->NLS parameters of the instance
NLS_SESSION_PARAMETERS ---------->NLS parameters of the user session
PRODUCT_COMPONENT_VERSION ---------->version and status information for component products
ROLE_TAB_PRIVS ---------->Table privileges granted to roles
SESSION_PRIVS ---------->Privileges which the user currently has set
SESSION_ROLES ---------->Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP ---------->Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES ---------->Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP ---------->Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

[debasisdas]

SQL: "IN" Function
--------------------------------------------------------------------------------
The IN function helps reduce the need to use multiple OR conditions.

The syntax for the IN function is:

SELECT columns FOM tables
WHERE column1 in (value1, value2, .... value_n);

This SQL statement will return the records where column1 value = value 1 or value2..., or value_n. The IN function can be used in any valid SQL statement - select, insert, update, or delete.

Example #1
The following is an SQL statement that uses the IN function:

SELECT * FROM suppliers
WHERE supplier_name in ( 'IBM', 'HCL', 'SPA');

This would return all rows where the supplier_name is either IBM, HCL, or SPA.

It is equivalent to the following statement:

SELECT * FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'HCL' OR supplier_name = 'SPA';

Using the IN function increases the readability of the statement (easier to read and more efficient).

Example #2
You can also use the IN function with numeric values.

SELECT * FROM emp
WHERE deptno in (10, 20, 30);
 

This SQL statement would return all employees where the deptno is either 10 or 20 or 30 .

It is equivalent to the following statement:

SELECT * FROM orders
WHERE deptno = 10 OR deptno = 20 OR deptno = 30 ;
 

Example #3 - "NOT IN" (for Negation)

The IN function can also be combined with the NOT operator.

For example,

SELECT * FROM suppliers
WHERE supplier_name not in ( 'PAM', 'HCL', 'SPA');
 

This would return all rows where the supplier_name is neither PAM, HCL, or SPA. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

[debasisdas]

SQL: LIKE Condition
--------------------------------------------------------------------------------
The LIKE condition allows to use wildcards in the where clause of an SQL statement. This allows to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.

The patterns that you can choose from are:

% allows you to match any string of any length (including zero length)

_ allows you to match on a single character

Examples using % wildcard

The first example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Deb'.

SELECT * FROM suppliers WHERE supplier_name like 'Deb%';
 

You can also use the wildcard multiple times within the same string. For example,

SELECT * FROM suppliers WHERE supplier_name like '%eba%';
 

In this example, we are looking for all suppliers whose name contains the characters 'eba'.

You could also use the LIKE condition to find suppliers whose name does not start with 'D'. For example,

SELECT * FROM suppliers WHERE supplier_name not like 'D%';
 

By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'D'.

Examples using _ wildcard
Next, let's explain how the _ wildcard works. Remember that the _ is looking for only one character.

For example,

SELECT * FROM suppliers WHERE supplier_name like 'Ba_sha';
 

This SQL statement would return all suppliers whose name is 6 characters long, where the first two characters is 'Ba' and the last three characters is 'sha'. For example, it could return suppliers whose name is 'Baasha', 'Batsha', 'Badsha' ,'Bapsha' etc.

Here is another example,

SELECT * FROM suppliers WHERE account_number like '12317_';
 

You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:

123170,123171,..........123179.

Examples using Escape Characters

Next, in Oracle, let's say you wanted to search for a % or a _ character in a LIKE condition.

Since these are special characters they have special meaning in SQL statments .
You can do this using an Escape character.

Please note that you can define an escape character as a single character (length of 1) ONLY.

For example,

SELECT * FROM suppliers WHERE supplier_name LIKE '!%' escape '!';
 

This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.

Here is another bit critical example:

SELECT * FROM suppliers WHERE supplier_name LIKE 'H%!%' escape '!';
 

This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.


You can also use the Escape character with the _ character. For example,

SELECT * FROM suppliers WHERE supplier_name LIKE 'H%!_' escape '!';
 

This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.

[debasisdas]

Sampe example USING CASE
--------------------------------------------------
Syntax
-----------------
CASE search_expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
[ELSE
default_result]
END

In this case the search expression is a single field .

Example #1

 SELECT EMPNO,ENAME,
 CASE JOB
 WHEN 'CLERK' THEN 'HI'
 WHEN 'MANAGER' THEN 'HELLO'
 WHEN 'SALESMAN' THEN 'BYE'
 ELSE
 'GOOD BYE'
 END AS MESSAGE
 FROM EMP;
 

The above query searches on job field and checks the values ,if condition satisfies THEN part is executed.If no conditin satisfies it goes to else part which is otional.

Sample Example Using SEARCHED CASE
-------------------------------------------------------------------
Syntax
---------------
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
[ELSE
default_result]
END

In this case the search expression can be based on more than one field.

Example #2

SELECT EMPNO,ENAME,
 CASE 
 WHEN DEPTNO=10 THEN 'SALES'
 WHEN SAL>3000 THEN 'MARKETING'
 WHEN JOB='MANAGER' THEN 'PRODUCTION'
 ELSE
 'RESEARCH'
 END AS DEPT
 FROM EMP;
 

In the above query we can match the case based on more than one field values.