# 1. Querying Data
### Select
* It is used to query data from tables using `SELECT` statement
* It has following clauses
* Select distinct rows using `DISTINCT` class
* Sort rows using `ORDER BY` clause
* Filter rows using `WHERE` clause
* Select subset of rows from table using `LIMIT` or `FETCH` class
* Group rows into groups using `GROUP BY` clause
* Filter groups using `HAVING` clause
* Join with other tables using **joins** such as `INNTER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN` clauses
* Perform set operations using `UNION, INTERSECT, EXCEPT` clauses
* PostgreSQL evaluates the `FROM` clause before the `SELECT` clause in the SELECT statement
* We can also use SELECT statement with expression e.g
* `SELECT 5 * 3;`
#### Concatenation Operator `||`
We can use this operator to concatenate multiple columns together e.g
```sql
SELECT
first_name || ' ' || last_name,
email
FROM
customer;
```
***
### Column Aliases
* It allows us to assign a column or expression in SELECT statement a temporary name
* The `AS` keyword is optional.
* Following are two styles of defining column aliases
#### Example 1
```sql
SELECT
first_name "First Name",
last_name Surname
FROM customer;
```
#### Example 2
```sql
SELECT
first_name || ' ' || last_name AS full_name
FROM
customer;
```
***
### Order By
* It is used to sort the rows of the result set, we use it with the `SELECT` clause
* We can get the results in `ASC` or `DESC` order, it uses `ASC` by default
* PostgreSQL evaluates the clauses in the SELECT statment in the following order:
\*
```
```
#### Example 1
```sql
SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name ASC,
last_name DESC;
```
#### Example 2
```sql
SELECT
first_name,
LENGTH(first_name) len
FROM
customer
ORDER BY
len DESC;
```
#### `NULL` Clause
* In db, NULL is a marker that indicates missing or unknown data
* When you sort rows that contains NULL, you can specify order of NULL with other non-null values using the `NULLS FIRST` or `NULLS LAST` option of `ORDER BY` clause
***
### Select Distinct
* `DISTINCT` clause is used to remove duplicate rows from a result set
* It can be applied to one or more columns
* If we specify multiple columns, DISTINCT clause will evaluate the duplicate based on the combination of values of columns given
#### Example 1
```sql
SELECT
DISTINCT bcolor
FROM
distinct_demo
ORDER BY
bcolor;
```
#### Example 2
```sql
SELECT
DISTINCT bcolor,
fcolor
FROM
distinct_demo
ORDER BY
bcolor,
fcolor;
```
#### DISTINCT ON
* PostgreSQL also provides the `DISTINCT ON (expression)` to keep the “first” row of each group of duplicates
* The order of rows returned from the SELECT statement is unspecified therefore the “first” row of each group of the duplicate is also unspecified.
* It is a good practice to always use the ORDER BY clause with the DISTINCT ON(expression) to make the result set predictable.
* Notice that the DISTINCT ON expression must match the leftmost expression in the ORDER BY clause.
#### Example 3
```sql
SELECT
DISTINCT ON (bcolor) bcolor,
fcolor
FROM
distinct_demo
ORDER BY
bcolor,
fcolor;
```
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://notes.nomanaziz.me/development/database/sql/basics/1.-querying-data.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
