Creating Tables and Views in AWS RDS PostgreSQL

#PostgreSQL Cheat Sheet for AWS RDS

#1. Creating a Table
CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    column3 datatype,
    ...
);

#Example:
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary NUMERIC(10, 2)
);

#2. Creating a View
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

#Example:
CREATE VIEW employee_salaries AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

#3. Inserting Data into a Table
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

#Example:
INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('John', 'Doe', '[email protected]', '2023-01-15', 60000);

#4. Updating Data in a Table
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

#Example:
UPDATE employees
SET salary = 65000
WHERE employee_id = 1;

#5. Deleting Data from a Table
DELETE FROM table_name
WHERE condition;

#Example:
DELETE FROM employees
WHERE employee_id = 1;

#6. Dropping a Table
DROP TABLE table_name;

#Example:
DROP TABLE employees;

#7. Dropping a View
DROP VIEW view_name;

#Example:
DROP VIEW employee_salaries;

#8. Selecting Data from a Table
SELECT column1, column2, ...
FROM table_name
WHERE condition;

#Example:
SELECT first_name, last_name, email
FROM employees
WHERE hire_date > '2022-01-01';

#Additional PostgreSQL Cheat Sheet for AWS RDS

#9. Creating an Index
CREATE INDEX index_name
ON table_name (column1, column2, ...);

#Example:
CREATE INDEX idx_employee_last_name
ON employees (last_name);

#10. Adding a Column to an Existing Table
ALTER TABLE table_name
ADD COLUMN column_name datatype;

#Example:
ALTER TABLE employees
ADD COLUMN department VARCHAR(50);

#11. Modifying a Column in an Existing Table
ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_datatype;

#Example:
ALTER TABLE employees
ALTER COLUMN salary TYPE NUMERIC(12, 2);

#12. Renaming a Column in an Existing Table
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

#Example:
ALTER TABLE employees
RENAME COLUMN hire_date TO start_date;

#13. Renaming a Table
ALTER TABLE old_table_name
RENAME TO new_table_name;

#Example:
ALTER TABLE employees
RENAME TO staff;

#14. Adding a Primary Key Constraint
ALTER TABLE table_name
ADD PRIMARY KEY (column1, column2, ...);

#Example:
ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

#15. Adding a Foreign Key Constraint
ALTER TABLE table_name
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES other_table (column_name);

#Example:
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments (department_id);

#16. Creating a Schema
CREATE SCHEMA schema_name;

#Example:
CREATE SCHEMA hr;

#17. Setting the Search Path
SET search_path TO schema_name;

#Example:
SET search_path TO hr;

#18. Granting Privileges
GRANT privilege_type ON object_type object_name TO user_name;

#Example:
GRANT SELECT, INSERT ON employees TO hr_user;

#19. Revoking Privileges
REVOKE privilege_type ON object_type object_name FROM user_name;

#Example:
REVOKE INSERT ON employees FROM hr_user;

#20. Backing Up a Database
pg_dump -h hostname -U username -d database_name -F c -b -v -f backup_file

#Example:
pg_dump -h mydbinstance.123456789012.us-west-2.rds.amazonaws.com -U myuser -d mydatabase -F c -b -v -f mydatabase_backup

#21. Restoring a Database
pg_restore -h hostname -U username -d database_name -v backup_file

#Example:
pg_restore -h mydbinstance.123456789012.us-west-2.rds.amazonaws.com -U myuser -d mydatabase -v mydatabase_backup

#Advanced PostgreSQL Cheat Sheet for AWS RDS

#22. Creating a Unique Constraint
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

#Example:
ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

#23. Creating a Check Constraint
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

#Example:
ALTER TABLE employees
ADD CONSTRAINT check_salary CHECK (salary > 0);

#24. Creating a Composite Primary Key
ALTER TABLE table_name
ADD PRIMARY KEY (column1, column2, ...);

#Example:
CREATE TABLE project_assignments (
    employee_id INT,
    project_id INT,
    assignment_date DATE,
    PRIMARY KEY (employee_id, project_id)
);

#25. Using Common Table Expressions (CTEs)
WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name
WHERE condition;

#Example:
WITH recent_hires AS (
    SELECT first_name, last_name, hire_date
    FROM employees
    WHERE hire_date > '2023-01-01'
)
SELECT first_name, last_name
FROM recent_hires
WHERE last_name LIKE 'S%';

#26. Using Window Functions
SELECT column1, column2, 
       window_function() OVER (PARTITION BY column3 ORDER BY column4) AS alias
FROM table_name;

#Example:
SELECT employee_id, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

#27. Creating a Trigger
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();

#Example:
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

#28. Creating a Function
CREATE FUNCTION function_name(parameters)
RETURNS return_type AS 
$$

BEGIN
    -- function body
END;

$$
 LANGUAGE plpgsql;
    
#Example:
CREATE FUNCTION update_modified_column()
RETURNS TRIGGER AS 
$$

BEGIN
    NEW.modified = NOW();
    RETURN NEW;
END;

$$
 LANGUAGE plpgsql;
    
#29. Creating a Stored Procedure
CREATE PROCEDURE procedure_name(parameters)
LANGUAGE plpgsql
AS 
$$

BEGIN
    -- procedure body
END;

$$
;

#Example:
CREATE PROCEDURE raise_salary(emp_id INT, increase NUMERIC)
LANGUAGE plpgsql
AS 
$$

BEGIN
    UPDATE employees
    SET salary = salary + increase
    WHERE employee_id = emp_id;
END;

$$
;

#30. Using JSON Functions
SELECT column1->>'key' AS alias
FROM table_name
WHERE column1 @> '{"key": "value"}';

#Example:
SELECT data->>'name' AS name
FROM json_table
WHERE data @> '{"active": true}';

#31. Using Array Functions
SELECT column1[array_position] AS alias
FROM table_name
WHERE column1 @> ARRAY[value];

#Example:
SELECT skills[1] AS first_skill
FROM employees
WHERE skills @> ARRAY['Python'];

#32. Using Full-Text Search
SELECT column1
FROM table_name
WHERE to_tsvector(column1) @@ to_tsquery('search_term');

#Example:
SELECT title
FROM articles
WHERE to_tsvector(title) @@ to_tsquery('PostgreSQL');

#33. Using Recursive CTEs
WITH RECURSIVE cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
    UNION ALL
    SELECT column1, column2, ...
    FROM table_name, cte_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name;

#Example:
WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id, first_name, last_name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.first_name, e.last_name
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

#Advanced PostgreSQL Cheat Sheet for AWS RDS (Continued)

#34. Using Lateral Joins
SELECT column1, column2, ...
FROM table1
LEFT JOIN LATERAL (
    SELECT column3, column4, ...
    FROM table2
    WHERE table2.column = table1.column
) alias ON true;

#Example:
SELECT e.employee_id, e.first_name, p.project_name
FROM employees e
LEFT JOIN LATERAL (
    SELECT project_name
    FROM projects p
    WHERE p.employee_id = e.employee_id
    LIMIT 1
) p ON true;

#35. Using the CASE Statement
SELECT column1,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE result3
       END AS alias
FROM table_name;

#Example:
SELECT employee_id, salary,
       CASE
           WHEN salary < 50000 THEN 'Low'
           WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
           ELSE 'High'
       END AS salary_range
FROM employees;

#36. Using the COALESCE Function
SELECT COALESCE(column1, column2, ...) AS alias
FROM table_name;

#Example:
SELECT employee_id, COALESCE(phone, 'N/A') AS contact_number
FROM employees;

#37. Using the NULLIF Function
SELECT NULLIF(column1, column2) AS alias
FROM table_name;

#Example:
SELECT employee_id, NULLIF(bonus, 0) AS adjusted_bonus
FROM employees;

#38. Using the GREATEST and LEAST Functions
SELECT GREATEST(column1, column2, ...) AS alias,
       LEAST(column1, column2, ...) AS alias
FROM table_name;

#Example:
SELECT employee_id, GREATEST(salary, bonus) AS max_compensation,
       LEAST(salary, bonus) AS min_compensation
FROM employees;

#39. Using the DISTINCT ON Clause
SELECT DISTINCT ON (column1) column1, column2, ...
FROM table_name
ORDER BY column1, column2;

#Example:
SELECT DISTINCT ON (department) department, employee_id, salary
FROM employees
ORDER BY department, salary DESC;

#40. Using the GROUP BY Clause
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

#Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

#41. Using the HAVING Clause
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > value;

#Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

#42. Using the UNION Operator
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
UNION
SELECT first_name, last_name
FROM contractors;

#43. Using the EXCEPT Operator
SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
EXCEPT
SELECT first_name, last_name
FROM former_employees;

#44. Using the INTERSECT Operator
SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
INTERSECT
SELECT first_name, last_name
FROM project_team;

#45. Using the ARRAY_AGG Function
SELECT column1, ARRAY_AGG(column2) AS alias
FROM table_name
GROUP BY column1;

#Example:
SELECT department, ARRAY_AGG(employee_id) AS employee_ids
FROM employees
GROUP BY department;

#46. Using the STRING_AGG Function
SELECT column1, STRING_AGG(column2, delimiter) AS alias
FROM table_name
GROUP BY column1;

#Example:
SELECT department, STRING_AGG(first_name, ', ') AS employee_names
FROM employees
GROUP BY department;

#47. Using the JSONB Data Type
CREATE TABLE table_name (
    column1 JSONB
);
Example:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_details JSONB
);

#48. Querying JSONB Data
SELECT column1->>'key' AS alias
FROM table_name
WHERE column1 @> '{"key": "value"}';

#Example:
SELECT order_details->>'product_name' AS product_name
FROM orders
WHERE order_details @> '{"status": "shipped"}';

#49. Using the HSTORE Data Type
CREATE EXTENSION hstore;
CREATE TABLE table_name (
    column1 HSTORE
);

#Example:
CREATE EXTENSION hstore;
CREATE TABLE product_attributes (
    product_id SERIAL PRIMARY KEY,
    attributes HSTORE
);

#50. Querying HSTORE Data
SELECT column1->'key' AS alias
FROM table_name
WHERE column1 ? 'key';

#Example:
SELECT attributes->'color' AS color
FROM product_attributes
WHERE attributes ? 'color';

#Advanced PostgreSQL Cheat Sheet for AWS RDS (Continued)

#51. Using the UPSERT (INSERT ON CONFLICT) Statement
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target) 
DO UPDATE SET column1 = EXCLUDED.column1, column2 = EXCLUDED.column2, ...;

#Example:
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', '[email protected]')
ON CONFLICT (employee_id) 
DO UPDATE SET email = EXCLUDED.email;

#52. Using the PARTITION BY Clause
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
) PARTITION BY partition_type (column);

#Example:
CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

#53. Creating Partitions
CREATE TABLE partition_name PARTITION OF table_name
FOR VALUES partition_specification;

#Example:
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

#54. Using the CUBE and ROLLUP Clauses
SELECT column1, column2, ..., aggregate_function(column3)
FROM table_name
GROUP BY CUBE (column1, column2, ...);

#Example:
SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY ROLLUP (department, job_title);

#55. Using the GROUPING SETS Clause
SELECT column1, column2, ..., aggregate_function(column3)
FROM table_name
GROUP BY GROUPING SETS ((column1), (column2), ...);

#Example:
SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((department), (job_title));

#56. Using the FILTER Clause with Aggregate Functions
SELECT column1, aggregate_function(column2) FILTER (WHERE condition) AS alias
FROM table_name
GROUP BY column1;

#Example:
SELECT department, COUNT(*) FILTER (WHERE salary > 50000) AS high_earners
FROM employees
GROUP BY department;

#57. Using the GENERATE_SERIES Function
SELECT generate_series(start, stop, step) AS alias;

#Example:
SELECT generate_series(1, 10, 1) AS number;

#58. Using the LAG and LEAD Functions
SELECT column1, column2,
       LAG(column2, offset, default) OVER (PARTITION BY column3 ORDER BY column4) AS alias,
       LEAD(column2, offset, default) OVER (PARTITION BY column3 ORDER BY column4) AS alias
FROM table_name;

#Example:
SELECT employee_id, salary,
       LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY hire_date) AS previous_salary,
       LEAD(salary, 1, 0) OVER (PARTITION BY department ORDER BY hire_date) AS next_salary
FROM employees;

#59. Using the CROSSTAB Function (requires tablefunc extension)
CREATE EXTENSION tablefunc;
SELECT * FROM crosstab(
    'SELECT rowid, attribute, value FROM table_name ORDER BY 1,2',
    'SELECT DISTINCT attribute FROM table_name ORDER BY 1'
) AS crosstab_result(rowid INT, attribute1 datatype, attribute2 datatype, ...);

#Example:
CREATE EXTENSION tablefunc;
SELECT * FROM crosstab(
    'SELECT employee_id, month, salary FROM employee_salaries ORDER BY 1,2',
    'SELECT DISTINCT month FROM employee_salaries ORDER BY 1'
) AS ct(employee_id INT, jan NUMERIC, feb NUMERIC, mar NUMERIC, ...);

#60. Using the ARRAY Functions
SELECT array_agg(column) AS alias
FROM table_name
GROUP BY column;

#Example:
SELECT department, array_agg(employee_id) AS employee_ids
FROM employees
GROUP BY department;

#61. Using the JSONB Functions
SELECT jsonb_set(column, '{path}', 'value'::jsonb) AS alias
FROM table_name;

#Example:
SELECT jsonb_set(order_details, '{status}', '"shipped"'::jsonb) AS updated_order
FROM orders;

#62. Using the HSTORE Functions
SELECT hstore(column) AS alias
FROM table_name;

#Example:
SELECT hstore('key', 'value') AS store
FROM table_name;

#63. Using the TO_CHAR Function
SELECT TO_CHAR(column, 'format') AS alias
FROM table_name;

#Example:
SELECT TO_CHAR(salary, 'FM999,999,999.00') AS formatted_salary
FROM employees;

#64. Using the TO_DATE Function
SELECT TO_DATE(column, 'format') AS alias
FROM table_name;

#Example:
SELECT TO_DATE('2023-01-15', 'YYYY-MM-DD') AS formatted_date;

#65. Using the TO_TIMESTAMP Function
SELECT TO_TIMESTAMP(column, 'format') AS alias
FROM table_name;

#Example:
SELECT TO_TIMESTAMP('2023-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS') AS formatted_timestamp;

#66. Using the EXTRACT Function
SELECT EXTRACT(field FROM column) AS alias
FROM table_name;

#Example:
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees;

#67. Using the DATE_TRUNC Function
SELECT DATE_TRUNC('field', column) AS alias
FROM table_name;

#Example:
SELECT DATE_TRUNC('month', hire_date) AS hire_month
FROM employees;

#68. Using the AGE Function
SELECT AGE(column) AS alias
FROM table_name;

#Example:
SELECT AGE(hire_date) AS tenure
FROM employees;

#69. Using the INTERVAL Data Type
SELECT column + INTERVAL 'value unit' AS alias
FROM table_name;

#Example:
SELECT hire_date + INTERVAL '1 year' AS next_anniversary
FROM employees;

#Advanced PostgreSQL Cheat Sheet for AWS RDS (Continued)

#70. Using the SET Operations (continued)
SELECT column1, column2, ...
FROM table1
UNION [ALL]
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
UNION
SELECT first_name, last_name
FROM contractors;

#71. Using the EXCEPT Operator
SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
EXCEPT
SELECT first_name, last_name
FROM former_employees;

#72. Using the INTERSECT Operator
SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
INTERSECT
SELECT first_name, last_name
FROM project_team;

#73. Using the ARRAY Functions
SELECT array_agg(column) AS alias
FROM table_name
GROUP BY column;

#Example:
SELECT department, array_agg(employee_id) AS employee_ids
FROM employees
GROUP BY department;

#74. Using the JSONB Functions
SELECT jsonb_set(column, '{path}', 'value'::jsonb) AS alias
FROM table_name;

#Example:
SELECT jsonb_set(order_details, '{status}', '"shipped"'::jsonb) AS updated_order
FROM orders;

#75. Using the HSTORE Functions
SELECT hstore(column) AS alias
FROM table_name;

#Example:
SELECT hstore('key', 'value') AS store
FROM table_name;

#76. Using the TO_CHAR Function
SELECT TO_CHAR(column, 'format') AS alias
FROM table_name;

#Example:
SELECT TO_CHAR(salary, 'FM999,999,999.00') AS formatted_salary
FROM employees;

#77. Using the TO_DATE Function
SELECT TO_DATE(column, 'format') AS alias
FROM table_name;

#Example:
SELECT TO_DATE('2023-01-15', 'YYYY-MM-DD') AS formatted_date;

#78. Using the TO_TIMESTAMP Function
SELECT TO_TIMESTAMP(column, 'format') AS alias
FROM table_name;

#Example:
SELECT TO_TIMESTAMP('2023-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS') AS formatted_timestamp;

#79. Using the EXTRACT Function
SELECT EXTRACT(field FROM column) AS alias
FROM table_name;

#Example:
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees;

#80. Using the DATE_TRUNC Function
SELECT DATE_TRUNC('field', column) AS alias
FROM table_name;

#Example:
SELECT DATE_TRUNC('month', hire_date) AS hire_month
FROM employees;

#81. Using the AGE Function
SELECT AGE(column) AS alias
FROM table_name;

#Example:
SELECT AGE(hire_date) AS tenure
FROM employees;

#82. Using the INTERVAL Data Type
SELECT column + INTERVAL 'value unit' AS alias
FROM table_name;

#Example:
SELECT hire_date + INTERVAL '1 year' AS next_anniversary
FROM employees;

#83. Using the SET Operations
SELECT column1, column2, ...
FROM table1
UNION [ALL]
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
UNION
SELECT first_name, last_name
FROM contractors;

#84. Using the EXCEPT Operator
SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
EXCEPT
SELECT first_name, last_name
FROM former_employees;

#85. Using the INTERSECT Operator
SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
INTERSECT
SELECT first_name, last_name
FROM project_team;

#86. Using the ARRAY Functions
SELECT array_agg(column) AS alias
FROM table_name
GROUP BY column;

#Example:
SELECT department, array_agg(employee_id) AS employee_ids
FROM employees
GROUP BY department;

#87. Using the JSONB Functions
SELECT jsonb_set(column, '{path}', 'value'::jsonb) AS alias
FROM table_name;

#Example:
SELECT jsonb_set(order_details, '{status}', '"shipped"'::jsonb) AS updated_order
FROM orders;

#88. Using the HSTORE Functions
SELECT hstore(column) AS alias
FROM table_name;

#Example:
SELECT hstore('key', 'value') AS store
FROM table_name;

#89. Using the TO_CHAR Function
SELECT TO_CHAR(column, 'format') AS alias
FROM table_name;

#Example:
SELECT TO_CHAR(salary, 'FM999,999,999.00') AS formatted_salary
FROM employees;

#90. Using the TO_DATE Function
SELECT TO_DATE(column, 'format') AS alias
FROM table_name;

#Example:
SELECT TO_DATE('2023-01-15', 'YYYY-MM-DD') AS formatted_date;

#91. Using the TO_TIMESTAMP Function
SELECT TO_TIMESTAMP(column, 'format') AS alias
FROM table_name;

#Example:
SELECT TO_TIMESTAMP('2023-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS') AS formatted_timestamp;

#92. Using the EXTRACT Function
SELECT EXTRACT(field FROM column) AS alias
FROM table_name;

#Example:
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees;

#93. Using the DATE_TRUNC Function
SELECT DATE_TRUNC('field', column) AS alias
FROM table_name;

#Example:
SELECT DATE_TRUNC('month', hire_date) AS hire_month
FROM employees;

#94. Using the AGE Function
SELECT AGE(column) AS alias
FROM table_name;

#Example:
SELECT AGE(hire_date) AS tenure
FROM employees;

#95. Using the INTERVAL Data Type
SELECT column + INTERVAL 'value unit' AS alias
FROM table_name;

#Example:
SELECT hire_date + INTERVAL '1 year' AS next_anniversary
FROM employees;

#96. Using the SET Operations
SELECT column1, column2, ...
FROM table1
UNION [ALL]
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
UNION
SELECT first_name, last_name
FROM contractors;

#97. Using the EXCEPT Operator
SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
EXCEPT
SELECT first_name, last_name
FROM former_employees;

#98. Using the INTERSECT Operator
SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

#Example:
SELECT first_name, last_name
FROM employees
INTERSECT
SELECT first_name, last_name
FROM project_team;

#99. Using the ARRAY Functions
SELECT array_agg(column) AS alias
FROM table_name
GROUP BY column;

#Example:
SELECT department, array_agg(employee_id) AS employee_ids
FROM employees
GROUP BY department;

#100. Using the JSONB Functions
SELECT jsonb_set(column, '{path}', 'value'::jsonb) AS alias
FROM table_name;

#Example:
SELECT jsonb_set(order_details, '{status}', '"shipped"'::jsonb) AS updated_order
FROM orders;