UPDATE query in SQL

In the following query, the subquery

select name1 from testa where no1=6;

returns no rows. In that case, it will update with NULL

SQL> update mytab set name=(select name1 from testa where no1=6), tno=9 where tno=1;

1 row updated.

SQL> select * from mytab;

TNO NAME
---------- ---------------
9
2 suman
3 ravi
4 sai
5 sample
6
7

7 rows selected.

ORDER BY clause in SQL

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
5 test 1000
6 sdf 3223
1 vinay 8000
2 suman 2000
1 vinay 9000
3 ravi 3000
4 sai 2000
2 suman 4000
1 vinay 3000
1 vinay 3000
2 suman 4000


SQL> select * from sample order by sno;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
1 vinay 9000
1 vinay 3000
1 vinay 8000
2 suman 4000
2 suman 2000
2 suman 4000
3 ravi 3000
3 ravi 5000
4 sai 8000
4 sai 2000


[OR]

Above query can also be rewritten as follows:
SQL> select * from sample order by 1;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
1 vinay 9000
1 vinay 3000
1 vinay 8000
2 suman 4000
2 suman 2000
2 suman 4000
3 ravi 3000
3 ravi 5000
4 sai 8000
4 sai 2000

Joins in SQL

Joins:

SELECT t1.column, t2.column
FROM t1
[NATURAL JOIN t2] |
[JOIN t2 USING (column_name)] |
[JOIN t2
ON (t1.column_name = t2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN t2
ON (t1.column_name = t2.column_name)]|
[CROSS JOIN t2];

Natural Join is based on having all the column names same in both two tables i.e join condition is an equijoin of all columns with same name. The column names that have same name in both the tables should be of same type, else an error is returned.
SQL> select empno, ename, deptno
2 from emp
3 natural join dept;

EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20

EMPNO ENAME DEPTNO
---------- ---------- ----------
7900 JAMES 30
7902 FORD 20
7934 MILLER 10

14 rows selected.


USING clause: Can be used when several columns have the same names but the data types are different. You can match only one column. Note that table name or alias name should not be used in the referenced columns.

SQL> select empno,ename,deptno
2 from emp
3 join dept using (deptno);

EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20

EMPNO ENAME DEPTNO
---------- ---------- ----------
7900 JAMES 30
7902 FORD 20
7934 MILLER 10

14 rows selected.


Using JOIN with the ON clause: Natural join is an equijoin of all column names with same name. Using the ON clause you specify columns to join.

SQL> select empno,ename,d.deptno,sal
2 from emp e
3 join dept d on e.deptno=d.deptno;

EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7369 SMITH 20 800
7499 ALLEN 30 1600
7521 WARD 30 1250
7566 JONES 20 2975
7654 MARTIN 30 1250
7698 BLAKE 30 2850
7782 CLARK 10 2450
7788 SCOTT 20 3000
7839 KING 10 5000
7844 TURNER 30 1500
7876 ADAMS 20 1100

EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7900 JAMES 30 950
7902 FORD 20 3000
7934 MILLER 10 1300

14 rows selected.

Inner Join will display only the matched rows. A join between the two tables that returns the results of the inner join as well as the unmatched rows from left (or right) tables is called left (or right) outer join.

Full outer join returns the results of an inner join as well as the results of a left and right join.

Using IN clause in SQL

SQL> select * from emp where empno in(7369,7876);

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- --------------------------------
7369 SMITH CLERK 7902 17-DEC-80 12.00.00.0000000 AM
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM

Hierarchial Queries

SQL> select empno, ename, job, mgr from emp
2 start with empno=7521
3 connect by prior mgr=empno;

EMPNO ENAME JOB MGR
---------- ---------- --------- ----------
7521 WARD SALESMAN 7698
7698 BLAKE MANAGER 7839
7839 KING PRESIDENT

CrossJoin in SQL

Cross join returns the cartesian product i.e. all rows in the first table is joined to all rows in the second table. To aviod cartesian product, always include a valid join condition.

SQ> select empname, dname from emp cross join dept;

TimeZones in SQL

Current time
SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
2 from dual;

Current Time
------------------------
Tue 10-Feb-2009 00:40:27

[OR]

SQL> alter session set nls_date_format='dd-mon-yyyy HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
10-feb-2009 00:47:48

[OR]


SQL> select current_date from dual;

CURRENT_DATE
--------------------
10-feb-2009 00:48:52

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
----------------------------------------------------
-05:00

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
------------------------------------------------------
10-FEB-09 12.53.13.998000 AM -05:00

Above returns TimeStamp with Time ZOne

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
------------------------------------------
10-FEB-09 12.54.29.396000 AM

Above returns only timestamp



SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
-----------------------------------------------
-05:00

SQL> select dbtimezone from dual;

DBTIME
------
+00:00


SQL> select hiredate from emp;

HIREDATE
--------------------
17-dec-1980 00:00:00
20-feb-1981 00:00:00
22-feb-1981 00:00:00
02-apr-1981 00:00:00
28-sep-1981 00:00:00
01-may-1981 00:00:00
09-jun-1981 00:00:00
19-apr-1987 00:00:00
17-nov-1981 00:00:00
08-sep-1981 00:00:00
23-may-1987 00:00:00

HIREDATE
--------------------
03-dec-1981 00:00:00
03-dec-1981 00:00:00
23-jan-1982 00:00:00

14 rows selected.


SQL> desc emp
Name Null? Type
----------------------------------------- -------- -----------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)


You can convert from DATE to TIMESTAMP when the column has data, but you cannot convert from DATE or TIMESTAMP to TIMESTAMP WITH TIME ZONE unless the column is empty.

You can specify the fractional seconds precision for timestamp. If none is specified, as in the above example, then it defaults to 6.

SQL> alter table emp modify hiredate timestamp(7);

Table altered.

SQL> select hiredate from emp;

HIREDATE
------------------------------------
17-DEC-80 12.00.00.0000000 AM
20-FEB-81 12.00.00.0000000 AM
22-FEB-81 12.00.00.0000000 AM
02-APR-81 12.00.00.0000000 AM
28-SEP-81 12.00.00.0000000 AM
01-MAY-81 12.00.00.0000000 AM
09-JUN-81 12.00.00.0000000 AM
19-APR-87 12.00.00.0000000 AM
17-NOV-81 12.00.00.0000000 AM
08-SEP-81 12.00.00.0000000 AM
23-MAY-87 12.00.00.0000000 AM

HIREDATE
------------------------------------
03-DEC-81 12.00.00.0000000 AM
03-DEC-81 12.00.00.0000000 AM
23-JAN-82 12.00.00.0000000 AM

14 rows selected.

EXTRACT function
------------
SELECT EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND]
[TIMEZONE_HOUR] [TIMEZONE_MINUTE]
[TIMEZONE_REGION] [TIMEZONE_ABBR]
FROM [datetime_value_expression] [interval_value_expression]);


SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
2009

SQL> select extract(timezone_region from current_timestamp) from dual;

EXTRACT(TIMEZONE_REGIONFROMCURRENT_TIMESTAMP)
----------------------------------------------------------------
UNKNOWN

SQL> select extract(timezone_abbr from current_timestamp) from dual;

EXTRACT(TI
----------
UNK

TZ_OFFSET function:
--------------------------
returns the time zone offset. For example, if the function returns -05:00, it indicates that the time zone where the command was executed is five hours behind UTC (Coordinated Universal Time).


SQL> select tz_offset(sessiontimezone) from dual;

TZ_OFFS
-------
-05:00

SQL> select tz_offset(dbtimezone) from dual;

TZ_OFFS
-------
+00:00

Query v$timezone_names to get valid time zone name values:
SQL> select * from v$timezone_names;


FROM_TZ function: Converts a TIMESTAMP value to TIMESTAMP WITH TIME ZONE value
----------------------------

SQL> select from_tz(timestamp '2008-03-20 10:00:00', 'US/Pacific') from dual;

FROM_TZ(TIMESTAMP'2008-03-2010:00:00','US/PACIFIC')
---------------------------------------------------------------------------
20-MAR-08 10.00.00.000000000 AM US/PACIFIC


SQL> select from_tz(timestamp '2008-03-20 10:00:00', '-05:00') from dual;

FROM_TZ(TIMESTAMP'2008-03-2010:00:00','-05:00')
---------------------------------------------------------------------------
20-MAR-08 10.00.00.000000000 AM -05:00

TO_TIMESTAMP
---------------
SQL> select to_timestamp('05-12-09 13:20:00', 'MM-DD-YY HH24:MI:SS') from dual;

TO_TIMESTAMP('05-12-0913:20:00','MM-DD-YYHH24:MI:SS')
---------------------------------------------------------------------------
12-MAY-09 01.20.00.000000000 PM


TO_TIMESTAMP_TZ
-------------------
SQL> select to_timestamp_tz('05-12-09 13:20:00 -5:00', 'MM-DD-YY HH24:MI:SS TZH:TZM') from dual;

TO_TIMESTAMP_TZ('05-12-0913:20:00-5:00','MM-DD-YYHH24:MI:SSTZH:TZM')
---------------------------------------------------------------------------
12-MAY-09 01.20.00.000000000 PM -05:00


TO_YMINTERVAL: Convert character string to an INTERVAL YEAR TO MONTH datatype.
-------------------
SQL> select hiredate from emp;

HIREDATE
-----------------------------------------
17-DEC-80 12.00.00.0000000 AM
20-FEB-81 12.00.00.0000000 AM
22-FEB-81 12.00.00.0000000 AM

SQL> select hiredate + to_yminterval('02-01') as new_hire_date from emp;

NEW_HIRE_DATE
-------------------------------------------------------------------------
17-JAN-83 12.00.00.000000000 AM
20-MAR-83 12.00.00.000000000 AM
22-MAR-83 12.00.00.000000000 AM

The character string can also have negative value. Belos, it returns a date that is one year and two months before the hire date.
SQL> select hiredate + to_yminterval('-01-02') as new_hire_date from emp;

NEW_HIRE_DATE
---------------------------------------------------------------------------
17-OCT-79 12.00.00.000000000 AM
20-DEC-79 12.00.00.000000000 AM
22-DEC-79 12.00.00.000000000 AM

SET VERIFY command in SQLPlus

Suppressing old and new values
By default, when SQL*Plus encounters a defined variable, it prints the original line and the line with the substitued values:

define s="'some string'"
define d=dual

select &s from &d;

old 1: select &s from &d
new 1: select 'some string' from dual

'SOMESTRING
-----------
some string

This behavior can be turned with setting verify to off:

SET VERIFY OFF

define thousand=1000
define twelve = 12
define plus = +

select &thousand &plus &twelve from dual;

1000+12
----------
1012

Using new_value to define a variable in SQLPlus

SQL> col run_date_time new_value today
SQL> select to_char(sysdate,'yyyymmdd hh24:mi') run_date_time from dual;

SQL> select '&&today' from dual;
old 1: select '&&today' from dual
new 1: select '20100223 09:36' from dual

'2010022309:36
--------------
20100223 09:36

SQL> select '&&run_date_time' from dual;
old 1: select '&&run_date_time' from dual
new 1: select '20100223_101323' from dual

'20100223_10132
---------------
20100223_101323

SQL> select '&run_date_time' from dual;
old 1: select '&run_date_time' from dual
new 1: select '20100223_101323' from dual

'20100223_10132
---------------
20100223_101323

Using Regular Expressions in 10g

Oracle Database 10g introduces support for Regular Expressions, which is a method of describing both simple and complex patterns for searching and manipulating. You can use several predefined meta character symbols in the pattern matching.

Meta Characters:

* matches zero or more occurrences

+ matches one or more occurrence

? matches zero or one occurrence

. matches any character in the supported character set, except NULL

| Alternation operator for specifying alternative matches (a|b matches a or b)

^/$ Matches start of line/end of line

[] Bracket expression for a matching list matching any one of the expressions in the list

[...] match any character in the list

[^...] match any character not in the list

{m} matches exactly m occurrences

{m,} matches atleast m occurrences

{m,n} matches atleast m times but no more than n times

(...) Subexpression, treat expression as a unit

SQL> select * from emp where regexp_like(ename, '^A+');

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- -------------------------------
7499 ALLEN SALESMAN 7698 20-FEB-81 12.00.00.0000000 AM
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM



SQL> select * from emp where regexp_like(ename, 'A+');

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ---------------------------------
7499 ALLEN SALESMAN 7698 20-FEB-81 12.00.00.0000000 AM
7521 WARD SALESMAN 7698 22-FEB-81 12.00.00.0000000 AM
7654 MARTIN SALESMAN 7698 28-SEP-81 12.00.00.0000000 AM
7698 BLAKE MANAGER 7839 01-MAY-81 12.00.00.0000000 AM
7782 CLARK MANAGER 7839 09-JUN-81 12.00.00.0000000 AM
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM
7900 JAMES CLERK 7698 03-DEC-81 12.00.00.0000000 AM


SQL> select * from emp where regexp_like(ename, 'AMS$');

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ------------------------------
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM

Using WITH clause

WITH clause can be used only with the SELECT clause and can hold more than one query. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block.

Using WITH clause has following advantages:

• It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
• It can improve performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace

Example:
SQL> with emp_sal as(select sno, sum(salary) as tot_sal from sample group by sno),
avg_sal as (select sum(tot_sal)/count(*) as tot_avg from emp_sal)
select * from emp_sal where tot_sal > (select tot_avg from avg_sal)
order by sno;

SNO TOT_SAL
---------- ----------
1 23000
2 10000
4 10000

WHERE Vs HAVING clause in SQL statement

Listed below are some reasons on when to use WHERE or HAVING clause in SQL SELECT statement.

• WHERE clause is used to exclude rows before the grouping of data
• Aggregate functions cannot be used in the WHERE clause
• HAVING clause is used to exclude one or more aggregated results after grouping data
• HAVING clause conditions can have aggregate functions
• WHERE and HAVING clauses can be used together in a SQL statement

All about using INDEXES

Indexes are used to improve the performance of data retrieval. They are automatically created when primary key or unique key is created. You can manually create it using CREATE INDEX statement and CREATE TABLE statement.

CREATE INDEX with CREATE TABLE
SQL> create table test
(tno number(3) primary key using index (create index tno_idx on test(tno)), tname varchar(20));

SQL> select index_name, index_type from user_indexes where table_name='TEST';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TNO_IDX NORMAL

[OR]

SQL> create table test (tno number(3), tname varchar(20));

Table created.

SQL> create index tno_idx on test(tno);

Index created.

SQL> alter table test add primary key (tno)
using index tno_idx;

Table altered.

NOTE that index is automatically created when you specify a column as primary key.

Using EXISTS and NOT EXISTS

EXISTS: Is a boolen values that ensures that the inner query does not continue when at least one match is found by the condition. Inner query does not specifically return a specific value, so a constant can be selected.

SQL> select empno, ename, mgr, deptno from emp e
where exists (select 'X' from emp where mgr=e.empno);

EMPNO ENAME MGR DEPTNO
---------- ---------- ---------- ----------
7566 JONES 7839 20
7698 BLAKE 7839 30
7782 CLARK 7839 10
7788 SCOTT 7566 20
7839 KING 10
7902 FORD 7566 20

6 rows selected.

SQL> select deptno, dname from dept d
where not exists (select 'X' from emp where deptno=d.deptno);

DEPTNO DNAME
---------- --------------
40 OPERATIONS

[OR]

NOT IN can be used as an alternative for NOT EXISTS operator.

SQL> select deptno, dname from dept d
where deptno not in (select deptno from emp);

DEPTNO DNAME
---------- --------------
40 OPERATIONS

DICTIONARY view

DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

SQL> select * from dictionary;

TABLE_NAME
------------------------------
COMMENTS
------------------------------------------------
DBA_ROLES
All Roles which exist in the database

DBA_PROFILES
Display all profiles and their limits

USER_RESOURCE_LIMITS
Display resource limit of the user

DELETE rows

To delete selected rows in a table:
SQL> delete testa where name1='test';

1 row deleted.

[OR]

SQL> delete from testa where name1='ravi';

1 row deleted.

To delete all the rows in the table:
SQL> delete master;

3 rows deleted.

SQL> rollback;

Rollback complete.

SQL> select * from master;

MNO MNAME
---------- ---------------
1 test1
2 test2
3 test3

[OR]

SQL> delete from master;

3 rows deleted.

BETWEEN...AND

SQL> select * from mytab where tno between 3 and 5;

TNO NAME TEST
---------- --------------- ----------
3 ravi 1
4 sai 1
5 sample 1


SQL> select ename,job from emp where empno not between 7000 and 7500;

ENAME JOB
---------- ---------
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER

ALTER TABLE...[ADD/MODIFY/DROP/SET UNUSED]

ALTER TABLE statement can be used to add a new column, modify an existing column, define default value for new column, or drop a column.

ALTER TABLE table_name
ADD (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table_name
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table
DROP (column);
[OR]
ALTER TABLE table_name
DROP COLUMN column_name;

SQL> alter table sample
add (deptname varchar(20));

Table altered.

SQL> alter table sample
modify (deptname varchar(10));

Table altered.

SQL> alter table sample
drop (deptname);

Table altered.

OR

SQL> alter table master
drop column dept;

Table altered.

Using SET UNUSED option to mark one or more columns as unused.
ALTER TABLE
SET UNUSED();
OR
ALTER TABLE
SET UNUSED COLUMN ;

Using SET UNUSED option in ALTER TABLE Statement:

SQL> select * from sample;

SNO SNAME STEST
---------- -------------------- ----------
1 NJ test1
2 KS test2
3 WA test3
4 OH test4

SQL> alter table sample
set unused (stest);

Table altered.

SQL> select * from sample;

SNO SNAME
---------- ----------------
1 NJ
2 KS
3 WA
4 OH


To remove all the columns that are marked as unused.
ALTER TABLE
DROP UNUSED COLUMNS;

Adding Constraints:

ALTER TABLE
ADD [CONSTRAINT ]
type ();



To modify existing column and make it a primary key.
SQL> alter table test
modify tno primary key;

Table altered.

Modify existing column and make it as foreign key.
SQL> alter table child
add constraint mas_chi_fk
foreign key (cno)
references master(mno);

Table altered.

To delete child rows when a parent row is deleted, use ON DELETE CASCADE option:
SQL> alter table child
add constraint mas_chi_fk
foreign key (cno)
references master(mno) on delete cascade;

Deferring Constraints:
Constraints can be deferred on creation.
ALTER TABLE test
ADD CONSTRAINT test_tno_pk
PRIMARY KEY (tno)
DEFERRABLE INITIALLY DEFERRED;

Change a specific attribute:
SET CONSTRAINTS test_tno_pk IMMEDIATE;

Change all constraints for a session:
ALTER SESSION SET CONSTRAINTS= IMMEDIATE;

Drop Constraints:
SQL> alter table child
drop constraint mas_chi_fk;

To drop the primary key on a table and also drop the associated foreign key constraint:
SQL> alter table master
drop primary key cascade;

To disable and enable the constraints:
SQL> alter table child
disable constraint mas_chi_fk;

SQL> alter table child
enable constraint mas_chi_fk;

CASCASE CONSTRAINTS clause:
Is used along with DROP COLUMN clause that will drop all referential integrity constriants that refer to primary and unique keys defined on dropped columns.

ALTER TABLE test
DROP COLUMN tno CASCADE CONSTRAINTS;

ALTER TABLE test
DROP (tno_pk, eno_fk, tname) CASCADE CONSTRAINTS;

Alter table to add a column which cannot contain NULL

Walk-through example:
Alter table 'mytab' to add a column called 'test' which cannot contain NULL.

SQL> desc mytab
Name Null? Type
----------------------------------------- -------- --------------------------
TNO NUMBER(3)
NAME VARCHAR2(15)

SQL> alter table mytab add test number(8,2) not null;
alter table mytab add test number(8,2) not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column


SQL> alter table mytab add test number(8,2) default 0 not null;

Table altered.

SQL> desc mytab
Name Null? Type
----------------------------------------- -------- --------------------------
TNO NUMBER(3)
NAME VARCHAR2(15)
TEST NOT NULL NUMBER(8,2)

SQL> select * from mytab;

TNO NAME TEST
---------- --------------- ----------
9 0
2 suman 0
3 ravi 0
4 sai 0
5 sample 0
6 0
7 0

7 rows selected.

Query to retrieve Nth highest salary records

Get the records with Nth highest salary of employee:
SQL> select empno, ename, sal from emp
where sal = (select max(sal) from emp e1 where n<(select count(*) from emp e2 where e1.sal<=e2.sal));

Ex: To get the records that has 2nd highest salary in the emp table:

SQL> select empno, ename, sal from emp
where sal = (select max(sal) from emp e1 where 2<(select count(*) from emp e2 where e1.sal<=e2.sal));

EMPNO ENAME SAL
---------- ---------- ----------
7788 SCOTT 3000
7902 FORD 3000