Oracle SQL Cheat Sheet – Pedagogy Zone

By
On:

here’s a basic cheat sheet for Oracle SQL

SELECT Query

SELECT col1, col2
FROM table
JOIN table2 ON table1.col = table2.col
WHERE condition
GROUP BY column_name
HAVING condition
ORDER BY col1 ASC|DESC;

SELECT Keywords

DISTINCT: Removes
duplicate results
SELECT DISTINCT product_name
FROM product;
BETWEEN: Matches a
value between two
other values (inclusive)
SELECT product_name
FROM product
WHERE price BETWEEN 50 AND 100;
IN: Matches to any of
the values in a list
SELECT product_name
FROM product
WHERE category IN
('Electronics', 'Furniture');
LIKE: Performs
wildcard matches using
_ or %
SELECT product_name
FROM product
WHERE product_name
LIKE '%Desk%";

Joins

SELECT t1.*, t2.*
FROM t1
join_type t2 ON t1.col = t2.col;

INNER JOIN: show all matching
records in both tables.
LEFT JOIN: show all records from left table, and any matching records from right table.
RIGHT JOIN: show all records from
right table, and any matching records from left table.
FULL JOIN: show all records from
both tables, whether there is a match or not.

CASE Statement

Simple Case

CASE name
WHEN 'John' THEN 'Name John'
WHEN 'Steve' THEN 'Name Steve'
ELSE 'Unknown'
END

Searched Case

CASE
WHEN name='John' THEN 'Name John'
WHEN name='Steve' THEN 'Name Steve'
ELSE 'Unknown'
END

Common Table Expression

WITH queryname AS (
SELECT col1, col2
FROM firsttable)
SELECT col1, col2..
FROM queryname...;

Modifying Data

Insert INSERT INTO tablename
(col1, col2...)
VALUES (val1, val2);
Insert from a
Table
INSERT INTO tablename
(col1, col2...)
SELECT col1, col2...
Insert Multiple
Rows
INSERT
INTO tablename (col1, col2)
VALUES (valA1, valB1)
INTO tablename (col1, col2)
VALUES (valA2, valB2)
SELECT * FROM dual;
Update UPDATE tablename
SET col1 = val1
WHERE condition;
Update with
a Join
UPDATE t
SET col1 = val1
FROM tablename t
INNER JOIN table x
ON t.id = x.tid
WHERE condition;
Delete DELETE FROM tablename
WHERE condition;

Indexes

Create Index

CREATE INDEX indexname
ON tablename (cols);

Drop Index

DROP INDEX indexname;

Set Operators

UNION: Shows unique
rows from two result sets.
UNION ALL: Shows all
rows from two result sets.
INTERSECT: Shows rows that
exist in both result sets.
EXCEPT: Shows rows that exist
in the first result set but not
the second.

Aggregate Functions

SUM: Finds a total of the numbers provided
COUNT: Finds the number of records
AVG: Finds the average of the numbers provided
MIN: Finds the lowest of the numbers provided
MAX: Finds the highest of the numbers provided

Common Functions

  • LENGTH(string): Returns the length of the provided string
    INSTR(string, substring, [start_position], [occurrence]): Returns the position of the substring within the specified string.
  • TO_CHAR(input_value, [fmt_mask], [nls_param]): Converts a date
    or a number to a string
  • TO_DATE(charvalue, [fmt_mask], [nls_date_lang]): Converts a
    string to a date value.
  • TO_NUMBER(input_value, [fmt_mask], [nls_param]): Converts a
    string value to a number.
  • ADD_MONTHS(input_date, num_months): Adds a number of
    months to a specified date.
  • SYSDATE: Returns the current date, including time.
  • CEIL(input_val): Returns the smallest integer greater than the
    provided number.
  • FLOOR(input_val): Returns the largest integer less than the
    provided number.
  • ROUND(input_val, round_to): Rounds a number to a specified
    number of decimal places.
  • TRUNC(input_value, dec_or_fmt): Truncates a number or date to a
    number of decimals or format.
  • REPLACE(whole_string, string_to_replace, [replacement_string]):
    Replaces one string inside the whole string with another string.
  • SUBSTR(string, start_position, [length]): Returns part of a value,
    based on a position and length.

Create Table

Create Table CREATE TABLE tablename (
column_name data_type
);

Create Table with Constraints

CREATE TABLE tablename (
column_name data_type NOT NULL,
CONSTRAINT pkname PRIMARY KEY (col),
CONSTRAINT fkname FOREIGN KEY (col)
REFERENCES other_table(col_in_other_table),
CONSTRAINT ucname UNIQUE (col),
CONSTRAINT ckname CHECK (conditions)
);

Create Temporary
Table
CREATE GLOBAL TEMPORARY TABLE
tablename (
colname datatype
) ON COMMIT DELETE ROWS;
Drop Table DROP TABLE tablename;

Alter Table

Add Column ALTER TABLE tablename
ADD columnname datatype;
Drop Column ALTER TABLE tablename
DROP COLUMN columnname;
Modify Column ALTER TABLE tablename MODIFY
columnname newdatatype;
Rename Column ALTER TABLE tablename RENAME COLUMN
currentname TO newname;
Add Constraint ALTER TABLE tablename ADD
CONSTRAINT constraintname
constrainttype (columns);
Drop Constraint ALTER TABLE tablename DROP
constraint_type constraintname;
Rename Table sp_rename
'old_table_name',
'new_table_name';

Window/Analytic Functions

function_name ( arguments ) OVER (
[query_partition_clause]
[ORDER BY order_by_clause
[windowing_clause] ] )

Example using RANK, showing the student details and their rank according to the fees_paid, grouped by gender:

SELECT
student_id, first_name, last_name, gender, fees_paid,
RANK() OVER (
PARTITION BY gender ORDER BY fees_paid
) AS rank_val
FROM student;

Subqueries

Single Row SELECT id, last_name, salary
FROM employee
WHERE salary = (
SELECT MAX(salary)
FROM employee
);
Multi Row SELECT id, last_name, salary
FROM employee
WHERE salary IN (
SELECT salary
FROM employee
WHERE last_name LIKE 'C%'
);
Read More Topics
HTML cheat sheet with examples
OOPs Interview Questions
Python interview questions and answers for freshers

Santhakumar Raja

I am the founder of Pedagogy Zone, a dedicated education platform that provides reliable and up-to-date information on academic trends, learning resources, and educational developments.

For Feedback - techactive6@gmail.com