SQL Cheat Sheet – Oracle SQL & PostgrSQL

The database is nothing but a piece of software in which we store information. In a relational database, we store information in the form of tables, in which we have columns and rows. When you developing any web application then you need a database to store the user information. There are two types of databases, Relational Databases, and NoSQL Databases. Oracle database is one type of relational database.

Oracle SQL is the world's most widely used database management system. It is used to store and retrieve information. Oracle database is designed for enterprise grid computing.

PostgreSQL is an open-source, powerful and advanced version of SQL that supports different functions of SQL including, foreign keys, subqueries, functions, and user-defined types.

In this quick reference cheat sheet, we will show Oracle SQL and PostgreSQL commands with examples.

Oracle SQL Cheat Sheet

A cheat sheet is a set of notes used for quick reference. In this Oracle Cheat Sheet, I will show you all basic to advanced Oracle SQL commands with examples.

Basic Commands

To create a database, run the following command:

CREATE DATABASE test
DATAFILE 'test_system' SIZE 10M
LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K,
GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K;

Where:

• test is the name of the database you want to create.
• test_system is the tablespace of the new database which is 10 MB.
• test_log1a and test_log1b are the redo log groups and each contains 500 KB members.

If you want to delete the database named test.

First, connect to the database with the following command:

export ORACLE_SID=test
sqlplus "/ as sysdba"

Next, shut down the database with the following command:

SQL> shutdown immediate;

Next, start the database in exclusive mode:

SQL> startup mount exclusive restrict;

Finally, drop the database with the following command:

SQL> drop database;

To check the installed version of Oracle, run the command below:

SQL> SELECT * FROM V$VERSION;

To view the database name, run the following command:

SQL> SELECT * FROM GLOBAL_NAME;

To view NLS parameters, run the following command:

SQL> SELECT * FROM V$NLS_PARAMETERS;

To check the size of the database including, its Data files, Temporary files, Redo logs and Control files, run the following command:

SELECT ROUND(
SUM(Q1."Data Files" +
Q2."Temp Files" +
Q3."Redo Logs" +
Q4."Control Files"
)/1024/1024/1024,  2)
AS "Total Size (GB)"
FROM
(SELECT SUM(bytes) "Data Files" from DBA_DATA_FILES) Q1,
(SELECT SUM(bytes) "Temp Files" from DBA_TEMP_FILES) Q2,
(SELECT SUM(bytes) "Redo Logs" from V_$LOG) Q3,
(SELECT SUM(BLOCK_SIZE * FILE_SIZE_BLKS)"Control Files" FROM V$CONTROLFILE) Q4;

Create and Manage User

Create a new user named user1, run the following command:

SQL> CREATE USER user1 IDENTIFIED BY password;

To change the password of the user, run the following command:

SQL> ALTER USER user1 IDENTIFIED BY newpassword;

To list all users and profile, run the following command:

SQL> SELECT USERNAME, ACCOUNT_STATUS, PROFILE FROM DBA_USERS;

To list all roles, run the following command:

SQL> SELECT * FROM DBA_ROLES;

To create a user profile, run the following command:

SQL> CREATE PROFILE MY_PROFILE LIMIT;

To list all user profiles, run the following command:

SQL> SELECT * FROM DBA_PROFILES;

Set a password expiry to 30 days, run the following command:

SQL> ALTER PROFILE MY_NEW_PROFILE LIMIT PASSWORD_LIFE_TIME 30;

To set a password to never expire, run the following command:

SQL> ALTER PROFILE MY_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;

To view all privileges granted to a user on other users table, run the following command:

SQL> SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='USERNAME';

Create and Manage Table

To create a table named student, run the following command:

CREATE TABLE student
(
id integer not null,
name varchar2(20),
CONSTRAINT student_id_constraint UNIQUE (id)
);

To delete a table, run the following command:

DROP TABLE student;

To add an extra column name Jayesh to student table, run the following command:

ALTER TABLE student
ADD Jayesh varchar(255);

To delete a column name Jayesh in a table, run the following command:

ALTER TABLE student
DROP COLUMN Jayesh;

To modify the data type of a column in a table, run the following command:

ALTER TABLE student
MODIFY Jayesh varchar2(255);

To add a constraint on a table student, run the following command:

ALTER TABLE student ADD constraint;

To drop a constraint from a table student, run the following command:

ALTER TABLE student DROP constraint;

Rename a table from student to teacher, run the following command:

ALTER TABLE student RENAME TO teacher;

Rename a column column1 to column3. run the following command:

ALTER TABLE student RENAME column1 TO column3;

Remove all data in a table student, run the following command:

TRUNCATE TABLE student;

To query all rows and columns from a table student, run the following command:

SELECT * FROM student;

To query data in columns column1, column2 from a table student, run the following command:

SELECT column1, column2 FROM student;

To query data in columns column1, column2 from a table student and sort the result in ascending or descending order, run the following command:

SELECT column1, column2 FROM student
ORDER BY column1 ASC [DESC];

To query data in columns column1, column2 from multiple tables, run the following command:

SELECT column1, column2
FROM table1
INNER JOIN table2 ON condition;

Create and Manage Index and View

Index in oracle is used to retrieves records with greater efficiency. By default, Oracle creates B-tree indexes.

To create an index named student_idx on a table named student, run the following command:

CREATE INDEX student_idx
ON student (student_name);

To create an index with two fields, run the following command:

CREATE INDEX student_idx
ON student (student_name, age);

To rename an index from student_idx to teacher_idx, run the following command:

ALTER INDEX student_idx
RENAME TO teacher_idx;

To collect statistics on an index, run the following command;

ALTER INDEX student_idx
REBUILD COMPUTE STATISTICS;

To delete an index, run the following command:

DROP INDEX student_idx;

Create a view with column column1 and column2, run the following command:

CREATE VIEW v(column1,column12)
AS
SELECT column1, column2
FROM student;

Create a view with check option, run the following command:

CREATE VIEW v(column1,column12)
AS
SELECT column1, column2
FROM student;
WITH [CASCADED | LOCAL] CHECK OPTION;

Create a temporary view, run the following command:

CREATE TEMPORARY VIEW v
AS
SELECT column1, column2
FROM student;

To delete a view, run the following command;

DROP VIEW view-name;

Advanced Commands

To retrieve the current date as timestamp, run the following command:

CURRENT_TIMESTAMP

To retrieve the current date as date, run the following command:

SYSDATE

List all content in recycle bin, run the following command;

SELECT * FROM RECYCLEBIN;

Remove all contents from recycle bin, run the following command:

PURGE RECYCLEBIN;

List all tables in the current schema, run the following command:

SELECT * FROM user_tables;

Show current database sessions, run the following command:

SELECT * FROM v$session

Show current processes, run the following command:

SELECT * FROM v$process

Show current RAM usage, run the following command:

SELECT * FROM v$sga

Create a backup table from the student table, run the following command:

CREATE TABLE student_bak AS
SELECT * FROM student;

Restore a backup table into the original table, run the following command:

INSERT INTO student
SELECT * FROM student_bak;

Show timezone of the current database, run the following command:

SELECT DBTIMEZONE FROM DUAL;

PostgreSQL Cheat Sheet

In this section, we will show you basic and advanced PostgreSQL commands with examples.

Create and Manage Database

To create a new database, run the following command:

CREATE DATABASE dbname;

To delete a database, run the following command:

DROP DATABASE dbname;

To list all databases, run the following command:

\l

To connect to the database, run the following command:

\c dbname;

To rename a database, run the following command:

ALTER DATABASE dbname RENAME TO newdbname;

Create and Manage User and Roles

To create a new user, run the following command:

CREATE ROLE username WITH LOGIN PASSWORD 'password';

To assign a user to the database, run the following command:

CREATE DATABASE dbname WITH OWNER username;

To grant a user the ability to create a new database, run the following command:

ALTER USER username CREATEDB;

To list all users, run the following command:

\du

To create a role with an existing username, run the following command:

CREATE ROLE username;

Create and Manage Table

To create a new table, run the following command:

CREATE TABLE tablename(
pk SERIAL PRIMARY KEY,
c1 type(size) NOT NULL,
c2 type(size) NULL,
);

To delete a table, run the following command:

DROP TABLE tablename CASCADE;

To rename a table, run the following command:

ALTER TABLE tablename RENAME TO newtablename;

To add a new column to a table, run the following command:

ALTER TABLE tablename ADD COLUMN columnname TYPE;

To delete a column from a table, run the following command:

ALTER TABLE tablename DROP COLUMN columnname;

To rename a column, run the following command:

ALTER TABLE tablename RENAME columnname TO newcolumnname;

To list all tables in the database, run the following command:

\dt

To describe a table, run the following command:

\d

To querying all data from tables, run the following command:

SELECT * FROM tablename;

To query data from a specified column, run the following command:

SELECT column_list
FROM table;

Return the number of rows of a table, run the following command:

SELECT COUNT (*)
FROM tablename;

To sort rows in ascending or descending order, run the following command:

SELECT select_list
FROM table
ORDER BY column ASC [DESC], column2 ASC [DESC],...;

To delete all row of a table, run the following command:

DELETE FROM tablename;

To delete specific rows based on a condition, run the following command:

DELETE FROM tablename
WHERE condition;

Create and Manage View and Index

To create a view, run the following command:

CREATE OR REPLACE viewname AS
query;

To delete a view, run the following command:

DROP VIEW [ IF EXISTS ] viewname;

To rename a view, run the following command:

ALTER VIEW viewname RENAME TO newviewname;

To list all views, run the following command:

\dv

To create a recursive view, run the following command:

CREATE RECURSIVE VIEW viewname(column_list) AS
SELECT column_list;

To create a materialized view, run the following command:

CREATE MATERIALIZED VIEW viewname
AS
query
WITH [NO] DATA;

To drop a materialized view, run the following command:

DROP MATERIALIZED VIEW viewname;

To create a new index on the specified table, run the following command:

CREATE [UNIQUE] INDEX indexname
ON tablename (column,...);

To delete an index, run the following command:

DROP INDEX indexname;

Backup and Restore Database and Table

To back up a single database, run the following command:

pg_dump -d dbname -f dbname_backup.sql

To back up all databases, run the following command:

pg_dumpall -f alldb_backup.sql

To restore a single database, run the following command:

su - postgres
psql -d dbname -f dbname_backup.sql

To restore all databases, run the following command:

su - postgres
psql -f alldb_backup.sql

To backup a table from a database, run the following command:

pg_dump -d dbname -t tablename -f tablename_backup.sql

To restore a table from the backup, run the following command:

psql -d dbname -f tablename_backup.sql

Conclusion

In the above guide, you learned basic and advanced SQL and PostgreSQL commands with examples. I hope this will help you in your day-to-day database operations.

SQL Cheat Sheet FAQs