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 ===== Preparation tasks ===== ==== CREATE TABLE ==== 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 - employee_ID is the primary key and ensures that each employee has a unique ID. - name and surname are VARCHAR fields that store the name and surname of the employee. - birthdate and employment_date use the `DATE` data type to store the birth and employment dates. - sex is stored as a CHAR(1) type to represent gender with one letter (`M` for male, `F` for female, etc.). - pronomen stores the employee's pronouns. - salary is stored as a `DECIMAL` value to account for financial precision. - 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