Cheatsheet - Oracle

Connecting & Session Control

Basic commands for connecting and managing your session.

# Connect from the command line (will prompt for password)
sqlplus username@connect_identifier
sqlplus username/password@connect_identifier

# Example connect string (using TNSNAMES.ORA entry)
sqlplus scott@orcl

In-Session Commands:

Command	Description
`CONNECT user/pass@db`	Connect to the database.
`DISCONNECT`	Disconnect from the database.
`SHOW USER;`	Display the current user.
`SET LINESIZE 200;`	Set the width of the output line.
`SET PAGESIZE 50;`	Set the number of lines per page of output.
`DESCRIBE <table_name>;`	Show the structure of a table or view.
`SPOOL <filename.txt>`	Start writing output to a file.
`SPOOL OFF`	Stop writing output to the file.
`/`	Execute the command in the SQL buffer.
`EXIT` or `QUIT`	Disconnect and terminate the session.

Data Definition Language (DDL)

Commands for defining and managing the structure of database objects.

Create a Table:

CREATE TABLE employees (
    employee_id   NUMBER(6) PRIMARY KEY,
    first_name    VARCHAR2(20),
    last_name     VARCHAR2(25) NOT NULL,
    email         VARCHAR2(25) UNIQUE,
    hire_date     DATE DEFAULT SYSDATE
);

Drop a Table:
```
DROP TABLE employees;
```
Alter a Table:
- Add a column: ALTER TABLE employees ADD (department_id NUMBER(4));
- Modify a column: ALTER TABLE employees MODIFY (first_name VARCHAR2(30));
- Drop a column: ALTER TABLE employees DROP COLUMN department_id;
- Rename a column: ALTER TABLE employees RENAME COLUMN first_name TO given_name;

Create an Index:

CREATE INDEX emp_lastname_idx ON employees (last_name);

Create a Sequence (for generating unique numbers):
```
CREATE SEQUENCE employee_seq START WITH 207 INCREMENT BY 1;
```
- Usage: employee_seq.NEXTVAL, employee_seq.CURRVAL

Data Manipulation Language (DML)

Commands for adding, modifying, and removing data.

Insert a Row:

INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (employee_seq.NEXTVAL, 'John', 'Doe', 'jdoe');

Update Records:

UPDATE employees SET first_name = 'Jane' WHERE last_name = 'Doe';

Delete Records:

DELETE FROM employees WHERE email = 'jdoe';

Merge (Upsert): Performs an update if the row exists, or an insert if it does not.

MERGE INTO employees e
USING (SELECT 100 AS id, 'Steven' AS fname, 'King' AS lname FROM dual) s
ON (e.employee_id = s.id)
WHEN MATCHED THEN
  UPDATE SET e.first_name = s.fname
WHEN NOT MATCHED THEN
  INSERT (employee_id, first_name, last_name)
  VALUES (s.id, s.fname, s.lname);

Transaction Control

Command	Description
`COMMIT;`	Make all changes since the last commit permanent.
`ROLLBACK;`	Discard all changes since the last commit.
`SAVEPOINT <name>;`	Create a point to which you can later roll back.
`ROLLBACK TO <name>;`	Roll back to a specific savepoint.

Data Query Language (DQL) - The `SELECT` Statement

Basic Query: SELECT first_name, last_name FROM employees;
Filtering with WHERE: SELECT * FROM employees WHERE hire_date > '01-JAN-2020';
Sorting with ORDER BY: SELECT * FROM employees ORDER BY last_name ASC, first_name DESC;
Oracle-Specific Row Limiting (ROWNUM and modern syntax):
- Get the first 10 rows (classic): SELECT * FROM employees WHERE ROWNUM <= 10;
- Pagination (modern 12c+):
```
SELECT * FROM employees
ORDER BY last_name
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
```

Aggregate Functions & Grouping:

SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

The dual Table: A special one-row, one-column table used for queries that don't need a real table.
```
SELECT SYSDATE, 1+1, USER FROM dual;
```

Joins

Inner Join (ANSI Syntax):

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Left Outer Join (ANSI Syntax):

SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Oracle-Specific Join Syntax (Legacy):

-- Inner Join
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- Left Outer Join (the '+' is on the side that may have NULLs)
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id (+);

Common Data Types

Category	Data Types
String	`VARCHAR2(size)`, `CHAR(size)`, `CLOB`
Numeric	`NUMBER(precision, scale)`, `INTEGER`, `FLOAT`
Date/Time	`DATE`, `TIMESTAMP`, `TIMESTAMP WITH TIME ZONE`
Binary	`BLOB` (Binary Large Object), `RAW`

Key Functions

String: SUBSTR(), INSTR(), LENGTH(), UPPER(), LOWER(), INITCAP(), TRIM(), REPLACE(), || (concatenation operator)
Numeric: ROUND(), TRUNC(), MOD(), CEIL(), FLOOR()
Date: SYSDATE, SYSTIMESTAMP, ADD_MONTHS(), LAST_DAY(), MONTHS_BETWEEN(), EXTRACT()
Conversion: TO_CHAR(), TO_DATE(), TO_NUMBER()
NULL-Related: NVL(expr1, expr2) (if expr1 is null, return expr2), NVL2(), COALESCE()
Analytical (Window) Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM(...) OVER (...)

User and Privilege Management

Create a User:

CREATE USER new_user IDENTIFIED BY "Password123";

Grant Privileges:

-- Grant basic connection and resource creation rights
GRANT CONNECT, RESOURCE TO new_user;

-- Grant specific privileges on an object
GRANT SELECT, INSERT ON employees TO new_user;

-- Grant unlimited tablespace quota
GRANT UNLIMITED TABLESPACE TO new_user;

Revoke Privileges:

REVOKE INSERT ON employees FROM new_user;

Data Dictionary Views

Query these views to get metadata about your database objects.

View	Description
`USER_OBJECTS`	Objects owned by the current user.
`ALL_OBJECTS`	Objects the current user has access to.
`DBA_OBJECTS`	All objects in the database (requires DBA privilege).
`USER_TABLES`	Tables owned by the current user.
`USER_TAB_COLUMNS`	Columns in tables owned by the current user.
`USER_CONSTRAINTS`	Constraints on tables owned by the current user.
`USER_INDEXES`	Indexes owned by the current user.
`V$SESSION`	Information about current database sessions.
`V$INSTANCE`	Information about the current instance.