Oracle 11g Cheat Sheet

Database Connection

CONNECT username/password@hostname:port/SID
CONNECT sys/password AS SYSDBA
DISCONNECT

Basic Queries

SELECT * FROM table_name;
SELECT column1, column2 FROM table_name;
SELECT DISTINCT column1 FROM table_name;
SELECT * FROM table_name WHERE condition;
SELECT * FROM table_name ORDER BY column1 [ASC|DESC];

Filtering

WHERE column = value
WHERE column LIKE 'pattern%'
WHERE column IN (value1, value2)
WHERE column BETWEEN value1 AND value2
WHERE condition1 AND condition2
WHERE condition1 OR condition2
WHERE NOT condition

Joins

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column

Aggregation

SELECT COUNT(*) FROM table_name;
SELECT SUM(column) FROM table_name;
SELECT AVG(column) FROM table_name;
SELECT MIN(column), MAX(column) FROM table_name;
SELECT column, COUNT(*) FROM table_name GROUP BY column;
SELECT column, COUNT(*) FROM table_name GROUP BY column HAVING COUNT(*) > 1;

Data Modification

INSERT INTO table_name (col1, col2) VALUES (val1, val2);
UPDATE table_name SET column1 = value1 WHERE condition;
DELETE FROM table_name WHERE condition;
MERGE INTO target_table USING source_table ON (condition)
  WHEN MATCHED THEN UPDATE SET col1 = val1
  WHEN NOT MATCHED THEN INSERT (col1, col2) VALUES (val1, val2);

Table Management

CREATE TABLE table_name (col1 datatype, col2 datatype);
CREATE TABLE new_table AS SELECT * FROM existing_table;
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY column_name new_datatype;
DROP TABLE table_name;
TRUNCATE TABLE table_name;

Indexes

CREATE INDEX index_name ON table_name (column);
CREATE UNIQUE INDEX index_name ON table_name (column);
DROP INDEX index_name;

Views

CREATE VIEW view_name AS SELECT columns FROM table WHERE condition;
CREATE OR REPLACE VIEW view_name AS SELECT columns FROM table WHERE condition;
DROP VIEW view_name;

Sequences

CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1;
SELECT seq_name.NEXTVAL FROM dual;
SELECT seq_name.CURRVAL FROM dual;
DROP SEQUENCE seq_name;

PL/SQL Basics

DECLARE
  variable_name datatype [:= value];
BEGIN
  -- statements
EXCEPTION
  WHEN exception_name THEN
    -- handler
END;

Cursors

DECLARE
  CURSOR cursor_name IS SELECT statement;
BEGIN
  OPEN cursor_name;
  FETCH cursor_name INTO variables;
  CLOSE cursor_name;
END;

Stored Procedures

CREATE OR REPLACE PROCEDURE proc_name (param1 IN datatype, param2 OUT datatype)
IS
BEGIN
  -- procedure body
END;
/

EXEC proc_name(param1_value, param2_value);

Functions

CREATE OR REPLACE FUNCTION func_name (param1 IN datatype) RETURN datatype
IS
  return_value datatype;
BEGIN
  -- function body
  RETURN return_value;
END;
/

SELECT func_name(param1) FROM dual;

Triggers

CREATE OR REPLACE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name
[FOR EACH ROW]
BEGIN
  -- trigger body
END;
/

DROP TRIGGER trigger_name;

Transactions

COMMIT;
ROLLBACK;
SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;
SET TRANSACTION READ ONLY;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Performance Tuning

EXPLAIN PLAN FOR SELECT statement;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
CREATE INDEX index_name ON table_name(column1, column2);
ANALYZE TABLE table_name COMPUTE STATISTICS;

User Management

CREATE USER username IDENTIFIED BY password;
ALTER USER username IDENTIFIED BY new_password;
GRANT privilege_name ON object_name TO username;
REVOKE privilege_name ON object_name FROM username;
DROP USER username [CASCADE];

Export/Import

-- Data Pump Export
expdp username/password DIRECTORY=dir_name DUMPFILE=file_name.dmp SCHEMAS=schema_name

-- Data Pump Import
impdp username/password DIRECTORY=dir_name DUMPFILE=file_name.dmp SCHEMAS=schema_name

Common Functions

-- String
UPPER(), LOWER(), INITCAP(), CONCAT(), SUBSTR(), LENGTH(), INSTR(), TRIM(), REPLACE()

-- Numeric
ROUND(), TRUNC(), MOD(), ABS(), CEIL(), FLOOR(), POWER(), SQRT()

-- Date
SYSDATE, ADD_MONTHS(), MONTHS_BETWEEN(), LAST_DAY(), NEXT_DAY(), TRUNC(), ROUND()

-- Conversion
TO_CHAR(), TO_NUMBER(), TO_DATE(), CAST()

-- NULL handling
NVL(), NVL2(), COALESCE(), NULLIF()

System Views

SELECT * FROM all_tables;
SELECT * FROM all_tab_columns;
SELECT * FROM all_views;
SELECT * FROM all_indexes;
SELECT * FROM all_constraints;
SELECT * FROM all_sequences;
SELECT * FROM all_users;
SELECT * FROM v$session;
SELECT * FROM v$database;


Generated from Deepseek Ai

Comments

Post a Comment

Popular posts from this blog

Terminal-Based Writing

Terminal-Based Writing: From Termux to Professional Docs (A Beginner’s Guide to WordGrinder + Pandoc) Do you love writing in the terminal but need professional-looking PDFs or Word files? This guide will show you how to: Write in Termux using wordgrinder (a simple terminal word processor). Convert to Markdown for flexibility. Generate beautiful PDFs/DOCX files using pandoc . No complex setups—just clear, step-by-step instructions. Let’s begin! Step 1: Install Termux & Tools (Skip if you already have Termux!) Install Termux (Android terminal emulator). Update packages: pkg update && pkg upgrade Install WordGrinder (terminal word processor) and Pandoc (document converter): pkg install wordgrinder pandoc Step 2: Write in WordGrinder WordGrinder is a lightweight word processor with bold, italics, and underlines . Basic Commands Open a new file: wordgrinder my_note.zws Shortcuts: Ctrl+S → Save Ctrl+B → Bold Ctrl+I → Italics...
Read more

Markdown Quick Tutorial

Markdown Quick Tutorial: The Essentials in 5 Minutes Markdown is a lightweight markup language for formatting plain text. It’s perfect for notes, docs, and even websites (like GitHub READMEs). Here’s all you need to know: 1. Basic Formatting Style Markdown Output Bold **bold** bold Italic *italic* italic Strikethrough ~~strike~~ strike Code `code` code Link [text](URL) Google Image ![alt](image.png) (Shows image) 2. Headings # Heading 1 (Biggest) ## Heading 2 ### Heading 3 #### Heading 4 3. Lists Bullet Points - Item 1 - Item 2 - Sub-item (indent with 2 spaces) Numbered Lists 1. First 2. Second 3. Third 4. Code Blocks Inline Code Use `print("Hello")` in Python. Multi-line Code Use triple backticks + language name: ```python def hello(): print("World") ``` (Shows with syntax highlighting!) 5. Tables | Name | Age | Job | |---------|-----|----------| | Alice | 25 | Engineer | | Bob | 30...
Read more

To install Jupyter Notebook on Ubuntu (Linux)

To install Jupyter Notebook on Ubuntu 24, follow these steps: Update the system : sudo apt update && sudo apt upgrade -y Install Python and pip (Ubuntu 24 typically comes with Python pre-installed): sudo apt install python3 python3-pip -y Install Jupyter Notebook : pip3 install jupyter Verify the installation : jupyter notebook --version Launch Jupyter Notebook : jupyter notebook This will start the Jupyter server and open a browser window. If it doesn’t, navigate to http://localhost:8888 in your browser. Optional: Install in a virtual environment (recommended to avoid package conflicts): Install virtualenv: sudo apt install python3-venv -y Create and activate a virtual environment: python3 -m venv jupyter_env source jupyter_env/bin/activate Install Jupyter Notebook inside the virtual environment: pip install jupyter Run Jupyter Notebook as above. Notes : Ensure you have an active internet connection. If you encounter permission issues with pi...
Read more