modul:m290:learningunits:lu07:aufgaben:01

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290:learningunits:lu07:aufgaben:01 [2024/09/30 13:50] – angelegt vdemirmodul:m290:learningunits:lu07:aufgaben:01 [2024/10/17 12:45] (aktuell) – vdemir
Zeile 1: Zeile 1:
-====== LU07.A01 - Preparations for DML commands ======+====== LU08.A01 - Preparatory work ======
  
 ===== Requirements  ===== ===== Requirements  =====
Zeile 6: Zeile 6:
   * Means of aid:    * Means of aid: 
       * Only teaching materials, no websearch, no use of ai.       * 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  +  * Expected result:  
-    * 2: INSERT +    * Conduct the database schema (instance) as foundation for our employee's data. 
-    * 3: UPDATE +    * Establishment of a MySQL **table employees** including all relevant attributes.
-    * 4: DELETE  +
  
-===== Preparation tasks ===== +===== Assignments ===== 
-==== CREATE TABLE ====+ 
 +==== A: DATA SCHEMA ==== 
 +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; 
 + 
 +==== B: 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". 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 (+  CREATE TABLE EMPLOYEES (
     employee_ID INT PRIMARY KEY,       -- Employee ID as the primary key     employee_ID INT PRIMARY KEY,       -- Employee ID as the primary key
     name VARCHAR(50) NOT NULL,         -- Name of the employee (max length 50 characters)     name VARCHAR(50) NOT NULL,         -- Name of the employee (max length 50 characters)
Zeile 26: Zeile 33:
     department VARCHAR(50) NOT NULL    -- Department where the employee works     department VARCHAR(50) NOT NULL    -- Department where the employee works
   );   );
-   
-   
  
 **Explanation** **Explanation**
   - **employee_ID** is the primary key and ensures that each employee has a unique ID.   - **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. +  - **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. +  - **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.).+  - **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.   - **pronomen** stores the employee's pronouns.
-  - **salary** is stored as a `DECIMAL` value to account for financial precision.+  - **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.   - **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.
  
- 
- 
- 
- 
- 
-===== Assignments ===== 
-==== Task A ==== 
-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). 
- 
-==== Task B ==== 
-Display all instances which are currently running on our database. 
- 
-==== Task C ==== 
-Create the database instances  
-  * myDB_PERFECT 
-  * myDB_OK 
-  * myDB_OBSOLETE 
- 
-and display your result. 
- 
-==== Task D ==== 
-Drop the instance "myDB_obsolete", as it obviously obsolete (no longer needed). Display the result. 
- 
-==== Task E ==== 
-Activate the instance "myDB_good" by using the USE command. 
- 
-==== Task F ==== 
-Display the system date by using the command "SELECT sysdate();" 
-  
 ===== Solution ===== ===== Solution =====
-[[modul:m290:learningunits:lu05:loesungen:l01|Lösung]] +[[modul:m290:learningunits:lu07:loesungen:l01|Lösung]]
  
 ===== Vocabulary ===== ===== Vocabulary =====
 ^ English ^ German ^  ^ English ^ German ^ 
-| obsolete | überflüssig | +| preparatary work | Vorarbeit |
-| credential | Berechtigungsnachweis |+
  
  
 ---- ----
 [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir
  • modul/m290/learningunits/lu07/aufgaben/01.1727697049.txt.gz
  • Zuletzt geändert: 2024/09/30 13:50
  • von vdemir