DDL Exercise 1

 -- ---------------------------------------------------------------------- -
-- 1. Beispiel) insert data
-- ---------------------------------------------------------------------- -
-- region
 
-- Fuegen Sie alle moeglichen Kombinationen der COLORS, NATIONALITIES
-- und PETS Datensaetze in die DATA Tablle ein.
 
-- region Beispiel Ressourcen)
 
create table colors
(
    value varchar(10) not null,
    primary key (value)
);
 
insert into colors values ('yellow');
insert into colors values ('blue');
insert into colors values ('red');
insert into colors values ('white');
insert into colors values ('green');
 
create table nationalities
(
    value varchar(20) not null,
    primary key (value)
);
 
insert into nationalities values ('Norway');
insert into nationalities values ('Ukraine');
insert into nationalities values ('England');
insert into nationalities values ('Spain');
insert into nationalities values ('Japan');
 
create table pets
(
    name varchar(20) not null,
    primary key (name)
);
 
insert into pets values ('fox');
insert into pets values ('horse');
insert into pets values ('snail');
insert into pets values ('dog');
insert into pets values ('zebra');
 
 
create table data
(
    color           varchar(20) not null,
    nationality     varchar(20) not null,
    pet             varchar(20) not null,
    primary key (color, nationality, pet),
    constraint fk_data_color foreign key (color)
        references colors (value),
    constraint fk_data_nationality foreign key (nationality)
        references nationalities (value),
    constraint fk_data_pet foreign key (pet)
        references pets (name)
);
 
-- endregion
 
 
-- region 1.1) INSERT Statements
 
 
 
 
-- endregion
 
 
-- region DROP Tables
DROP TABLE DATA;
DROP TABLE colors;
DROP TABLE nationalities;
DROP TABLE pets;
 
-- endregion
 
-- ---------------------------------------------------------------------- -
-- 2. Beispiel) DDL Statements
-- ---------------------------------------------------------------------- -
-- region
 
-- Erzeugen Sie die PROJECTS Tabellen in der Datenbank und uebernehmen Sie die 
-- Daten in die Datenbank. 
 
-- Adaptieren Sie anschliessend die Tabellen und Daten entsprechend der geforderten
-- Anforderungen.
 
-- region PROJECTS Ressourcen)
 
CREATE TABLE projects
(
    PROJECT_ID       int NOT NULL AUTO INCREMENT,
    TITLE            VARCHAR(100)  NOT NULL UNIQUE,
    DESCRIPTION      VARCHAR(4000),
    primary key (PROJECT_ID)
);
 
INSERT INTO projects (PROJECT_ID, description, title) VALUES (1,'Finite Elemente in der Simulation. Zur Simulation der Kraftstoffverbrennung in Motoren sollen Finite Elemente Methoden eingesetzt werden.', 'Finite Elemente in der Simulation');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (2,'Schnellhärteverfahren in der Betontechnik. Für den Einsatz unter extremen Bedingungen (Brückenbau) sollen Schnellhärteverfahren in der Betontechnik gefunden werdne.', 'Schnellhärteverfahren in der Betontechnik');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (3,'AI in der Robotertechnik. Entwickeln einer leistungsstarken AI für die Robotersteuerung im Roboterfussball', 'AI in der Robotortechnik');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (4,'Brückenbau im Meer. Es sollen Methoden zum Brückenbau zwischen Inseln erdacht und perfektioniert werden', 'Brückenbau im Meer');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (5,'Aufschüttung von künstlichen Inseln', 'Aufschüttung von künstlichen Inseln');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (6,'Bau von industriellen Flughäfen auf künstlichen Inseln.', 'Bau von industriellen Flughäfen auf künstlichen Inseln');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (7,'Numerische Methoden in der Differentiellen Analysis', 'Numerische Methoden in der Differentiellen Analysis');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (8,'Numerische Methoden in der Geometrischen Algebra', 'Numerische Methoden in der Geometrischen Algebra');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (9,'Computeralgorithmen auf Graphen', 'Computeralgorithmen auf Graphen');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (10,'Computeralgorithmen in der Diskreten Mathematik', 'Computeralgorithmen in der Diskreten Mathmatik');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (11,'Statistische Prognosen in der Wahlanalyse', 'Statistische Prognosen in der Wahlanalyse');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (12,'Algorithmen in der Mustererkennung', 'Algorithmen in der Mustererkennung');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (13,'Gesichtsmerkmale in der Mustererkennung', 'Gesichtmerkmale in der Mustererkennung');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (14,'Generierung von Zufallszahlen', 'Generierung von Zufallszahlen');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (15,'Gleichverteilte Zufallszahlen in statistischen Algorithmen', 'Gleichverteilte Zufallszahlen in statistischen Algorithmen');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (16,'Codierungsverfahren', 'Codierungsverfahren');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (17,'Codes in der Gruppenalgebra', 'Codes in der Gruppenalgebra');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (18,'Kürzeste Wege in Graphen berechnen', 'Kürzeste Wege in Graphen berechnen');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (19,'Numerische Methoden für die Fourier Transformation', 'Numerische Methoden für die Fourier Transformation');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (20,'Infrastruktur im alpinen Gelände', 'Infrastruktur im alpinen Gelände');
INSERT INTO projects (PROJECT_ID, description, title) VALUES (21,'Tunnelbau im alpienen Gelände', 'Tunnelbau im alpinen Gelände');
 
 
-- endregion
 
-- region 2.1) ALTER TABLE, UPDATE
 
-- Aendern Sie den Namen der PROJECTS Tabelle zu PROJECTS_BT
 
-- Ein Projekt wird eindeutig identifiziert durch eine PROJECT_ID. Fuer Projekte
-- wird ein TITLE (varchar(200) - not null, unique), eine DESCRIPTION (varchar(4000))
-- und ein Datum CREATED_AT (DATE - not null) gespeichert.
 
-- Die Werte der CREATED_AT Spalte muessen vor dem 12.01.2020 liegen! Stellen
-- Sie eine entsprechende Pruefung sicher.
 
-- Verwenden Sie den ALTER TABLE und UPDATE Befehl um die PROJECTS Tabelle und die
-- entsprechenden Daten zu adaptieren.
 
 
 
-- endregion
 
-- region REQUEST_FUNDING_PROJECTS, RESEARCH_FUNDING_PROJECTS Ressourcen
 
create table research_funding_projects
(
    project_id       int not null,
    is_eu_sponsored  bit     not null,
    is_ffg_sponsored bit     not null,
    is_fwf_sponsored bit     not null,
    primary key (project_id),
    constraint fk_rfp_project_id foreign key (project_id)
        references projects (PROJECT_ID)
);
 
create table request_funding_projects
(
    project_id       int not null,
    is_small_project bit    not null,
    primary key (project_id),
    constraint fk_researchfp_project_id foreign key (project_id)
        references projects (PROJECT_ID)
);
 
-- endregion
 
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (0, 1, 0, 1);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (1, 1, 0, 3);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (0, 1, 1, 7);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (0, 1, 1, 8);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (0, 0, 1, 9);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (0, 0, 1, 10);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (1, 0, 0, 12);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (0, 0, 1, 13);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (1, 0, 0, 14);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (0, 0, 1, 15);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (0, 1, 0, 16);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (1, 1, 0, 17);
INSERT INTO research_funding_projects (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id) VALUES (0, 0, 1, 18);
 
INSERT INTO request_funding_projects (is_small_project, project_id) VALUES (0, 2);
INSERT INTO request_funding_projects (is_small_project, project_id) VALUES (0, 4);
INSERT INTO request_funding_projects (is_small_project, project_id) VALUES (0, 5);
INSERT INTO request_funding_projects (is_small_project, project_id) VALUES (0, 6);
INSERT INTO request_funding_projects (is_small_project, project_id) VALUES (0, 11);
INSERT INTO request_funding_projects (is_small_project, project_id) VALUES (1, 19);
INSERT INTO request_funding_projects (is_small_project, project_id) VALUES (0, 20);
INSERT INTO request_funding_projects (is_small_project, project_id) VALUES (0, 21);
 
-- endregion
 
-- region 2.2) CREATE TABLE
 
-- Es werden 2 Arten von Projekten unterschieden: RESEARCH_FUNDING_PROJECTS und
-- REQUEST_FUNDING_PROJECTS. Fuer Forschungsfoerderungsprojekte werden zusaetzlich die
-- folgenden Spalten gespeichert: IS_EU_SPONSORED (bit - not null), IS_FFG_SPONSORED
-- (bit - not null), IS_FWF_SPONSORED (bit - not null).
 
-- Fuer Forschungsfoerderungsprojekte muss sichergestellt werden dass zumindestens einer
-- der 3 SPONSERED Werte gesetzt ist.
 
-- Fuer Auftragsforschungsprojekte wird zusaetzlich IS_SMALL_PROJECT  (bit - not null)
-- gespeichert.
 
-- Wird ein PROJECTS Datensatz geloescht, muss der entsprechende REQUEST_FUNDING_PROJECTS
-- bzw. RESEARCH_FUNDING_PROJEJECTS Datensatz ebenfalls geloescht werden!
 
-- Listen Sie alle Constraints der PROJECTS, REQUEST_FUNDING_PROJECTS bzw. RESEARCH_FUNDING_PROJECTS
-- Tabelle auf. Welche der Constraints sind dafuer verantwortlich das die Beziehungen zwischen
-- den Tabellen von der Datenbank als 1:1 Beziehungen erkannt werden?
 
 
 
 
 
-- endregion
 
 
-- region 2.3) TRUNCATE TABLE
 
-- Loeschen Sie alle Projekte!
 
 
 
 
-- endregion
 
-- region 2.4) DROP TABLE
 
-- Loeschen Sie die folgenden Tabellen und Ressourcen: 
 
-- PROJECTS_BT
-- REQUEST_FUNDING_PROJECTS
-- RESEARCH_FUNDING_PROJECTS
 
 
 
 
 
 
-- endregion
 
 
-- ---------------------------------------------------------------------- -
-- 3. Beispiel) DML Befehle
-- ---------------------------------------------------------------------- -
-- region
 
-- Legen Sie die folgenden Tabellen an und befüllen Sie sie mit Beispieldaten.
 
CREATE TABLE countries (
    country_id   CHAR(2)      NOT NULL,
    country_name VARCHAR(50)  NOT NULL UNIQUE,
    PRIMARY KEY (country_id)
);
 
CREATE TABLE locations (
    location_id   INT NOT NULL,
    street_address VARCHAR(100),
    postal_code    VARCHAR(10),
    city           VARCHAR(50),
    country_id     CHAR(2),
    PRIMARY KEY (location_id),
    FOREIGN KEY (country_id) REFERENCES countries(country_id)
);
 
CREATE TABLE departments (
    department_id   INT NOT NULL,
    department_name VARCHAR(50) NOT NULL UNIQUE,
    location_id     INT,
    PRIMARY KEY (department_id),
    FOREIGN KEY (location_id) REFERENCES locations(location_id)
);
 
CREATE TABLE employees (
    employee_id   INT NOT NULL,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    salary        DECIMAL(10,2),
    department_id INT,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
 
INSERT INTO countries VALUES ('US', 'United States');
INSERT INTO countries VALUES ('UK', 'United Kingdom');
INSERT INTO countries VALUES ('DE', 'Germany');
 
INSERT INTO locations VALUES (100, '5th Avenue 1', '10001', 'New York', 'US');
INSERT INTO locations VALUES (200, 'Oxford Street 12', 'EC1A', 'London', 'UK');
INSERT INTO locations VALUES (300, 'Bahnhofstrasse 7', '10115', 'Berlin', 'DE');
 
INSERT INTO departments VALUES (10, 'IT', 100);
INSERT INTO departments VALUES (20, 'HR', 200);
INSERT INTO departments VALUES (30, 'Finance', 300);
 
INSERT INTO employees VALUES (1, 'John', 'Miller', 5500, 10);
INSERT INTO employees VALUES (2, 'Anna', 'Smith', 6200, 10);
INSERT INTO employees VALUES (3, 'Robert', 'Taylor', 4800, 20);
INSERT INTO employees VALUES (4, 'Julia', 'Krause', 7100, 30);
INSERT INTO employees VALUES (5, 'Tom', 'Williams', 3900, 10);
 
-- region 3.1) CREATE TABLE AS SELECT ...
 
-- Legen Sie die Tabelle EMPLOYEE_REPORTS an. Die Spalten entsprechen den 
-- Spalten der angegebenen Tabellen!
 
-- EMPLOYEE_REPORTS
--
-- EMPLOYEE_ID       EMPLOYEES
-- FIRST_NAME        EMPLOYEES
-- LAST_NAME         EMPLOYEES
-- SALARY            EMPLOYEES
-- DEPARTMENT_NAME   DEPARTMENTS
-- COUNTRY_NAME      COUNTRIES
 
 
 
 
 
 
 
 
-- endregion 
 
 
-- region 3.2) ALTER TABLE
 
-- Erweitern Sie die EMPLOYEE_REPORTS Tabelle um folgende Constraints:
 
-- * PRIMARY KEY (EMPLOYEE_ID)
-- * Definieren Sie die entsprechenden FOREIGN KEY Constraints!
 
 
 
 
 
-- endregion
 
 
-- region 3.3) ALTER TABLE, UPDATE
 
-- Erweitern Sie die EMPLOYEE_REPORTS Tabelle um folgende Spalten: 
 
-- VERSION int not null   (Initialwert: 0)
-- INCOME VARCHAR(20)            (Initialwert: 'DEFAULT')
 
-- @VERSION: Die Versionsspalte zeigt an wie oft ein Datensatz
--           geaendert wurde. Fuer jede Aenderung eines Datensatzes ist der
--           Wert um 1 zu aendern.
 
-- @INCOME: Bei einem Einkommen das hoeher als 6000 (EMPLOYEES->SALARY) ist
--          ist der Wert HIGH_INCOME einzutragen.
 
 
 
 
-- endregion
 
 
-- region 3.4) DELETE FROM
 
-- Loeschen Sie alle Angestellten die nicht in einer IT Abteilung arbeiten.
-- Hinweis: IT, IT Support, IT Helpdesk
 
 
 
 
 
-- endregion
 
 
-- region 3.5) UPDATE
 
-- Aendern Sie den Namen der IT Abteilung zu 'IT - Softwareengineering'.
 
 
 
-- endregion
 
 
-- region 3.6) DROP TABLE
 
-- Loeschen sie die EMPLOYEES_REPORTS Tabelle.
 
 
 
-- endregion