Write, Run & Share Oracle queries online using OneCompiler's Oracle online editor for free. It's one of the robust, feature-rich online editors for Oracle running on Oracle 23c. Getting started with the OneCompiler's Oracle editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'Oracle' and start writing queries to learn and test online without worrying about tedious process of installation.
Oracle Database is the world's most popular enterprise relational database management system, built by Oracle Corporation. Known for its robustness, scalability, and comprehensive feature set, Oracle is the standard choice for mission-critical applications, data warehousing, and large-scale enterprise systems. It supports SQL and PL/SQL for powerful data manipulation and procedural programming.
Creates a new table with specified columns, data types, and constraints. Oracle uses NUMBER for numeric data, VARCHAR2 for variable-length strings, and DATE/TIMESTAMP for temporal data. Constraints like PRIMARY KEY, NOT NULL, UNIQUE, and CHECK enforce data integrity. Oracle supports advanced features like virtual columns and identity columns.
CREATE TABLE EMPLOYEE (
empId NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
dept VARCHAR2(20),
salary NUMBER(10,2),
hire_date DATE DEFAULT SYSDATE
);
-- Table with multiple constraints
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
emp_id NUMBER REFERENCES EMPLOYEE(empId),
amount NUMBER(10,2) CHECK (amount > 0),
order_date DATE DEFAULT SYSDATE
);
Adds new rows to a table. Specify column names explicitly for clarity, or provide values for all columns in order. Oracle supports INSERT ALL for multi-table inserts and INSERT with subqueries for bulk data loading. Use the RETURNING clause to get generated values like sequences back immediately.
INSERT INTO EMPLOYEE (empId, name, dept, salary)
VALUES (1, 'Clark', 'Sales', 50000);
INSERT INTO EMPLOYEE (empId, name, dept, salary)
VALUES (2, 'Dave', 'Accounting', 45000);
INSERT INTO EMPLOYEE (empId, name, dept, salary)
VALUES (3, 'Ava', 'Sales', 55000);
-- Insert with default values
INSERT INTO EMPLOYEE (empId, name) VALUES (4, 'Bob');
-- Insert from select
INSERT INTO employee_backup SELECT * FROM EMPLOYEE;
Retrieves data from tables with powerful filtering, sorting, and pagination. Oracle uses ROWNUM or FETCH FIRST for limiting results, and supports advanced features like hierarchical queries with CONNECT BY, analytic functions, and model clause. The SELECT statement is highly optimized for complex enterprise queries.
-- Select all
SELECT * FROM EMPLOYEE;
-- Select with condition
SELECT * FROM EMPLOYEE WHERE dept = 'Sales';
-- Select specific columns with ordering
SELECT name, salary FROM EMPLOYEE ORDER BY salary DESC;
-- Limit results (Oracle 12c+)
SELECT * FROM EMPLOYEE FETCH FIRST 10 ROWS ONLY;
-- Limit with offset
SELECT * FROM EMPLOYEE OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;
-- Using ROWNUM (older syntax)
SELECT * FROM EMPLOYEE WHERE ROWNUM <= 10;
Modifies existing rows in a table. Always include a WHERE clause to target specific rows. Oracle supports UPDATE with subqueries, correlated updates, and the RETURNING clause to get old or new values. For complex updates affecting multiple tables, consider using MERGE statement instead.
-- Update single column
UPDATE EMPLOYEE SET dept = 'Marketing' WHERE empId = 1;
-- Update multiple columns
UPDATE EMPLOYEE SET dept = 'Sales', salary = 52000 WHERE empId = 2;
-- Update with calculation
UPDATE EMPLOYEE SET salary = salary * 1.10 WHERE dept = 'Engineering';
-- Update with subquery
UPDATE EMPLOYEE SET salary = (SELECT AVG(salary) FROM EMPLOYEE)
WHERE empId = 3;
Removes rows from a table based on conditions. Always use WHERE to avoid deleting all data. TRUNCATE is faster for removing all rows but cannot be rolled back. Oracle supports DELETE with subqueries and RETURNING clause to capture deleted data for auditing or confirmation.
-- Delete specific rows
DELETE FROM EMPLOYEE WHERE empId = 1;
-- Delete with condition
DELETE FROM EMPLOYEE WHERE dept = 'Sales' AND salary < 45000;
-- Delete with subquery
DELETE FROM EMPLOYEE
WHERE salary < (SELECT AVG(salary) FROM EMPLOYEE);
-- Remove all data (faster than DELETE)
TRUNCATE TABLE EMPLOYEE;
Combines rows from multiple tables based on related columns. Oracle supports all standard join types: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS joins. Oracle also has the older (+) syntax for outer joins, though ANSI syntax is preferred for clarity and portability.
CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(30)
);
INSERT INTO departments VALUES (1, 'Sales');
INSERT INTO departments VALUES (2, 'Engineering');
-- Inner join
SELECT e.name, d.dept_name
FROM EMPLOYEE e
INNER JOIN departments d ON e.dept = d.dept_name;
-- Left outer join
SELECT e.name, d.dept_name
FROM EMPLOYEE e
LEFT JOIN departments d ON e.dept = d.dept_name;
-- Oracle traditional outer join syntax
SELECT e.name, d.dept_name
FROM EMPLOYEE e, departments d
WHERE e.dept = d.dept_name(+);
Oracle provides comprehensive aggregate functions for data analysis including COUNT, SUM, AVG, MIN, MAX, and statistical functions like STDDEV and VARIANCE. Use GROUP BY to aggregate by categories, HAVING to filter grouped results, and ROLLUP/CUBE for subtotals and grand totals.
-- Basic aggregates
SELECT
COUNT(*) as total_employees,
SUM(salary) as total_salary,
AVG(salary) as avg_salary,
MIN(salary) as min_salary,
MAX(salary) as max_salary
FROM EMPLOYEE;
-- Group by
SELECT dept, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM EMPLOYEE
GROUP BY dept;
-- Having clause
SELECT dept, COUNT(*) as emp_count
FROM EMPLOYEE
GROUP BY dept
HAVING COUNT(*) > 1;
-- Rollup for subtotals
SELECT dept, COUNT(*) as cnt, SUM(salary) as total
FROM EMPLOYEE
GROUP BY ROLLUP(dept);
Oracle supports subqueries in SELECT, FROM, WHERE, and HAVING clauses. Subqueries can return single values, lists, or result sets. Correlated subqueries reference outer query columns. Oracle also supports scalar subqueries in SELECT lists and inline views in the FROM clause.
-- Subquery in WHERE
SELECT * FROM EMPLOYEE
WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE);
-- Subquery with IN
SELECT * FROM EMPLOYEE
WHERE dept IN (SELECT dept FROM EMPLOYEE WHERE salary > 50000);
-- Correlated subquery
SELECT e1.name, e1.salary
FROM EMPLOYEE e1
WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE e2 WHERE e2.dept = e1.dept);
-- Inline view
SELECT * FROM (
SELECT name, salary, ROWNUM as rn FROM EMPLOYEE ORDER BY salary DESC
) WHERE rn <= 3;
Oracle's analytic (window) functions perform calculations across related rows without collapsing results. Functions like ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and running aggregates are essential for reporting and analytics. Use PARTITION BY to define groups and ORDER BY for ordering within partitions.
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rank_in_dept,
SUM(salary) OVER (PARTITION BY dept) as dept_total,
AVG(salary) OVER () as company_avg
FROM EMPLOYEE;
-- Running total
SELECT name, salary,
SUM(salary) OVER (ORDER BY empId) as running_total
FROM EMPLOYEE;
-- Compare with previous row
SELECT name, salary,
LAG(salary) OVER (ORDER BY empId) as prev_salary,
salary - LAG(salary) OVER (ORDER BY empId) as difference
FROM EMPLOYEE;
CASE provides conditional logic in SQL queries. Use searched CASE for complex conditions or simple CASE for value matching. CASE expressions can appear in SELECT, WHERE, ORDER BY, and even inside aggregate functions for conditional aggregation.
SELECT name, salary,
CASE
WHEN salary >= 55000 THEN 'Senior'
WHEN salary >= 45000 THEN 'Mid-Level'
ELSE 'Junior'
END as level
FROM EMPLOYEE;
-- Simple CASE
SELECT name,
CASE dept
WHEN 'Sales' THEN 'Revenue Team'
WHEN 'Engineering' THEN 'Tech Team'
ELSE 'Other'
END as team
FROM EMPLOYEE;
-- CASE in aggregation
SELECT
COUNT(CASE WHEN dept = 'Sales' THEN 1 END) as sales_count,
COUNT(CASE WHEN dept = 'Engineering' THEN 1 END) as eng_count
FROM EMPLOYEE;
Oracle provides extensive string functions for text manipulation. Common functions include SUBSTR, INSTR, LENGTH, UPPER, LOWER, TRIM, REPLACE, and CONCAT (or || operator). Regular expressions are supported through REGEXP_LIKE, REGEXP_SUBSTR, and REGEXP_REPLACE for advanced pattern matching.
SELECT
UPPER('hello') as upper_case,
LOWER('HELLO') as lower_case,
LENGTH('hello') as len,
'Hello' || ' ' || 'World' as concatenated,
SUBSTR('Hello World', 1, 5) as substring,
REPLACE('Hello World', 'World', 'Oracle') as replaced,
TRIM(' hello ') as trimmed
FROM DUAL;
-- Pattern matching
SELECT * FROM EMPLOYEE WHERE REGEXP_LIKE(name, '^A');
Oracle has powerful date handling with SYSDATE, SYSTIMESTAMP, and numerous functions for date arithmetic and extraction. ADD_MONTHS, MONTHS_BETWEEN, TRUNC, and EXTRACT are commonly used. Oracle dates include time by default; use TRUNC to work with date-only values.
SELECT
SYSDATE as current_date,
SYSTIMESTAMP as current_timestamp,
ADD_MONTHS(SYSDATE, 1) as next_month,
ADD_MONTHS(SYSDATE, -1) as last_month,
TRUNC(SYSDATE) as date_only,
EXTRACT(YEAR FROM SYSDATE) as year,
EXTRACT(MONTH FROM SYSDATE) as month,
TO_CHAR(SYSDATE, 'YYYY-MM-DD') as formatted
FROM DUAL;
-- Date difference
SELECT TRUNC(SYSDATE) - hire_date as days_employed FROM EMPLOYEE;
Sequences generate unique numeric values, commonly used for primary keys. CREATE SEQUENCE defines the sequence, and NEXTVAL gets the next value. Oracle 12c+ supports identity columns as an alternative. Sequences are independent objects that can be shared across tables.
-- Create sequence
CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 1;
-- Use sequence in insert
INSERT INTO EMPLOYEE (empId, name, dept) VALUES (emp_seq.NEXTVAL, 'New Emp', 'Sales');
-- Get current value (after NEXTVAL has been called)
SELECT emp_seq.CURRVAL FROM DUAL;
ALTER TABLE modifies table structure - add, modify, or drop columns and constraints. DROP TABLE removes tables permanently. Oracle supports PURGE to bypass the recycle bin. RENAME changes table names, and ALTER can also enable/disable constraints.
-- Add column
ALTER TABLE EMPLOYEE ADD email VARCHAR2(100);
-- Modify column
ALTER TABLE EMPLOYEE MODIFY name VARCHAR2(100);
-- Drop column
ALTER TABLE EMPLOYEE DROP COLUMN email;
-- Add constraint
ALTER TABLE EMPLOYEE ADD CONSTRAINT chk_salary CHECK (salary > 0);
-- Drop table
DROP TABLE EMPLOYEE;
-- Drop and purge (skip recycle bin)
DROP TABLE EMPLOYEE PURGE;