1 / 23

SQL Review

SQL Review. Just Enough to Get Us Started!!!. Learning Objectives. IST359 Refresher course Comparison Microsoft SQL Server to Oracle 11g in “Death Match”  Cover some of the improvements and annoyances between the two. References to learn more on your own. The Relational Database Model.

hong
Download Presentation

SQL Review

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. SQL Review Just Enough to Get Us Started!!!

  2. Learning Objectives IST359 Refresher course Comparison Microsoft SQL Server to Oracle 11g in “Death Match”  Cover some of the improvements and annoyances between the two. References to learn more on your own.

  3. The Relational Database Model

  4. Relational Terminology Categories Table / Relation Row Column Attribute Null Primary Key Candidate Key Composite Key Unique Constraint Check Constraint Default Foreign Key Entity Integrity Referential Integrity

  5. Tables Metadata Structured buckets for your data Columns have data types (physical domain) Columns have acceptable values (logical domain)

  6. Tables VS

  7. SQL - Language Data Definition Language Data Manipulation Language Data SELECT INSERT UPDATE DELETE Metadata • CREATE • ALTER • DROP

  8. Data Types VS SQL Server Oracle Id int State char(2) Name varchar2(50) Gpa decimal(4,3) Dob date Resume clob NO BIT TYPE!!! • Id int • State char(2) • Name varchar(50) • Gpa decimal(4,3) • Dob datetime • Resume text • IstMajor bit

  9. Boolean Types VS • There is no bit type in oracle. • Workaround: • char(1) • Check constraint

  10. Sequences VS • There is noint identity in oracle. • You create a sequenceobject and use thesequence during theinsert…. …How about a demo?

  11. Alter table VS You cannot manipulate more than column or constraint with alter table

  12. SQL - Language Data Definition Language Data Manipulation Language Data SELECT INSERT UPDATE DELETE Metadata • CREATE • ALTER • DROP

  13. No Auto Commit VS SQL Server defaults to“Auto Commit” Mode. INSERT, UPDATE, and DELETE statementsare permanent. In Oracle they are NOT You must explicitly COMMIT or ROLLBACK …How about a few demos?

  14. Varchar2 VS Why VARCHAR2? Only Oracle knows for sure. Empty String ‘’ is the same as NULL,in Oracle. … Demo?

  15. Date Data VS To Insert a date value in Oracle you need touse the to_date()function

  16. Dual Table VS You Can’tMake This Stuff Up! In Oracle you cannot useSELECT without the from clause so you need to use an empty table called dual. NO: YES:

  17. SELECT Processing HOW WE SAY IT HOW IT IS PROCESSED FROM WHERE GROUP BY HAVING SELECT (Projection) ORDER BY DISTINCT • SELECT (Projection) • DISTINCT • FROM • WHERE • GROUP BY • HAVING • ORDER BY

  18. SELECT - Demos • Aliasing • WHERE • ORDER BY • JOIN • MULTI-TABLE • OUTER • GROUP BY • HAVING

  19. No TOP VS Oracle has no TOP clause. So you need to use an addition to your where clause.

  20. Tooling VS SQL Server Oracle Oracle Enterprise Manager, Oracle SQL Developer OEM is for Database Administration SQL Dev is for Script and Query Writing • SQL Server Management Studio • SSMS does it all.

  21. VS SYSDATE or GETDATE http://www.dba-oracle.com/oracle_news/2005_12_16_sql_syntax_differences.htm

  22. Summary VS • Must end each SQL statement in ; No “go” • No INT Identity • (must use sequences, which are better anyways, imo) • Every INSERT, UPDATE and DELETE is a TRANSACTION • (SET IMPLICIT_TRANSACTIONS ON in SQL Server) • VARCHAR2 datatype NULL == “” • The Dual table….oy.

  23. SQL Review Questions? Comments?

More Related