1. /* Im weiteren DbLEic01 verwenden */
  2.  
  3. USE DbLEic01
  4.  
  5.  
  6. /* Falls Tabellen bereits existieren, sollen sie vorher gelöscht werden */
  7.  
  8. IF EXISTS(SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'tblMitarbeiter')
  9.   DROP TABLE tblMitarbeiter
  10. IF EXISTS(SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'tblAbteilung')
  11.   DROP TABLE tblAbteilung
  12. IF EXISTS(SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'tblProjekt')
  13.   DROP TABLE tblProjekt
  14. IF EXISTS(SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'tblRefMitarbeiterProjekt')
  15.   DROP TABLE tblRefMitarbeiterProjekt
  16.  
  17. /* Neue Tabellen kreieren */
  18.  
  19. CREATE TABLE tblMitarbeiter
  20. (
  21.   MitarbeiterID int IDENTITY PRIMARY KEY,
  22.   Nachname varchar(50),
  23.   Vorname varchar(50),
  24.   Geburtsdatum date,
  25.   Einstellungsdatum date,
  26.   AbteilungID int
  27. );
  28. CREATE TABLE tblAbteilung
  29. (
  30.   AbteilungID int IDENTITY PRIMARY KEY,
  31.   Abteilungsname varchar(50),
  32.   Abteilungsleiter int
  33. );
  34. CREATE TABLE tblProjekt
  35. (
  36.   ProjektID int IDENTITY PRIMARY KEY,
  37.   Projektbezeichnung varchar(50),
  38.   Projektstart date,
  39.   Abteilung int
  40. );
  41. CREATE TABLE tblRefMitarbeiterProjekt
  42. (
  43.   MitarbeiterID int,
  44.   ProjektID int
  45. );
  46.  
  47.  
  48. /* Tabellen mit Werten füllen */
  49.  
  50. INSERT INTO tblMitarbeiter VALUES ('Sabbel', 'Siegfried', '1976-05-04', '1999-07-01', 1);
  51. INSERT INTO tblMitarbeiter VALUES ('Rettig', 'Rudi', '1980-12-20', '2000-02-01', 2);
  52. INSERT INTO tblMitarbeiter VALUES ('Esser', 'Rita', '1979-05-04', '2010-09-01', 1);
  53. INSERT INTO tblMitarbeiter VALUES ('Kanther', 'Kurt', '1987-05-04', '2011-11-01', 1);
  54. INSERT INTO tblMitarbeiter VALUES ('Mann', 'Michaela', '1989-05-04', '2015-07-01', 3);
  55. INSERT INTO tblMitarbeiter VALUES ('Meier', 'Martina', '1979-05-04', '2013-03-01', 2);
  56. INSERT INTO tblMitarbeiter VALUES ('Schmitt', 'Max', '1970-05-04', '1990-01-01', 1);
  57. INSERT INTO tblMitarbeiter VALUES ('Gans', 'Gustav', '1986-05-04', '2003-02-01', 3);
  58.  
  59. INSERT INTO tblAbteilung VALUES ('Controlling', 3);
  60. INSERT INTO tblAbteilung VALUES ('Informatik', 6);
  61. INSERT INTO tblAbteilung VALUES ('Finanzen', 5);
  62.  
  63. INSERT INTO tblProjekt VALUES ('Investitionsplan', '2015-01-01', 1);
  64. INSERT INTO tblProjekt VALUES ('Webseite', '2016-04-01', 2);
  65. INSERT INTO tblProjekt VALUES ('Börsengang', '2014-12-01', 1);
  66.  
  67. INSERT INTO tblRefMitarbeiterProjekt VALUES (1, 1);
  68. INSERT INTO tblRefMitarbeiterProjekt VALUES (1, 2);
  69. INSERT INTO tblRefMitarbeiterProjekt VALUES (2, 2);
  70. INSERT INTO tblRefMitarbeiterProjekt VALUES (3, 3);
  71. INSERT INTO tblRefMitarbeiterProjekt VALUES (4, 1);
  72. INSERT INTO tblRefMitarbeiterProjekt VALUES (4, 3);
  73. INSERT INTO tblRefMitarbeiterProjekt VALUES (5, 3);
  74. INSERT INTO tblRefMitarbeiterProjekt VALUES (6, 3);
  75. INSERT INTO tblRefMitarbeiterProjekt VALUES (7, 3);
  76. INSERT INTO tblRefMitarbeiterProjekt VALUES (7, 2);
  77. INSERT INTO tblRefMitarbeiterProjekt VALUES (7, 1);
  78. INSERT INTO tblRefMitarbeiterProjekt VALUES (8, 3);
  79.  
  80. /* AUFG: Welche Abteilung(Name) ist in dem jeweiligen Projekt beteiligt? */
  81. SELECT DISTINCT Abteilungsname, Projektbezeichnung
  82. FROM tblAbteilung
  83. LEFT JOIN tblMitarbeiter ON tblAbteilung.AbteilungID = tblMitarbeiter.AbteilungID
  84. LEFT JOIN tblRefMitarbeiterProjekt ON tblMitarbeiter.MitarbeiterID = tblRefMitarbeiterProjekt.MitarbeiterID
  85. LEFT JOIN tblProjekt ON tblRefMitarbeiterProjekt.ProjektID = tblProjekt.ProjektID;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
DROP DATABASE IF EXISTS MitarbeiterDB;

-- Create the database
CREATE DATABASE MitarbeiterDB;
USE MitarbeiterDB;
DROP TABLE IF EXISTS tblMitarbeiter;
DROP TABLE IF EXISTS tblAbteilung;

-- Create tblMitarbeiter table
CREATE TABLE tblMitarbeiter (
    MitarbeiterID INT PRIMARY KEY AUTO_INCREMENT,
    Nachname VARCHAR(50),
    Vorname VARCHAR(50),
    Geburtsdatum DATE,
    Einstellungsdatum DATE,
    AbteilungID INT
);

-- Insert data into tblMitarbeiter
INSERT INTO tblMitarbeiter (Nachname, Vorname, Geburtsdatum, Einstellungsdatum, AbteilungID) VALUES
('Sabbel', 'Siegfried', '1976-05-04', '1999-07-01', 1),
('Rettig', 'Rudi', '1980-12-20', '2000-02-01', 2),
('Esser', 'Rita', '1979-03-03', '2010-09-01', 1),
('Kanther', 'Kurt', '1987-05-07', '2011-11-01', 1),
('Mann', 'Michaela', '1989-12-09', '2015-07-01', 3),
('Meier', 'Martina', '1979-04-22', '2013-03-01', 2),
('Schmitt', 'Max', '1970-04-13', '1990-01-01', 1),
('Gans', 'Gustav', '1988-02-22', '2003-02-01', 3);

-- Create tblAbteilung table
CREATE TABLE tblAbteilung (
    AbteilungID INT PRIMARY KEY,
    Abteilungsname VARCHAR(50),
    Abteilungsleiter INT,
    FOREIGN KEY (Abteilungsleiter) REFERENCES tblMitarbeiter(MitarbeiterID)
);

-- Insert data into tblAbteilung
INSERT INTO tblAbteilung (AbteilungID, Abteilungsname, Abteilungsleiter) VALUES
(1, 'Controlling', 3),
(2, 'Informatik', 6),
(3, 'Finanzen', 5);

-- Create tblProjekt table
CREATE TABLE tblProjekt (
    ProjektID INT PRIMARY KEY,
    Projektbezeichnung VARCHAR(50),
    Projektstart DATE,
    AbteilungID INT,
    FOREIGN KEY (AbteilungID) REFERENCES tblAbteilung(AbteilungID)
);

-- Insert data into tblProjekt
INSERT INTO tblProjekt (ProjektID, Projektbezeichnung, Projektstart, AbteilungID) VALUES
(1, 'Investitionsplan', '2015-01-01', 1),
(2, 'Webseite', '2016-04-01', 2),
(3, 'Börsengang', '2014-12-01', 1);

-- Create tblRefMitarbeiterProjekt table
CREATE TABLE tblRefMitarbeiterProjekt (
    MitarbeiterID INT,
    ProjektID INT,
    PRIMARY KEY (MitarbeiterID, ProjektID),
    FOREIGN KEY (MitarbeiterID) REFERENCES tblMitarbeiter(MitarbeiterID),
    FOREIGN KEY (ProjektID) REFERENCES tblProjekt(ProjektID)
);

-- Insert data into tblRefMitarbeiterProjekt
INSERT INTO tblRefMitarbeiterProjekt (MitarbeiterID, ProjektID) VALUES
(1, 1),
(1, 2),
(2, 2),
(3, 3),
(4, 1),
(4, 3),
(5, 3),
(6, 3),
(7, 3),
(7, 2),
(7, 1),
(8, 3);


Last modified: Thursday, 4 July 2024, 1:00 PM