Oracle Database Cheat Sheet by Bimbel Jakarta Timur

Radarhot.com
By -


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