LU07.A01 - Preparations for the assignment of the DML commands

• Work type: Individual
• Timeframe: 10 Minutes
• Means of aid:
  • Only teaching materials, no websearch, no use of ai.
• Establishment of a MySQL table employee and import of the provided inital data in order to perform the requirements of the task: 2: INSERT, 3: UPDATE, 4: DELETE

First of all we need a database schema (database workspace). Execute the following two lines on your MySQL installation.

CREATE DATABASE hr_database;
SHOW DATABASES;
USE hr_database;

After executing the three SQL commands above, the feedback from your database would look like in the image below:

To exercise the DML commands, we need a suitable table including a reasonable amout of data. The following SQL statement will create a table employee regarding all necessary attributes of an „average employee“.

CREATE TABLE EMPLOYEES (
  employee_ID INT PRIMARY KEY,       -- Employee ID as the primary key
  name VARCHAR(50) NOT NULL,         -- Name of the employee (max length 50 characters)
  surname VARCHAR(50) NOT NULL,      -- Surname of the employee (max length 50 characters)
  birthdate DATE NOT NULL,           -- Birthdate of the employee
  sex CHAR(1),                       -- Sex of the employee (M/F/O for other)
  pronomen VARCHAR(10),              -- Pronoun of the employee
  employment_date DATE NOT NULL,     -- Date when the employee was hired
  salary DECIMAL(10, 2) NOT NULL,    -- Salary of the employee (up to 10 digits, 2 decimal places)
  department VARCHAR(50) NOT NULL    -- Department where the employee works
);

Explanation

1. employee_ID is the primary key and ensures that each employee has a unique ID.
2. name and surname are 'VARCHAR' fields that store the name and surname of the employee.
3. birthdate and employment_date use the 'DATE' data type to store the birth and employment dates.
4. sex is stored as a 'CHAR(1)' type to represent gender with one letter ('M' for male, 'F' for female, etc.).
5. pronomen stores the employee's pronouns.
6. salary is stored as a 'DECIMAL' value to account for financial precision.
7. department is a VARCHAR(50) field that stores the name of the department the employee is associated with. It has a NOT NULL constraint to ensure that every employee is assigned to a department.

Once we have created our 'employees' table, we need a reasonable amount of data to be able to make the assignments. The following SQL INSERTS will provide you with the inital data of 30 employees. Though execute the follwing INSERT statements on your MySQL.

As we already know there are two ways how to import the inital data cosisting of 30 rows of emloyee data:

• multiple inserts
• single insert

Multiple inserts

INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (1, 'John', 'Doe', '1985-03-25', 'M', 'He/Him', '2015-01-15', 55000.00, 'Finance');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (2, 'Jane', 'Smith', '1990-07-19', 'F', 'She/Her', '2017-06-10', 60000.00, 'HR');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (3, 'Mike', 'Brown', '1982-11-02', 'M', 'He/Him', '2012-03-05', 75000.00, 'IT');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (4, 'Sarah', 'Johnson', '1970-01-14', 'F', 'She/Her', '2020-08-22', 50000.00, 'Marketing');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (5, 'Chris', 'Williams', '1987-04-11', 'M', 'He/Him', '2016-09-30', 65000.00, 'Finance');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (6, 'Anna', 'Lee', '1993-12-01', 'F', 'She/Her', '2018-04-12', 72000.00, 'IT');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (7, 'David', 'Kim', '1989-10-22', 'M', 'He/Him', '2014-11-08', 69000.00, 'Marketing');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (8, 'Laura', 'Clark', '1986-02-17', 'F', 'She/Her', '2013-05-01', 56000.00, 'HR');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (9, 'Jake', 'Lewis', '1962-09-05', 'M', 'He/Him', '2019-12-20', 59000.00, 'Finance');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (10, 'Emily', 'Martinez', '1996-06-13', 'F', 'She/Her', '2021-01-05', 61000.00, 'IT');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (11, 'Ryan', 'Turner', '1991-05-21', 'M', 'He/Him', '2017-07-19', 70000.00, 'Marketing');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (12, 'Sophia', 'Adams', '1988-12-06', 'F', 'She/Her', '2014-02-11', 62000.00, 'HR');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (13, 'Nathan', 'Garcia', '1994-03-29', 'M', 'He/Him', '2018-09-15', 66000.00, 'Finance');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (14, 'Olivia', 'Baker', '1983-08-11', 'F', 'She/Her', '2011-10-25', 78000.00, 'IT');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (15, 'Liam', 'Scott', '1958-12-18', 'M', 'He/Him', '2020-06-13', 53000.00, 'Marketing');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (16, 'Isabella', 'Carter', '1990-01-02', 'F', 'She/Her', '2016-03-30', 67000.00, 'HR');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (17, 'James', 'Rodriguez', '1984-05-14', 'M', 'He/Him', '2013-07-22', 72000.00, 'Finance');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (18, 'Mia', 'Lopez', '1998-09-27', 'F', 'She/Her', '2021-03-11', 51000.00, 'IT');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (19, 'Ethan', 'Harris', '1970-04-07', 'M', 'He/Him', '2019-10-05', 64000.00, 'Marketing');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (20, 'Charlotte', 'Moore', '1986-11-03', 'F', 'She/Her', '2014-12-30', 59000.00, 'HR');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (21, 'Lucas', 'Jackson', '1989-02-28', 'M', 'He/Him', '2015-08-18', 74000.00, 'Finance');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (22, 'Amelia', 'Young', '1991-06-23', 'F', 'She/Her', '2016-11-02', 62000.00, 'IT');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (23, 'Henry', 'King', '1993-10-08', 'M', 'He/Him', '2018-05-07', 67000.00, 'Marketing');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (24, 'Grace', 'Wright', '1987-03-19', 'F', 'She/Her', '2012-04-14', 71000.00, 'HR');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (25, 'Jack', 'White', '1962-08-15', 'M', 'He/Him', '2019-01-23', 69000.00, 'Finance');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (26, 'Chloe', 'Martin', '1996-12-31', 'F', 'She/Her', '2021-07-05', 54000.00, 'IT');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (27, 'Daniel', 'Green', '1985-09-25', 'M', 'He/Him', '2013-02-13', 76000.00, 'Marketing');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (28, 'Ella', 'Hall', '1994-04-30', 'F', 'She/Her', '2017-09-26', 63000.00, 'HR');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (29, 'Oliver', 'Nelson', '1993-07-21', 'M', 'He/Him', '2018-11-19', 64000.00, 'Finance');
INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES (30, 'Ava', 'Walker', '1958-02-11', 'F', 'She/Her', '2021-05-30', 52000.00, 'Marketing');

Single insert

INSERT INTO EMPLOYEES (employee_ID, name, surname, birthdate, sex, pronomen, employment_date, salary, department)
VALUES 
  (1, 'John', 'Doe', '1985-03-25', 'M', 'He/Him', '2015-01-15', 55000.00, 'Finance'),
  (2, 'Jane', 'Smith', '1990-07-19', 'F', 'She/Her', '2017-06-10', 60000.00, 'HR'),
  (3, 'Mike', 'Brown', '1982-11-02', 'M', 'He/Him', '2012-03-05', 75000.00, 'IT'),
  (4, 'Sarah', 'Johnson', '1970-01-14', 'F', 'She/Her', '2020-08-22', 50000.00, 'Marketing'),
  (5, 'Chris', 'Williams', '1987-04-11', 'M', 'He/Him', '2016-09-30', 65000.00, 'Finance'),
  (6, 'Anna', 'Lee', '1993-12-01', 'F', 'She/Her', '2018-04-12', 72000.00, 'IT'),
  (7, 'David', 'Kim', '1989-10-22', 'M', 'He/Him', '2014-11-08', 69000.00, 'Marketing'),
  (8, 'Laura', 'Clark', '1986-02-17', 'F', 'She/Her', '2013-05-01', 56000.00, 'HR'),
  (9, 'Jake', 'Lewis', '1962-09-05', 'M', 'He/Him', '2019-12-20', 59000.00, 'Finance'),
  (10, 'Emily', 'Martinez', '1996-06-13', 'F', 'She/Her', '2021-01-05', 61000.00, 'IT'),
  (11, 'Ryan', 'Turner', '1991-05-21', 'M', 'He/Him', '2017-07-19', 70000.00, 'Marketing'),
  (12, 'Sophia', 'Adams', '1988-12-06', 'F', 'She/Her', '2014-02-11', 62000.00, 'HR'),
  (13, 'Nathan', 'Garcia', '1994-03-29', 'M', 'He/Him', '2018-09-15', 66000.00, 'Finance'),
  (14, 'Olivia', 'Baker', '1983-08-11', 'F', 'She/Her', '2011-10-25', 78000.00, 'IT'),
  (15, 'Liam', 'Scott', '1958-12-18', 'M', 'He/Him', '2020-06-13', 53000.00, 'Marketing'),
  (16, 'Isabella', 'Carter', '1990-01-02', 'F', 'She/Her', '2016-03-30', 67000.00, 'HR'),
  (17, 'James', 'Rodriguez', '1984-05-14', 'M', 'He/Him', '2013-07-22', 72000.00, 'Finance'),
  (18, 'Mia', 'Lopez', '1998-09-27', 'F', 'She/Her', '2021-03-11', 51000.00, 'IT'),
  (19, 'Ethan', 'Harris', '1970-04-07', 'M', 'He/Him', '2019-10-05', 64000.00, 'Marketing'),
  (20, 'Charlotte', 'Moore', '1986-11-03', 'F', 'She/Her', '2014-12-30', 59000.00, 'HR'),
  (21, 'Lucas', 'Jackson', '1989-02-28', 'M', 'He/Him', '2015-08-18', 74000.00, 'Finance'),
  (22, 'Amelia', 'Young', '1991-06-23', 'F', 'She/Her', '2016-11-02', 62000.00, 'IT'),
  (23, 'Henry', 'King', '1993-10-08', 'M', 'He/Him', '2018-05-07', 67000.00, 'Marketing'),
  (24, 'Grace', 'Wright', '1987-03-19', 'F', 'She/Her', '2012-04-14', 71000.00, 'HR'),
  (25, 'Jack', 'White', '1962-08-15', 'M', 'He/Him', '2019-01-23', 69000.00, 'Finance'),
  (26, 'Chloe', 'Martin', '1996-12-31', 'F', 'She/Her', '2021-07-05', 54000.00, 'IT'),
  (27, 'Daniel', 'Green', '1985-09-25', 'M', 'He/Him', '2013-02-13', 76000.00, 'Marketing'),
  (28, 'Ella', 'Hall', '1994-04-30', 'F', 'She/Her', '2017-09-26', 63000.00, 'HR'),
  (29, 'Oliver', 'Nelson', '1993-07-21', 'M', 'He/Him', '2018-11-19', 64000.00, 'Finance'),
  (30, 'Ava', 'Walker', '1958-02-11', 'F', 'She/Her', '2021-05-30', 52000.00, 'Marketing');

Lösung

Volkan Demir