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.
bashconn 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
sqlROUND(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
on 2019/01/04
|
English,
Pemrograman
Label
Aljabar
Aritmatika
Autocad
Bimbel Jakarta Timur
Bimbingan Belajar
Biologi
CPNS
Corel Draw
Fisika
Geometri
IPA
Ilmu Pengetahuan
Info
Inspirasi
Islami
Kalkulus
Kimia
Kombinatorika
Manajemen
Matematika
Metode
OSN
Operasi Hitung
PAT PAS UAS
Pemrograman
Pengukuran
Photoshop
RadarHot Com
SEO
Soal
Software
Statistika
Trigonometri
Tutorial
Ujian Sekolah
video
Popular posts
-
Radarhot com is a leading Indonesian website dedicated to educational and scientific news. Catering to students, educators,...
-
Dalam Artikel Tabayyun atau tatsabbut (cross check) Bimbel Jakarta Timur mencoba menjelaskan secara rinci dalam Al Qur'an dan Hadist Sa...
-
In the ever-evolving academic landscape, access to high-quality, peer-reviewed research is essential for students, educators, and researche...