modul:m290:learningunits:lu07:aufgaben:02

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290:learningunits:lu07:aufgaben:02 [2024/10/01 08:27] – angelegt vdemirmodul:m290:learningunits:lu07:aufgaben:02 [2024/10/17 12:45] (aktuell) – vdemir
Zeile 1: Zeile 1:
-====== LU07.A02 - INSERT INTO ======+====== LU08.A02 - INSERT INTO ======
  
 ===== Requirements  ===== ===== Requirements  =====
Zeile 8: Zeile 8:
   * Expected Result:    * Expected Result: 
     * At least 30 rows of employees data, inserted into the **table employees**.     * At least 30 rows of employees data, inserted into the **table employees**.
-    * At least 10 rows are inserted with 10 separate inserts statements.  +    * 10 of the 30 rows are inserted with 10 separate insert statements.  
-    * At least 20 rows of data inserted with one single SQL statement.+    * 20 of the 30 rows are inserted with one single SQL statement.
  
-===== Preparation Assignments =====+===== Assignments =====
  
-==== A: DATA SCHEMA ==== +==== A: Separate INSERT statements ==== 
-First of all we need a database schema (database workspace). Execute the following two lines on your MySQL installation.+Below you will find ten lines with employee data that you should insert into the ‘Employees’ table, but with 10 separate INSERT commands.
  
-  CREATE DATABASE hr_database; +  1, 'John', 'Doe', '1985-03-25', 'M', 'He/Him', '2015-01-15', 55000.00, 'Finance' 
-  SHOW DATABASES; +  2, 'Jane', 'Smith', '1990-07-19', 'F', 'She/Her', '2017-06-10', 60000.00, 'HR' 
-  USE hr_database;+  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'
  
-==== B: CREATE TABLE ==== +==== B: One single INSERT statement ==== 
-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". +After inserting 10 lines of data with 10 separate SQL commands, the next step is to do the import with one single SQL commands, which is basicly more compact and more efficient, that the first approach. Insert the following 20 lines of data into the table "employees" 
- +
-  CREATE TABLE EMPLOYEES ( +
-    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. +
- +
-==== 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');+
  
 +  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:loesungen:l01|Lösung]]+[[modul:m290:learningunits:lu07:loesungen:l02|Lösung]]
  
 ===== Vocabulary ===== ===== Vocabulary =====
 ^ English ^ German ^  ^ English ^ German ^ 
-| preparatary work | Vorarbeit |+| ... | ... |
  
  
 ---- ----
 [[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/02.1727764031.txt.gz
  • Zuletzt geändert: 2024/10/01 08:27
  • von vdemir