1. Create Table
Format :
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
.....
columnN datatype,
PRIMARY KEY(one or more columns )
);
Contoh :
Create table department
(
dept_no int constraint dept_details_pk primary key
dept_name text NOT NULL,
Location varchar(15),
);
2. Drop Table
DROP TABLE table_name;
3. Show Table
3.1. SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
3.2. select * from information_schema.tables where table_schema='public';
3.3. SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'myschema' AND schemaname != 'information_schema';
4. Describe Table
SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'customer';
5. ALTER table
Format :
ALTER TABLE table_name action;
5.1. ADD Column
ALTER TABLE Station ADD COLUMN Latitude REAL;
5.2. Drop column
ALTER TABLE Station DROP COLUMN Latitude;
5.3. Rename Column
ALTER TABLE Station RENAME COLUMN St_Name TO Name;
5.4. Alter Column
ALTER TABLE Station1
ALTER COLUMN St_City TYPE Varchar(30),
ALTER COLUMN St_State TYPE Varchar(20);
5.5. Add NOT NULL constraint
ALTER TABLE Station1 ALTER COLUMN St_City SET NOT NULL;
5.6. Remove NOT NULL constraint
ALTER TABLE Station1 ALTER COLUMN St_City DROP NOT NULL;
5.7. Add PRIMARY KEY
ALTER TABLE Station1 ADD PRIMARY KEY (St_id);
6. Change Column Type
6.1. Single
ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type;
6.2. Multi
ALTER TABLE table_name
ALTER COLUMN column_name_1 [SET DATA] TYPE new_data_type,
ALTER COLUMN column_name_2 [SET DATA] TYPE new_data_type;
7. Truncate table
Format :
TRUNCATE TABLE table_name; [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
7.1. truncate table student_information;
7.2. TRUNCATE TABLE table_name RESTART IDENTITY;
7.3. TRUNCATE TABLE student_information RESTART IDENTITY;
7.4. TRUNCATE TABLE table_name1, table_name2 ...;
7.5. Delete all Record
TRUNCATE TABLE table_name CASCADE;
8. temporary table
CREATE TEMPORARY TABLE temp_table_name(...);
9. Column Alias
SELECT column_name AS alias_name FROM table_name Conditions...;
10. Sequence
Format :
CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name
[ AS { SMALLINT | INT | BIGINT } ]
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ]
[ CACHE cache ]
[ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]
10.1. CREATE SEQUENCE jtpsequence INCREMENT 3 START 20;
10.2. CREATE SEQUENCE Purchase_module_id START 5
INCREMENT 5 MINVALUE 5 OWNED BY Purchase_details.Module_id;
11. Identity Column
column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]
12. Upsert
12.1. INSERT INTO table_name(column_list) VALUES(value_list) ON CONFLICT target action;
12.2. INSERT INTO officers (officers_name, officers_addrerss ) VALUES('Olivia','Florida')
ON CONFLICT ON CONSTRAINT officers_name
DO NOTHING;
12.3. INSERT INTO officers VALUES(102,'Olivia','olivia22@gmail.com','Florida')
ON CONFLICT (officers_name) DO NOTHING;
12.4. INSERT INTO officers VALUES(102,'Olivia','olivia22@gmail.com','Florida')
ON CONFLICT (officers_name) DO UPDATE SET officers_address =
EXCLUDED.officers_address || ',' || officers.officers_address;
13. Subquery
13.1. Kondisi Subquery
SELECT c1.car_name, c1.car_model,
(SELECT MIN (car_id)
FROM car c2
WHERE c1.car_id = c2.car_id) Subquery1
FROM car c1;
13.2. Dalam Kategori
SELECT course.course_name, Subquery2.course_category_id
FROM course,
(SELECT course_categories.course_category_id, course_categories.course_category,
COUNT (course_category_id) AS total
FROM course_categories
GROUP BY course_categories.course_category_id, course_categories.course_category) Subquery2
WHERE Subquery2.course_category_id = course.course_id;
13.3. Kondisi Where
SELECT c.client_id, c.client_name, c.client_profession
FROM client c
WHERE c.client_id IN
(SELECT cd.client_id
FROM client_details cd
WHERE cd.client_id < 6
AND c.client_name LIKE 'M%');
