Module - 1: PostgreSQL Fundamentals Overview Module 2

Module - 2: Data Types in PostgreSQL Module 3

Module - 3: PostgreSQL: CRUD, Operators & Functions Module 4

Module - 4: PostgreSQL Query Essentials

# Module - 3: 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.