COP 3540 Introduction to Database Structures Lecture 19

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

Oracle vs. SQL Server Users and Databases l Oracle – – – l 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/My. SQL – – – l 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. My. SQL – Users can have different abilities at different levels l louis@localhost vs louis@Backstage. Software. com vs just louis

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

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

Oracle vs. SQL Server DDL Table Creation l Oracle CREATE TABLE TRANSCRIPT ( SCHEDULE_ID NUMBER, STUDENT_ID VARCHAR 2(10 BYTE), GRADE VARCHAR 2(2 BYTE), DROP_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)); l 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 l My. SQL CREATE TABLE TRANSCRIPT( SCHEDULE_ID INT, STUDENT_ID VARCHAR(10 ), GRADE VARCHAR(2 ), DROP_DATE, PRIMARY KEY (SCHEDULE_ID, STUDENT_ID)) ENGINE=INNODB;

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

Oracle vs. SQL Server SQL Differences - Dates l Oracle – – l 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 Get. Date() 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. l My. SQL – – System Date is Cur. Date() Dates are stored and returned with the following format: l – YYYY-MM-DD Insert of a date must used the above format.

Oracle vs. SQL Server SQL Differences – Workbench Scripts l Oracle – Default is all updates are held until a Commit or Rollback l SQLServer/My. SQL – – – 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 – String Functions l Oracle – – Concatenation = || (variables can be different types) Substr Instr Upper/Lower http: //psoug. org/reference/stri ng_func. html l SQL Server – Concatenation = + (must be all varchar type) – Substring Charindex Upper/Lower – – http: //msdn. microsoft. com/enus/library/ms 181984. aspx l SQL Server – Must use concat function – Mid and Substring Instr Upper/Lower – – http: //dev. mysql. com/doc/refman/5. 0/en/strin g-functions. html

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

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

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

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

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

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

Oracle vs. SQL Server PL/SQL vs Transact SQL l Oracle l SQLServer – No “batching” capability – – Parameters and declared variables can be of any name. Create or Replace available – – – 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 Insert Instructor Stored Procedure create or replace procedure Insert. Instructor(Instr. Id Instructor_id%type, Last. Name Instructor. Last_Name%Type, First. Name Instructor. First_Name%Type, Office Instructor. Office%type, City Instructor. city%type) as n integer; s. Message Varchar 2(100); url varchar(100) : = '"http: //db 11. eng. fau. edu: 7777/~lbradle 6_guest/Simple. Menu. HTML"'; BEGIN SELECT COUNT(*) INTO n FROM Instructor WHERE Instructor_ID = Instr. Id; IF n > 0 THEN s. Message : ='Instructor ' || Instr. Id ||' already in table, insert aborted'; ELSE INSERT INTO Instructor (Instructor_ID, Last_Name, First_Name, Office, City) VALUES (Instr. ID, Last. Name, First. Name, Office, City); s. Message : = 'Insert Complete for Instructor ' || Instr. Id; COMMIT; RETURNMENU(s. Message, url); END IF; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN BEGIN s. Message : ='You have tried to insert a duplicate instructor. '; RETURNMENU(s. Message, url); END; WHEN OTHERS THEN BEGIN s. Message : = 'Something Bad Happened. '; RETURNMENU(s. Message, url); END; end;

Oracle vs. SQL Server Insert Instructor Stored Procedure use lbradle 6_registration go create procedure Insert. Instructor @Instr. Id varchar, @Last. Name varchar, @First. Name varchar, @Office varchar, @City varchar, @Message varchar(100) output as Declare @n integer; BEGIN SELECT @n=COUNT(*) FROM Instructor WHERE Instructor_ID = @Instr. Id; IF (@n > 0) begin set @Message ='Instructor ' + @Instr. Id +' already in table, insert aborted'; return; end; INSERT INTO Instructor (Instructor_ID, Last_Name, First_Name, Office, City) VALUES (@Instr. ID, @Last. Name, @First. Name, @Office, @City); set @Message = 'Insert Complete for Instructor ' + @Instr. Id; END; l l use lbradle 6_registration go l declare @Message varchar(100);

Oracle vs. SQL Server Get Age Function CREATE OR REPLACE FUNCTION GETAGE (pd. Birthdate IN Date) RETURN NUMBER IS Age NUMBER; Year NUMBER; Day Number; Today. Year Number; Today. Day Number; Month Number; Today. Month Number; BEGIN Year : = To_Char(pd. Birth. Date, 'YYYY'); Today. Year : = To_Char(Sys. Date, 'YYYY'); AGE : = Today. Year - Year; Month : = To_Char(pd. Birth. Date, 'MM'); Today. Month : = To_Char(Sysdate, 'MM'); IF Month > Today. Month THEN AGE : = AGE - 1; Return Age; END IF; IF Month < Today. Month THEN Return Age; END IF; Day : = To_Char(Sys. Date, 'DD'); Today. Day : = To_Char(Sys. Date, 'DD'); IF Day > Today. Day THEN AGE : = AGE - 1; END IF; Return Age; END GETAGE; /

Oracle vs. SQL Server Get Age Function use lbradle 6_registration go CREATE FUNCTION GETAGE(@Birthdate Date) RETURNS NUMERIC AS BEGIN Declare @Age integer, @Year integer, @Day integer, @ Today. Year integer, @Today. Day integer; Declare @Month integer, @Today. Month integer; Set @Year = Year(@Birth. Date); Set @Today. Year = Year(GETDATE()); Set @AGE = @Today. Year - @Year; Set @Month = Month(@Birth. Date); SET @Today. Month = Month(GETDATE()); IF @Month > @Today. Month Begin Set @AGE = @AGE - 1; Return @Age; END; IF @Month < @Today. Month Begin Return @Age; END; Set @Day = Day(@birthdate); Set @Today. Day = Day(GETDATE()); IF @Day > @Today. Day Begin Set @AGE = @AGE - 1; END; Return @Age; END; GO select dbo. GETAGE(birth_date), * from student

Oracle vs. SQL Server Stored Procedure l Print Courses Stored Procedure Example – (Cursors, @@fetch_status, print) – use lbradle 6_registration; execute Print. Courses 'Bradley'; GO – –

Oracle vs. SQL Server Stored Procedure l l l l l l USE [lbradle 6_registration] CREATE procedure [dbo]. [Print. Courses] @ID varchar(10) as BEGIN declare @Inst. ID varchar(10), @Last. Name varchar(50), @First. Name varchar(50), @Course. ID varchar(10); declare @Room varchar(10), @Semester varchar(10), @Class. Time 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 @Inst. Id, @Last. Name, @First. Name, @Course. ID, @Semester, @Room, @Class. Time; if @@fetch_status <> 0 break; print @Inst. Id+' '+@Last. Name+' '+ @First. Name+' '+@Course. ID+' '+ @Semester + ' ' + @Room + ' ' + @Class. Time; end; close xs; deallocate xs; END; GO

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

Oracle vs. SQL Server Command Line Tools l Oracle – l SQLPlus l SQL Server – Requires entries into a parameter file to work. – l SQLCMD (This used to be called OSQL and you may see that in use at some companies) My. Sql – My. SQL

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