Data Definition and Data Types - Exercise
01. Create Tables
CREATE TABLE `minions` (
	id INT AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE `towns` (
	town_id INT AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY(town_id)
);

02. Alter Minions Table
ALTER TABLE `minions`
ADD COLUMN `town_id` INT NOT NULL,
ADD CONSTRAINT fk_minions_towns
FOREIGN KEY (`town_id`)
REFERENCES `towns` (`id`)

03. Insert Records in Both Tables
INSERT INTO `towns`(`id`, `name`)
VALUES (1, 'Sofia'), (2, "Plovdiv"), (3, "Varna");

INSERT INTO `minions` (`id`, `name`, `age`, `town_id`)
VALUES (1, "Kevin", 22, 1),
		(2, "Bob", 15, 3),
        (3, "Steward", NULL, 2);

04. Truncate Table Minions
TRUNCATE TABLE minions;

05. Drop All Tables
DROP TABLE `minions`;
DROP TABLE `towns`;

06. Create Table People
CREATE TABLE `people` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(200) NOT NULL,
    `picture` BLOB,
    `height` DOUBLE(10 , 2 ),
    `weight` DOUBLE(10 , 2 ),
    `gender` CHAR(1) NOT NULL,
    `birthdate` DATE NOT NULL,
    `biography` TEXT
);

INSERT INTO `people` (`name`, `gender`, `birthdate`)
VALUES
('Desi', 'F', DATE(NOW())),
('Peter', 'M', DATE(NOW())),
('Ivan', 'M', DATE(NOW())),
('Tanya', 'F', DATE(NOW())),
('Martin', 'M', DATE(NOW()));

07. Create Table Users
CREATE TABLE `users` (
	`id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(30) NOT NULL,
    `password` VARCHAR(26) NOT NULL,
    `profile_picture` BLOB,
    `last_login_time` TIME,
    `is_deleted` BOOLEAN
);

INSERT INTO `users` (`username`, `password`)
VALUES
('Test1', 'Pass1'),
('Test2', 'Pass2'),
('Test3', 'Pass3'),
('Test4', 'Pass4'),
('Test5', 'Pass5');