Observations (SQL Server – TSL and Oracle – PL/SQL Comparison)

Here are some useful information for those who knows TSQL and wants to learn PL/SQL.

Lets start with the data types

Oracle Data Types

Now lets go to the query commands:

SELECT INTO
SQL Server
SELECT getdate() SampleColumn, ‘sample text’ SampleColumn2 INTO SampleTable;
Oracle
INSERT INTO SampleTable (SampleColumn1, SampleColumn2) VALUES (sysdate, ‘sample text’);

INSERT
SQL Server
INSERT SampleTable VALUES (‘sample text’, 100);
Or
INSERT INTO SampleTable VALUES (‘sample text’, 100);
Oracle
INSERT INTO SampleTable VALUES (‘sample text’, 100);

UPDATE
SQL Server
UPDATE SampleTable1 SET SampleColumn1 = SampleTable2.SampleColumn1 FROM SampleTable1, SampleTable2 WHERE SampleTable1.SampleColumn1 LIKE ‘Test%’ and SampleTable2.SampleColumn2 = ‘some text’;
Oracle
UPDATE SampleTable1 SET SampleColumn1 = (SELECT a.SampleColumn1 FROM SampleTable2 a WHERE SampleTable2.SampleColumn2 = ‘some text’) WHERE SampleTable1.SampleColumn1 LIKE ‘Test%’;

DELETE
SQL Server
DELETE SampleTable WHERE SampleColumn LIKE ‘Test%’;
Or
DELETE FROM SampleTable WHERE SampleColumn LIKE ‘Test%’;
Oracle
DELETE FROM SampleTable WHERE SampleColumn LIKE ‘Test %’;

OUTER JOIN
SQL Server
SELECT d.DepartmentName, e.EmployeeName FROM Department d, Employees e WHERE d.EmployeeNumber *= e.EmployeeNumber;
Or
SELECT d.DepartmentName, e.EmployeeName FROM Department d LEFT OUTER JOIN Employees e ON d.EmployeeNumber = e.EmployeeNumber
Oracle
SELECT d.DepartmentName,e. EmployeeName FROM Department d, Employees e WHERE d.EmployeeNumber = e.EmployeeNumber (+);

PIVOT
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;
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;

ALIAS
SQL Server
SELECT a = DepartmentID , b = DepartmentName, c = EmployeeNumber FROM Department;
Or
SELECT DepartmentID AS a, DepartmentName AS b, EmployeeNumber AS c FROM Department;
Oracle
SELECT DepartmentID a, DepartmentName b, EmployeeNumber c FROM Department;

These are just some of my observations and what I commonly use definitely there will be a lot more!

Use Dual

SQL Server

select getdate();

Oracle

select sysdate from dual;

Select Into

SQL Server

select getdate() mycolumn into mytable;

Oracle

insert into mytable (mycolumn) values(sysdate);

Inserts

SQL Server

Insert mytable values(‘more text’);

Oracle

Insert into mytable values(‘more text’);

Updates

SQL Server

update mytable set mycolumn=myothertable.mycolumn from mytable,myothertable where mytable.mycolumn like ‘MY%’ and myothertable.myothercolumn=’some text’;

Oracle

update mytable set mycolumn=(select a.mycolumn from myothertable a where myothertable.myothercolumn=’some text) where mytable.mycolumn like ‘MY%’;

Deletes

SQL Server

delete mytable where mycolumn like ‘some%’;

Oracle

delete from mytable where mycolumn like ‘some%’;

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 (+);

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;

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;

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’;

Oracle

Delete from products where ( a, b ) in ( select a, b from product_deletes where c = ‘d’ );

Column Aliases

SQL Server

select a=deptid, b=deptname,c=empno from dept;

Oracle

select deptid a, deptname b, empno c from dept;

Sub-queries

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);

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));