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

CREATE DATABASE NewDatabaseName;

DROP DATABASE

This command is used to drop an existing SQL database

DROP DATABASE NewDatabaseName;

CREATE TABLE

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

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

--- 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

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

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

To select all columns

SELECT * 
FROM table1;

WHERE

This command is used to filter records

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

AND, OR, NOT

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

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

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

ORDER BY

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