INTRODUCTION

1. INTRODUCTION GUEST SPEAKER: DAWAR NAQVI OCCUPATION: ORACLE SPEACILIST, LOS ANGELES COUNTY, DEPARTMENT OF HEALTH SERVICES

2. Objectives • ORACLE VS OTHER • COMMON SQL TIPS • MAKE YOU THINK • DATE QUERIES • SPELLING OUT A NUMBER • SQL QUERY to SPELL THE NUMBER • TO CREATE A EXCEL SPREAD SHEET VIA SQLPLUS • WRAP UP --- QUESTION & ANSWERS

3. ORACLE VS OTHER Informix and Ingress -- they do not exist as entities any longer. They have been bought by companies that already have database products of their own. I'm not sure if you will recognize Informix 3 years from now or maybe you will (as it will be exactly the same as it is now).

4. Sybase Well, I spend a large amount of my time helping people convert from Sybase to something else. Since they sold themselves to MS a couple of years back and MS built a better sqlserver then sqlserver, they sort of put themselves out of the database business. I do not know personally of anyone doing new development with this product.

5. DB2 they have taken the "we'll build a database for every problem" approach. Ask them how many database products they actually have (and be sitting down when you do this as it will take a while to go through the list). Even DB2 doesn't mean "DB2" -- the different flavors of DB2 are incompatible with each other (different features and functions).

6. MS this is easy. Oracle is available on every platform of any worth. It always has been, always will be. 10 years ago, NT stood for NOT THERE. Unix was king. Go back 5 more years. Unix was just becoming cool, VAX VMS ruled, mainframes were starting to be considered that old stodgy technology (remember, PCs were still just starting to appear everywhere then as well). 5 Years before that -- mainframes ruled, mini computer systems just starting out. Now, fast forward 5 years from now.

7. To paraphrase and twist a famous commercial ask yourself "where do you want to be". In 5 years, I'll bet Windows is not the place - it'll be replaced by some new hardware/software combo and then where are you? you are with a database that limits you to exactly one OS -- their OS. That is the major, #1 difference between Oracle and other. If anyone feels Windows is the Platform of the 21st century..... and always will be, I guess its a safe choice. If they look at history, really look at it, they might begin to feel a little different.

8. SQL and Some tips • select * from v$version; • select sysdate from dual; • select * from nls_session_parameters where parameter = NLS_DATE_FORMAT';

9. Make You Think Mode of numbers 12876, 12874, 12000, 12222 I would like to round off to the multiples of ten, if the last number is less than 5 make it to next lower of 10. or if it is more than 5 than next higher of 10. 12880, 12870, 12000, 12220

10. SQL> select round (12876, -1), round (12874,-1) from dual; ROUND(12876,-1) ROUND(12874,-1) --------------- --------------- 12880 12870

11. Date Queries SQL> select to_char(sysdate,'j') from dual; TO_CHAR ------- 2454207 1 row selected. SQL> select to_date(2454207,'j') from dual; TO_DATE(2 --------- 16-APR-07 1 row selected. J instead for “Julian calendar” http://en.wikipedia.org/wiki/Julian_calendar

12. SPELLING Out a Number SQL> select to_char(sysdate,'J'),to_char(sysdate,'Jsp') from dual; TO_CHAR TO_CHAR(SYSDATE,'JSP') ------- ------------------------------------------------------------------------------ 2454207 Two Million Four Hundred Fifty-Four Thousand Two Hundred Seven 1 row selected.

13. create or replace function spell_number( p_number in number ) return varchar2 as type myArray is table of varchar2(255); l_str myArray := myArray('', ' thousand ’,'million', ' billion ', ' trillion ', 'quadrillion ', ' quintillion ', ' sextillion ', ' septillion ', ' octillion ', ' nonillion ', ' decillion ', ' undecillion ', ' duodecillion ' );

14. l_num varchar2(50) default trunc( p_number ); l_return varchar2(4000); begin for i in 1 .. l_str.count loop exit when l_num is null;

15. if ( substr(l_num, length(l_num)-2, 3) <> 0 ) then l_return := to_char( to_date( substr(l_num, length(l_num)-2, 3), 'J' ), 'Jsp' ) || l_str(i) || l_return; end if; l_num := substr( l_num, 1, length(l_num)-3 ); end loop; return l_return; end; / Function created.

16. SQL Query to spell the number select spell_number(12345678901234567890123456789012345678 ) from dual; SPELL_NUMBER(12345678901234567890123456789012345678) ------------------------------------------------------------------------------ Twelve undecillion Three Hundred Forty-Five decillion Six Hundred Seventy-Eight nonillion Nine Hundred One octillion Two Hundred Thirty-Four septillion Five Hundred Sixty-Seven sextillion Eight Hundred Ninety quintillion One Hundred Twenty-Threequadrillion Four Hundred Fifty-Six trillion Seven Hundred Eighty-Nine billion TwelvemillionThree Hundred Forty-Five thousandSix Hundred Seventy-Eight

17. TO Create a Excel SpreaD SheeT VIA SQLpluS SQL> SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF SQL&gt; SPOOL c:\temp\test_xls1.xls <br> SQL&gt; select * from scott.emp; SQL&gt; SPOOL OFF <br> SQL&gt; SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT ON

18. Result

19. Wrap Up • Questions and Answers Good Luck