Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290:learningunits:lu07:loesungen:l01 [2024/09/30 15:12] – vdemir
+++ modul:m290:learningunits:lu07:loesungen:l01 [2024/10/17 12:44] (aktuell) – vdemir
@@ Zeile 1: / Zeile 1: @@
-====== LU07.S01 - Preparations for the assignment of the DML commands ======
+====== LU08.S01 - Preparatory work======
-===== Preparation Assignments =====
+===== A: Creating the data schema =====
-==== A: Creating the data schema ====
 First of all we need a database schema (database workspace). Execute the following two lines on your MySQL installation.
@@ Zeile 13: / Zeile 11: @@
 **Result in webstorm**
+Please note, that the table is actually empty, that means that a "SELECT COUNT(*) FROM employees" leads to "0" results.
 {{:modul:m290:learningunits:lu07:aufgaben:m290.lu07.s01a.png?500|}}
+==== B: CREATE TABLE =====
-==== 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".
   CREATE TABLE EMPLOYEES (
@@ Zeile 25: / Zeile 24: @@
 **Result in Webstorm**
 After creating the table "employees" the result in our Webstorm ought to look like the figure above:
 {{:modul:m290:learningunits:lu07:loesungen:m290.lu07.s01b.png?600|}}
-==== C1: 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
-**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');
-===== Solution =====
-[[modul:m290:learningunits:lu07:loesungen:l01|Lösung]]
 ===== Vocabulary =====
 ^ English ^ German ^
-| ... | ... |
+| ought to | sollte |
 ----
 [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir