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
sqlplus username/password@hostname:port/SID
- Connect to an Oracle database using SQL*Plus.
conn username/password@hostname:port/SID
- Alternative connection command.
Basic SQL Commands
SELECT * FROM table_name;
- Retrieve all rows and columns from a table.
SELECT column1, column2 FROM table_name;
- Retrieve specific columns from a table.
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
- Insert a new record into a table.
UPDATE table_name SET column1 = value1 WHERE condition;
- Update existing records in a table.
DELETE FROM table_name WHERE condition;
- Delete records from a table.
Database Operations
Create Database
CREATE DATABASE database_name;
Drop Database
DROP DATABASE database_name;
Create Table
CREATE TABLE table_name (
column1 datatype CONSTRAINT,
column2 datatype CONSTRAINT,
...
);
Drop Table
Constraints
Primary Key
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column);
- Add a primary key constraint.
Foreign Key
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column) REFERENCES other_table (other_column);
- Add a foreign key constraint.
Unique Key
ALTER TABLE table_name ADD CONSTRAINT unique_name UNIQUE (column);
- Add a unique key constraint.
Indexes
Create Index
CREATE INDEX index_name ON table_name (column);
- Create an index on a table.
Drop Index
Views
Create View
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
- Create a view based on a SELECT query.
Drop View
Transactions
Begin Transaction
Commit
- Save changes made in the current transaction.
Rollback
- Undo changes made in the current transaction.
PL/SQL Basics
Anonymous Block
DECLARE
variable_name datatype;
BEGIN
EXCEPTION
WHEN exception_name THEN
END;
- Create an anonymous PL/SQL block.
Stored Procedure
CREATE OR REPLACE PROCEDURE procedure_name IS
BEGIN
END procedure_name;
- Create a stored procedure.
Stored Function
CREATE OR REPLACE FUNCTION function_name RETURN datatype IS
BEGIN
RETURN value;
END function_name;
- Create a stored function.
Trigger
CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
BEGIN
END trigger_name;
User and Role Management
Create User
CREATE USER username IDENTIFIED BY password;
Grant Privileges
GRANT privilege TO username;
- Grant specific privileges to a user.
Create Role
Grant Role
GRANT role_name TO username;
Common Commands
Show Tables
SELECT table_name FROM all_tables;
- List all tables in the current schema.
Describe Table
- Show the structure of a table.
Show Users
SELECT username FROM all_users;
- List all users in the database.
Show Current User
- Display the current user.
Useful Functions
String Functions
UPPER(string);
LOWER(string);
SUBSTR(string, start_position, length);
- Convert to upper/lowercase, substring extraction.
Numeric Functions
ROUND(number, decimal_places);
TRUNC(number, decimal_places);
- Round and truncate numbers.
Date Functions
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