PostgreSQL Cheatseet

psql.sql

-- Install: https://www.2ndquadrant.com/en/blog/pginstaller-install-postgresql/
-- Env postgresn in windows: https://sqlbackupandftp.com/blog/setting-windows-path-for-postgres-tools

-- Nyalain service
-- Run (Win + R) -> services.msc
-- Cari PostgreSQL -> klik kanan -> start

-- Menjalankan perintah SQL di CMD
-- cd C:\Program Files\2ndQuadrant\PostgreSQL\12\bin
-- psql.exe -U postgres
-- masukan password, misal: admin

-- Create User
CREATE USER nauval WITH PASSWORD '123456' CREATEDB;
-- Get list users
\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 nauval    | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


-- Drop user
DROP USER [USERNAME];

-- Buat database
CREATE DATABASE ecommerce;

-- Menampilkan databases
\l

-- Memlilih database
\c ecommerce;

-- DATA TYPES

-- smallint	2 bytes	small-range integer	-32768 to +32767
-- integer	4 bytes	typical choice for integer	-2147483648 to +2147483647
-- bigint	8 bytes	large-range integer	-9223372036854775808 to 9223372036854775807
-- decimal	variable	user-specified precision,exact	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
-- numeric	variable	user-specified precision,exact	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
-- real	4 bytes	variable-precision,inexact	6 decimal digits precision
-- double precision	8 bytes	variable-precision,inexact	15 decimal digits precision
-- smallserial	2 bytes	small autoincrementing integer	1 to 32767
-- serial	4 bytes	autoincrementing integer	1 to 2147483647
-- bigserial	8 bytes	large autoincrementing integer	1 to 9223372036854775807

-- boolean	1 byte	state of true or false

-- character varying(n), varchar(n)    variable-length with limit
-- character(n), char(n)   fixed-length, blank padded
-- text    variable unlimited length

-- Membuat table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age INTEGER,
  isActive BOOLEAN DEFAULT true
);

CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  userId INTEGER,
  itemName VARCHAR(100) NOT NULL,
  CONSTRAINT fk_user FOREIGN KEY (userId) REFERENCES users(id)
);

-- Menampilkan tables
\d

-- Menampilkan struktur table
\d users

-- Insert value ke table
INSERT INTO users (name, age, isActive) VALUES 
  ('Nauval', 22, false),
  ('Sasuke', 23, true),
  ('Luffy', 24, false),
  ('Thorfin', 25, true),
  ('Eren', 26, false),
  ('Nate', 27, false);

INSERT INTO items (userId, itemName) VALUES 
  (2,'Mouse'),
  (3,'Keyboard'),
  (4,'Earphone');

-- Menampilkan semua data users
SELECT * FROM users;


-- Update data users
UPDATE users SET name = 'Naruto' WHERE id = 1;

-- Delete data users
DELETE FROM users WHERE id = 1;

-- Operators

-- +	Addition - Adds values on either side of the operator	a + b will give 5
-- -	Subtraction - Subtracts right hand operand from left hand operand	a - b will give -1
-- *	Multiplication - Multiplies values on either side of the operator	a * b will give 6
-- /	Division - Divides left hand operand by right hand operand	b / a will give 1
-- %	Modulus - Divides left hand operand by right hand operand and returns remainder	b % a will give 1
-- ^	Exponentiation - This gives the exponent value of the right hand operand	a ^ b will give 8
-- |/	square root	|/ 25.0 will give 5
-- ||/	Cube root	||/ 27.0 will give 3
-- !	factorial	5 ! will give 120
-- !!	factorial (prefix operator)	!! 5 will give 120

-- =	Checks if the values of two operands are equal or not, if yes then condition becomes true.	(a = b) is not true.
-- !=	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(a != b) is true.
-- <>	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(a <> b) is true.
-- >	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.	(a > b) is not true.
-- <	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.	(a < b) is true.
-- >=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.	(a >= b) is not true.
-- <=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.	(a <= b) is true.

-- AND The AND operator allows the existence of multiple conditions in a PostgresSQL statement's WHERE clause.
-- OR The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negate operator.
-- NOT The OR operator is used to combine multiple conditions in a PostgresSQL statement's WHERE clause.

SELECT * FROM users WHERE name LIKE '%Na%' AND age < 25 AND isActive != true;

-- Expression
-- There are several built-in functions like avg(), sum(), count() to perform what is known as aggregate data calculations against a table or a specific table column.
SELECT COUNT(*) AS records FROM users;

-- Date expression
SELECT CURRENT_TIMESTAMP;


-- GROUP BY
SELECT name, sum(age) FROM users GROUP BY name;

-- HAVING
-- SELECT column1, column2
-- FROM table1, table2
-- WHERE [ conditions ]
-- GROUP BY column1, column2
-- HAVING [ conditions ]
-- ORDER BY column1, column2
SELECT name FROM users GROUP BY name HAVING sum(age) > 50;

-- JOIN
-- INNER JOIN
ecommerce=# SELECT name,itemName FROM users INNER JOIN items ON users.id = items.userId;
  name  | itemname 
--------+----------
 Shidqi | Mouse
 Nauval | Keyboard
 Sasuke | Earphone
(3 rows)

-- LEFT OUTER JOIN
ecommerce=# SELECT name,itemName FROM users LEFT OUTER JOIN items ON users.id = items.userId;
  name   | itemname 
---------+----------
 Shidqi  | Mouse
 Nauval  | Keyboard
 Sasuke  | Earphone
 Thorfin |
 Eren    |

(21 rows)

-- RIGHT OUTER JOIN
ecommerce=# SELECT name,itemName FROM users RIGHT OUTER JOIN items ON users.id = items.userId;
  name  | itemname 
--------+----------
 Shidqi | Mouse
 Nauval | Keyboard
 Sasuke | Earphone
(3 rows)

-- FULL OUTER JOIN
ecommerce=# SELECT name,itemName FROM users FULL OUTER JOIN items ON users.id = items.userId;
  name   | itemname 
---------+----------
 Shidqi  | Mouse
 Nauval  | Keyboard
 Sasuke  | Earphone
 Thorfin |
 Eren    |

(21 rows)