modul:m290:learningunits:lu07:aufgaben:01

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290:learningunits:lu07:aufgaben:01 [2024/10/01 08:28] – vdemirmodul:m290:learningunits:lu07:aufgaben:01 [2024/10/17 12:45] (aktuell) – vdemir
Zeile 1: Zeile 1:
-====== LU07.A01 - Preparatory work ======+====== 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.
-  * Expected result: Establishment of a MySQL **table employees** including all relevant attributes.+  * Expected result:  
 +    * Conduct the database schema (instance) as foundation for our employee's data. 
 +    * Establishment of a MySQL **table employees** including all relevant attributes.
  
-===== Preparation Assignments =====+===== Assignments =====
  
 ==== A: DATA SCHEMA ==== ==== A: DATA SCHEMA ====
Zeile 40: Zeile 42:
   - **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.
- 
-==== C: Inital data import ==== 
-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 
- 
-**C1: 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'); 
- 
-**C2: 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'); 
  
 ===== Solution ===== ===== Solution =====
  • modul/m290/learningunits/lu07/aufgaben/01.1727764090.txt.gz
  • Zuletzt geändert: 2024/10/01 08:28
  • von vdemir