Oracle Cheat Sheet

Here’s a cheat sheet for Oracle Database, covering some common commands and SQL queries:

Oracle SQL Queries

Connect to Database:

CONNECT username/password@hostname:port/service_name

Create a Table:

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

Insert Data:

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

Select Data:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Update Data:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Delete Data:

DELETE FROM table_name
WHERE condition;

Join Tables:

SELECT *
FROM table1
JOIN table2 ON table1.column = table2.column;

Aggregate Functions:

SELECT COUNT(column), AVG(column), SUM(column)
FROM table_name
WHERE condition;

Group By:

SELECT column, COUNT(*)
FROM table_name
GROUP BY column;

Order By:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC, column2 DESC;

Database Administration

Create User:

CREATE USER username IDENTIFIED BY password;

Grant Privileges:

GRANT privilege
ON object
TO user;

Revoke Privileges:

REVOKE privilege
ON object
FROM user;

Backup Database:

BACKUP DATABASE;

Restore Database:

RECOVER DATABASE;

System Information

List Tables:

SELECT table_name
FROM user_tables;

Describe Table:

DESCRIBE table_name;

Show Indexes:

SELECT index_name, column_name
FROM user_ind_columns
WHERE table_name = 'your_table';

PL/SQL (Procedural Language/SQL)

Create Stored Procedure:

CREATE OR REPLACE PROCEDURE procedure_name
IS
BEGIN
    -- Procedure logic
END procedure_name;

Execute Procedure:

EXEC procedure_name;

Tips

Use bind variables (:variable) to avoid SQL injection.
Regularly perform database backups for data integrity.
Utilize indexes and analyze table statistics for query optimization.

This Oracle cheat sheet covers basic SQL queries and database administration commands. Adjust the examples based on your specific database structure and requirements. For more detailed information, refer to the Oracle Database Documentation.