COP 3540 – Introduction to Database Structures

1. COP 3540 – Introduction to Database Structures Lecture 19 SQL Server vs. Oracle

2. Oracle Each database must have exactly one associated user/owner. If other users are to access the database they must be Granted the access and must specify the database name on all table. Default database names on tables is the user-id signed in to Oracle SQLServer/MySQL Databases and User-IDs are not directly associated. When connecting to a SQLServer database, the database to be accessed can be specified along with the User-Id and Password Security allows different User-IDs to have different level of authority for different databases. The verb USE can be invoked to set the “catalog” after sign in, or to change from one to another. Grants are also available and direct database access using the database name is available. MySQL Users can have different abilities at different levels louis@localhost vs louis@BackstageSoftware.com vs just louis Oracle vs. SQL Server Users and Databases

3. Oracle When a User-ID is created, a default database with the same name is also created. SQL Server/MySQL Databases are created as needed and the names are not associated with a specific user. Oracle vs. SQL Server Database Creation

4. Oracle Sequences usually enforced with a trigger on the table. SQLServer Identity columns that are part of the table and maintained by the system. Discuss distributed processing. MySql Auto-Increment columns that are part of the table and maintained by the system. Oracle vs. SQL Server Unique Record Identification

5. Oracle CREATE TABLE TRANSCRIPT ( SCHEDULE_ID NUMBER, STUDENT_ID VARCHAR2(10 BYTE), GRADE VARCHAR2(2 BYTE), DROP_DATE DATE ); CREATE UNIQUE INDEX PK_TRANSCRIPT ON TRANSCRIPT (SCHEDULE_ID, STUDENT_ID); ALTER TABLE TRANSCRIPT ADD ( CONSTRAINT PK_TRANSCRIPT PRIMARY KEY (SCHEDULE_ID, STUDENT_ID)); ALTER TABLE TRANSCRIPT ADD ( CONSTRAINT FK_TRANSCRIPT__STUDENT FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT (STUDENT_ID)); ALTER TABLE TRANSCRIPT ADD ( CONSTRAINT FK_TRANSCRIPT__SCHEDULE FOREIGN KEY (SCHEDULE_ID) REFERENCES SCHEDULE (SCHEDULE_ID)); SQL Server CREATE TABLE dbo.Transcript( Student_ID varchar(10) NOT NULL, Schedule_ID numeric(18, 0) NOT NULL, Grade varchar(2) NULL, Drop_Date date NULL, PRIMARY KEY CLUSTERED ( Student_ID ASC, Schedule_ID ASC) ) GO ALTER TABLE dbo.Transcript WITH CHECK ADD FOREIGN KEY(Schedule_ID) REFERENCES dbo.Schedule (Schedule_ID) GO ALTER TABLE dbo.Transcript WITH CHECK ADD FOREIGN KEY(Student_ID) REFERENCES dbo.Student (Student_ID) GO MySQL CREATE TABLE TRANSCRIPT( SCHEDULE_ID INT, STUDENT_ID VARCHAR(10 ), GRADE VARCHAR(2 ), DROP_DATE DATE, PRIMARY KEY (SCHEDULE_ID, STUDENT_ID)) ENGINE=INNODB; Oracle vs. SQL Server DDL Table Creation

6. Oracle Varchar2 Date has both Date and Time Number SQLServer/MySQL Varchar Date holds only date values. Datetime holds date and time Numeric MySQL requires you to specify numeric type from tinyint to bigint Oracle vs. SQL Server DDL Table Creation

7. Oracle System Date is SYSDATE Converting to a date uses the TO_DATE function. Converting from a date uses the TO_CHAR function. Can use a string date on some occasions but may not always be safe. SQLServer System Date is GetDate() Converting to a date uses the CONVERT function with specific conversion types. Converting from a date uses the CAST or CONVERT functions. Can use a string date on some occasions but may not always be safe. Oracle vs. SQL Server SQL Differences - Dates • MySQL • System Date is CurDate() • Dates are stored and returned with the following format: • YYYY-MM-DD • Insert of a date must used the above format.

8. Oracle Default is all updates are held until a Commit or Rollback SQLServer/MySQL Default is all updates are committed immediately unless they are processed in a transaction. BEGIN TRANSACTION COMMIT –or- ROLLBACK Oracle vs. SQL Server SQL Differences – Workbench Scripts

9. Oracle Concatenation = || (variables can be different types) Substr Instr Upper/Lower http://psoug.org/reference/string_func.html SQL Server Concatenation = + (must be all varchar type) Substring Charindex Upper/Lower http://msdn.microsoft.com/en-us/library/ms181984.aspx Oracle vs. SQL Server SQL Differences – String Functions • SQL Server • Must use concat function • Mid and Substring • Instr • Upper/Lower http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

10. Oracle TO_CHAR(expression, Format) TO_DATE(expression, Date Format) SQLServer/MySQL CAST ( expression AS data_type [ ( length ) ] ) CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) http://msdn.microsoft.com/en-us/library/ms187928.aspx Oracle vs. SQL Server SQL Differences – Variable Conversions

11. ORACLE SELECT COURSE_ID FROM SCHEDULE WHERE SCHEDULE_ID IN ( SELECT SCHEDULE_ID FROM SCHEDULE WHERE SEMESTER ='SPRING2013‘) MINUS SELECT DISTINCT SCHEDULE_ID FROM TRANSCRIPT) SQLServer SELECT COURSE_ID FROM SCHEDULE WHERE SCHEDULE_ID IN ( SELECT SCHEDULE_ID FROM SCHEDULE WHERE SEMESTER ='SPRING2013‘ EXCEPT SELECT DISTINCT SCHEDULE_ID FROM TRANSCRIPT) Oracle vs. SQL Server SQL Differences – Minus vs Except MySQL only supports Union. Intersect and Minus must be done with subqueries or joins.

12. Oracle SELECT NVL(City,’Boca’) FROM Instructor (Latest version of Oracle also supports COALESCE) SQL Server/MySQL SELECT COALESCE(City,’Boca’) FROM Instructor Oracle vs. SQL Server SQL Differences – Handling Nulls

13. Oracle SELECT SYSDATE FROM DUAL SELECT 3*5+2 FROM DUAL SQL Server/MySQL SELECT GETDATE() SELECT 3*5+2 Oracle vs. SQL Server SQL Differences - Dual

14. Oracle SELECT * from Student WHERE ROWNUM = 1 ORDER BY Last_Name ORDER BY AFTER WHERE SQL Server SELECT TOP 1 * FROM Student ORDER BY Last_Name ORDER BY BEFORE TOP Oracle vs. SQL Server SQL Differences – Rownum vs. Top

15. Oracle ALTER USER <username> IDENTIFIED BY <new_password>; SQLServer EXEC sp_password 'oldpassword‘, 'newpassword', 'UserName' " MySQL SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here'); Oracle vs. SQL Server Changing Passwords

16. Oracle No “batching” capability Parameters and declared variables can be of any name. Create or Replace available SQLServer All or nothing series of commands separated by the keyword “GO” Parameters and declared varaibles must begin with an “@” Create or Alter but cannot be both. Safest to drop the Procedure/Function to update it. Oracle vs. SQL Server PL/SQL vs Transact SQL

17. Oracle vs. SQL ServerInsert Instructor Stored Procedure create or replace procedure InsertInstructor(InstrId Instructor.Instructor_id%type, LastName Instructor.Last_Name%Type, FirstName Instructor.First_Name%Type, Office Instructor.Office%type, City Instructor.city%type) as n integer; sMessage Varchar2(100); url varchar(100) := '"http://db11.eng.fau.edu:7777/~lbradle6_guest/SimpleMenu.HTML"'; BEGIN SELECT COUNT(*) INTO n FROM Instructor WHERE Instructor_ID = InstrId; IF n > 0 THEN sMessage :='Instructor ' || InstrId ||' already in table, insert aborted'; ELSE INSERT INTO Instructor (Instructor_ID, Last_Name, First_Name, Office, City) VALUES (InstrID, LastName, FirstName, Office, City); sMessage := 'Insert Complete for Instructor ' || InstrId; COMMIT; RETURNMENU(sMessage, url); END IF; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN BEGIN sMessage :='You have tried to insert a duplicate instructor.'; RETURNMENU(sMessage, url); END; WHEN OTHERS THEN BEGIN sMessage := 'Something Bad Happened.'; RETURNMENU(sMessage, url); END; end;

18. Oracle vs. SQL ServerInsert Instructor Stored Procedure use lbradle6_registration go create procedure InsertInstructor @InstrId varchar,@LastName varchar, @FirstName varchar, @Office varchar, @City varchar, @Message varchar(100) output as Declare @n integer; BEGIN SELECT @n=COUNT(*) FROM Instructor WHERE Instructor_ID = @InstrId; IF (@n > 0) begin set @Message ='Instructor ' + @InstrId +' already in table, insert aborted'; return; end; INSERT INTO Instructor (Instructor_ID, Last_Name, First_Name, Office, City) VALUES (@InstrID, @LastName, @FirstName, @Office, @City); set @Message = 'Insert Complete for Instructor ' + @InstrId; END; • use lbradle6_registration • go • declare @Message varchar(100); • execute InsertInstructor 'Jones','Jones','Tom','EE903','Miami',@message output; • print @message; • go

19. Oracle vs. SQL ServerGet Age Function CREATE OR REPLACE FUNCTION GETAGE (pdBirthdate IN Date) RETURN NUMBER IS Age NUMBER; Year NUMBER; Day Number; TodayYear Number; TodayDay Number; Month Number; TodayMonth Number; BEGIN Year := To_Char(pdBirthDate,'YYYY'); TodayYear := To_Char(SysDate,'YYYY'); AGE := TodayYear - Year; Month := To_Char(pdBirthDate,'MM'); TodayMonth := To_Char(Sysdate, 'MM'); IF Month > TodayMonth THEN AGE := AGE - 1; Return Age; END IF; IF Month < TodayMonth THEN Return Age; END IF; Day := To_Char(SysDate,'DD'); TodayDay := To_Char(SysDate,'DD'); IF Day > TodayDay THEN AGE := AGE - 1; END IF; Return Age; END GETAGE; /

20. Oracle vs. SQL ServerGet Age Function use lbradle6_registration go CREATE FUNCTION GETAGE(@Birthdate Date) RETURNS NUMERIC AS BEGIN Declare @Age integer, @Year integer, @Day integer, @TodayYear integer, @TodayDay integer; Declare @Month integer, @TodayMonth integer; Set @Year = Year(@BirthDate); Set @TodayYear = Year(GETDATE()); Set @AGE = @TodayYear - @Year; Set @Month = Month(@BirthDate); SET @TodayMonth = Month(GETDATE()); IF @Month > @TodayMonth Begin Set @AGE = @AGE - 1; Return @Age; END; IF @Month < @TodayMonth Begin Return @Age; END; Set @Day = Day(@birthdate); Set @TodayDay = Day(GETDATE()); IF @Day > @TodayDay Begin Set @AGE = @AGE - 1; END; Return @Age; END; GO select dbo.GETAGE(birth_date), * from student

21. Oracle vs. SQL ServerStored Procedure • Print Courses Stored Procedure Example • (Cursors, @@fetch_status, print) • use lbradle6_registration; • execute PrintCourses 'Bradley'; • GO

22. Oracle vs. SQL ServerStored Procedure • USE [lbradle6_registration] • CREATE procedure [dbo].[PrintCourses] @ID varchar(10) as • BEGIN • declare @InstID varchar(10), @LastName varchar(50), @FirstName varchar(50), @CourseID varchar(10); • declare @Room varchar(10), @Semester varchar(10), @ClassTime varchar(20); • declare xs cursor for • select I.Instructor_ID, I.Last_Name, I.First_Name, S.Course_ID, S.Semester, S.Room, S.Class_Time • from Instructor I, Schedule S Where I.Instructor_id = S.Instructor_id • AND I.Instructor_id = @ID order by I.Instructor_ID; • open xs; • while 1 = 1 • begin • fetch xs into @InstId, @LastName, @FirstName, @CourseID, @Semester, @Room, @ClassTime; • if @@fetch_status <> 0 break; • print @InstId+' '+@LastName+' '+ @FirstName+' '+@CourseID+' '+ @Semester + ' ' + @Room + ' ' + @ClassTime; • end; • close xs; • deallocate xs; • END; • GO

23. Oracle Changes allowed to record being modified SQLServer Changes NOT allowed to record being modified except in INSTEAD OF triggers http://msdn.microsoft.com/en-us/library/ms189799.aspx Oracle vs. SQL Server SQL Differences - Triggers

24. Oracle SQLPlus Requires entries into a parameter file to work. SQL Server SQLCMD (This used to be called OSQL and you may see that in use at some companies) MySql MySQL Oracle vs. SQL ServerCommand Line Tools

25. Oracle User_Tables User_Tab_Columns (link on Table_Name column) User_Procedures (includes functions) User_Triggers User_Indexes SQLServer Sys.Tables Sys.Columns (link on Object_ID to Tables) Sys.Procedures Sys.Triggers Sys.Indexes Oracle vs SQLServerMetaData