Introduction
Oracle Database is a multi-model database management system known for its robustness and scalability. Understanding Oracle SQL commands is essential for managing and querying Oracle databases efficiently. This cheat sheet provides a quick reference to some of the most commonly used Oracle SQL commands.
Oracle Database Commands Cheat Sheet
Data Manipulation Language (DML) Commands
| Command | Description | Syntax | Example |
|---|---|---|---|
SELECT |
Retrieves data from a database. | SELECT column1, column2 FROM table_name; |
SELECT first_name, last_name FROM students; |
INSERT |
Adds new records to a table. | INSERT INTO table_name (column1, column2) VALUES (value1, value2); |
INSERT INTO students (first_name, last_name) VALUES ('Mary', 'Doe'); |
UPDATE |
Modifies existing records in a table. | UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; |
UPDATE students SET first_name = 'John' WHERE last_name = 'Doe'; |
DELETE |
Removes records from a table. | DELETE FROM table_name WHERE condition; |
DELETE FROM students WHERE last_name = 'Doe'; |
Data Definition Language (DDL) Commands
| Command | Description | Syntax | Example |
|---|---|---|---|
CREATE |
Creates a new database and objects, such as a table, index, view, or stored procedure. | CREATE TABLE table_name (column1 datatype1, column2 datatype2, ...); |
CREATE TABLE students (student_id NUMBER PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50), age NUMBER); |
ALTER |
Adds, deletes, or modifies columns in an existing table. | ALTER TABLE table_name ADD column_name datatype; |
ALTER TABLE students ADD email VARCHAR2(100); |
DROP |
Deletes an existing table from a database. | DROP TABLE table_name; |
DROP TABLE students; |
TRUNCATE |
Deletes the data inside a table, but not the table itself. | TRUNCATE TABLE table_name; |
TRUNCATE TABLE students; |
Data Control Language (DCL) Commands
| Command | Description | Syntax | Example |
|---|---|---|---|
GRANT |
Gives specific privileges to users or roles. | GRANT privilege_name ON object TO user_name; |
GRANT SELECT ON students TO scott; |
REVOKE |
Takes away privileges previously granted to users or roles. | REVOKE privilege_name ON object FROM user_name; |
REVOKE SELECT ON students FROM scott; |
Querying Data Commands
| Command | Description | Syntax | Example |
|---|---|---|---|
SELECT |
Retrieves data from a database. | SELECT column1, column2 FROM table_name; |
SELECT first_name, last_name FROM students; |
WHERE |
Filters rows based on a specified condition. | SELECT * FROM table_name WHERE condition; |
SELECT * FROM students WHERE age > 18; |
ORDER BY |
Sorts the result set in ascending or descending order based on a specified column. | SELECT * FROM table_name ORDER BY column_name ASC|DESC; |
SELECT * FROM students ORDER BY last_name DESC; |
GROUP BY |
Groups rows based on the values in a specified column, often used with aggregate functions like COUNT, SUM, AVG, etc. | SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name; |
SELECT class, COUNT(*) FROM students GROUP BY class; |
HAVING |
Filters grouped results based on a specified condition. | SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING condition; |
SELECT class, COUNT(*) FROM students GROUP BY class HAVING COUNT(*) > 5; |
Joining Commands
| Command | Description | Syntax | Example |
|---|---|---|---|
INNER JOIN |
Returns rows with matching values in both tables. | SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column; |
SELECT * FROM students INNER JOIN subjects ON students.student_id = subjects.student_id; |
LEFT JOIN |
Returns all rows from the left table and the matching rows from the right table. | SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column; |
SELECT * FROM students LEFT JOIN subjects ON students.student_id = subjects.student_id; |
RIGHT JOIN |
Returns all rows from the right table and the matching rows from the left table. | SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; |
SELECT * FROM students RIGHT JOIN subjects ON students.student_id = subjects.student_id; |
FULL JOIN |
Returns all rows when there is a match in either the left table or the right table. | SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column; |
SELECT * FROM students FULL JOIN subjects ON students.student_id = subjects.student_id; |
CROSS JOIN |
Combines every row from the first table with every row from the second table, creating a Cartesian product. | SELECT * FROM table1 CROSS JOIN table2; |
SELECT * FROM students CROSS JOIN subjects; |
SELF JOIN |
Joins a table with itself. | SELECT * FROM table1 t1, table1 t2 WHERE t1.column = t2.column; |
SELECT * FROM students s1, students s2 WHERE s1.student_id = s2.student_id; |
NATURAL JOIN |
Matches columns with the same name in both tables. | SELECT * FROM table1 NATURAL JOIN table2; |
SELECT * FROM students NATURAL JOIN subjects; |
Subqueries in SQL
| Command | Description | Syntax | Example |
|---|---|---|---|
IN |
Determines whether a value matches any value in a subquery result. | SELECT column(s) FROM table WHERE value IN (subquery); |
SELECT * FROM students WHERE age IN (SELECT age FROM teachers); |
ANY |
Compares a value to any value returned by a subquery. | SELECT column(s) FROM table WHERE value < ANY (subquery); |
SELECT * FROM students WHERE age < ANY (SELECT age FROM teachers); |
ALL |
Compares a value to all values returned by a subquery. | SELECT column(s) FROM table WHERE value > ALL (subquery); |
SELECT * FROM students WHERE age > ALL (SELECT age FROM teachers); |
Aggregate Functions Commands
| Command | Description | Syntax | Example |
|---|---|---|---|
COUNT() |
Counts the number of rows or non-null values in a specified column. | SELECT COUNT(column_name) FROM table_name; |
SELECT COUNT(student_id) FROM students; |
SUM() |
Calculates the sum of all values in a specified column. | SELECT SUM(column_name) FROM table_name; |
SELECT SUM(age) FROM students; |
AVG() |
Calculates the average (mean) of all values in a specified column. | SELECT AVG(column_name) FROM table_name; |
SELECT AVG(age) FROM students; |
MIN() |
Returns the minimum (lowest) value in a specified column. | SELECT MIN(column_name) FROM table_name; |
SELECT MIN(age) FROM students; |
MAX() |
Returns the maximum (highest) value in a specified column. | SELECT MAX(column_name) FROM table_name; |
SELECT MAX(age) FROM students; |
String Functions in SQL
| Command | Description | Syntax | Example |
|---|---|---|---|
CONCAT() |
Concatenates two or more strings into a single string. | SELECT CONCAT(string1, string2, ...) AS concatenated_string FROM table_name; |
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM students; |
SUBSTR() |
Extracts a substring from a string. | SELECT SUBSTR(string, start_position, length) AS substring FROM table_name; |
SELECT SUBSTR(first_name, 1, 3) AS substring FROM students; |
LENGTH() |
Returns the length (number of characters) of a string. | SELECT LENGTH(string) AS length FROM table_name; |
SELECT LENGTH(first_name) AS length FROM students; |
UPPER() |
Converts all characters in a string to uppercase. | SELECT UPPER(string) AS uppercase_string FROM table_name; |
SELECT UPPER(first_name) AS uppercase_first_name FROM students; |
LOWER() |
Converts all characters in a string to lowercase. | SELECT LOWER(string) AS lowercase_string FROM table_name; |
SELECT LOWER(last_name) AS lowercase_last_name FROM students; |
TRIM() |
Removes specified prefixes or suffixes (or whitespace by default) from a string. | SELECT TRIM([LEADING | TRAILING | BOTH] characters FROM string) AS trimmed_string FROM table_name; |
SELECT TRIM(TRAILING ' ' FROM first_name) AS trimmed_name FROM students; |
LPAD() |
Pads the left side of a string with another string. | SELECT LPAD(string, length, pad_string) AS padded_string FROM table_name; |
SELECT LPAD(first_name, 10, '*') AS padded_name FROM students; |
RPAD() |
Pads the right side of a string with another string. | SELECT RPAD(string, length, pad_string) AS padded_string FROM table_name; |
SELECT RPAD(first_name, 10, '*') AS padded_name FROM students; |
REPLACE() |
Replaces occurrences of a substring within a string. | SELECT REPLACE(string, old_substring, new_substring) AS replaced_string FROM table_name; |
SELECT REPLACE(first_name, 'a', 'o') AS replaced_name FROM students; |
Date and Time SQL Commands
| Command | Description | Syntax | Example |
|---|---|---|---|
SYSDATE |
Returns the current date and time. | SELECT SYSDATE FROM dual; |
SELECT SYSDATE FROM dual; |
SYSTIMESTAMP |
Returns the current timestamp. | SELECT SYSTIMESTAMP FROM dual; |
SELECT SYSTIMESTAMP FROM dual; |
EXTRACT() |
Extracts a specific part (e.g., year, month, day) from a date or time. | SELECT EXTRACT(part FROM date_expression) AS extracted_part; |
SELECT EXTRACT(YEAR FROM SYSDATE) AS year FROM dual; |
ADD_MONTHS() |
Adds a specified number of months to a date. | SELECT ADD_MONTHS(date_expression, number) AS new_date; |
SELECT ADD_MONTHS(SYSDATE, 1) AS new_date FROM dual; |
MONTHS_BETWEEN() |
Calculates the number of months between two dates. | SELECT MONTHS_BETWEEN(date1, date2) AS difference_in_months; |
SELECT MONTHS_BETWEEN('2024-04-11', SYSDATE) AS difference_in_months FROM dual; |
TO_CHAR() |
Converts a date or time to a specified format. | SELECT TO_CHAR(date_expression, 'format') AS formatted_date; |
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS formatted_date FROM dual; |
TO_DATE() |
Converts a string to a date. | SELECT TO_DATE(string, 'format') AS date FROM dual; |
SELECT TO_DATE('2024-04-11', 'YYYY-MM-DD') AS date FROM dual; |
NEXT_DAY() |
Returns the date of the next specified weekday after a given date. | SELECT NEXT_DAY(date, 'weekday') AS next_day FROM dual; |
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') AS next_day FROM dual; |
LAST_DAY() |
Returns the last day of the month for a given date. | SELECT LAST_DAY(date) AS last_day FROM dual; |
SELECT LAST_DAY(SYSDATE) AS last_day FROM dual; |
ROUND() |
Rounds a date to the nearest day, month, or year. | SELECT ROUND(date, 'format') AS rounded_date FROM dual; |
SELECT ROUND(SYSDATE, 'MONTH') AS rounded_date FROM dual; |
TRUNC() |
Truncates a date to the specified unit (day, month, year). | SELECT TRUNC(date, 'format') AS truncated_date FROM dual; |
SELECT TRUNC(SYSDATE, 'YEAR') AS truncated_date FROM dual; |
Conditional Expressions
| Command | Description | Syntax | Example |
|---|---|---|---|
CASE |
Allows you to perform conditional logic within a query. | SELECT column1, column2, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END AS alias FROM table_name; |
SELECT student_id, first_name, CASE WHEN age > 18 THEN 'Adult' ELSE 'Minor' END AS age_group FROM students; |
DECODE() |
Evaluates a list of conditions and returns a value based on the first match. | SELECT DECODE(expression, search1, result1, search2, result2, ..., default) AS alias FROM table_name; |
SELECT student_id, first_name, DECODE(age, 18, 'Adult', 17, 'Minor', 'Unknown') AS age_group FROM students; |
COALESCE() |
Returns the first non-null value from a list of values. | SELECT COALESCE(value1, value2, ...) AS alias FROM table_name; |
SELECT COALESCE(middle_name, first_name) AS preferred_name FROM students; |
NVL() |
Replaces null with a specified value. | SELECT NVL(expression, value) AS alias FROM table_name; |
SELECT NVL(age, 18) AS age FROM students; |
Set Operations
| Command | Description | Syntax | Example |
|---|---|---|---|
UNION |
Combines the result sets of two or more SELECT statements into a single result set. | SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2; |
SELECT first_name, last_name FROM students UNION SELECT first_name, last_name FROM teachers; |
INTERSECT |
Returns the common rows that appear in both result sets. | SELECT column1, column2 FROM table1 INTERSECT SELECT column1, column2 FROM table2; |
SELECT first_name, last_name FROM students INTERSECT SELECT first_name, last_name FROM teachers; |
MINUS |
Returns the distinct rows from the left result set that are not present in the right result set. | SELECT column1, column2 FROM table1 MINUS SELECT column1, column2 FROM table2; |
SELECT first_name, last_name FROM students MINUS SELECT first_name, last_name FROM teachers; |
Transaction Control Commands
| Command | Description | Syntax | Example |
|---|---|---|---|
COMMIT |
Saves all the changes made during the current transaction and makes them permanent. | COMMIT; |
BEGIN TRANSACTION; INSERT INTO students (first_name, age) VALUES ('Alice', 20); COMMIT; |
ROLLBACK |
Undoes all the changes made during the current transaction and discards them. | ROLLBACK; |
BEGIN TRANSACTION; INSERT INTO students (first_name, age) VALUES ('Bob', 22); ROLLBACK; |
SAVEPOINT |
Sets a point within a transaction to which you can later roll back. | SAVEPOINT savepoint_name; |
BEGIN TRANSACTION; INSERT INTO students (first_name, age) VALUES ('Carol', 21); SAVEPOINT before_update; UPDATE students SET age = 23 WHERE first_name = 'Carol'; SAVEPOINT after_update; DELETE FROM students WHERE age > 21; ROLLBACK TO before_update; COMMIT; |
ROLLBACK TO SAVEPOINT |
Rolls back to a specific savepoint within a transaction. | ROLLBACK TO SAVEPOINT savepoint_name; |
BEGIN TRANSACTION; INSERT INTO students (first_name, age) VALUES ('David', 24); SAVEPOINT before_update; UPDATE students SET age = 25 WHERE first_name = 'David'; SAVEPOINT after_update; DELETE FROM students WHERE age > 24; ROLLBACK TO SAVEPOINT before_update; COMMIT; |
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; INSERT INTO students (first_name, age) VALUES ('Emily', 26); COMMIT; |
Conclusion
Mastering Oracle SQL commands is essential for efficiently managing databases and performing administrative tasks. This cheat sheet provides a quick reference to some of the most commonly used commands, helping you navigate and operate your Oracle databases more effectively. Keep this guide handy to make the most of Oracle SQL. Happy coding!