Working With Commands

Creating New DataBase

-- Create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS company_db;

-- Show the list of databases
SHOW DATABASES;

-- Use the newly created database
USE company_db;


-- Create the "worker" table
CREATE TABLE worker (
    worker_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    manager_id INT,
    CONSTRAINT fk_manager_id FOREIGN KEY (manager_id) REFERENCES worker(worker_id)
);

-- Create the "bonus" table
CREATE TABLE bonus (
    bonus_id INT AUTO_INCREMENT PRIMARY KEY,
    worker_id INT,
    bonus_amount DECIMAL(8, 2),
    bonus_date DATE,
    CONSTRAINT fk_worker_id FOREIGN KEY (worker_id) REFERENCES worker(worker_id)
);

-- Create the "title" table
CREATE TABLE title (
    title_id INT AUTO_INCREMENT PRIMARY KEY,
    worker_id INT,
    job_title VARCHAR(50),
    title_start_date DATE,
    CONSTRAINT fk_worker_id_title FOREIGN KEY (worker_id) REFERENCES worker(worker_id)
);

Populating Data

-- Use the "company_db" database
USE company_db;

-- Insert data into the "worker" table
INSERT INTO worker (first_name, last_name, hire_date, department, salary, manager_id)
VALUES
    ('John', 'Doe', '2020-01-15', 'HR', 55000.00, NULL),
    ('Jane', 'Smith', '2019-08-10', 'Finance', 60000.00, 1),
    ('Alice', 'Johnson', '2021-03-22', 'Engineering', 65000.00, 1),
    ('Bob', 'Williams', '2020-05-01', 'Sales', 58000.00, 2);

-- Insert data into the "bonus" table
INSERT INTO bonus (worker_id, bonus_amount, bonus_date)
VALUES
    (1, 2000.00, '2021-12-05'),
    (2, 1500.00, '2022-01-20'),
    (3, 1800.00, '2022-02-15');

-- Insert data into the "title" table
INSERT INTO title (worker_id, job_title, title_start_date)
VALUES
    (1, 'HR Manager', '2020-01-15'),
    (2, 'Financial Analyst', '2019-08-10'),
    (3, 'Software Engineer', '2021-03-22');
    

Bonus Table

Title Table

Worker Table