Here's a complete reference guide that categorizes and compares the most commonly used SQL queries in both SQL Server and Oracle, organized by,

1. DDL – Data Definition Language

Used for defining or modifying database objects like tables and columns.

Oracle Syntax Oracle Syntax
Operation SQL Server Syntax Oracle Syntax
Create Table CREATE TABLE table_name (col1 INT, col2 VARCHAR(100)); Same
Drop Table DROP TABLE table_name; Same
Add Column ALTER TABLE table_name ADD col_name datatype; Same
Drop Column ALTER TABLE table_name DROP COLUMN col_name; Same
Modify Column ALTER TABLE table_name ALTER COLUMN col_name datatype; ALTER TABLE table_name MODIFY (col_name datatype);
Rename Column EXEC sp_RENAME 'table.old', 'new', 'COLUMN'; ALTER TABLE table_name RENAME COLUMN old TO new;
Rename Table EXEC sp_RENAME 'old_table', 'new_table'; RENAME old_table TO new_table;

2. DML – Data Manipulation Language

Used for managing the data within tables.

Oracle Syntax Oracle Syntax
Operation SQL Server Syntax Oracle Syntax
Insert INSERT INTO table_name VALUES (...); Same
Update UPDATE table_name SET col = val WHERE ...; Same
Delete DELETE FROM table_name WHERE ...; Same
Merge (Upsert) MERGE INTO target USING source ON (condition) Same
Truncate TRUNCATE TABLE table_name; Same

3. TCL – Transaction Control Language

Used to manage changes made by DML operations.

Operation SQL Server Syntax Oracle Syntax
Begin Transaction BEGIN TRAN; BEGIN; or auto (depending)
Commit COMMIT; COMMIT;
Rollback ROLLBACK; ROLLBACK;
Savepoint SAVE TRAN save_name; SAVEPOINT sp_name;
Rollback to Savepoint ROLLBACK TRAN save_name; ROLLBACK TO sp_name;

4. DCL – Data Control Language

Used to control access and privileges on database objects.

Operation SQL Server Syntax Oracle Syntax
Grant Privileges GRANT SELECT ON table TO user; Same
Revoke Privileges REVOKE SELECT ON table FROM user; Same
Create User CREATE LOGIN user WITH PASSWORD = 'pwd';
CREATE USER user FOR LOGIN user;		 CREATE USER user IDENTIFIED BY pwd;
Assign Role ALTER ROLE role_name ADD MEMBER user; GRANT role_name TO user;

5. DQL – Data Query Language

Used to query data from tables.

Operation SQL Server Syntax Oracle Syntax
Select All SELECT * FROM table_name; Same
Select with WHERE SELECT * FROM table_name WHERE condition; Same
Order By SELECT * FROM table ORDER BY col DESC; Same
LIKE Clause SELECT * FROM table WHERE col LIKE 'A%'; Same
Top Rows SELECT TOP 5 * FROM table; SELECT * FROM table WHERE ROWNUM <= 5;
Aggregate Functions SELECT COUNT(*), AVG(col) FROM table; Same
Group By SELECT col, COUNT(*) FROM table GROUP BY col; Same

Bonus Query Comparisons

Purpose SQL Server Example Oracle Example
Backup Table SELECT * INTO backup FROM original; CREATE TABLE backup AS SELECT * FROM original;
Insert from SELECT INSERT INTO t2 SELECT * FROM t1; Same
Check Procedure Exists SELECT * FROM sys.objects WHERE name='proc'; SELECT * FROM user_objects WHERE object_name='PROC';
Hex to Text Conversion SELECT CONVERT(VARCHAR(50), 0x48656C6C6F); UTL_RAW.CAST_TO_VARCHAR2('48656C6C6F');
Execute Procedure (XML) EXEC ProcName 'XML'; BEGIN ProcName('XML'); END;

Summary Table – SQL Query Categories

Category Full Form Common Operations
DDL Data Definition Language CREATE, ALTER, DROP
DML Data Manipulation Language INSERT, UPDATE, DELETE, MERGE
TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT
DCL Data Control Language GRANT, REVOKE, CREATE USER, ROLES
DQL Data Query Language SELECT, WHERE, ORDER BY, GROUP BY