Oracle Database Cheat Sheet by Bimbel Jakarta Timur

Radarhot.com September 14, 2024

Oracle Database is a powerful and complex RDBMS, offering a wide range of features for efficient data management. This cheat sheet provides a quick reference to essential commands and operations.

Connecting to Oracle Database

sql 
sqlplus username/password@hostname:port/SID

Connect to an Oracle database using SQL*Plus.

bash 
conn username/password@hostname:port/SID

Alternative connection command.

Basic SQL Commands

sql 
SELECT * FROM table_name;

Retrieve all rows and columns from a table.

sql 
SELECT column1, column2 FROM table_name;

Retrieve specific columns from a table.

sql 
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Insert a new record into a table.

sql 
UPDATE table_name SET column1 = value1 WHERE condition;

Update existing records in a table.

sql 
DELETE FROM table_name WHERE condition;

Delete records from a table.

Database Operations

Create Database

sql 
CREATE DATABASE database_name;

Create a new database.

Drop Database

sql 
DROP DATABASE database_name;

Delete a database.

Create Table

sql 
CREATE TABLE table_name (
    column1 datatype CONSTRAINT,
    column2 datatype CONSTRAINT,
    ...
);

Create a new table.

Drop Table

sql 
DROP TABLE table_name;

Delete a table.

Constraints

Primary Key

sql 
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column);

Add a primary key constraint.

Foreign Key

sql 
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column) REFERENCES other_table (other_column);

Add a foreign key constraint.

Unique Key

sql 
ALTER TABLE table_name ADD CONSTRAINT unique_name UNIQUE (column);

Add a unique key constraint.

Indexes

Create Index

sql 
CREATE INDEX index_name ON table_name (column);

Create an index on a table.

Drop Index

sql 
DROP INDEX index_name;

Delete an index.

Views

Create View

sql 
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Create a view based on a SELECT query.

Drop View

sql 
DROP VIEW view_name;

Delete a view.

Transactions

Begin Transaction

sql 
BEGIN;

Start a new transaction.

Commit

sql 
COMMIT;

Save changes made in the current transaction.

Rollback

sql 
ROLLBACK;

Undo changes made in the current transaction.

PL/SQL Basics

Anonymous Block

sql 
DECLARE
    variable_name datatype;
BEGIN
    -- PL/SQL statements
EXCEPTION
    WHEN exception_name THEN
        -- exception handling
END;

Create an anonymous PL/SQL block.

Stored Procedure

sql 
CREATE OR REPLACE PROCEDURE procedure_name IS
BEGIN
    -- PL/SQL statements
END procedure_name;

Create a stored procedure.

Stored Function

sql 
CREATE OR REPLACE FUNCTION function_name RETURN datatype IS
BEGIN
    -- PL/SQL statements
    RETURN value;
END function_name;

Create a stored function.

Trigger

sql 
CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
BEGIN
    -- PL/SQL statements
END trigger_name;

Create a trigger.

User and Role Management

Create User

sql 
CREATE USER username IDENTIFIED BY password;

Create a new user.

Grant Privileges

sql 
GRANT privilege TO username;

Grant specific privileges to a user.

Create Role

sql 
CREATE ROLE role_name;

Create a new role.

Grant Role

sql 
GRANT role_name TO username;

Assign a role to a user.

Common Commands

Show Tables

sql 
SELECT table_name FROM all_tables;

List all tables in the current schema.

Describe Table

sql 
DESC table_name;

Show the structure of a table.

Show Users

sql 
SELECT username FROM all_users;

List all users in the database.

Show Current User

sql 
SELECT user FROM dual;

Display the current user.

Useful Functions

String Functions

sql 
UPPER(string);
LOWER(string);
SUBSTR(string, start_position, length);

Convert to upper/lowercase, substring extraction.

Numeric Functions

sql 
ROUND(number, decimal_places);
TRUNC(number, decimal_places);

Round and truncate numbers.

Date Functions

sql 
SYSDATE;
TO_DATE('YYYY-MM-DD', 'format');

Get current date, convert string to date.

This cheat sheet provides a quick reference to commonly used commands and operations in Oracle Database, helping you manage and manipulate your data efficiently.