Rakesh Chakrabarti
[email protected]
Oracle 10g (grid computing) SQL Introduction:
Computaire -> to Compute/calculate Data Computer Information Large amount of Inter related Data – database DBMS: DBMS: Databa Database se managem management ent System System ANSI defn. of DBMS - Readymade s/w allows you to insert update delete and process Ms Excel dBASE foxbase fox base Foxpro Clipper Database Dataflex Dataease Advance Revelation Quattro pro etc. Oracle RDMS relational +OODBMS Object Oriented= ORDBMS RDBMS examples: /Informix (fastest), Oracle (most popular 53% market share of world RDBMS market Client/Server environment / 74% market share of world RDBMS market internet environment Best s/w tools, mix programming is easy) /Sybase (NY Stock Exchange downwards), MS SQL server (upcoming Limitation Windows Domain) /Ingres, Progress (character based only on DOS and UNIX) /CICS, TELON, IDMS, IBM DB2 (Mainframe like ATM machine) /MS Access Vatcom SQL Paradox (PC based not for n/w) /My /MySQL SQL (Sun (Sun Micro Microsy syst stem emss Weak Weak RDBM RDBMS S more more than than 2la 2lakh kh rec recor ords ds not not in in one one tabl table) e) etc. etc.
DBMS
Diff
RDBMS
1. Nomenclature Field Column / Attribute Record Row / Tuple / Entity F il e table/relation/entity class 2. DBMS Relationshi Relationship p between between two two files files maintaine maintained d progra programmati mmatically cally RDBMS Relationship between two two tables maintained at the time of table table creation 3. DBMS DBMS High High netw networ ork k traf traffic fic RDBMS Low networ network k traffic traffic 4. DBMS DBMS proc proces essi sing ng at Clie Client nt end RDBMS processing processing at Server Server end 5. DBMS Client Client Serv Server er archi architec tectur ture e not suppo supporte rted d RDBMS Most RDBMS support support Client Server architect architecture ure
6. DBMS DBMS file file leve levell loc locki king ng RDBM RDBMS S row leve levell loc locki king ng [Ora [Oracl cle e 10g 10g autom utomat atic ic row row lev level el loc locking king]] New version of Oracle cell level locking 7. DBMS Distri Distribut buted ed data databa bases ses not not suppor supported ted RDBMS Most RDBMS support support Distributed Distributed database database [Largest Distributed databases in Oracle Yahoo! 20000 Oracle Linux Server in Silicon Valley and in Australia] 8. DBM DBMS No Se Security RDBMS Multiple level of Security Security [Oracle Server Database crated crated in another Partition Partition of HDD] Level: logging in [username/password] /command [DBA restriction on using Commands (134 command in Oracle 10g)] /object [Grant Permissions on Owner Object]
Oracle Tools/Oracle Products SQL o Structured Query Language o Common to all database o Conforms to ANSI standards. And ISO standard o Initially by IBM 1978-79 Now controlled by AN SI o Sequel o Commands (12 commands) Create drop alter Insert update delete Select Grant revoke Rollback commit savepoint SQL*Plus/iSQL*Plus o Extension to SQL o To remove the limitations of SQL Commands (54 commands) o Character based Reporting tool (ERPs) o Oracle Client s/w o Interface with the database for running SQL and SQL*Plus commands PL/SQL o Procedural language o Programming language of Oracle o 4 GL (as C++ supports OOPS Exception E rror handlers Encapsulation) Oracle Forms (Oracle Forms 10g) o Creating data entry screens Oracle Reports (Oracle Reports 10g free of cost) o Creating reportsfont color web XML HTML/DHTML Oracle Menus o Popup, full screen, toolbars, pull down, bar menu etc. Oracle Graphics o Pie charts, bar graphics (TV channel share election sport rating) •
•
•
•
•
•
•
•
Ids Integrated Integrated Developmen Developmentt Suite [forms+repo [forms+reports+m rts+menus+ enus+grap graphics] hics] o Front end no I in market Exp (export utility used for taking Backups of table data) Imp (Import utility used for restoring from Backups of table data) Oraterm Sql*loader OEM Oracle Case Designer 2000 Pro*c Pro*c++ Pro*java Oracle Applications [] Oracle Financial (Oracle Financial Financial 11i) o ERP enterprise wide Resource Planning S/W readym ymad ade e for for fina financ ncia iall app appli lica cati tion onss [no [no 1 is SAP] SAP] o read o customization can be done as source code is all ready given Oracle Manufacturing (Oracle Manufacturing 11i) o ERP enterprise wide Resource Planning S/W readymade for Manufacturi Manufacturing ng Industry Industry (no I JD Edwards [taken by Oracle]) o readymade o customization can be done as source code is all ready given Oracle Hrms (Oracle HRMS 11i) o ERP enterprise wide Resource Planning S/W (no I Peoplesoft [taken by Oracle] ) o readymade for HRD o customization can be done as source code is all ready given Oracle Crm (Oracle CRM 11i) o ERP enterprise wide Resource Planning S/W no I Siebel systems [taken by Oracle]) o readymade for CRM o customization can be done as source code is all ready given o
• • • • • • • • • • • •
•
•
•
•
SQL o o o o o o o
DDL DML DM L DCL DCL DQL DQL DTL
Structured Query Language Standard Query Language Common to all database Conforms to ANSI standards. And ISO standard Initially by IBM 1978-79 Now controlled by AN SI Commands (12 commands) Execution is top to bottom left to right
data data defin definit itio ion n lan langu guag age e [Cre [Creat ate e dro drop p alt alter er]] data data mani manipu pula lati tion on lang langua uage ge [Inse [Insert rt upda update te dele delete te]] data data cont contro roll lang langua uage ge [Gr [Gran antt Rev Revok oke] e] data ata quer query y lang langua uage ge [se [sele lect ct]] data transactional language [Rollback commit savepoint]
Rules of table names/ column names: o Must be 1-30 characters long. Object names or Qualifier are case insensitive EMP or emp same
o o o o
Should not be an Oracle Server reserved word. Must contain only A-Z, a-z, 0-9, _, _ , $, and #. [avoid $ and # can’t work in other RDBMS] Must begin with a letter/Alphabet (use descriptive name) Must not a duplicate name of another object owned by same user.
Data type: CHAR
(ALLOW (ALLOWS S ANY ANY CHARACT CHARACTER ER VALUE, VALUE, 1-2000) 1-2000) TAKER Char instead of Varchar2 for better search Padded by spaces if all fields not inserted NUMBER (ALLOWS ANY NUMBER 1.0*10-130 T0 9.9*+125) If numbe number(7, r(7,2) 2) input input 100. 100.555 555 it assu assumes mes as roun roundin ding g takes takes place place as as 100.56 100.56 DATE (1/1/10000 BC – 31/12/10000 AD) [VALID DATE BETWEEN January 1, 4712 B.C. TO December 31, 9999 A.D.] ARITHMETIC OF DATA IS POSSIBLE Italian calendar Julian Date British calendar Georgian Date (allowed date in oracle) 5th Oct 1582 AD to 14th Oct 1582 AD not exist in oracle • Date and time stored together DEFAULT DAY 01 MONTH (current month) century current AD time 00:00:00 • *year can’t be default VAR VARCH CHAR AR (ALL (ALLOW OWS S ANY ANY CHAR CHARAC ACTE TER R VALU VALUE, E, 1-4 1-400 000) 0) Old Older er vers versio ion n of ora oracl cle e1 In 10g for VARCHAR backward compatibility VARCHAR2 (ALLOWS ANY CHARACTER VALUE, 1-4000) oracle 7 str string comparison ca can do done in in VA VARCHAR2 as >= >=’A’ LONG 2 GB of character RAW 2000 bytes of binary data Multimedia barcodes small pictures signatures thumb impressions LONG RAW 2 GB of binary data photo music sound video • • • •
•
•
Max 1000 columns per table Only 1 column either long or long raw in 1 table [Recommended 1 columned table with 1 long column] TABL TABLE: E: EM EMP P COLUMN: EMPNO CHAR (4) ENAME VARCHAR2 (25) SALARY NUMBER (7, 2) CITY varchar2(15) DOB date Create table EMP { EMPNO CHAR (4), ENAME VARCHAR2 (25), SALARY NUMBER (7, 2), CITY varchar2(15), DOB date }; 1. INSERT INTO EMP VALUES ( ‘1’, ‘Amit’, 5000, ‘Bombay’, ’10-JAN-1975’ ); *2. INSERT INTO EMP (EMPNO, ENAME, SALARY, CITY, DOB) VALUES(‘2’, ‘Ravi’, 5000, ‘Jack’, ’19-JAN-1975’); 3. INSERT INTO EMP (EMPNO, SALARY) VALUES(‘0’,15000);
4. INSERT INTO EMP VALUES(‘0’,’Rakesh’,null, null, null); * Recommended Null values same for “char varchar2 date number” Null value in middle takes 1 byte (in case char also 1 bytes) If ending columns have null then 0 bytes for that field Query Select * from emp * me meta character [all columns] Logi Login: n: Host Hostst stri ring ng server ip or machine name Dbname Protocol Port no Where clause Oracle searches on the media like HDD then sends to Server RAM then sends to Client RAM Relational Operator as precednce 1. GREATER TH THAN > 2. GREA GREATE TER R THAN THAN EQUAL EQUAL TO >= 3. LESS THAN < 4. LESS LESS THAN THAN EQUA EQUAL L TO TO <= <= 5. NOT NOT EQU EQUAL AL TO <> / ^= ^= /! /! = 6. EQUAL TO = BETWEEN A AND B ONLY 2 VALUES mutually inclusive Not between A AND B ONLY 2 VALUES mutually exclusive IN (SET) LIST OF VALUES discrete LIKE A CHARACTER PATTERN IS NULL
/faster than < = >
LOGICAL CONDITION 1. AND 2. OR 3. NOT PRECEDENCE OF OPERATOR 1. Arithmetic Arithmetic [ARTIHMETIC [ARTIHMETIC EXPRES EXPRESSIONS/OP SIONS/OPERATOR ERATOR BY PRECEDENCE PRECEDENCE ( ) ** * / + -] () ** /*+- sql ** Exponentiation sal**3 sal**(1/3) 2. Conc Conca atena tenati tion on 3. Comp Compar aris ison on Cond Cond.. 4. Is [no [not] t] nul null, l, lik like, e, [no [not] t] in in 5. [Not [Not]] betw etween een 6. NOT NOT LOGI LOGICA CAL L COND CONDIT ITIO ION N 7. AND AND LOGI LOGICA CAL L COND CONDIT ITIO ION N 8. OR LOGI LOGICA CAL L CON CONDI DITI TION ON Select Sal*12 from emp; // sal*12 Computed Table/virtual column/fake column Alias Select Sal*12 “ANNUAL” from emp; Select Sal*12 ANNUAL from emp;
Make alias case sensitive sensitive as in “ ” or long character “ ” Sele Select ct uniq unique| ue|Di Dist stin inct ct enam ename, e, job job fro from m emp emp;; comb combin inat atio ion n Distinct | unique Order by clause clause desc Sorting select * from emp where ename >=’A’ and ename ename <’B’ Wildcards % DENOTES 0 OR MANY CHARACTER | _ | _ DENOTES 1 CHARACTER [underscore] ‘\_P%’ ESCAPE ’\’ [HERE ERE ESCAPE APE CHARACTER CTER \ \] =any(10,20 0,20)) ~ in faster <>all(10,20) ~ not in faster • • • •
in any
any more than the maximum =any as IN all all all >all less less than than the the ma maxim ximum um <> <>AL ALL L as NOT IN not operator can be use with in any all
Update Delete Delete delete delete from from emp; emp; ANSI ANSI delete delete emp; emp; delete delete emp; emp; Drop All DDLs are autocommit Commit rollback Transaction Control DML commands Savepoint (Commit/rollback/savepoint (Commit/rollback/savep oint savepoint_name /rollback to savepoint_name) Exit; command in SQL*Plus then it automatically commit All pending transactions Any other case improper exit power failure n/w failure Creating second savepoint earlier savepoint is deleted Creating second savepoint earlier savepoint is in the memory. Savepoint sequentially first s2 then s1; CREATE table new_emp ( employee_id NUMBER, name VARCHAR2(30)); INSERT INTO new_emp SELECT employee_id , last_name from employees; Savepoint s1; UPDATE new_emp set name = UPPER(name); Savepoint s2; update new_emp set name = lower(name) where employee_id =180; Rollback to s2; Delete from new_emp where employee_id =180; Rollback to s1; Query in table view committed data of all users and uncommitted data of your transactions Whether the other users view only committed data of all users in database Delete command Marked for deletion Locks: Prevent destructive interaction between concurrent transactions Require no user action Record locking in Oracle is Automatic only in update or delete • Other user only view those data Held when transaction in progress locks only user modified data • •
• •
To avoid a wait lock the record manually.(Pessimistic Locking) Explicit locking User puts manual lock o Select * from table_name [where clause] for update [nowait/wait timeN]; o Select * from emp for update wait 60; o Provide rollback or commit to unlock
• •
Selec Selectt * from from tab; tab; tab tab [sy [syst stem em tabl table/ e/da data ta dict dictio iona nary ry/d /dat atab abas ase e cat catal alog og]] 352 system table in Oracle 10g in 9i 326 SQL*Plus comm comman and d passw passwor ord[ d[pa pass ss][ ][;] ;] describe [desc] tab_name [;] SQL Buffer Buffer tempo temporar rary y text file file EXECUTE / Run[r] (list and execute) af i e d t abbreviation of of list list[l [l]] what hat is in the buff buffer er SQL> 1 2* SQL> 2* SQL> SQL> 1 2* SQL> 1* SQL> SQL> 1 2* SQL> 1* SQL> SQL> 1 2 3* SQL> 2 SQL> SQL> 1 2*
Chan Change ge pass passwo word rd /bin/a /bin/afie fiedt. dt.buf buf last last SQL comman command d
6 people founded Oracle Corp. list list the buff buffer er cont conten ents ts
L SELECT * FROM EMP; * buffer emp L2 FROM EMP; C/ EMP/DEPT C CHANGE L SELECT ENAME FROM EMP; * buffer emp L1 SELECT ENAME A ,SAL A APPEND L SELECT ENAME, SAL FROM EMP; L1 SELECT ENAME,SAL I ,DEPTNO I insert L SELECT ENAME, SAL ,deptno FROM EMP; L2 ,deptno DEL del delete L SELECT ENAME, SAL FROM EMP;
Save the buffer as file
SQL> save \path\]file_name[.extn] SQL> save [\path \]file_name[.extn] replace //default //default location location is /bin/file_na /bin/file_name.sq me.sqll //default //default extension extension .sql To Ru Run th the sa saved fifile SQL>@[\filepath\]filename[.extn] Save Saved d fil file e Loa Load d in in the the buff buffer er SQL> SQL> get get [\ [\ path path \]fi \]file le_n _nam ame[ e[.e .ext xtn] n] SQL> SQL> Defi Define ne_ed _edit itor or = [note [notepa pad d /textpad/wordpad/vi/edit.com] System editor for Oracle in windows notepad in unix vi for user_session only To make make change changess perma permanent nent modify modify the the star startup tup file file for for SQL*P SQL*Plus lus C:\oracle\product\10.2.0\client_1\sqlplus\admin\ glogin.sql glogin.sql --- Copyright (c) 1988, 2004, Oracle Corporation. Corporation. All Rights Reserved. Reserved. --- NAME -- glogin.sql --- DESCRIPTION -- SQL*Plus global login "site profile" file --- Add any SQL*Plus commands here that are to be executed when a -- user starts starts SQL*Plus, SQL*Plus, or uses the SQL*Plus CONNECT command --- USAGE -- This script is automatically run --- Used by Trusted Oracle COLUMN ROWLABEL FORMAT A15 -- Used for the SHOW ERRORS command COLUMN LINE/COL FORMAT A8 COLUMN ERROR FORMAT A65 WORD_WRAPPED -- Used for the SHOW SGA command COLUMN name_col_plus_show_sga FORMAT a24 COLUMN units_col_plus_show_sga FORMAT a15 -- Defaults for SHOW PARAMETERS COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE -- Defaults for SHOW RECYCLEBIN COLUMN origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME' COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME' COLUMN objtype_plus_show_recyc FORMAT a12 HEADING 'OBJECT TYPE' COLUMN droptime_plus_show_recyc FORMAT a19 HEADING 'DROP TIME' -- Defaults for SET AUTOTRACE EXPLAIN report -- These column definitions are only used when SQL* Plus -- is connected to Oracle 9.2 or earlier. COLUMN id_plus_exp FORMAT 990 HEADING i COLUMN parent_id_plus_exp FORMAT 990 HEADING p COLUMN plan_plus_exp FORMAT a60 COLUMN object_node_plus_exp FORMAT a8 COLUMN other_tag_plus_exp FORMAT a29 COLUMN other_plus_exp FORMAT a44 -- Default for XQUERY COLUMN result_plus_xquery HEADING 'Result Sequence' -- MODIFY HERE Define_editor = notepad
-- Copyright (c) 1988, 2004, Oracle Corporation. Corporation. All Rights Reserved. Reserved. ************************************ Edit [ed] [\path \]file_name[.extn] afie afiedt dt.b .buf uf
[afiedt.buf]
only only one one SQL SQL comm comma and don’ don’tt del delet ete e / don don’t ’t put ;
set feedback off/on set pagesize [14] set linesize [80] set verify on/off
max 50000 max 50000
Substitution variables: &v_name pre pr processor before co compilation ‘& ‘&v_na v_name me’’ &v_na v_name me = CLE CLERK RK|& |&v_ v_na name me = ‘CL ‘CLER ERK’ K’ && Accept v_name datatype prompt ‘text’ Acc Accep eptt v_n v_nam ame e num numbe berr [pr [prom ompt pt ‘Ple ‘Pleas ase e giv give e a dept deptno no:’ :’]] Prompt te text Prompt We Welcome Define/undefined v_name [= 10] Select * from emp where deptno = &v_name; Set sqlprompt ‘iflex>’
v_na v_name me sess sessio ion n var varia iabl ble e
Multiple lines give a hyphen in SQL plus SQL> desc – > emp iSQL*Plus http://trainer:5560/isqlplus/ Help index Help [topic] @ @@ / ACCEPT APPEND ARCHIVE LOG ATTRIBUTE BREAK BTITLE CHANGE CLEAR COLUMN COMPUTE CONNECT
COPY DEFINE DEL DESCRIBE DISCONNECT EDIT EXECUTE EXIT GET HELP HOST INPUT LIST PASSWORD
PAUSE SHUTDOWN PRINT SPOOL PROMPT SQLPLUS QUIT START RECOVER STARTUP REMARK STORE REPFOOTER TIMING REPHEADER TTITLE RESERVED WORDS (SQL) UNDEFINE RESERVED WORDS (PL/SQL) VARIABLE RUN WHENEVER OSERROR SAVE WHENEVER SQLERROR SET SHOW
SQL functions Single row functions Char functions Concatenation operator || fname||lname Conc Concat at(f (fna name me,l ,lna name me)) only only 2 par param amet eter er Nested function 255 func. In Oracle 9i but in 10g no limit Uppe Upperr (1 (1 par param amet eter er)) case case-i -ins nsen ensi siti tive ve quer query y
Lower (1 parameter) Initcap(1 parameter) 1st letter converted Upper else Lower for every word Initcap(Arun pal) Arun Pal Lpad(parameter, length, ‘char har’) overloading OOP OOP concept Rpad(parameter, length, ‘char’) Ltrim(par Ltrim(paramet ameter, er, length, ‘char’) Ltrim(ename Ltrim(ename,, ‘*$%&’) ‘*$%&’) Rtrim(parameter, length, ‘char’) Rtrim(ename, ‘*$%&’) Subs Substtring ring(p (pa arame ramete terr, ±st ±sta art_p rt_pos os)) sta start_p rt_pos os to end end Substr Substring ing(pa (param ramete eter, r, ±sta ±start_ rt_pos pos,, 2) start_ start_pos pos to 2 more more charchar-> > tota totall 2 Substring(parameter, -3 -3) start 3rd last position Substring(parameter, -3,2) start 3 rd last position to 2 more char-> total 2 Replace (parameter, old_str, new_str) Repl Replac ace e (par (param amet eter er,, old_st old_str) r) all all old_ old_st strr is rem remov oved ed Tran Transl slat ate( e(en enam ame, e,’u ’un’ n’,, ’xy’ ’xy’)) char charac acte terr by cha chara ract cter er u=x u=x n=y n=y same same len lengt gth h Trans anslate ate(ename,’un ’un’, ’x’) u=x n is is re removed Inst Instr( r(pa para rame mete ter, r, src srch_ h_st str) r) retu return rn typ type e is num numbe berr in fir first st occ occur ur_p _pos os Instr(para Instr(parameter, meter, srch_str, srch_str, ±star ±start_pos t_pos)) begin start from start_pos start_pos Instr(parameter, srch_str, ±start_pos,occur) Instr(ename Instr(ename,, ‘ab’ ‘ab’ ,4, 2) 2) found ab from from 4th pos and 2 nd occurrence Leng Lengtth(pa h(para rame metter) er) retur eturn n typ type is is num number ber st Ascii(parameter) ascii value of 1 char Chr( Chr(nu numb mber er)) numb number er 0-25 0-255 5 Soundex(parameter)) remove the vowel then Soundex(parameter then check European Accent Select * from emp where Sounder(‘Arun’) = Soundex(‘Aroon’) Parameter = String/Column_name Dua Dual Sy System Dum Dumm my ta table datatype ch char(1) Number Functions ROUND(number) float to integer ROUND(number, ±n) n’th digit after decimal point 1291,-2 1300 TRUNC(number) cuts 0ff decimal parts TRUNC(number, ±n ±n) cuts 0f 0ff de decimal pa parts/REPLACE wi with 0s 0s 1291,-2 1200 ceil(number) adds on one ifif ha has an any de decimal va value interest ca calc. floor( floor(numb number) er) cuts cuts 0ff 0ff decima decimall part partss positive number no difference between trunc and floor trunk(2.3) 2 floor(2.3) 2 negative number difference between trunc and floor trunk(-2.3) -2 floor(-2.3) -3 sign( ign(±n ±num umbe berr) retur eturn n if +ve +ve ans ans: +1 -ve ans: ans: -1 0 ans: ans: 0 sign(x-y) +1 x>y -1 x
DATE± DATE± NUMBER = DATE DATE - DATE = DAYS DATE± DATE±NUMBER/24 = DATE MONT MONTHS HS_B _BET ETW WEEN EEN (DA (DAT TE, DATE DATE)) cale calend ndar ar mont months hs *Decimal part balance days hours minutes seconds ADD_MONTHS (DATE, ± number) decimal part balance days hours minutes seconds Adding/Reducing 2 months as by calendar NEXT_DA NEXT_DAY Y (DATE, (DATE, DAY)here DAY)here Day= Day= Monday, Monday,….Su ….Sunda nday y o/p in Oracl Oracle e Date Date format format LAST_DAY (D (DATE) o/p la last DA DAY of of mo month in in Or Oracle Da Date fo format DDDD-MON-R N-RR ROUND TRUNC DATE FORMAT MODEL TO_CHAR(DATE,’FORMAT MODEL’) ’FORMAT MODEL . GERMAN SEPARATOR AMERICAN SEPARATOR / BRITISH SEPARATOR : FRENCH SEPARATOR * ITALIAN SEPARATOR upper lower initcap J JS JSP JT JTH JS JSPTH TO_D O_DATE( ATE(T TO_C O_CHAR HAR(2000, 000,’’J’),’JSP’ SP’) DY DAY D DSP DTH DSPTH [DAY OF WEEK SUNDAY = 1] DD DDSP DDSPTH DDTH DDD DDDSP DDDTH DDDSPTH W WSP WTH WSPTH (1-5 WEAK OF MONTH) MON TH) WW WWSP WWTH WWSPTH (1-52 WEAK OF YEAR) MM MMSP MMTH MMSPTH MON MONTH Y YY YYY YYY YYYY YYYY YEAR YEAR TO_C TO_CHAR HAR(2 (2000 000,’ ,’ye year ar’) ’)
TIME FORMATS: AM/ AM/PM PM A.M. A.M./P /P.M .M.. HH HH24 MI SS ENVIRONMENT FUNCTION: (Used to maintain logs of user actions) SELECT USER, UID FROM DUAL; SELECT USERENV (‘TERMINAL’), USERENV (‘SESSIONID’) FROM DUAL; In standard use USER, USERENV (‘TERMINAL’) in insert statement. CONNECT / DISCONNECT List function (independent of data type) Search for null pessimistic query = null null if Null Null compar compariso ison n true true then then returns returns null null Treatment is [not] null used instead of [!]= of [!]= null Any arithmetic operation with null returns null Treatment: nt: (for for all datat tatypes) a. NVL(EX NVL(EX1,EX 1,EX2) 2) EX1 EX1 NULL NULL PUT/R PUT/RETUR ETURN N EX2 EX2 Greatest Greatest (ex1… (ex1… ex255) ex255) LEAST(ex LEAST(ex1… 1… ex255) any data type Select Greatest (sal, 3000) from emp; return greater of 3000 and =3000
Select Least (sal, 3000) from emp;
return lesser of 3000 and =3000
DECODE (COL/EXP, SEARCH1, RESULT1, SEARCH2,RESULT2, [DEFAULT_RESULT]) If DEFAULT_RESULT not specified then null is returned. Select decode (sign (sal-5000), 1,’H’,-1,’L’, 0,’M’) FROM EMP;
Multi row functions Group/Aggregate function Sum(column) Avg(column) Min(column) max (column) count(sal) count(*) stddev (column) variance (column) select Aggregate_func (sal) from emp where deptno = 1; used to retrieve the rows from server disk to server ram
Limitations of SQL in Aggregate_func : Cant use in where clause Select * from emp where sal> avg(sal) not done Select * from emp where sal> (Select avg(sal) from emp ); solution solve in SQL*Plus Select Single_row_func (column), Aggregate_func (column) from table_name; not done Select column, Aggregate_func (column) from table_name; not done Select column, Aggregate_func (column) from table_name group by column; solution solve in SQL*Plus 1. Only selected column rows are retrieved from derver hdd to server ram 2. Sorting Sorting column column wise which specified specified in group group by by clause clause 3. Grou Groupi ping ng be be done done col colum umn n wise wise 4. Comput Compute e the the operat operation ion using using loop loop 5. havi having ng work work as a filte filterr 6. Sorting Sorting used used in at last last in select select statemen statementt using using order order by Group by Rules *Whichever Column is in select statement must be in group by * Whichever Column is in group by Clause need not be present in select statement *where clause can come before group by *sequence of columns in group by aff ects speed of processing *Specify condition on group by output use having [strictly in group by] *sorted o/p use order by after group by Group function can be used in having h aving clause without using select statement Select Where • •
• • •
Group by Having Order by
Nested group functions Max(sum(sal)) allowed for Oracle SQL not in ANSI SQL Matrix report Select deptno, count(*), sum(sal), max(sal), min(sal), avg(nvl(sal,0)) from emp group by deptno; SQL Joins Cartesian product Join condtn omitted/invalid All rows in 1 st table joined to all rows in 2 nd table Guidelines: Always use table_name.column_name for better output. Select dept.deptno,dname,ename from emp, dept where dept.deptno=emp. .deptno; Driven table emp more no. of rows Driving ta table dept lesser no no. of of ro rows [recommended] sequence of columns in from affects speed of processing in join condtn. Type of joins Equijoin sim simple jo join /i /inner jo join based on on pr primary ke keys an and fo foreign ke keys You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions Use = for Non-equijoin Frequently used for 90% Non-Equijoin don’t us use = between is simplest process for non-equijoin Use != for Non-equijoin Frequently not used Outer Join (+) placed on the side of the join that is deficient/missing in information in/or operator prohibited as In Inner join do-while loop use Use (+) sign next to child table or where the foreign key is used FULL OUTER JOIN TO VIEW all unmatched data from both tables. Arbitary join condtn. On outer join And expression Outer join can appear only one side of the expression In OUTER JOIN Cannot use IN operator/cannot be linked with OR • operator You cannot link a condition that is involved in an outer join to another condition by using the OR operator. Sel Self Jo Join Open Open twic twice e ta table ble in in Ram Ram and and Joi Join n ta table ble to to it. it. It is slow slow.. Based on recursion try to to avoid • • •
•
*If common column define qualifying ambiguous column names with table aliases upto 30 char Sql 1999 ANSI SQL Join EQUIVALANCE ORACLE SQL Cross Cartesian product Natural common co column (s (same na name an and da datatype) us used ON clause Join on (col name) Equijoin Using clause Left/Right/Full outer join
join using Left outer join
Non-equijoin /In-equijoin Outer join
Sub Query A single row subquery can retrieve only one row but many columns. A single row subquery can retrieve data from more than one table. A multiple row subquery can retrieve multiple rows and multiple columns. A multiple row subquery can be compared by using the “>” operator (only case of all/any). The multiple-row subquery cannot be used with the comparison operator A multiple-row subquery cannot be used in an INSERT statement to insert multiple rows at a time. Subquery Retrieve data based on an unknown condition Multiple row • Subquery or inner query includes Where / Having/ From / Operator • Subquery in parenthesis ( ) • No limit in levels in 10g ( 9i upto 255 level) • RHS of comparison condition. • If Top Top-N analy nalysi siss the then n ord order er by nee neede ded d othe otherrwise wise not not • Sing Single le row row oper operat ator or with with Sing Single le row row Sub Subqu quer ery y (re (retu turn rn one one row row)) • Mult Mu ltip iple le row row ope opera rato torr with with Mul Multi tipl ple e row row Subq Subque uery ry (re (retu turn rn mul multi tipl ple e rows rows)) • Executes sub queries first • Comm Common on Erro Error: r: More More than than one one row row retu returrned ned for for a sin singl gle e row row subq subque uery ry Single row operator with Multiple row sub query Use IN/NOT IN instead of =, LIKE, BETWEEN, IS, <> [can’t use] • *Com *Compa pari riso son n of two two null null valu values es yiel yields ds a nul nulll the then n whe where re cond condtn tn.. Is not not tru true e • in • any any any >any more more than than the the ma maxi ximu mum m =any =any as IN • all all less than the maximum <>ALL as NOT IN • not operator can be use with in any all
Select * from emp Where sal =any (Select sal from emp where job = ‘M’);
slower
Select * from emp Where sal IN (Select sal from emp where job = ‘M’);
faster
Select * from emp Where sal>= (Sel (Selec ectt min( min(sa sal) l) fro from m emp emp whe where re job job = ‘M’ ‘M’); );
fast fastes estt
Select * from emp Where sal > all
(Select sal from emp where job = ‘M’);
slower
Select * from emp Where sal > (Sele (Select ct ma max(s x(sal al)) from from em emp p whe where re job job = ‘M’) ‘M’);;
fast fastes estt
SET Operator Union combine o/p of both tables and and suppress suppress the duplicates in same structured select statement and same data types column name in o/p from 1st select statement Union all combine o/p of both tables and not suppress suppress the duplicates Intersect common o/p of both tables and and suppress the duplicates duplicates Minus first table contents contents which which not present present in second second suppress suppress the the duplicat duplicates es pseudo column Programmer created computed field expression function column System created Rownum Rowid
in re respect to to Ta Table it it us using but but rowid is is th the ad address of of th the ro row
Spool Spool filename Spool abc Spool off
Ed abc.lst
ALTER: ALTER: DDL comma command nd Direct effect: Rena ename a table RENA ENAME OL OLD_TA _TABLE_NAM NAME TO NE NEW_TABLE_NA E_NAM ME; Add a column alter ALTER TABLE EMP ADD COLUMN_NAME DATATYPE (n); Drop a column ALTER TABLE EMP DROP COLUMN COLUMN_NAME; Increase the width of a column ALTER TABLE EMP DROP MODIFY COLUMN_NAME DATATYPE (n); Indirect effect: Decrease the width of a column Set the column column contents contents to to null Decrease Decrease the width width of a column column Add another column x UPDATE EMP SET X = ENAME, ENAME = NULL; Decrease the width of a column UPDATE EMP SET ENAME=X;
Change a data type Set the column contents to null ALTER TABLE EMP MODIFY COLUMN_NAME DATATYPE (n); Copy a table CREATE TABLE N_TABLE_NAME AS SELECT * FROM O_TABLE_NAME; Structure of n_table_name are created based on select statement. then data cpoied to n_table_name from o_table_name Copy a structure of table Create table n_table_name as select * from o_table_name; wher where e 2=1; 2=1; -- abno abnorm rma al cond condit itio ion n Change sequence of columns Create table n_table_name as select empno, sal, ename from o_table_name; Drop o_table_name Rename n_table_name to o_table_name Rename column Create table n_table_name as select empno, sal SALARY, ename from o_table_name; Drop o_table_name Rename n_table_name to o_table_name Alter table table_name RENAME column O_COLUMN_NAME TO N_COLUMN_NAME; Alter table spj rename Column qty to qtty; Indexes: Speed up the search operation /faster access/ Improve performance of some queries (SELECT) Index contain table_column in ascending order and corresponding row id Null values are not stored in index Composite index two or more columns index key (column or set of column on based which index is created ) First column of index Primary index Second column of index Secondary index CREATE INDEX index_name ON table_name (column_name1,…, column_nameN [desc]); CREATE INDEX index_name ON table_name UPPER(column_name1); //case-insensitive search User_ind_columns user_indexes More than 1 index on same single column not allowed CREATE Unique index index_name ON table_name (column_name); DROP INDEX index_name; //to remove index index definition from data dictionary --------------------------------------------------------------------------------------------------------------------------------------------------------------------
Constraints • Orac Oracle le serv server er uses uses cons constr trai aint ntss to prev preven entt inval invalid id data data entr entry y into into tabl tables es.. • Cons Constr trai aint ntss m mus ustt be sati satisf sfie ied d for for the the oper operat atio ion n to to succ succee eed. d. • Enfor Enforce ce rule ruless at at tab table le and and vie view w leve level. l. (Row (Row inse insert rtio ion, n, dele deleti tion on,, upd updat ating ing). ). • To preve revent nt DELE DELETI TION ON OF A TAB TABLE LE if ther there e ar are dep depen end denc encies. ies. • Prov rovide ide rul rules es for for Or Oracle acle Tool Toolss (e. (e.g g. Ora Oracl cle e Dev Devel elop oper er). ). • Cons Constr trai aint ntss stor stored ed in data data dict dictio iona nary ry (USE (USER_ R_CO CONS NSTR TRAI AINT NTS) S).. • Prov Provid ide e a me mean anin ingf gful ul name name foll follow ow stan standa dard rd obje object ct-n -nam amin ing g rul rules es.. • If not not spec specify ify name name Orac Oracle le serv server er gener generat ates es a nam name e as as SYS SYS_Cn _Cn [n is is int integ eger er to ma make ke the the name unique] • Crea Create te a con const stra rain intt at at the the same same time time or afte afterr of of tab table le crea creati tion on.. • Define a Constraint in ta table or column level. • Crea Create te tabl table e sch schem ema. a.ta tabl ble_ e_na name me (col (colum umn_n n_nam ame e data data type type(s (siz ize) e) [defa [defaul ultt expr] expr] [column_constraint], …,[table_constraint]); • colum column_ n_co cons nstr trai aint nt leve level: l: refe refere renc nce e a sing single le colu column mn defin defined ed with within in a spec specifi ifica cati tion on for for owning column, define any type of integrity constraint. column [constraint constraint_name] constraint_type e.g. e.g. create create tabl table e t1(Emp t1(Emp_id _id numb number( er(6) 6) const constrai raint nt emp_i emp_id_u d_uk k unique unique,, ename ename char( char(10) 10) not not null); here constraint for Emp_id is user named & constraint constraint for ename system named table_constraint level: reference one or more columns defined separately from definitions of column, any type of integrity constraint except NOT NULL. [constraint constraint_name] constraint_type (column_name) e.g. e.g. create create tabl table e t1(Emp t1(Emp_id _id numb number( er(6), 6), ename ename char char(10) (10) not not null, null, const constrai raint nt emp_i emp_id_u d_uk k unique(Emp_id)); • NOT NOT NUL NULL L can can be spec specif ifie ied d onl only y in in col colum umn n lev level el not not in in tab table le leve levell • UNIQ NIQUE every value in a column must be unique • Sing Single le colum column n (set (set of colu column mns) s) uses uses this this const constra rain intt is is cal calle led d uni uniqu que e key key if two two or more more column comprises this groups of columns is called composite unique key. • UNIQU UNIQUE E can can be be spe speci cifi fied ed in col colum umn n or or tab table le leve level. l. Comp Compos osit ite e uni uniqu que e key key spec specifi ified ed tabl table e level • Uniq Unique ue Con Const stra rain ints ts allo allow w the the inpu inputt of of null nullss bec becau ause se null nullss are are not not con consid sider ered ed equa equall to to anything. So user also defines NOT NULL to prevent null acceptance • Orac Oracle le serv server er enfor enforce cess UNIQ UNIQUE UE con const stra rain ints ts by impl implic icit itly ly crea creati ting ng a uni uniqu que e ind index ex on the the unique key column or columns. • Prim Primar ary y key key cons constr trai aint nt is is a colu column mn or sets sets of col colum umns ns,, only only one one pri prima mary ry key key is is def defin ined ed.. • Enfor Enforce ce uniqu uniquen enes esss for for colu column mn or colu column mn comb combin inat atio ion n but but don’ don’tt acc accep eptt nul nulll val value ues. s. • Prim Primar ary y key key can can be be spe speci cifi fied ed in colu column mn or tabl table e leve level. l. Comp Compos osit ite e Pri Prima mary ry key key spe speci cifi fied ed table level • Tabl Table e con conta tain inss only only one one pri prima mary ry key key con const stra rain intt but but seve severa rall uni uniqu que e con const stra rain ints ts.. • A UNI UNIQU QUE E ind index ex is auto automa mati tica call lly y cre creat ated ed for for a Prim Primar ary y key key colu column mn.. • Fore Foreig ign n Key Key refe refere rent ntia iall int integ egri rity ty desi design gnat ates es a col column umn or comb combin inat atio ion n of of col colum umns ns & establishes a relationship between a primary key or a unique key in same or different table. • Fore Foreig ign n Key Key must must ma matc tch h an an exi exist stin ing g val value ue in the the par paren entt tab table le or be NULL NULL.. • Fore Foreig ign n Key Key are are base based d on on dat data a val value uess and and are are pur purel ely y logi logica cal, l, not not phys physic ical al,, poi point nter ers. s. • Fore Foreig ign n key key can can be be spe speci cifie fied d in in col colum umn n or or tab table le lev level el.. Com Compo posi site te Fore Foreig ign n key key spec specif ifie ied d table level
• crea create te tabl table e t1( t1(Em Emp_ p_id id numb number er(6) (6) const constra rain intt emp emp_i _id_ d_fk fk refe refere renc nces es dept dept (Emp (Emp_i _id) d)); ); • crea create te tabl table e t1(E t1(Emp mp_i _id d numb number er(6) (6),, const constra rain intt em emp_ p_id id_f _fk k Fore Foreig ign n key key (Emp (Emp_i _id) d) references dept (Emp_id)); • FORE FOREIG IGN N KEY KEY:: Defi Define ness the the colu column mn in the the chi child ld tabl table e at at the the tabl table e con const stra rain intt lev level el • REFERENCES: Identifies the table and column in the parent table • ON DELE DELETE TE CASC CASCAD ADE: E: dele delete tess the the depe depend nden entt rows rows in the the chil child d tabl table e when when a row row in parent table is deleted • ON DELET DELETE E SET SET NULL NULL:: con conve vert rtss dep depen ende dent nt forei foreign gn key key val value uess to to nul nulll whe when n par parent ent tabl table e is deleted • Defa Defaul ultt beh beha avior vior of Fore Foreig ign n Key Key is ca called lled Rest Restrrict ict rul rule. e. • CHEC CHECK K is is a con condi diti tion on that that each each row row mus mustt sat satis isfy fy (bot (both h colu column mn or tabl table e lev level el)) • Refe Refere renc nces es to CURR CURRVA VAL, L, NEXT NEXTVA VAL, L, LEVE LEVEL, L, ROWN ROWNUM UM pseu pseudo doco colu lumn mnss • Calls to to SYS SYSDA DAT TE, UI UID, US USER AND USER SERENV fu funct nctions • Queries th that re refer to ot other her va values ues in in ot other ro rows • Sing Single le colum column n can can have have mult multip iple le CHECK CHECK Const Constra rain ints ts whic which h ref refer er to the the col column umn in its its definition. • No lim limit it to the the no. no. of CHE CHECK CK Con Const stra rain ints ts whic which h use userr can can defi define ne on a col colum umn. n. • sala salary ry numb number er(8) (8) chec check k (sa (sala lary ry>0 >0)) | city city varc varcha har2 r2(10 (10), ), cons constr trai aint nt emp emp_ci _city ty_c _ck k che check ck (city in (‘Kol’,’Delhi’,’Mumbai’)) • Add Add or or Dr Drop a con const strraint aint but can’ can’tt mod modif ify y it its st struct ructur ure e • ALTE ALTER R TABL TABLE E ADD ADD const constra rain intt cons constr trai aint nt_na _name me const constra rain int_ t_ty type pe (COLU (COLUMN MN_N _NAM AME[ E[s] s]); ); • ALTE ALTER R TAB TABLE LE EM EMP P ADD ADD CONS CONSTR TRAI AINT NT EM EMP_ P_MA MANA NAGE GER_ R_FK FK FORE FOREIG IGN N KEY KEY (MGR (MGRID ID)) REFERENCES EMP(EMPID); • If use userr doe doesn sn’t ’t spec specif ify y cons constr trai aint nt_n _nam ame e syst system em its itsel elff gene genera rate te a name name.. • Chan Change ge exis existi ting ng colu column mn to not not null null cons constr trai aint nt if the the tab table le is em empt pty y or colum column n has has value value for every row. • Add Add a NOT NOT NUL NULL L cons constr trai aint nt onl only y by by usin using g MODI MODIFY FY cla claus use e of ALTE ALTER R TABL TABLE E stat statem emen entt • ALTE ALTER R TABL TABLE E T1 MODI MODIFY FY NAME NAME CON CONSTRA STRAIN INT T NAM AME_ E_NN NN NOT NULL NULL;; • ALTE ALTER R TAB TABL LE TAB TABLE LE_N _NAM AME E DRO DROP P CON CONST STRA RAIN INT T con const strraint aint_n _na ame • CASCA CASCADE DE opti option on use used d to to dro drop p dep depen ende dent nt cons constr trai aint ntss (ref (refer eren enti tial al inte integr grit ity y to to PK PK or or UK). UK). • CASC CASCAD ADE E CON CONST STRA RAIN INTS TS used used alon along g DRO DROP P COL COLUM UMN N clau clause se/t /to o dro drop p mul multi tico colu lumn mn constraints. • ALTE ALTER R TAB TABLE LE TABL TABLE_ E_NA NAME ME DROP DROP prim primar ary y key key|u |uni niqu que( e(co colu lumn mn)| )| CONS CONSTR TRAI AINT NT constraint_name [cascade]; • ALTE ALTER R TABL TABLE E TAB TABLE LE_N _NAM AME E DROP DROP (col (colum umn_ n_na name me)) CASC CASCAD ADE E CONS CONSTR TRAI AINT NTS; S; • After After drop droppi ping ng a const constra rain intt it it no no lon longe gerr ava avail ilab able le by Orac Oracle le serv server er data data dict dictio iona nary ry • DISAB DISABLE LE Cla Claus use e to to deac deacti tiva vate te an an inte integr grit ity y cons constr trai aint nt// appl apply y casc cascad ade e to disa disabl ble e depe depend nden entt constraint • Alte Alterr tab table le tab table le_n _nam ame e disa disabl ble e cons constr trai aint nt con const stra rain int_ t_na name me cas casca cade de;; • Disa Disabl blin ing g a uni uniqu que e or or pri prima mary ry key key con const stra rain intt rem remov oves es the the uni uniqu que e ind index ex • Enab Enable le Cla Claus use e to to act activ ivat ate e an an int integ egri rity ty const constra rain intt use used d bot both h in CREA CREATE TE TABL TABLE E or ALTE ALTER R TABLE statement • Afte Afterr ena enabl blin ing g con const stra rain int, t, data data must must fit fit to to it it oth other erwi wise se repo report rt erro error. r. • Enab Enabli ling ng a uni uniqu que e or or pri prima mary ry key key con const stra rain intt cre creat ate e the the uniq unique ue inde index x • Enab Enabli ling ng a pri prima mary ry key key con const stra rain intt dis disab able led d wit with h CASC CASCADE ADE opti option on doe doess not not ena enabl ble e fore foreig ign n keys dependent on this primary key. • Data Data dict dictio iona nary ry view viewss to to vie view w con const stra rain ints ts for user user give given n or or sys syste tem-a m-ass ssig igne ned d nam name e • Sele Select ct CONS CONSTR TRAI AINT NT_n _nam ame, e, CONS CONSTR TRAI AINT NT_t _typ ype, e, sear search ch_c _con ondi diti tion on from from USER_CONSTRAINTS; o Here Here C CHEC CHECK, K, P PRIM PRIMAR ARY Y KEY KEY,, RR RREF EFER EREN ENTI TIAL AL INTE INTEGR GRIT ITY, Y, UUN UUNIQ IQUE UE KEY KEY o NOT NULL also CHECK type CONSTRAINT. • Sele Select ct CONS CONSTR TRAI AINT NT_n _nam ame, e, colu column mn_n _nam ame e from from USER USER_C _CON ONS_ S_CO COLU LUMN MNS; S;
select dept.*, a.tot from dept,( select emp.deptno, sum (emp.empno) (emp.empno) tot from emp group by emp.deptno --having emp.deptno = 10 10)a )a where dept.deptno = a.deptno
