Basic SQL Part 2

Unique values (DISTINCT)

The DISTINCT keyword in SQL is used to return only unique values in the results of a query.

SELECT DISTINCT(column1) FROM table_name;

This query returns every unique value from column1 from table_name.

Aggregate functions

In SQL, there are five important aggregate functions for data analysts/scientists:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

Example:

SELECT COUNT(*) FROM table_name WHERE column1 = 'something';

It counts the number of rows in the SQL table in which the value in column1 is 'something'.

GROUP BY

The GROUP BY clause in SQL groups rows with the same values in one or more columns. It is typically used in combination with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX to calculate statistics for each group.

Example:

SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;

This query counts the number of values in column2 - for each group of unique column1 values.

SELECT column1, MIN(column2) FROM table_name GROUP BY column1;

This query finds the minimum value in column2 - for each group of unique column1 values.

Aliases

You can rename columns, tables, subqueries, or anything.

SELECT column1, COUNT(column2) AS number_of_values FROM table_name GROUP BY column1;

This query counts the number of values in column2 - for each group of unique column1 values. Then it renames the COUNT(column2) column to number_of_values.

HAVING

The execution order of the different SQL keywords doesn't allow you to filter with the WHERE clause on the result of an aggregate function (COUNT, SUM, etc.). This is because WHERE is executed before the aggregate functions. But that's what HAVING is for.

SELECT column1, COUNT(column2) 
FROM table_name 
GROUP BY column1 
HAVING COUNT(column2) > 100;

This query counts the number of values in column2 - for each group of unique column1 values. It returns only those results where the counted value is greater than 100.

A Note on Data Types in SQL

As of recent updated versions, SQLite database supports the following datatypes:

| Type       | Description                                     | Example                   |
| ---------- | ----------------------------------------------- | ------------------------- |
| NULL       | A null value                                    | NULL                      |
| INTEGER    | A signed integer                                | 1, 2, 3, -1               |
| REAL       | A floating point value                          | 3.2, 0.1, -2.0            |
| TEXT       | A text string                                   | "CoderSchool", "is cool"  |
| BLOB       | A blob of data, stored exactly as it was input  | Binary data like images   |

Additionally, certain data types are commonly used across other database management systems. For instance, in PostgreSQL and Oracle, common datatypes includes:

| Type       | Description                                     | Example                   |
| ---------- | ----------------------------------------------- | ------------------------- |
| BOOLEAN    | A boolean value                                 | True, False, Null         |
| VARCHAR(n) | An n-length string                              | "Coder" - VARCHAR(5)      |
| TIMESTAMP  | Stores both date and time values.               | 2022-31-12 23:00:00       |

NOTE: SQLite does not have a storage class set aside for storing dates or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values.