PostgreSQL: CRUD, Operators & Functions

Lesson 1: CRUD Operations in PostgreSQL

1.1 Create Operation

 Introduction to CREATE Statement

The `CREATE` statement in PostgreSQL is pivotal for constructing database objects like tables, indexes, views, and schemas.

When creating a table, the syntax involves specifying the table name and defining columns with their respective data types and constraints.

```sql

CREATE TABLE students (

  id SERIAL PRIMARY KEY,

  name VARCHAR (100),

  age INT,

  email VARCHAR (100)

);

```

- Explanation: Here, `students` is the table name. Columns like `id`, `name`, `age`, and `email` are defined with their respective data types (`VARCHAR`, `INT`) and constraints (`SERIAL`, `PRIMARY KEY`).

 Inserting Data

The `INSERT` statement allows the addition of data into created tables.

```sql

INSERT INTO students (name, age, email) VALUES ('John Doe', 25, 'john@example.com');

```

- Explanation: This command inserts a new row into the `students` table, providing values for the `name`, `age`, and `email` columns.

1.2 Read Operation

 SELECT Statement

The `SELECT` statement retrieves data from tables.

```sql

SELECT * FROM students;

SELECT name, age FROM students WHERE age > 20;

```

- Explanation: The first query fetches all columns (``) from the `students` table, while the second query selectively retrieves the `name` and `age` columns where the `age` is greater than 20.

 Filtering and Sorting

To precisely retrieve data, PostgreSQL offers the `WHERE` clause for filtering and `ORDER BY` for sorting.

Example:

```sql

SELECT * FROM students WHERE age BETWEEN 20 AND 30 ORDER BY age DESC;

```

- Explanation: This query fetches records where the age falls between 20 and 30, ordering the result by age in descending order.

1.3 Update Operation

 UPDATE Statement

The `UPDATE` statement modifies existing data in a table.

```sql

UPDATE students SET age = 26 WHERE name = 'John Doe';

```

- Explanation: This command updates the `age` of the student named 'John Doe' to 26.

 Alter Table

The `ALTER TABLE` command allows the modification of table structures, such as adding new columns.

```sql

ALTER TABLE students ADD COLUMN phone_number VARCHAR (15);

```

- Explanation: This command adds a new column named `phone_number` to the `students` table with a `VARCHAR` data type that can hold up to 15 characters.

1.4 Delete Operation

 DELETE Statement

The `DELETE` statement removes specific data from tables.

```sql

DELETE FROM students WHERE id = 1;

```

- Explanation: This command deletes the row where the `id` is equal to 1 from the `students` table.

Lesson 2: Operators in PostgreSQL

2.1 Arithmetic Operators

 Basic Arithmetic

Arithmetic operators in PostgreSQL (`+`, `-`, ``, `/`) facilitate fundamental mathematical operations.

Example:

```sql

SELECT 10 + 5, 20 - 8, 6  4, 24 / 3;

```

- Explanation: These operations would result in addition, subtraction, multiplication, and division respectively.

 Modulus Operator

The `%` operator calculates the remainder of a division operation.

Example:

```sql

SELECT 10 % 3;

```

- Explanation: This operation would yield 1, as it calculates the remainder when 10 is divided by 3.

2.2 Comparison Operators

 Equalities and Inequalities

Comparison operators (`=`, `!=`, `>`, `<`, `>=`, `<=`) help compare values and produce boolean results.

Example:

```sql

SELECT * FROM students WHERE age > 20;

SELECT name FROM employees WHERE salary >= 50000;

```

- Explanation: These queries retrieve records where the `age` is greater than 20 or where the `salary` is equal to or greater than 50000.

2.3 Logical Operators

 AND, OR, NOT

Logical operators (`AND`, `OR`, `NOT`) combine conditions to filter data effectively.

Example:

```sql

SELECT * FROM products WHERE category = 'Electronics' AND price > 500;

SELECT * FROM employees WHERE NOT (department = 'HR' OR age > 30);

```

- Explanation: These queries filter records based on specified conditions. The first query retrieves products that belong to the 'Electronics' category and have a price greater than 500. The second query selects employees who are neither from the 'HR' department nor over 30 years old.

 IN Operator

The `IN` operator simplifies queries by specifying multiple possible values for a column.

Example:

```sql

SELECT * FROM students WHERE department IN ('Computer Science', 'Mathematics', 'Physics');

```

- Explanation: This query retrieves records of students enrolled in the 'Computer Science', 'Mathematics', or 'Physics' departments.

 Additional Operators:

 String Operators

Exploration of operators for string concatenation (`||`) and pattern matching (`LIKE`, `ILIKE`) for text comparisons.

 Bitwise Operators

Understanding operators like `&`, `|`, `~`, `<<`, `>>` for manipulating individual bits in binary values.

 IS NULL Operator

Using `IS NULL` and `IS NOT NULL` to check for null values within columns.

 COALESCE and NULLIF Functions

Explaining functions that complement operators for managing null values in queries.

Custom Operators

Introduction to creating user-defined operators to perform specialized operations.

These additional operators provide a broader understanding of operators in PostgreSQL, enabling learners to leverage the full potential of data manipulation and querying capabilities within the database system.

Lesson 3: Logical Operators

In PostgreSQL, logical operators are used to perform logical operations between two or more conditions in queries. These operators help in creating complex conditions by combining simpler ones. 

In this lesson, we delve into logical operators in PostgreSQL, exploring their functionality and practical application.

 3.1 Boolean Operations

 TRUE/FALSE Values

PostgreSQL handles boolean data with `TRUE` and `FALSE` values. Understanding their representation and usage is fundamental to data manipulation.

 Boolean Functions

Explore essential functions like `COALESCE` and `NULLIF`, which play crucial roles in modifying and managing boolean data. For instance:

```sql

-- COALESCE function example

SELECT COALESCE(column_name, default_value) AS modified_value

FROM table_name;

```

 3.2 CASE Statement

 Conditional Logic

Utilize the powerful `CASE` statement to implement conditional logic within SQL queries. Consider this example:

```sql

SELECT name, 

       CASE 

          WHEN age > 20 THEN 'Adult'

          ELSE 'Minor'

       END AS age_group 

FROM students;

```

The `CASE` statement evaluates conditions and returns the corresponding result, allowing for dynamic categorization of data based on specified criteria. In this case, the query categorizes students as 'Adult' or 'Minor' based on their age.

Understanding and effectively using logical operators, boolean data, and conditional statements like `CASE` enriches your PostgreSQL proficiency, enabling you to manipulate data precisely and efficiently.

Lesson 4: Aggregate Functions

Lesson 4 delves into the powerful world of aggregate functions, enabling you to perform computations on multiple rows within a single column. Understanding these functions is pivotal for summarizing and extracting insights from data.

 4.1 Common Aggregate Functions

Aggregate functions like `SUM`, `AVG`, and `COUNT` play a pivotal role in data summarization:

- SUM: Calculates the total sum of numeric values in a column.

- AVG: Computes the average of numeric values in a column.

- COUNT: Determines the number of rows that meet specified criteria.

- MIN and MAX: Identifies the minimum and maximum values within a column.

These functions offer vital tools for extracting meaningful statistics from datasets, allowing for efficient data analysis and interpretation.

 4.2 GROUP BY Clause

The `GROUP BY` clause enables the grouping of data based on specific criteria, facilitating aggregated computations within those defined groups:

```sql

SELECT department, AVG(salary) 

FROM employees 

GROUP BY department;

```

This example demonstrates grouping employee data by department, computing the average salary within each department.

 4.3 HAVING Clause

The `HAVING` clause complements `GROUP BY`, allowing for the filtration of aggregated data based on specified conditions:

```sql

SELECT department, AVG(salary) 

FROM employees 

GROUP BY department 

HAVING AVG(salary) > 50000;

```

In this instance, it filters the grouped data, displaying departments where the average salary exceeds $50,000.

This comprehensive module equips learners with essential skills to efficiently manipulate and manage data using PostgreSQL. Mastery of these aggregate functions, `GROUP BY`, and `HAVING` lays a robust groundwork for advanced data analysis and utilization within this relational database system.