/*Lösung zu Übung 3
  by Marc Landolt

 SQL Version: mysql  Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2
*/

/*---------------=================[[[[[ Datenbank anlegen ]]]]]=================---------------*/

DROP DATABASE IF EXISTS ue3;
CREATE DATABASE ue3;

USE ue3 ;

/*---------------=================[[[[[ Benutzer  anlegen ]]]]]=================---------------*/

DROP PROCEDURE IF EXISTS DropUserIfExists ;

DELIMITER $$
CREATE PROCEDURE DropUserIfExists(IN benutzer CHAR(16))
BEGIN
  DECLARE zaehler BIGINT DEFAULT 0 ;
  SELECT COUNT(*)
  INTO zaehler
    FROM `mysql`.`user`
      WHERE `User` = benutzer ;
  
  IF zaehler > 0 THEN 
         DROP USER `ue3` ;
  END IF;
END ;$$
DELIMITER ;

CALL DropUserIfExists("ue3") ;



CREATE USER ue3 IDENTIFIED BY "ue3";

/*---------------=================[[[[[ Berechtigungen vergeben ]]]]]=================---------------*/
GRANT ALL PRIVILEGES ON ue3.* TO `ue3`;

FLUSH PRIVILEGES;

USE ue3;

/*---------------=================[[[[[ Artikeltabelle erstellen und mit Daten abfüllen ]]]]]=================---------------*/
CREATE TABLE ART 
	(artikelnr VARCHAR(6) NOT NULL ,
	 mwstcd TINYINT(1),
	 ARTbez VARCHAR(255),
	 ARTlp DECIMAL(12,5),
	 ARTbst SMALLINT UNSIGNED ZEROFILL,	/*ohne vorzeichen*/
	 ARTminbst SMALLINT UNSIGNED,	/*ohne vorzeichen*/
	 ARTvpckng VARCHAR(255),
	 ARTlager CHAR(4),
	 ARTkannwgf SMALLINT,
	 ARTbstvorschlag SMALLINT,
	 ARTnbst DATE,
	 ARTnbstmenge SMALLINT,
     PRIMARY KEY(artikelnr) );

DESCRIBE ART;	/*Tabellenstruktur anzeigen*/

INSERT INTO ART values ("123456", "1", "Thinkpad T61p", "3499.95", "1", "1", "Einzeln Verpackt", "5", "FALSE", "1", CURDATE(), "5");
INSERT INTO ART values ("234567", "1", "ASUS EEE 901", "499.95", "1", "1", "Einzeln Verpackt", "5", "FALSE", "1", DATE("2008-01-01"), "5");
INSERT INTO ART values ("345678", "1", "HP Mini Note", "699.95", "1", "1", "Einzeln Verpackt", "5", "FALSE", "1", DATE("2008-06-01"), "5");

SELECT * FROM ART WHERE ARTbez IN ("Thinkpad T61p", "ASUS EEE 901", "HP Mini Note");
SELECT * FROM ART WHERE ARTbez LIKE ("%i%");

SELECT AVG (ARTlp) FROM ART;		/*DURCHSCHNITTSPREIS*/

/*---------------=================[[[[[ Bestellungstabelle erstellen und mit Daten abfüllen ]]]]]=================---------------*/

CREATE TABLE BST
	(bestellnr INT auto_increment,
	 kundennr INT,
	 BSTdatum DATE, /* default current_date(), 		WESHALB GEHT DAS NICHT*/
	/*BSTtimestamp TIMESTAMP default CURRENT_TIMESTAMP,	UND DAS HIER GEHT*/
	 BSTldatum DATE,
	 BSTrbetrag DECIMAL(12,5),
     PRIMARY KEY(bestellnr) );

DROP PROCEDURE IF EXISTS addStuff ;

DELIMITER $$
CREATE PROCEDURE bestellungenHinzufuegen()
BEGIN
  DECLARE zaehler BIGINT DEFAULT 0 ;
  WHILE zaehler < 10 DO
	INSERT INTO BST (kundennr, BSTdatum, BSTldatum, BSTrbetrag) VALUES ("0101", CURDATE(), DATE_ADD(CURDATE(), INTERVAL zaehler DAY), zaehler*100);
    SET zaehler = zaehler + 1;
  END WHILE;
END; $$
DELIMITER ;

CALL bestellungenHinzufuegen();


SELECT * FROM BST;

/*---------------=================[[[[[ Girokontotabelle erstellen und mit Daten abfüllen ]]]]]=================---------------*/

CREATE TABLE GIR
	(kundennr INT,
	 GIRinhaber VARCHAR(255),
	 GIRblz CHAR(9),
	 kontonr CHAR(9),
     PRIMARY KEY(kontonr) );

INSERT INTO GIR VALUES ("1", "Marc Landolt", "231", "667991.40F");
INSERT INTO GIR VALUES ("2", "Ursula Landolt", "231", "667992.40F");
INSERT INTO GIR VALUES ("3", "Dominik Landolt", "231", "667993.40F");

SELECT * FROM GIR;

/*---------------=================[[[[[ Kundentabelle erstellen und mit Daten abfüllen ]]]]]=================---------------*/

CREATE TABLE KND
	(kundennr INT auto_increment,
	 KNDstatus CHAR(2),
	 KNDname VARCHAR(100),
	 KNDstrasse VARCHAR(100),
	 KNDplz CHAR(10),
	 KNDort VARCHAR(100),
	 KNDlbest DATE,
	 KNDlwaktion DATE,
	 KNDzahlung CHAR(2),
      PRIMARY KEY(kundennr) );

INSERT INTO KND (KNDstatus, KNDname, KNDstrasse, KNDplz, KNDort, KNDlbest, KNDlwaktion, KNDzahlung) VALUES
	("W", "Marc Landolt", "Rombachtäli 13", "5022", "Rombach", DATE_SUB(CURDATE(), INTERVAL 1 MONTH), DATE_SUB(CURDATE(), INTERVAL 2 MONTH), "B");
INSERT INTO KND (KNDstatus, KNDname, KNDstrasse, KNDplz, KNDort, KNDlbest, KNDlwaktion, KNDzahlung) VALUES
	("W", "Ursula Landolt", "Irgendwo 13", "5123", "Agglo Aarau", DATE_SUB(CURDATE(), INTERVAL 1 MONTH), DATE_SUB(CURDATE(), INTERVAL 2 MONTH), "B");
INSERT INTO KND (KNDstatus, KNDname, KNDstrasse, KNDplz, KNDort, KNDlbest, KNDlwaktion, KNDzahlung) VALUES
	("W", "Dominik Landolt", "Telli", "5004", "Aarau", DATE_SUB(CURDATE(), INTERVAL 1 MONTH), DATE_SUB(CURDATE(), INTERVAL 2 MONTH), "B");
INSERT INTO KND (KNDstatus, KNDname, KNDstrasse, KNDplz, KNDort, KNDlbest, KNDlwaktion, KNDzahlung) VALUES
	("W", "Marc Landolt", "Rombachtäli 13", "5022", "Rombach", DATE_SUB(CURDATE(), INTERVAL 1 MONTH), DATE_SUB(CURDATE(), INTERVAL 2 MONTH), "B");

SELECT * FROM KND, GIR WHERE KND.kundennr=GIR.kundennr;

/*---------------=================[[[[[ PositionsTabelle erstellen und mit Daten abfüllen ]]]]]=================---------------*/

CREATE TABLE POS
	(bestellnr INT,
	 artikelnr VARCHAR(6),
	 POSmwst CHAR(10),
	 POSbstmenge INT,
	 POSlmenge INT,
	 POSgpreis DECIMAL(12,5),
      PRIMARY KEY(bestellnr, artikelnr) );

INSERT POS VALUES ("101", "123456", "7.5%", "1", "1", "3499.95");

SELECT * FROM ART,POS WHERE ART.artikelnr = POS.artikelnr;

/*---------------=================[[[[[ MWSTTabelle erstellen und mit Daten abfüllen ]]]]]=================---------------*/

CREATE TABLE MWST
	(mwstcd	TINYINT,
	 MWSTansatz CHAR(10),
	 MWSTtext VARCHAR(100),
       PRIMARY KEY(mwstcd) );

INSERT INTO MWST VALUES ("0", "0.0%", "befreit");
INSERT INTO MWST VALUES ("2", "3.0%", "3%");
INSERT INTO MWST VALUES ("1", "7.5%", "7.5%");

SELECT * FROM MWST;


/*INSERT INTO BST (kundennr, BSTdatum, 
/*Daten, Parameter und Passwörter in Gänsefüsschen sonst alles backquotes*/
/* Die Konstrukte IF, CASE, LOOP, WHILE, REPLACE ITERATE und LEAVE sind vollständig implementiert.