Notes

DDL - Create, Drop, Alter
DCL - Grant, Deny, Revoke
DML - Select, Insert, Update, Delete

***///***
Make sure we use compatible data structures to avoid errors.
double-check if you're using INT or text, for example.
***///***

Syntax for table creation:
CREATE TABLE: It starts the new table definition.
Column name: Unique for each column
Data type: It specifies what kind of information we're going to store.
Restrictions: NOT NULL, PRIMARY KEY, FOREIGN KEY, etc

Syntax on table creation:

* CREATE TABLE: Starts the table creation.
* Name of column: Identifier for each column.
* Data type: Specifies the type of information to store.
* Restrictions: NOT NULL, PRIMARY KEY, FOREIGN KEY, ETC.
* CHECK: Validates data based on specific conditions.
* UNIQUE: Assures non-key unique values.

MODIFYING EXISTING TABLES:
* ALTER TABLE: Starts modifying an existing table.
* ADD COLUMN: Adds new columns to the table.
* ALTER COLUMN: Modifies the definition of existing columns.
* DROP COLUMN: Deletes columns from a table.

ADDING ROWS:
INSERT INTO nombreTabla (columna1, columna2)
VALUES (valor11, valor12), (valor21, valor22), (valor31, valor32), ...

******************************

******************************
HOW TO CREATE A DATABASE?

CREATE DATABASE Students;

****
Show the database:

SHOW DATABASES;

****
Create a table

CREATE TABLE students(
  id_estudiante INT PRIMARY KEY,
    nombre VARCHAR(100),
  edad INT,
    birthday DATE
);

****
We can see the table with describe
DESCRIBE estudiantes;

CREATE TABLE subjects(
  id_subject INT PRIMARY KEY,
    subject_name VARCHAR(100),
    teacher VARCHAR(100)
);


****
how to create a table with foreign keys:

CREATE TABLE inscriptions(
  id_student INT,
    id_subject INT,
    FOREIGN KEY (id_student) REFERENCES students(id_student),
    FOREIGN KEY (id_subject) REFERENCES subjects(id_subject)
);

*****
Add a table with decimals and dates

CREATE TABLE teachers(
  id_teacher INT PRIMARY KEY,
    teacher_name VARCHAR(100),
    salary DECIMAL(10,2) DEFAULT 1000.00,
    hiring_date DATE DEFAULT (CURRENT_DATE),
    age INT CHECK (age BETWEEN 25 AND 70)
);

*****
Add phones to the table

ALTER TABLE teachers ADD phone VARCHAR(15);

****

Delete a table

DROP TABLE inscriptions;


****

JOIN - Table combinations
inner join - It takes rows when there's a coincidence between the tables

left join - It includes all the rows from the left table

right join - It includes all the rows from the right table

Examples :

SELECT RENTAL_DATE, NAME FROM RENTAL

SELECT * FROM INVENTORY WHERE INVENTORY_ID = 367

SELECT * FROM FILE WHERE FILM_id = 60

(We try to use the join to keep the film name)

SELECT RENTAL_ID, RENTAL_DATE, RENTAL.INVENTORY_ID FROM RENTAL 
INNER JOIN INVENTORY ON RENTAL.INVENTORY_ID = INVENTORY.INVENTORY_ID


-SELECT * FROM INVENTORY WHERE INVENTORY_ID = 367

-SELECT * FROM FILE WHERE FILM_ID = 60