SQL Tutorial: create, alter i drop tables

Introducció

Abans de fer create tables hem de saber quins són els tipus de dades que podem fer servir a MySQL:

• https://www.sqltutorial.org/sql-data-types/

I després podrem estudiar ja com es creen les taules (i també esborrar i modificar): Section 12: Working with table structures

• CREATE TABLE – create a new table in the database.
• ALTER TABLE – modify the structure of an existing table.
• DROP TABLE – remove the tables permanently.
• TRUNCATE TABLE – delete all data in a big table fast and efficiently.
• https://www.sqltutorial.org/sql-create-table/

T2: CREATE TABLE

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL
);

CREATE TABLE trainings (
    employee_id INT,
    course_id INT,
    taken_date DATE,
    PRIMARY KEY (employee_id , course_id)
);

T3: ALTER TABLE

ALTER TABLE courses ADD credit_hours INT NOT NULL;

ALTER TABLE courses 
ADD fee NUMERIC (10, 2) AFTER course_name,
ADD max_limit INT AFTER course_name;

ALTER TABLE courses MODIFY fee NUMERIC (10, 2) NOT NULL;

ALTER TABLE courses DROP COLUMN fee;

ALTER TABLE courses 
DROP COLUMN max_limit,
DROP COLUMN credit_hours;

CREATE TABLE candidates (
    id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

ALTER TABLE candidates
ADD COLUMN phone VARCHAR(50);

ALTER TABLE candidates
ADD COLUMN home_address VARCHAR(255),
ADD COLUMN dob DATE,
ADD COLUMN linkedin_account VARCHAR(255);

CREATE TABLE persons (
    person_id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    phone VARCHAR(25),
    email VARCHAR(255)
);

ALTER TABLE persons
DROP COLUMN email;

ALTER TABLE persons
DROP COLUMN date_of_birth,
DROP COLUMN phone;

T4: DROP TABLE

(TBD)

---

creat per Joan Quintana Compte, desembre 2021