Oracle SQL PART I

SQL TOPics

DML, DDL, DCL, TCL, OERATORS, WHERE CLAUSE, FUNCTION, SUB QUERY,SET OPERATORS, CONSTRAINT, Joins, View, Index, Synonym, privilege, Group by, rollup, cube, timestamp, Hierarchical retrieval, External Table.

DATA TYPES

Number – 10^2

Char – Max 255 byte (Fill empty space)

Varchar – Will occupy 2kb(depend on number of char)

Date –

Timestamp

      SQL > create table t1 (doj timestamp with local time zone)

      SQL > insert into t1 value (’15-nov-12 09:34:28 AM’)

Interval day to second

Interval year to month

Clob

Blob

Long – cannot include in group by ot order by

      Only one long can be used per table

      No constraint defined

      Column can not copy when table is created using sub query

      To over come use clob

Notes

Maximum 1000 column can be added & no limit for rows

Environment

SQL > set pages 100

SQL > set line 100

SQL > set col 20

SQL > / - To run previous command

Timestamp

SQL > select current_timestamp from dual;

Types Of SQL

1.DDL (Create,Alter,drop,rename,truncate,comment)

Note : DDL is auto commit & Exit also

2.DML  (Select,Insert,update,delete,merge

3.DCL (Grant,revoke)

4. TCL (Commit,Rolleback)

Types of tables in Oracle DB

1. User table – users created tables constains user information
2. Data dictionary – Server created table contains database information

DATA Dictionary

All tables in Data dictionary are owned by sys user. The base tables are rarely accessed by users because information in them is not easy to understand. So users use the DATA Dictionary to view the information which is much more easier to understand

DATA Dictionary categorized into 4 types

1.  User_  - Information about user owned objects

2.  ALL_   - Information about all database objects & relational tables accessible to users

3.  DBA_  - Is a restricted view, which can be accessed only by people who have been assigned the

4.  V$  -   These views are dynamic performance views, database server performance, memory, & locking

dynamic performance views example

SQL > select sql_text,execution from V$SQL where cpu_time >200000

SQL > select * from v$session where machine=’SYSTEM1’ and logon_time=sysdate-1

SQL > select sid,ctime from v$lock where block > 0

1. What are the SQL statement and their associated number of execution where the CPU time consumed is greater than 200000 micro scond
2. What session logged in from the system1 computer within last day
3. What are the session IDs of any session that are currently holding a lock that is blocking another user, and how long has that lock been held?

Querying the DATA Dictionary

SQL > select table_name from user_table – See the names of table owned by user

SQL > select DISTINCT object_type from user_objects – View distinct objects type owned by the user

SQL > select  * from user_catalog_view table,view,sysnonym,and sequence-  owned by the user 

SQL > select * from cat – User catalog has the synonym called CAT

SQL > show parameter db_name – to see db name

SQL> select * from dictionary

SQL> select * from user_tables

SQL> select * from user_tabs_columns - To see the column information

SQL> desc user_constraint

SQL> desc user_cons_columns - To see the column constraint

SQL> desc user_views

SQL> desc user_sequences

SQL> desc user_tab_comments

SQL> desc user_col_columns

Confirming Privilege gtranted

ROLE_SYS_PRIVS  - System privilege granted to roles

ROLE_TAB_PRIVS  - Table privilege granted to roles

USER_ROLE_PRIVS - Roles accessible  by the user

USER_TAB_PRIVS_MADE - Object privilege granted on the user object

USER_TAB_PRIVS_RECD - Object privilege granted to the user (Shared tables)

USER_COL_PRIVS_MADE - Object privilege granted on the column of the user object

USER_COL_PRIVS_RECD - Object privilege granted to the user specific columns

USER_SYS_PRIVS - System privilege granted to user

Alias

SQL > select emp,sal+sal*.1 [as] “NEW SALARY”

Select statement.

SQL > select * from emp where com is null

SQL > select * from user.table

SQL > insert into <table> values (300,DEAFUL) – To insert with default value

SQL > Create table <name> (Col1 Datatype,col2  Datatype);

SQL > Create table <name> as (select * from <name>);

SQL > Select * into <New table> from <old names>;

SQL > Select * into new_table_name [IN externaldatabase/dbname.mdb]
FROM old_tablename

SQL > select dept_name || q’[,It is custom quote]’ – To give the custome quote

SQL > select emp_id,name,job_id from emp where job_id like '%sa\_%' ESCAPE '\';

SQL > insert into  <Table> as (select  * from <Table2>

SQL > insert into <Table> select no,name from table2- To insert from another table

SQL > Alter table <name> add <Col Datatype,col2 Datatype>

SQL > Alter table <name> modify <Col Datatype>

SQL > Alter table <name>  drop column  <Col Datatype>

Note: While Droping multiple column keyword ‘column’ is not necessary

SQL > Alter table <name>  rename column  <Col Datatype>

SQL > Alter table <table> modify DOJ default sysdate;

To Delete the duplicate sno

Follwing query will show duplicate record of maximum rows

SQL > select sno,name from emp where rowed not in (select max(rowid) from <table> group by sno)

SQL > delete from emp where rowed not in (select max(rowid) from <table> group by sno)

To disable column

SQL > alter table t1 set unused column <col> - It is not possible to enable to unused column

SQL > alter table t1 drop unused column <col>

To view the unused information

SQL> select * from user_unused_col_tabs

Distributed Queries

The Oracle distributed database management system architecture lets you access data in remote databases using Oracle Net and an Oracle Database server. You can identify a remote table, view, or materialized view by appending @dblink to the end of its name. The dblink must be a complete or partial name for a database link to the database containing the remote table, view, or materialized view.

SQL > SELECT r.product_id, l.ad_id, r.press_release  FROM pm.print_media@remote r, pm.print_media l   FOR UPDATE OF l.ad_id;

SQL > insert into <table> values (………….);

SPOOL

SQL > spool d:\filename

SQL > spool off

COMMIT & ROLLBACK

Auto roll back can appear in follwing scenario

1.Abnormal close

2. N/W failure

3. Power Failure

DDL & DCL statements are Auto commit statement

Locking

1. Implicit locking
2. Explicit locking

Implicit locking

Exclusive : Locks out other user

Share : Allow other user to access the server

Flashback

SQL > drop table <name>

SQL > select * from user_recyclebin ;

SQL > flashback table <table> before drop – to undo the drop table

Permanent Delete

SQL > Purge recycle bin – After the table drop

SQL > drop table <table> purge;

Commenting table

SQL > Comment on table <table> is “This is table comment”;

SQL > select * from all/user_tab_comments where table_name=<’TABLE’>

Commenting on column

SQL > Comment  on column <Table>.<Column> is ‘Column comment’;

SQL > Select * from all/user_col_comments where table_name=<’TABLE’>;

Drop comment

SQL > comment on table <t1> is ‘ ‘

DATABASE OBJECTS

Table,View,Seqence,Synonym,etc.

Functions

1.Single line function

2. Aggreigate function

1.Single line function

Character function

      Upper,lower,substr,concat,instr,lpad,rpad,trim,length,initcap,replace,least(‘aa’,’bb’,’cc’)

Date function

      sysdate,systimestamp,trunc,Months_between,Next_day,add_months,last_day

Number function

      Sum,avg,stdev,round,trunc,abs,floor,ceil,mod

General function

      NVl,NVL2,NULLIF,COALSECE,CASE,DECODE

Conversion function

      To_char,to_number,to_date,cast(0 as real),

trim('   tech   ')	would return 'tech'
trim(' '  from  '   tech   ')	would return 'tech'
trim(leading '0' from '000123')	would return '123'
trim(trailing '1' from 'Tech1')	would return 'Tech'
trim(both '1' from '123Tech111')	would return '23Tech'

Character function

SQL > select emp_id,concat(fname,lastname),length(lastname),instr(lastname,’a’) “Name has ‘a’” from emp where substr (jobid,4)=’REP’

Tricky question

If we want to query names whose first letter start with ‘n’ means

SQL > select * from emp where substr(lastname,-1,1)=’n’

DATE function

Date internally stored in database in following format

Century     year  month day   hours minute      second

 19         94    03    07    10    20    00

SQL > select months_between(’01-mar-1980’,’02-mar-1985’), add_months(’01-mar-80’,6),next_day(’01-mar-2012’,Friday),last_day(’01-mar-1980’) from dual

SQL > select months_between (’20-mar-1985’,sysdate)-1 “no of month u r birth”

months_between (’20-mar-1985’,sysdate)/12-1 “no of Year u r birth”

SQL> select trunc(sysdate,'month') from dual

SQL> select trunc(sysdate,'year') from dual

Date & Time format element

AM.,PM.     - Meridian indicator

A.M.,P.M.   - Meridian indicator with period

HH,HH12,HH24 – hour,hour in 12, hour in 24

MI    - Minute

SS    - Seconds 0-59

SSSS  - Seconds past mid night

/.,   - Puntuation is reproduced

“MSG” – Comment is reproduced

Specifying suffix to influence display

Th – For output like 4^th

SP – Spell out like four

SPTH – Output like ‘Foruth’

Fm – is used to remove the padded space

SQL > select  to_char(doj,’fmddspth “of” month yyyy fm HH:MI:SS AM) from dual

O/P like -> Seventeenth of December 1980 12:00:01: AM

Number function

SQL > select round(45.1234),trunk(45.1234),mod(200,3) from dual

ROUND(1234.345,2) ROUND(1234.345,0) ROUND(1234.345,-2) – Round left to the decimal point
----------------- ----------------- ------------------
1234.35 1234 1200 

TRUNC(1234.345,2) TRUNC(1234.345,0) TRUNC(1234.345,-2) – Truncate left to the decimal point
----------------- ----------------- ------------------
1234.34 1234 1200

General function

SQL > Select nvl(sal,0) from emp – All null rows filled with 0

SQL > select nvl2(sal,’Not null’,’Null’) – If parameter 1 is null the replace it with third parameter if not null replace it with second parameter

SQL > select nullif(sal,sal2) from emp –Compare two parameter if both are equal return null otherwise return the first parameter.

SQL > select coalesce(sal,sal2,0) from emp- Returns the first non null expression. If  first expression is null return second expression if second also null returns third expression.

The Decode expression

SQL > select job,sal,

Decode (job,’IT’, sal*10

      ‘SALES’,sal*20

      ‘PURCHASE’,sal*30) “Revised sal” from emp

The case expression

SQL > select name,job_id,sal,

(case job_id      when ‘IT’ then sal*10

            When ‘sales’ then sal*20

                  Else sal

                  End) “Revised sal” from emp;

Extract

SQL > select extract(month from sysdate) from dual

SQL > select extract(year from sysdate) from dual

FromTZ function

Convert the timestamp function into timestamp with time zone value

SQL > select from_tz(timestamp,’2000-03-28 08:00:00’,’3:00’) from dual

Exists operator

Find the employee who have at least one person reporting to them

SQL > select empid,name,jobid from emp xx where exists (

      Select * from emp where managerid=xx.managerid)

With Clause

Scenario

Using with clause write query to display the department name & total salaries for those department whose total salaries is greater than the average salary accorss the department

SQL > with

      Dept_cost as (

             Select dept_name,sum(salary) as “total sal” from emp,dept

             Where emp.dept_id=dept.dept_id group by dept_name)

      Avg_cost as (

             Select sum(dept_cost)/count(*) as “average” from dept_cost)

      Select * from dept_cost where dept_total >

      (select * from avg_cost) order by dept_name;

Steps

1. calculate the total salary for every dept and store the result in dept_cost
2. calculate the avg salary using dept_cost and store it in avg_cost
3. compare total salary with averge & produce the result                                       

  Converstion function
to_char function formats
9 - To represent the number  - 99999 will print 1234

0 - To force 0 to be displayed - 00000 will print 01234

$ - places $ symbol - $1234

L - Used floating currency - L9999- FF1234

. - Print decimal point-999.99 - 123.4

, - prins thousand  indicator-99,999-12,345

SQL> select lastname,to_char(doj,'dd-mm-yy') from emp;

SQL> select lastname,to_date('12-03-12','dd-mm-yy') from emp;

2.Aggreigate function (or) Group function

SUM,AVG,MIN,MAX,COUNT

Nesting group function

SQL > select max(avg(sal)) from emp group by dept_id

Group by clause

SQL > select deptno,count(*),sum(sal) from emp where group by deptno

Having clause in Groupby

SQL > select deptno,count(*),sum(sal) from emp group by deptno having sal sum(sal) >10000

Note : In the above query if we want to restrict like sal>10000 instead sum(sal) then query should be

SQL > select deptno,count(*),sal from emp where sal >10000 group by deptno >10000

Roll up &  Cube operator in Group by clause

Cube – Produce subtotal line for each department & calculate total for each job<First parameter> and sal <Second parameter> & Grant total at the end of the query and total for each job

Rollup – Similar to cube except does not return total for each sal <Second parameter>

Only for first parameter and grant total.

SQL > select deptno,count(*),sum(sal) from emp where group by rollup(deptno,sal)

SQL > select deptno,count(*),sum(sal) from emp where group by  cube(deptno,sal)

Grouping Set

Grouping sets is further extension of the GROUP BY clause that you can use to specify multiple grouping of data. A single select statement can now be using GROUPING SET to specify various grouping (which can also include ROLLUP , CUBE operator) rather than multiple select statement combined by UNION ALL operator

SQL > select dept_id,job_id, manager_id, avg(sal) from emp

        Group by grouping sets ((dept_id,job_id,manager_id), (dept_id,managr_id),(job_id,managr_id));

Constraint

Primary key

Unique

Not null

Check

Foreign key

SQL> create table ord_details (ord_id number(2) constraint ord_id_uk unique not null,

ord_date date default sysdate not null, ord_amt number(5,2) constraint ord_amt_min check (ord_amt>50), ord_status varchar(15) constraint ord_stat_check check (ord_status in ('shopped','not shopped')), ord_pay_mode varchar(15) constraint ord_pay_chk check (ord_pay_mode in('cheque','cash','CAD')));

SQL > create table <name> (sno number constraint pk_tb primary key)

SQL > create table <name> (sno number,name varchar, constraint comp_tb primary key (sno,name) – Is a composite primary key

SQL > alter table <name> add constraint ch_tb checn (sal >5000 and sal < 10000)

Note: To add notnull constraint to the existing table we have to use modify in alter command not add

      Not null constraint cannot be a composit or table level constraint

SQL > alter table <name> modify <column> notnull – for all other constraint

SQL > alter table <name> add constraint <cons>  primary key(xxx)

SQL > alter table <name> drop <cons1,cons2,cons3> cascad constraint – To delete multiple constraint

Disabling constraint

SQL > alter table t1 enable/disable constraint <constraint name>

Foreign Key

SQL > alter table <name> add constraint fr_table foreign key (sno) references table(sno) on delete cascade

Note : On delete set null  - converts the dependent FK values to NULL

To check a primary key has how may foreign key

SQL > select table_name,constraint_name from user_constraint where r_constraint_name = ‘<PK constraint name>’

Note

A child can belong to multilple parent

To delete the primary key constraint from parent table we have to remove the foreign key constraint first and then primary key.

To delete both in single query

SQL > alter table <name> drop constraint <pk> cascade

SQL > drop table <Parent> casecade constraint – To delete parent table with foreign key

To see the constraint  

SQL > select * from user_constraint

SQL > select constraint_name,constraint_type from user_constraint where table_name=’AA’

SQL > select constraint_name,column_name from user_cons_column – Column associated constraint

To see the coulumn constraint

SQL > select column_name,constraint_name from user_cons_columns where table_name=’AAA’

Deferring constraint

SQL> CREATE TABLE blick (num NUMBER, str VARCHAR2(1)); Table created.

 SQL> ALTER TABLE blick ADD CONSTRAINT pk_blick PRIMARY KEY (num) DEFERRABLE INITIALLY IMMEDIATE;

Table altered.

SQL>  INSERT INTO blick (num, str) VALUES (1, 'A'); 1 row created.

SQL> INSERT INTO blick (num, str) VALUES (1, 'B');

ORA-00001: unique constraint (PPDEV.PK_BLICK) violated 

SQL> SET CONSTRAINT pk_blick DEFERRED; Constraint set.

SQL> INSERT INTO blick (num, str) VALUES (1, 'B');

1 row created.

SQL> UPDATE blick SET num=2 WHERE str='B'; 1 row updated.  -- validate the new rows pass the constraint SET CONSTRAINT pk_blick IMMEDIATE; Constraint set. COMMIT; Commit complete.