Oracle Database Cheat Sheet

Oracle database cheat sheet or Oracle cheat sheet will provide the information of commands used in Oracle database in one place. Oracle database cheat sheet will list down all the popular or frequently used sql queries.

Commonly used List of queries of Oracle database will be listed in Oracle cheat sheet.

Create database

CREATE DATABASE USER;

---

Delete database

Drop database User;

---

Create table

CREATE TABLE USER_DETAILS (ID NUMBER(10), NAME VARCHAR2(100), CITY VARCHAR2(50), COUNTRY VARCHAR2(50), REGION VARCHAR2(50));

---

Insert into table

INSERT INTO USER_DETAILS VALUES (1,”ALLEN”,”Bristol”,”UK,”Europe”); 

---

Select All data from table

Select * from User_details;

---

Select few records

Select name,location from User_Details where region=”Asia”;

---

Update data into table

Update User_details SET region=”Europe” where name=”Vikki”;

---

Delete data from table

DELETE from User_Details where id=”1”;

---

Add column into table

ALTER TABLE user_details ADD (age int);

---

Alter Column

ALTER TABLE USER_DETAILS MODIFY (NAME VARCHAR2(100));

---

Delete column

ALTER  TABLE User_Details DROP COLUMN age;

---

Create Sequence

CREATE SEQUENCE USER_ID INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 10000  CYCLE CACHE 2;

---

Drop Sequence

DROP SEQUENCE USER_ID; 

---

Create index/ Add Index

CREATE INDEX User_Id_index ON User_Details(name);

---

Drop index

DROP INDEX User_Id_index;

---

Add Primary key

ALTER TABLE User_Details ADD CONSTRAINT id_pk PRIMARY KEY (id);

---

Delete primary key

ALTER TABLE User_Details DROP CONSTRAINT id_pk;

---

Joins

SELECT USER_DETAILS.ID, USER_DETAILS.NAME, ADDRESS.home_address  

FROM USER_DETAILS   

INNER JOIN ADDRESS  

ON USER_DETAILS.ID = ADDRESS.ID;

---

Order By

SELECT ID,NAME FROM User_details ORDER BY NAME;

---

Create User

CREATE USER testuser1 IDENTIFIED BY password123;

---

Grant/Assign Privileges to user

GRANT SELECT,INSERT,UPDATE ON USER_DETAILS TO developer1;

---

Revoke/Remove Privileges from User

REVOKE INSERT,UPDATE ON USER_DETAILS from tester1;

---

Drop user 

DROP USER tester1;

---