1 / 31

oracle training institutes in hyderabad

Oracle 11g Institutes : kelly technologies is the best Oracle 11g Training Institutes in Hyderabad. Providing Oracle 11g training by real time faculty in Hyderabad.

kellytechnologies
Download Presentation

oracle training institutes in hyderabad

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Beginning SQL:Differences Between SQL Server and Oracle Presented By www.kellytechno.com

  2. Introduction If you're new to SQL or just new to Oracle SQL, perhaps coming from a Microsoft SQL Server environment, it may seem like the two versions should be very similar, and they are, to a certain degree, but they are also very different in some important and basic ways. www.kellytechno.com

  3. Agenda I. Quick Intro for SQL Server Users II. Some Detail: Joins, Subqueries, Deletes III. Certain Conceptual Differences IV. Powerful New Features V. Summary & References www.kellytechno.com

  4. Don’t Use Databases SQL Server use mydatabase Oracle connect mydatabase/mypassword www.kellytechno.com

  5. SQL Server select getdate(); Oracle select sysdate from dual; Use Dual www.kellytechno.com

  6. SQL Server select getdate() mycolumn into mytable; Oracle insert into mytable (mycolumn) values(sysdate); Select Into www.kellytechno.com

  7. SQL Server Insert mytable values(‘more text’); Oracle Insert into mytable values(‘more text’); Inserts www.kellytechno.com

  8. Updates SQL Server update mytable set mycolumn=myothertable.mycolumn from mytable,myothertable where mytable.mycolumn like 'MY%' and myothertable.myothercolumn='some text'; www.kellytechno.com

  9. Updates Oracle update mytable set mycolumn= (select a.mycolumn from myothertable a where myothertable.myothercolumn='some text'; ) where mytable.mycolumn like 'MY%'; www.kellytechno.com

  10. Deletes SQL Server delete mytable where mycolumn like 'some%'; Oracle delete from mytable where mycolumn like 'some%'; www.kellytechno.com

  11. Software SQL Server • isql • osql: for queries developed in SQL Analyzer Oracle • sqlplus www.kellytechno.com

  12. II. A Little More Detail • Outer Join • Sub-Queries in Place of Columns • Deletes With a Second From Clause www.kellytechno.com

  13. Outer Join SQL Server select d.deptname, e.ename from dept d, emp e where d.empno *= e.enum; Oracle select d.deptname,e.ename from dept d, emp e where d.empno = e.enum (+); www.kellytechno.com

  14. SubQueries in Place of Columns • SQL Server select distinct year, q1 = (select Amount amt FROM sales where Quarter=1 AND year = s.year), q2 = (SELECT Amount amt FROM sales where Quarter=2 AND year = s.year), q3 = (SELECT Amount amt FROM sales where Quarter=3 AND year = s.year), q4 = (SELECT Amount amt FROM sales where Quarter=4 AND year = s.year) from sales s; www.kellytechno.com

  15. SubQueries in Place of Columns Oracle SELECT year, DECODE( quarter, 1, amount, 0 ) q1, DECODE( quarter, 2, amount, 0 ) q2, DECODE( quarter, 3, amount, 0 ) q3, DECODE( quarter, 4, amount, 0 ) q4 FROM sales s; www.kellytechno.com

  16. Delete with Second From Clause SQL Server delete from products from products, product_deletes where products.a = product_deletes.a and products.b = product_deletes.b and product_deletes.c = 'd'; www.kellytechno.com

  17. Delete with Second From Clause Oracle delete from products where ( a, b ) in ( select a, b from product_deletes where c = 'd' ); www.kellytechno.com

  18. III. More Depth • The Connect Concept • Other Conceptual Differences • Data Type Differences • Column Aliases • Sub-Queries www.kellytechno.com

  19. The Connect Concept SQL Server Multiple databases Oracle Single Database Multiple tablespaces, schemas, users www.kellytechno.com

  20. Other Conceptual Differences SQL Server Database owner, DBO Group/Role Non-unique index T-SQL stored procedure { Trigger Compex rule Column identity property Oracle Schema Role Index PL/SQL procedure PL/SQL function BEFORE trigger After trigger Sequence www.kellytechno.com

  21. Only in Oracle • Clusters • Packages • Triggers for each row • Synonyms • Snapshots www.kellytechno.com

  22. Data Type Differences SQL Server Oracle INTEGER NUMBER(10) SMALLINT NUMBER(6) TINYINT NUMBER(3) REAL FLOAT FLOAT FLOAT BIT NUMBER(1) VARCHAR(n) VARCHAR2(n) TEXT CLOB IMAGE BLOB BINARY(n) RAW(n) or BLOB www.kellytechno.com

  23. Data Type Differences SQL Server Oracle VARBINARY RAW(n) or BLOB DATETIME DATE SMALL-DATETIME DATE MONEY NUMBER(19,4) NCHAR(n) CHAR(n*2) NVARCHAR(n) VARCHAR(n*2) SMALLMONEY NUMBER(10,4) TIMESTAMP NUMBER SYSNAME VARCHAR2(30), VARCHAR2(128) www.kellytechno.com

  24. Time SQL Server Datetime: 1/300th second Oracle Date: 1 second Timestamp: 1/100 millionth second www.kellytechno.com

  25. Column Aliases SQL Server select a=deptid, b=deptname,c=empno from dept; Oracle select deptid a, deptname b, empno c from dept; www.kellytechno.com

  26. Sub-queries, again • SQL Server SELECT ename, deptname FROM emp, dept WHERE emp.enum = 10 AND(SELECT security_code FROM employee_security WHERE empno = emp.enum) = (SELECT security_code FROM security_master WHERE sec_level = dept.sec_level); www.kellytechno.com

  27. Sub-queries, again Oracle SELECT empname, deptname FROM emp, dept WHERE emp.empno = 10 AND EXISTS (SELECT security_code FROM employee_security es WHERE es.empno = emp.empno AND es.security_code = (SELECT security_code FROM security_master WHERE sec_level =dept.sec_level)); www.kellytechno.com

  28. Powerful New Features • Regular Expressions: Operators & Functions • Operator: REGEXP_LIKE • Functions: REGEXP_INSTR • REGEXP_SUBSTR • REGEXP_REPLACE www.kellytechno.com

  29. Regular Expressions Select zip from zipcode where regexp_like (zip, ‘[^[:digit:]]’); www.kellytechno.com

  30. Regular Expressions SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234', ' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at FROM dual www.kellytechno.com

  31. Thanks & Regards

More Related