LU07.A01 - Preparations for 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

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 EMPLOYEE (
  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.

Make sure, that your MySQL database system is running and connect with you webstorm editor to your database with your DBA credentials (user: root, password: yourPW).

Display all instances which are currently running on our database.

Create the database instances

• myDB_PERFECT
• myDB_OK
• myDB_OBSOLETE

and display your result.

Drop the instance „myDB_obsolete“, as it obviously obsolete (no longer needed). Display the result.

Activate the instance „myDB_good“ by using the USE command.

Display the system date by using the command „SELECT sysdate();“

Lösung

Volkan Demir