Oracle Database Cheat Sheet by Bimbel Jakarta Timur

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.

https://www.radarhot.com/2019/01/kursus-komputer-pemrograman-oracle.html