Databases

01. Create Tables
CREATE TABLE `students` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    `age` INT NULL,
    `grade` DOUBLE NOT NULL,
    `project_id` INT NOT NULL
);

CREATE TABLE `projects` (
    `project_id` INT AUTO_INCREMENT PRIMARY KEY,
    `project_name` VARCHAR(50) NOT NULL,
    `project_size` VARCHAR(50) NOT NULL
);

02. Insert Data
INSERT INTO `students` (id, first_name, last_name, age, grade, project_id)
VALUES
(1, 'Guy', 'Gilbert', 15, 4.5, 1),
(2, 'Kevin', 'Brown', 17, 5.4, 5),
(3, 'Roberto', 'Tamburello', 19, 6.0, 2),
(4, 'Linda', 'Smith', 18, 5.0, 3),
(5, 'John', 'Stones', 16, 4.25, 6),
(6, 'Nicole', 'Nelson', 17, 5.50, 4);

INSERT INTO `projects` (project_id, project_name, project_size)
VALUES
(1, 'Multiplication Table', '25.4 MB'),
(2, 'Objects and Classes', '12.3 MB'),
(3, 'Databases', '14.4 MB'),
(4, 'Data Types', '17.2 MB'),
(5, 'For Loop', '34.5 MB'),
(6, 'Conditional Statements', '23.5 MB');

03.	Find Last Name, Grade and Project Name
SELECT st.`last_name`, st.`grade`, pr.`project_name`
FROM `students` st
JOIN `projects` pr ON s.`project_id` = p.`project_id`;

04.	Find First Name, Grade and Project Data
SELECT st.`first_name`, st.`grade`, pr.`project_name`, pr.`project_size`
FROM `students` st
JOIN `projects` pr ON st.project_id = pr.project_id;

05.	Find First 5 Last Name and Age
SELECT `last_name`, `age` FROM students
LIMIT 5;