> For the complete documentation index, see [llms.txt](https://data-to-production.gitbook.io/product-docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://data-to-production.gitbook.io/product-docs/fundamentals/getting-started/step-4-mysql-postgres-and-oracle-db/quick-review-on-sql.md).

# Quick review on SQL

This section provides a brief overview of SQL. For a complete course on SQL, please refer to the resources.

### \* SQL Data Types

### \* SQL Commands:

1. SELECT
2. WHERE
3. AND, OR, NOT
4. ORDER BY
5. CREATE DATABASE
6. DROP DATABASE
7. CREATE TABLE
8. DROP TABLE
9. INSERT INTO
10. JOINS

### Data Types

Numerous data types are available depending on the software you use. However, we will focus on a few of the commonly used data types in MySQL.

* **VARCHAR(size) :** this is used to store character strings of variable length. The "**size"** parameter specifies the max length of the string that can be stored in the column
* **INTEGER:** used to store whole numbers (positive or negative) without decimal points.
* **DATE** - used to store date values.
* **BOOLEAN** - used to store true/false values.
* **DECIMAL** - used to store exact numeric values with decimal points.
* **FLOAT** - used to store approximate numeric values with decimal points.

### CREATE DATABASE

 This command is used to ***create a new SQL database***

```sql
CREATE DATABASE NewDatabaseName;
```

### DROP DATABASE

This command is used to ***drop an existing SQL database***

```sql
DROP DATABASE NewDatabaseName;
```

### CREATE TABLE

This command is used to ***create a new table*** within a database

```sql
CREATE TABLE TableName (
    column1 datatype constraint,
    column2 datatype constraint,
    .....
);
```

common constraints:

```
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Used to create and retrieve data from the database very quickly
```

### ALTER

```sql
--- add new column
ALTER TABLE table_name
ADD column_name datatype;

--- alter column datatype
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

--- alter column constraint
ALTER TABLE table_name
MODIFY COLUMN column_name datatype constraint;

--- drop column
ALTER TABLE table_name
DROP COLUMN column_name;
```

### DATES

```sql
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY
```

### SELECT

This command is used to ***select any columns*** from a table within a database

```sql
SELECT column1, column2, column3, ...
FROM table1;
```

To select **all** columns

```sql
SELECT * 
FROM table1;
```

### WHERE 

This  command  is used to filter records

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

### AND, OR, NOT

```sql
-- AND
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
```

```sql
-- OR
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
```

```sql
-- NOT
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
```

### ORDER BY 

```sql
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
```