PostgreSQL Commands

Creating 100 PostgreSQL queries is extensive, but here's a collection of essential and commonly-used queries grouped by categories, including table management, CRUD operations, data filtering, joins, aggregations, transactions, and functions. These should cover most basics and intermediate skills.

Table Management

Create a Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

Add a Column

ALTER TABLE employees ADD COLUMN hire_date DATE;

Modify Column Data Type

ALTER TABLE employees ALTER COLUMN age TYPE SMALLINT;

Rename a Column

ALTER TABLE employees RENAME COLUMN department TO dept;

Drop a Column

ALTER TABLE employees DROP COLUMN hire_date;

Rename a Table
```
ALTER TABLE employees RENAME TO staff;
```
Drop a Table
```
DROP TABLE IF EXISTS employees;
```

Add a Unique Constraint

ALTER TABLE employees ADD CONSTRAINT unique_name UNIQUE (name);

Add a Foreign Key

ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept) REFERENCES departments(dept_name);

Create a New Schema
```
CREATE SCHEMA company_data;
```

Basic Insert, Update, Delete

Insert Data

INSERT INTO employees (name, age, dept, salary) VALUES ('Alice', 30, 'IT', 50000);

Insert Multiple Rows

INSERT INTO employees (name, age, dept, salary) VALUES
('Bob', 35, 'HR', 45000),
('Charlie', 28, 'Finance', 55000);

Update Data

UPDATE employees SET salary = 60000 WHERE name = 'Alice';

Delete Data

DELETE FROM employees WHERE name = 'Charlie';

Upsert Data (Insert on Conflict)

INSERT INTO employees (id, name, age, dept, salary)
VALUES (1, 'Alice', 30, 'IT', 50000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

Basic Select Queries

Select All Columns
```
SELECT * FROM employees;
```
Select Specific Columns
```
SELECT name, dept FROM employees;
```

Select with WHERE

SELECT * FROM employees WHERE dept = 'Finance';

Select with ORDER BY

SELECT * FROM employees ORDER BY salary DESC;

Select with LIMIT
```
SELECT * FROM employees LIMIT 10;
```
Select with OFFSET
```
SELECT * FROM employees OFFSET 5;
```
Select Distinct Values
```
SELECT DISTINCT dept FROM employees;
```

Select with LIKE

SELECT * FROM employees WHERE name LIKE 'A%';

Select with BETWEEN

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

Select with IN

SELECT * FROM employees WHERE dept IN ('IT', 'HR');

Select with IS NULL

SELECT * FROM employees WHERE dept IS NULL;

Select with NOT IN

SELECT * FROM employees WHERE dept NOT IN ('IT', 'HR');

Aggregation Queries

Count Rows

sql
Copy code
SELECT COUNT(*) FROM employees;

Average Salary

sql
Copy code
SELECT AVG(salary) FROM employees;

Sum of Salaries

sql
Copy code
SELECT SUM(salary) FROM employees;

Maximum Salary

sql
Copy code
SELECT MAX(salary) FROM employees;

Minimum Salary

sql
Copy code
SELECT MIN(salary) FROM employees;

Group By Department

sql
Copy code
SELECT dept, COUNT(*) FROM employees GROUP BY dept;

Group By with Aggregate Function

sql
Copy code
SELECT dept, AVG(salary) AS avg_salary FROM employees GROUP BY dept;

Group By with HAVING

sql
Copy code
SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING COUNT(*) > 5;

Joins

Inner Join

sql
Copy code
SELECT e.name, e.salary, d.manager
FROM employees e
INNER JOIN departments d ON e.dept = d.dept_name;

Left Join

sql
Copy code
SELECT e.name, d.manager
FROM employees e
LEFT JOIN departments d ON e.dept = d.dept_name;

Right Join

sql
Copy code
SELECT e.name, d.manager
FROM employees e
RIGHT JOIN departments d ON e.dept = d.dept_name;

Full Outer Join

sql
Copy code
SELECT e.name, d.manager
FROM employees e
FULL OUTER JOIN departments d ON e.dept = d.dept_name;

Self Join

sql
Copy code
SELECT e1.name, e2.name AS coworker
FROM employees e1
JOIN employees e2 ON e1.dept = e2.dept AND e1.id != e2.id;

Cross Join

sql
Copy code
SELECT e.name, p.project
FROM employees e
CROSS JOIN projects p;

Transactions

Begin Transaction
```
sql
Copy code
BEGIN;
```
Commit Transaction
```
sql
Copy code
COMMIT;
```
Rollback Transaction
```
sql
Copy code
ROLLBACK;
```
Savepoint in Transaction
```
sql
Copy code
SAVEPOINT my_savepoint;
```

Rollback to Savepoint

sql
Copy code
ROLLBACK TO SAVEPOINT my_savepoint;

Data Types and Casting

Cast to Integer
```
sql
Copy code
SELECT '123'::INTEGER;
```
Cast to Date
```
sql
Copy code
SELECT '2024-01-01'::DATE;
```

Extract Part of Date

sql
Copy code
SELECT EXTRACT(YEAR FROM CURRENT_DATE);

Convert Data Type

sql
Copy code
SELECT CAST(salary AS NUMERIC(12, 2)) FROM employees;

Subqueries

Subquery in SELECT

sql
Copy code
SELECT name, (SELECT AVG(salary) FROM employees) AS avg_salary FROM employees;

Subquery in WHERE

sql
Copy code
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Subquery in FROM

sql
Copy code
SELECT dept, avg_salary FROM (SELECT dept, AVG(salary) AS avg_salary FROM employees GROUP BY dept) AS dept_avg;

Correlated Subquery

sql
Copy code
SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

Window Functions

Row Number

sql
Copy code
SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;

Rank

sql
Copy code
SELECT name, RANK() OVER (ORDER BY salary DESC) FROM employees;

Dense Rank

sql
Copy code
SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;

Lead

sql
Copy code
SELECT name, salary, LEAD(salary) OVER (ORDER BY salary) AS next_salary FROM employees;

Lag

sql
Copy code
SELECT name, salary, LAG(salary) OVER (ORDER BY salary) AS prev_salary FROM employees;

Moving Average

sql
Copy code
SELECT name, AVG(salary) OVER (ORDER BY age ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM employees;

Conditional Expressions

CASE Statement

sql
Copy code
SELECT name, salary, CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;

COALESCE

sql
Copy code
SELECT name, COALESCE(dept, 'Not Assigned') FROM employees;

NULLIF

sql
Copy code
SELECT NULLIF(salary, 0) FROM employees;

Conditional Expressions (continued)

Using GREATEST Function

sql
Copy code
SELECT name, GREATEST(salary, 50000) AS adjusted_salary FROM employees;

Using LEAST Function

sql
Copy code
SELECT name, LEAST(salary, 100000) AS capped_salary FROM employees;

CASE with Multiple Conditions

sql
Copy code
SELECT name,
       CASE
           WHEN salary > 80000 THEN 'High'
           WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_category
FROM employees;

Advanced Subqueries

EXISTS Clause

sql
Copy code
SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.manager = e.name);

NOT EXISTS Clause

sql
Copy code
SELECT name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM projects p WHERE p.employee_id = e.id);

IN with Subquery

sql
Copy code
SELECT name FROM employees WHERE dept IN (SELECT dept_name FROM departments WHERE region = 'East');

Correlated Subquery with UPDATE

sql
Copy code
UPDATE employees e SET salary = salary * 1.1 WHERE salary < (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

Correlated Subquery in DELETE

sql
Copy code
DELETE FROM employees e WHERE e.salary < (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

Array Subquery

sql
Copy code
SELECT ARRAY(SELECT name FROM employees WHERE dept = 'Finance') AS finance_team;

Text Functions

Concatenate Strings

sql
Copy code
SELECT name || ' works in ' || dept AS description FROM employees;

Substring Extraction

sql
Copy code
SELECT SUBSTRING(name FROM 1 FOR 3) AS initials FROM employees;

Uppercase Conversion

sql
Copy code
SELECT UPPER(name) AS uppercase_name FROM employees;

Lowercase Conversion

sql
Copy code
SELECT LOWER(name) AS lowercase_name FROM employees;

Length of a String

sql
Copy code
SELECT name, LENGTH(name) AS name_length FROM employees;

Replace Substring

sql
Copy code
SELECT REPLACE(name, 'a', '@') AS modified_name FROM employees;

Trim Whitespace

sql
Copy code
SELECT TRIM(' ' FROM name) AS trimmed_name FROM employees;

Split String into Array

sql
Copy code
SELECT STRING_TO_ARRAY(name, ' ') FROM employees;

Position of Substring

sql
Copy code
SELECT POSITION('a' IN name) AS first_a_position FROM employees;

Date/Time Functions

Current Date
```
sql
Copy code
SELECT CURRENT_DATE;
```
Current Time
```
sql
Copy code
SELECT CURRENT_TIME;
```
Current Timestamp
```
sql
Copy code
SELECT CURRENT_TIMESTAMP;
```

Extract Year

sql
Copy code
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees;

Date Difference in Days

sql
Copy code
SELECT name, AGE(hire_date) AS days_since_hired FROM employees;

Add Interval to Date

sql
Copy code
SELECT name, hire_date + INTERVAL '1 year' AS next_anniversary FROM employees;

Truncate Date

sql
Copy code
SELECT DATE_TRUNC('month', hire_date) AS hire_month FROM employees;

Date Part

sql
Copy code
SELECT name, DATE_PART('day', hire_date) AS hire_day FROM employees;

Date Formatting

sql
Copy code
SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') AS formatted_hire_date FROM employees;

Array Functions

Creating an Array

sql
Copy code
SELECT ARRAY['Finance', 'IT', 'HR'] AS departments;

Array Length

sql
Copy code
SELECT ARRAY_LENGTH(ARRAY['Finance', 'IT', 'HR'], 1);

Access Array Element

sql
Copy code
SELECT (ARRAY['Finance', 'IT', 'HR'])[1] AS first_department;

Array Concatenation

sql
Copy code
SELECT ARRAY[1, 2, 3] || ARRAY[4, 5, 6] AS combined_array;

Unnest Array

sql
Copy code
SELECT unnest(ARRAY['Finance', 'IT', 'HR']) AS dept;

User-Defined Functions

Create a Simple Function

sql
Copy code
CREATE FUNCTION calculate_bonus(salary NUMERIC) RETURNS NUMERIC AS $$
BEGIN
    RETURN salary * 0.1;
END;
$$ LANGUAGE plpgsql;

Using the Function

sql
Copy code
SELECT name, calculate_bonus(salary) AS bonus FROM employees;

Create Function with Conditional Logic

sql
Copy code
CREATE FUNCTION get_performance_bonus(salary NUMERIC) RETURNS NUMERIC AS $$
BEGIN
    IF salary > 50000 THEN
        RETURN salary * 0.15;
    ELSE
        RETURN salary * 0.1;
    END IF;
END;
$$ LANGUAGE plpgsql;

Aggregate Function with Filter

sql
Copy code
SELECT AVG(salary) FILTER (WHERE dept = 'IT') AS avg_it_salary FROM employees;

Using CTE with Recursive Query

WITH RECURSIVE dept_hierarchy AS ( SELECT dept_id, dept_name, parent_id FROM departments WHERE parent_id IS NULL UNION ALL SELECT d.dept_id, d.dept_name, d.parent_id FROM departments d JOIN dept_hierarchy dh ON d.parent_id = dh.dept_id ) SELECT * FROM dept_hierarchy;   

This list covers a wide range of PostgreSQL functionality, from basic table management and data manipulation to more advanced array handling, date operations, and user-defined functions. This should give you a robust foundation for working with PostgreSQL!

Table Management​

Basic Insert, Update, Delete​

Basic Select Queries​

Aggregation Queries​

Joins​

Transactions​

Data Types and Casting​

Subqueries​

Window Functions​

Conditional Expressions​

Conditional Expressions (continued)​

Advanced Subqueries​

Text Functions​

Date/Time Functions​

Array Functions​

User-Defined Functions​