PostgreSQL Tutorial

Data Types

Querying & Filtering Data

Managing Tables

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

Working with JOINS & Schemas

Roles & Permissions

Working with Sets

Subquery & CTEs

User-defined Functions

Important In-Built Functions

PostgreSQL PL/pgSQL

Variables & Constants

Stored Procedures

Working with Triggers

Working with Views & Indexes

Errors & Exception Handling

PostgreSQL - SELECT

The SELECT statement is foundational in SQL and PostgreSQL. It is used to query data from one or more tables in a database. The SELECT statement retrieves zero or more rows based on the criteria you specify.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

column1, column2, ...: List of columns you want to retrieve. Use * to retrieve all columns from the table.
table_name: The name of the table from which to retrieve data.
WHERE condition: Optional. Specifies which rows to retrieve based on a condition.

Examples:

Select all columns from a table:
```
SELECT * FROM employees;
```

Select specific columns:

SELECT first_name, last_name FROM employees;

Using the WHERE clause:

Filter results based on specific criteria.

SELECT first_name, last_name FROM employees WHERE department = 'HR';

Distinct values:
To get distinct values from a column:
```
SELECT DISTINCT department FROM employees;
```
Ordering the results:
```
SELECT first_name, last_name FROM employees ORDER BY last_name ASC;
```
The ORDER BY clause sorts the results based on the specified column(s). Use ASC for ascending (default) and DESC for descending order.
Limiting the results:
```
SELECT first_name, last_name FROM employees LIMIT 10;
```
The LIMIT clause restricts the number of rows returned.

Combining conditions:

Using AND, OR, and NOT operators:

SELECT first_name, last_name FROM employees WHERE department = 'HR' AND salary > 50000;

Aggregation:
Combine multiple rows of data into a single row based on common values:
```
SELECT department, COUNT(*) FROM employees GROUP BY department;
```
Here, the GROUP BY clause groups results by the department, and the COUNT(*) function counts the number of employees in each department.

Advanced Features:

PostgreSQL's SELECT also supports more advanced features like:

Joins: Combine rows from two or more tables based on related columns.
Subqueries: Embed one query within another.
Functions: Use built-in functions (like SUM, AVG, etc.) or user-defined functions.
Window functions: Perform calculations across a set of rows that are related to the current row.
Common Table Expressions (CTEs): Create temporary result sets for complex queries using the WITH clause.

The SELECT statement is at the heart of SQL. Its flexibility and power in PostgreSQL, combined with extensions and additional features, allow for retrieving data in virtually any format or arrangement you might need.

How to use SELECT in PostgreSQL: The SELECT statement retrieves data from one or more tables.
```
SELECT column1, column2
FROM your_table;
```

SELECTing specific columns in PostgreSQL:

SELECT column1, column2
FROM your_table;

Using WHERE clause in PostgreSQL SELECT:

SELECT column1, column2
FROM your_table
WHERE condition;

Sorting results with ORDER BY in PostgreSQL SELECT:

SELECT column1, column2
FROM your_table
ORDER BY column1 ASC, column2 DESC;

Limiting results with LIMIT in PostgreSQL SELECT:

SELECT column1, column2
FROM your_table
LIMIT 10;

Skipping rows with OFFSET in PostgreSQL SELECT:

SELECT column1, column2
FROM your_table
OFFSET 5;

Filtering results with WHERE and LIKE in PostgreSQL SELECT:

SELECT column1, column2
FROM your_table
WHERE column1 LIKE 'prefix%';

JOIN operations in PostgreSQL SELECT:

SELECT t1.column1, t2.column2
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id;

Aggregating data with GROUP BY in PostgreSQL SELECT:

SELECT column1, COUNT(*)
FROM your_table
GROUP BY column1;

HAVING clause in PostgreSQL SELECT:

SELECT column1, COUNT(*)
FROM your_table
GROUP BY column1
HAVING COUNT(*) > 10;

Subqueries in PostgreSQL SELECT:

SELECT column1, (SELECT AVG(column2) FROM another_table) AS avg_value
FROM your_table;

Using DISTINCT in PostgreSQL SELECT:

SELECT DISTINCT column1
FROM your_table;

Combining multiple SELECT statements in PostgreSQL:

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

Window functions in PostgreSQL SELECT:

SELECT column1, column2, SUM(column2) OVER (PARTITION BY column1 ORDER BY column2) AS running_total
FROM your_table;

Common table expressions (CTE) in PostgreSQL SELECT:

WITH your_cte AS (
   SELECT column1, column2
   FROM your_table
)
SELECT * FROM your_cte;