Table of Contents

1. Basic Query Statements
2. Data Manipulation Statements
3. Table Structure Operations
4. Index Operations
5. View Operations
6. Stored Procedures and Functions
7. Transaction Control
8. Permission Management
9. Data Type Differences
10. Common Function Comparisons

Basic Query Statements

1. SELECT Queries

Oracle

-- Basic query
SELECT col_a, col_b FROM target_table WHERE filter_condition;

-- Pagination (Oracle 12c+)
SELECT * FROM (
    SELECT t.*, ROWNUM rnum FROM (
        SELECT * FROM staff ORDER BY staff_code
    ) t WHERE ROWNUM <= 30
) WHERE rnum > 20;

-- Pagination (Oracle 12c+ using OFFSET FETCH)
SELECT * FROM staff 
ORDER BY staff_code 
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

MySQL/OBMySQL

-- Basic query
SELECT col_a, col_b FROM target_table WHERE filter_condition;

-- Pagination
SELECT * FROM staff 
ORDER BY staff_code 
LIMIT 20 OFFSET 10;

-- Alternative syntax
SELECT * FROM staff 
ORDER BY staff_code 
LIMIT 20, 10;

2. JOIN Operations

Oracle

-- Inner join
SELECT s.staff_code, s.first_name, d.division_name
FROM staff s
INNER JOIN divisions d ON s.division_id = d.division_id;

-- Left outer join
SELECT s.staff_code, s.first_name, d.division_name
FROM staff s
LEFT JOIN divisions d ON s.division_id = d.division_id;

-- Right outer join
SELECT s.staff_code, s.first_name, d.division_name
FROM staff s
RIGHT JOIN divisions d ON s.division_id = d.division_id;

-- Full outer join
SELECT s.staff_code, s.first_name, d.division_name
FROM staff s
FULL OUTER JOIN divisions d ON s.division_id = d.division_id;

MySQL/OBMySQL

-- Inner join
SELECT s.staff_code, s.first_name, d.division_name
FROM staff s
INNER JOIN divisions d ON s.division_id = d.division_id;

-- Left outer join
SELECT s.staff_code, s.first_name, d.division_name
FROM staff s
LEFT JOIN divisions d ON s.division_id = d.division_id;

-- Right outer join
SELECT s.staff_code, s.first_name, d.division_name
FROM staff s
RIGHT JOIN divisions d ON s.division_id = d.division_id;

-- MySQL doesn't support FULL OUTER JOIN, use UNION instead
SELECT s.staff_code, s.first_name, d.division_name
FROM staff s
LEFT JOIN divisions d ON s.division_id = d.division_id
UNION
SELECT s.staff_code, s.first_name, d.division_name
FROM staff s
RIGHT JOIN divisions d ON s.division_id = d.division_id
WHERE s.staff_code IS NULL;

3. Aggregate Functions

Oracle

-- Basic aggregates
SELECT 
    COUNT(*) AS total_rows,
    SUM(remuneration) AS total_pay,
    AVG(remuneration) AS avg_pay,
    MAX(remuneration) AS max_pay,
    MIN(remuneration) AS min_pay
FROM staff;

-- Grouped aggregates
SELECT 
    division_id,
    COUNT(*) AS staff_count,
    AVG(remuneration) AS avg_salary
FROM staff
GROUP BY division_id
HAVING COUNT(*) > 8;

-- Window functions
SELECT 
    staff_code,
    first_name,
    remuneration,
    ROW_NUMBER() OVER (ORDER BY remuneration DESC) AS pay_rank,
    RANK() OVER (ORDER BY remuneration DESC) AS pay_rank_with_ties,
    DENSE_RANK() OVER (ORDER BY remuneration DESC) AS dense_rank,
    LAG(remuneration, 1) OVER (ORDER BY staff_code) AS prev_salary,
    LEAD(remuneration, 1) OVER (ORDER BY staff_code) AS next_salary
FROM staff;

MySQL/OBMySQL

-- Basic aggregates
SELECT 
    COUNT(*) AS total_rows,
    SUM(remuneration) AS total_pay,
    AVG(remuneration) AS avg_pay,
    MAX(remuneration) AS max_pay,
    MIN(remuneration) AS min_pay
FROM staff;

-- Grouped aggregates
SELECT 
    division_id,
    COUNT(*) AS staff_count,
    AVG(remuneration) AS avg_salary
FROM staff
GROUP BY division_id
HAVING COUNT(*) > 8;

-- Window functions (MySQL 8.0+)
SELECT 
    staff_code,
    first_name,
    remuneration,
    ROW_NUMBER() OVER (ORDER BY remuneration DESC) AS pay_rank,
    RANK() OVER (ORDER BY remuneration DESC) AS pay_rank_with_ties,
    DENSE_RANK() OVER (ORDER BY remuneration DESC) AS dense_rank,
    LAG(remuneration, 1) OVER (ORDER BY staff_code) AS prev_salary,
    LEAD(remuneration, 1) OVER (ORDER BY staff_code) AS next_salary
FROM staff;

Data Manipulation Statements

1. INSERT Operations

Oracle

-- Single row insert
INSERT INTO staff (staff_code, first_name, last_name, email, join_date)
VALUES (2001, 'Alice', 'Brown', 'alice.brown@company.com', SYSDATE);

-- Multiple row insert
INSERT ALL
    INTO staff VALUES (2002, 'Bob', 'White', 'bob.white@company.com', SYSDATE)
    INTO staff VALUES (2003, 'Carol', 'Green', 'carol.green@company.com', SYSDATE)
SELECT * FROM dual;

-- Insert from another table
INSERT INTO staff_archive
SELECT * FROM staff WHERE division_id = 15;

-- Conditional insert
INSERT FIRST
    WHEN remuneration > 8000 THEN INTO high_earners
    WHEN remuneration > 5000 THEN INTO medium_earners
    ELSE INTO low_earners
SELECT staff_code, first_name, remuneration FROM staff;

MySQL/OBMySQL

-- Single row insert
INSERT INTO staff (staff_code, first_name, last_name, email, join_date)
VALUES (2001, 'Alice', 'Brown', 'alice.brown@company.com', NOW());

-- Multiple row insert
INSERT INTO staff (staff_code, first_name, last_name, email, join_date)
VALUES 
    (2002, 'Bob', 'White', 'bob.white@company.com', NOW()),
    (2003, 'Carol', 'Green', 'carol.green@company.com', NOW());

-- Insert from another table
INSERT INTO staff_archive
SELECT * FROM staff WHERE division_id = 15;

-- Conditional insert using CASE
INSERT INTO staff (staff_code, first_name, last_name, remuneration)
SELECT 
    staff_code,
    first_name,
    last_name,
    CASE 
        WHEN remuneration > 8000 THEN remuneration
        ELSE 6000
    END AS remuneration
FROM temp_staff;

2. UPDATE Operations

Oracle

-- Basic update
UPDATE staff 
SET remuneration = remuneration * 1.15, 
    modified_date = SYSDATE
WHERE division_id = 15;

-- Update with subquery
UPDATE staff s
SET remuneration = (
    SELECT AVG(remuneration) 
    FROM staff 
    WHERE division_id = s.division_id
)
WHERE division_id IN (15, 25);

-- MERGE statement (UPSERT)
MERGE INTO staff s
USING (SELECT 2001 as staff_id, 'Alice' as fname, 'Brown' as lname FROM dual) src
ON (s.staff_code = src.staff_id)
WHEN MATCHED THEN
    UPDATE SET s.first_name = src.fname, s.last_name = src.lname
WHEN NOT MATCHED THEN
    INSERT (staff_code, first_name, last_name) VALUES (src.staff_id, src.fname, src.lname);

MySQL/OBMySQL

-- Basic update
UPDATE staff 
SET remuneration = remuneration * 1.15, 
    modified_date = NOW()
WHERE division_id = 15;

-- Update with subquery
UPDATE staff s
SET remuneration = (
    SELECT AVG(remuneration) 
    FROM staff 
    WHERE division_id = s.division_id
)
WHERE division_id IN (15, 25);

-- INSERT ... ON DUPLICATE KEY UPDATE (UPSERT)
INSERT INTO staff (staff_code, first_name, last_name, email)
VALUES (2001, 'Alice', 'Brown', 'alice.brown@company.com')
ON DUPLICATE KEY UPDATE 
    first_name = VALUES(first_name),
    last_name = VALUES(last_name),
    email = VALUES(email);

3. DELETE Operations

Oracle

-- Basic delete
DELETE FROM staff WHERE division_id = 15;

-- Delete with subquery
DELETE FROM staff 
WHERE division_id IN (
    SELECT division_id 
    FROM divisions 
    WHERE location_code = 1800
);

-- Remove duplicate records
DELETE FROM staff s1
WHERE ROWID > (
    SELECT MIN(ROWID) 
    FROM staff s2 
    WHERE s1.staff_code = s2.staff_code
);

MySQL/OBMySQL

-- Basic delete
DELETE FROM staff WHERE division_id = 15;

-- Delete with subquery
DELETE FROM staff 
WHERE division_id IN (
    SELECT division_id 
    FROM divisions 
    WHERE location_code = 1800
);

-- Remove duplicate records
DELETE s1 FROM staff s1
INNER JOIN staff s2 
WHERE s1.id > s2.id AND s1.staff_code = s2.staff_code;

Table Structure Operations

1. CREATE TABLE

Oracle

-- Basic table creation
CREATE TABLE staff (
    staff_code NUMBER(8) PRIMARY KEY,
    first_name VARCHAR2(30) NOT NULL,
    last_name VARCHAR2(35) NOT NULL,
    email VARCHAR2(35) UNIQUE NOT NULL,
    phone_number VARCHAR2(25),
    join_date DATE DEFAULT SYSDATE,
    role_code VARCHAR2(12) NOT NULL,
    remuneration NUMBER(10,2),
    bonus_percent NUMBER(3,2),
    supervisor_id NUMBER(8),
    division_id NUMBER(5),
    CONSTRAINT staff_rem_min CHECK (remuneration > 0),
    CONSTRAINT staff_email_uk UNIQUE (email),
    CONSTRAINT staff_div_fk FOREIGN KEY (division_id) REFERENCES divisions(division_id)
);

-- Create tablespace
CREATE TABLESPACE staff_data
DATAFILE 'staff_data01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 20M MAXSIZE 1G;

-- Create table in specific tablespace
CREATE TABLE staff (
    staff_code NUMBER(8) PRIMARY KEY,
    first_name VARCHAR2(30)
) TABLESPACE staff_data;

MySQL/OBMySQL

-- Basic table creation
CREATE TABLE staff (
    staff_code INT(8) PRIMARY KEY,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(35) NOT NULL,
    email VARCHAR(35) UNIQUE NOT NULL,
    phone_number VARCHAR(25),
    join_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    role_code VARCHAR(12) NOT NULL,
    remuneration DECIMAL(10,2),
    bonus_percent DECIMAL(3,2),
    supervisor_id INT(8),
    division_id INT(5),
    CONSTRAINT staff_rem_min CHECK (remuneration > 0),
    CONSTRAINT staff_email_uk UNIQUE (email),
    CONSTRAINT staff_div_fk FOREIGN KEY (division_id) REFERENCES divisions(division_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create partitioned table
CREATE TABLE staff_partitioned (
    staff_code INT PRIMARY KEY,
    first_name VARCHAR(30),
    join_date DATE
) PARTITION BY RANGE (YEAR(join_date)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

2. ALTER TABLE

Oracle

-- Add columns
ALTER TABLE staff ADD (
    incentive_bonus NUMBER(10,2) DEFAULT 0,
    status VARCHAR2(12) DEFAULT 'ACTIVE'
);

-- Modify columns
ALTER TABLE staff MODIFY (
    remuneration NUMBER(12,2),
    email VARCHAR2(45)
);

-- Drop column
ALTER TABLE staff DROP COLUMN incentive_bonus;

-- Add constraint
ALTER TABLE staff ADD CONSTRAINT staff_rem_max CHECK (remuneration <= 200000);

-- Drop constraint
ALTER TABLE staff DROP CONSTRAINT staff_rem_max;

-- Rename table
ALTER TABLE staff RENAME TO personnel;

MySQL/OBMySQL

-- Add columns
ALTER TABLE staff 
ADD COLUMN incentive_bonus DECIMAL(10,2) DEFAULT 0,
ADD COLUMN status VARCHAR(12) DEFAULT 'ACTIVE';

-- Modify columns
ALTER TABLE staff 
MODIFY COLUMN remuneration DECIMAL(12,2),
MODIFY COLUMN email VARCHAR(45);

-- Drop column
ALTER TABLE staff DROP COLUMN incentive_bonus;

-- Add constraint
ALTER TABLE staff ADD CONSTRAINT staff_rem_max CHECK (remuneration <= 200000);

-- Drop constraint
ALTER TABLE staff DROP CONSTRAINT staff_rem_max;

-- Rename table
ALTER TABLE staff RENAME TO personnel;
-- Alternative
RENAME TABLE staff TO personnel;

3. DROP TABLE

Oracle

-- Drop table
DROP TABLE staff;

-- Drop table with cascade
DROP TABLE staff CASCADE CONSTRAINTS;

-- Drop table and purge
DROP TABLE staff PURGE;

MySQL/OBMySQL

-- Drop table
DROP TABLE staff;

-- Drop table if exists
DROP TABLE IF EXISTS staff;

-- Drop table and release space
DROP TABLE staff;

Index Operations

1. Creating Indexes

Oracle

-- Unique index
CREATE UNIQUE INDEX staff_email_idx ON staff(email);

-- Composite index
CREATE INDEX staff_div_rem_idx ON staff(division_id, remuneration);

-- Function-based index
CREATE INDEX staff_upper_name_idx ON staff(UPPER(first_name));

-- Bitmap index
CREATE BITMAP INDEX staff_div_bitmap_idx ON staff(division_id);

-- Reverse key index
CREATE INDEX staff_code_reverse_idx ON staff(staff_code) REVERSE;

MySQL/OBMySQL

-- Unique index
CREATE UNIQUE INDEX staff_email_idx ON staff(email);

-- Composite index
CREATE INDEX staff_div_rem_idx ON staff(division_id, remuneration);

-- Prefix index
CREATE INDEX staff_name_prefix_idx ON staff(first_name(12));

-- Fulltext index
CREATE FULLTEXT INDEX staff_name_fulltext_idx ON staff(first_name, last_name);

-- Spatial index
CREATE SPATIAL INDEX location_spatial_idx ON sites(geolocation);

2. Managing Indexes

Oracle

-- Rebuild index
ALTER INDEX staff_email_idx REBUILD;

-- Rebuild index online
ALTER INDEX staff_email_idx REBUILD ONLINE;

-- Drop index
DROP INDEX staff_email_idx;

-- View index information
SELECT index_name, table_name, uniqueness, status 
FROM user_indexes 
WHERE table_name = 'STAFF';

MySQL/OBMySQL

-- Rebuild index
ALTER TABLE staff DROP INDEX staff_email_idx;
CREATE UNIQUE INDEX staff_email_idx ON staff(email);

-- Analyze index
ANALYZE TABLE staff;

-- Drop index
DROP INDEX staff_email_idx ON staff;

-- View index information
SHOW INDEX FROM staff;

View Operations

1. Creating Views

Oracle

-- Simple view
CREATE VIEW staff_div_view AS
SELECT s.staff_code, s.first_name, s.last_name, d.division_name
FROM staff s
JOIN divisions d ON s.division_id = d.division_id;

-- Read-only view
CREATE VIEW staff_readonly_view AS
SELECT staff_code, first_name, last_name, remuneration
FROM staff
WITH READ ONLY;

-- Force view
CREATE FORCE VIEW staff_force_view AS
SELECT * FROM nonexistent_table;

-- Materialized view
CREATE MATERIALIZED VIEW staff_rem_mv
REFRESH FAST ON COMMIT AS
SELECT division_id, AVG(remuneration) AS avg_rem, COUNT(*) AS staff_cnt
FROM staff
GROUP BY division_id;

MySQL/OBMySQL

-- Simple view
CREATE VIEW staff_div_view AS
SELECT s.staff_code, s.first_name, s.last_name, d.division_name
FROM staff s
JOIN divisions d ON s.division_id = d.division_id;

-- Read-only view
CREATE VIEW staff_readonly_view AS
SELECT staff_code, first_name, last_name, remuneration
FROM staff;

-- Updatable view with check option
CREATE VIEW staff_updatable_view AS
SELECT staff_code, first_name, last_name, division_id
FROM staff
WHERE division_id IS NOT NULL
WITH CHECK OPTION;

2. Managing Views

Oracle

-- Modify view
CREATE OR REPLACE VIEW staff_div_view AS
SELECT s.staff_code, s.first_name, s.last_name, d.division_name, s.remuneration
FROM staff s
JOIN divisions d ON s.division_id = d.division_id;

-- Drop view
DROP VIEW staff_div_view;

-- Refresh materialized view
EXECUTE DBMS_MVIEW.REFRESH('staff_rem_mv');

MySQL/OBMySQL

-- Modify view
CREATE OR REPLACE VIEW staff_div_view AS
SELECT s.staff_code, s.first_name, s.last_name, d.division_name, s.remuneration
FROM staff s
JOIN divisions d ON s.division_id = d.division_id;

-- Drop view
DROP VIEW staff_div_view;

Stored Procedures and Functions

1. Stored Procedures

Oracle

-- Create procedure
CREATE OR REPLACE PROCEDURE adjust_staff_remuneration(
    p_staff_id IN NUMBER,
    p_new_remuneration IN NUMBER,
    p_result OUT VARCHAR2
)
IS
    v_record_count NUMBER;
BEGIN
    -- Check if staff exists
    SELECT COUNT(*) INTO v_record_count
    FROM staff
    WHERE staff_code = p_staff_id;
    
    IF v_record_count = 0 THEN
        p_result := 'Staff member not found';
        RETURN;
    END IF;
    
    -- Update remuneration
    UPDATE staff
    SET remuneration = p_new_remuneration
    WHERE staff_code = p_staff_id;
    
    p_result := 'Remuneration updated successfully';
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        p_result := 'Error: ' || SQLERRM;
        ROLLBACK;
END;
/

-- Execute procedure
DECLARE
    v_result VARCHAR2(100);
BEGIN
    adjust_staff_remuneration(2001, 7500, v_result);
    DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

MySQL/OBMySQL

-- Create procedure
DELIMITER //
CREATE PROCEDURE adjust_staff_remuneration(
    IN p_staff_id INT,
    IN p_new_remuneration DECIMAL(10,2),
    OUT p_result VARCHAR(100)
)
BEGIN
    DECLARE v_record_count INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_result = CONCAT('Error: ', SQLERRM);
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- Check if staff exists
    SELECT COUNT(*) INTO v_record_count
    FROM staff
    WHERE staff_code = p_staff_id;
    
    IF v_record_count = 0 THEN
        SET p_result = 'Staff member not found';
        ROLLBACK;
    ELSE
        -- Update remuneration
        UPDATE staff
        SET remuneration = p_new_remuneration
        WHERE staff_code = p_staff_id;
        
        SET p_result = 'Remuneration updated successfully';
        COMMIT;
    END IF;
END //
DELIMITER ;

-- Execute procedure
CALL adjust_staff_remuneration(2001, 7500, @result);
SELECT @result;

2. Functions

Oracle

-- Create function
CREATE OR REPLACE FUNCTION calculate_annual_bonus(
    p_staff_id IN NUMBER
) RETURN NUMBER
IS
    v_remuneration NUMBER;
    v_bonus_rate NUMBER := 0.15;
BEGIN
    SELECT remuneration INTO v_remuneration
    FROM staff
    WHERE staff_code = p_staff_id;
    
    RETURN v_remuneration * v_bonus_rate;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE;
END;
/

-- Use function
SELECT staff_code, first_name, calculate_annual_bonus(staff_code) AS annual_bonus
FROM staff;

MySQL/OBMySQL

-- Create function
DELIMITER //
CREATE FUNCTION calculate_annual_bonus(p_staff_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_remuneration DECIMAL(10,2);
    DECLARE v_bonus_rate DECIMAL(3,2) DEFAULT 0.15;
    
    SELECT remuneration INTO v_remuneration
    FROM staff
    WHERE staff_code = p_staff_id;
    
    RETURN v_remuneration * v_bonus_rate;
END //
DELIMITER ;

-- Use function
SELECT staff_code, first_name, calculate_annual_bonus(staff_code) AS annual_bonus
FROM staff;

Trasnaction Control

1. Transaction Management

Oracle

-- Begin transaction (implicit)
UPDATE staff SET remuneration = remuneration * 1.12 WHERE division_id = 15;
UPDATE divisions SET budget = budget * 1.12 WHERE division_id = 15;

-- Commit transaction
COMMIT;

-- Rollback transaction
ROLLBACK;

-- Savepoints
SAVEPOINT before_adjustment;
UPDATE staff SET remuneration = remuneration * 1.18 WHERE division_id = 25;
SAVEPOINT after_adjustment;
UPDATE staff SET remuneration = remuneration * 0.92 WHERE division_id = 35;

-- Rollback to savepoint
ROLLBACK TO SAVEPOINT after_adjustment;
ROLLBACK TO SAVEPOINT before_adjustment;

MySQL/OBMySQL

-- Begin transaction
START TRANSACTION;
-- Alternative
BEGIN;

UPDATE staff SET remuneration = remuneration * 1.12 WHERE division_id = 15;
UPDATE divisions SET budget = budget * 1.12 WHERE division_id = 15;

-- Commit transaction
COMMIT;

-- Rollback transaction
ROLLBACK;

-- Savepoints
START TRANSACTION;
SAVEPOINT before_adjustment;
UPDATE staff SET remuneration = remuneration * 1.18 WHERE division_id = 25;
SAVEPOINT after_adjustment;
UPDATE staff SET remuneration = remuneration * 0.92 WHERE division_id = 35;

-- Rollback to savepoint
ROLLBACK TO SAVEPOINT after_adjustment;
ROLLBACK TO SAVEPOINT before_adjustment;

2. Transaction Isolation Levels

Oracle

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Set read-only transaction
SET TRANSACTION READ ONLY;

-- Set read-write transaction
SET TRANSACTION READ WRITE;

MySQL/OBMySQL

-- Set isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Check current isolation level
SELECT @@transaction_isolation;

Permission Management

1. User Management

Oracle

-- Create user
CREATE USER new_member IDENTIFIED BY secret_pass;

-- Change password
ALTER USER new_member IDENTIFIED BY new_secret_pass;

-- Drop user
DROP USER new_member CASCADE;

-- Lock/unlock user
ALTER USER new_member ACCOUNT LOCK;
ALTER USER new_member ACCOUNT UNLOCK;

MySQL/OBMySQL

-- Create user
CREATE USER 'new_member'@'localhost' IDENTIFIED BY 'secret_pass';

-- Change password
ALTER USER 'new_member'@'localhost' IDENTIFIED BY 'new_secret_pass';

-- Drop user
DROP USER 'new_member'@'localhost';

-- Lock/unlock user
ALTER USER 'new_member'@'localhost' ACCOUNT LOCK;
ALTER USER 'new_member'@'localhost' ACCOUNT UNLOCK;

2. Permission Management

Oracle

-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON staff TO new_member;
GRANT CREATE SESSION TO new_member;
GRANT CREATE TABLE TO new_member;

-- Grant roles
GRANT CONNECT, RESOURCE TO new_member;

-- Revoke permissions
REVOKE SELECT ON staff FROM new_member;
REVOKE CREATE TABLE FROM new_member;

-- Create role
CREATE ROLE hr_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON staff TO hr_role;
GRANT hr_role TO new_member;

MySQL/OBMySQL

-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON staff TO 'new_member'@'localhost';
GRANT ALL PRIVILEGES ON company_db.* TO 'new_member'@'localhost';

-- Revoke permissions
REVOKE SELECT ON staff FROM 'new_member'@'localhost';
REVOKE ALL PRIVILEGES ON company_db.* FROM 'new_member'@'localhost';

-- Refresh permissions
FLUSH PRIVILEGES;

-- Create role (MySQL 8.0+)
CREATE ROLE 'hr_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON staff TO 'hr_role';
GRANT 'hr_role' TO 'new_member'@'localhost';

Data Type Differences

1. Numeric Types | Type | Oracle | MySQL/OBMySQL | |---|---|---| | Integer | NUMBER(12) | INT, BIGINT | | Decimal | NUMBER(12,3) | DECIMAL(12,3) | | Floating | BINARY_FLOAT, BINARY_DOUBLE | FLOAT, DOUBLE |
2. String Types | Type | Oracle | MySQL/OBMySQL | |---|---|---| | Fixed length | CHAR(15) | CHAR(15) | | Variable length | VARCHAR2(300) | VARCHAR(300) | | Large text | CLOB | TEXT, LONGTEXT |
3. Date/Time Types | Type | Oracle | MySQL/OBMySQL | |---|---|---| | Date | DATE | DATE | | Time | TIMESTAMP | TIME | | Date/Time | TIMESTAMP | DATETIME, TIMESTAMP |
4. Binary Types | Type | Oracle | MySQL/OBMySQL | |---|---|---| | Binary | BLOB | BLOB, LONGBLOB | | Raw data | RAW(2000) | VARBINARY(2000) |

Common Functon Comparisons

1. String Functions

Oracle

-- String concatenation
SELECT first_name || ' ' || last_name AS full_name FROM staff;

-- String length
SELECT LENGTH(first_name) FROM staff;

-- Case conversion
SELECT UPPER(first_name), LOWER(last_name) FROM staff;

-- Substring
SELECT SUBSTR(first_name, 2, 4) FROM staff;

-- Replace
SELECT REPLACE(email, '@', '[at]') FROM staff;

-- Trim spaces
SELECT TRIM(' ' FROM first_name) FROM staff;

MySQL/OBMySQL

-- String concatenation
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM staff;

-- String length
SELECT LENGTH(first_name) FROM staff;

-- Case conversion
SELECT UPPER(first_name), LOWER(last_name) FROM staff;

-- Substring
SELECT SUBSTRING(first_name, 2, 4) FROM staff;

-- Replace
SELECT REPLACE(email, '@', '[at]') FROM staff;

-- Trim spaces
SELECT TRIM(first_name) FROM staff;

2. Numeric Functions

Oracle

-- Round
SELECT ROUND(remuneration, 2) FROM staff;

-- Truncate
SELECT TRUNC(remuneration, 2) FROM staff;

-- Modulo
SELECT MOD(remuneration, 1500) FROM staff;

-- Absolute value
SELECT ABS(remuneration - 6000) FROM staff;

-- Power
SELECT POWER(remuneration, 2) FROM staff;

MySQL/OBMySQL

-- Round
SELECT ROUND(remuneration, 2) FROM staff;

-- Truncate
SELECT TRUNCATE(remuneration, 2) FROM staff;

-- Modulo
SELECT MOD(remuneration, 1500) FROM staff;

-- Absolute value
SELECT ABS(remuneration - 6000) FROM staff;

-- Power
SELECT POW(remuneration, 2) FROM staff;

3. Date Functions

Oracle

-- Current date
SELECT SYSDATE FROM dual;

-- Date arithmetic
SELECT SYSDATE + 10 FROM dual;
SELECT SYSDATE - INTERVAL '2' DAY FROM dual;

-- Date formatting
SELECT TO_CHAR(join_date, 'YYYY-MM-DD') FROM staff;

-- String to date
SELECT TO_DATE('2023-02-15', 'YYYY-MM-DD') FROM dual;

-- Date difference
SELECT MONTHS_BETWEEN(SYSDATE, join_date) FROM staff;

MySQL/OBMySQL

-- Current date
SELECT NOW() FROM dual;

-- Date arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 10 DAY) FROM dual;
SELECT DATE_SUB(NOW(), INTERVAL 2 DAY) FROM dual;

-- Date formatting
SELECT DATE_FORMAT(join_date, '%Y-%m-%d') FROM staff;

-- String to date
SELECT STR_TO_DATE('2023-02-15', '%Y-%m-%d') FROM dual;

-- Date difference
SELECT DATEDIFF(NOW(), join_date) FROM staff;

Performance Optimization Techniques

1. Query Optimization

Oracle

-- Use EXISTS instead of IN
SELECT * FROM staff s
WHERE EXISTS (
    SELECT 1 FROM divisions d 
    WHERE d.division_id = s.division_id 
    AND d.location_code = 1800
);

-- Use UNION ALL instead of UNION (if no deduplication needed)
SELECT staff_code, first_name FROM staff WHERE division_id = 15
UNION ALL
SELECT staff_code, first_name FROM staff WHERE division_id = 25;

-- Use ROWNUM to limit results
SELECT * FROM (
    SELECT t.*, ROWNUM rnum FROM (
        SELECT * FROM staff ORDER BY remuneration DESC
    ) t WHERE ROWNUM <= 15
) WHERE rnum > 0;

MySQL/OBMySQL

-- Use EXISTS instead of IN
SELECT * FROM staff s
WHERE EXISTS (
    SELECT 1 FROM divisions d 
    WHERE d.division_id = s.division_id 
    AND d.location_code = 1800
);

-- Use UNION ALL instead of UNION (if no deduplication needed)
SELECT staff_code, first_name FROM staff WHERE division_id = 15
UNION ALL
SELECT staff_code, first_name FROM staff WHERE division_id = 25;

-- Use LIMIT to restrict results
SELECT * FROM staff 
ORDER BY remuneration DESC 
LIMIT 15;

2. Index Optimization

Oracle

-- Composite index (leftmost prefix principle)
CREATE INDEX staff_div_rem_idx ON staff(division_id, remuneration, join_date);

-- Function-based index
CREATE INDEX staff_upper_name_idx ON staff(UPPER(first_name));

-- Analyze table statistics
ANALYZE TABLE staff COMPUTE STATISTICS;

MySQL/OBMySQL

-- Composite index (leftmost prefix principle)
CREATE INDEX staff_div_rem_idx ON staff(division_id, remuneration, join_date);

-- Prefix index
CREATE INDEX staff_name_prefix_idx ON staff(first_name(12));

-- Analyze table statistics
ANALYZE TABLE staff;