CREATE TABLE `t_persdat` ( `PersNr` int(11) NOT NULL auto_increment, `PersName` varchar(30) collate latin1_general_ci default NULL, `PersVorName` varchar(30) collate latin1_general_ci default NULL, `Geschlecht` char(1) collate latin1_general_ci default NULL, `AbtNr` int(11) NOT NULL default '0', `Eintritt` date default NULL, `Gehalt` float(10,2) default NULL, PRIMARY KEY (`PersNr`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=11 ; -- -- Daten für Tabelle `t_persdat` -- INSERT INTO `t_persdat` VALUES (1, 'Albertsen', 'Alfred', 'm', 10, '1998-04-01', 4500.32); INSERT INTO `t_persdat` VALUES (2, 'Behrend', 'Bern', 'm', 10, '2001-05-04', 5500.62); INSERT INTO `t_persdat` VALUES (3, 'Carstensen', 'Claus', 'm', 10, '2001-05-04', 4935.16); INSERT INTO `t_persdat` VALUES (4, 'Diedrichs', 'Dirk', 'm', 10, '1999-04-21', 6509.02); INSERT INTO `t_persdat` VALUES (5, 'Eggers', 'Eleonore', 'w', 10, '2000-07-12', 5551.72); INSERT INTO `t_persdat` VALUES (6, 'Freitag', 'Felicitas', 'w', 10, '2001-12-01', 4512.32); INSERT INTO `t_persdat` VALUES (7, 'Gottlich', 'Gerda', 'w', 14, '2002-01-27', 3512.30); INSERT INTO `t_persdat` VALUES (8, 'Hanspach', 'Herma', 'w', 14, '2003-03-01', 3517.62); INSERT INTO `t_persdat` VALUES (9, 'Immbach', 'Ilka', 'w', 10, '2003-04-01', 3912.32); Aufgabe 3 SELECT * FROM t_persdat WHERE `AbtNr` = 10 Aufgabe 4 SELECT * FROM t_persdat WHERE `Eintritt` > '01.11.2001' Aufgabe 5 SELECT * FROM t_persdat WHERE `Gehalt` > 4500.00 AND `AbtNr` = 10 Aufgabe 6 SELECT * FROM t_persdat WHERE `Geschlecht` = 'm' AND (`Gehalt` > 5000.00 OR `Eintritt` < '2000-11-01') Aufgabe 7 SELECT * FROM t_persdat WHERE Gehalt = (SELECT MAX(Gehalt) FROM t_persdat) Aufgabe 8a SELECT COUNT(PersNr) AS ZEILENANZAHL FROM t_persdat Aufgabe 8b SELECT Geschlecht, COUNT(Geschlecht) FROM t_persdat GROUP BY Geschlecht Aufgabe 9 SELECT AVG(Gehalt) AS Durchschnittsgehalt FROM t_persdat Aufgabe 10a SELECT sum( Gehalt ) / ( count( * ) ) Durchschnittsgehalt FROM t_persdat auf 2 Stellen Gerundet SELECT round( sum( Gehalt ) / ( count( * ) ) , 2 ) Durchschnittsgehalt FROM t_persdat Aufgabe 10b nein Aufgabe 11a SELECT 4.5 *2 AS Lösung Aufgabe 11b SELECT PersName, Gehalt, Gehalt * 1.035 AS Neues_Gehalt FROM t_persdat Gerundet auf 2 Stellen SELECT PersName, Gehalt, Round( Gehalt * 1.035, 2 ) AS Neues_Gehalt FROM t_persdat Aufgabe 12 SELECT Datediff("2005-01-01", "2004-01-01") oder SELECT date_add( "2004-02-28", INTERVAL 1 DAY ) Aufgabe 13 SELECT dayofweek( Eintritt ) FROM t_persdat WHERE PersName = 'Diedrichs' Aufgabe 14 SELECT DateDiff((SELECT (Eintritt)FROM t_persdat WHERE PersName = 'Freitag'), (SELECT (Eintritt)FROM t_persdat WHERE PersName = 'Behrend')) AS Zeitabstand Aufgabe 15 SELECT PersName, length( PersName ) AS Buchstaben FROM t_persdat Aufgabe 16 SELECT sqrt( 625 ) AS Ergebnis Aufgabe 17 UPDATE t_persdat SET Gehalt=NULL Where PersNr=5 SELECT PersName FROM t_persdat where ISNULL(Gehalt) Aufgabe 18 SELECT PersName FROM t_persdat WHERE PersName LIKE '%tag' Aufgabe 19 SELECT PersName FROM t_persdat WHERE PersName LIKE 'B%' AND Eintritt BETWEEN "2001-01-01" AND "2001-12-31" Aufgabe 20 SELECT PersName FROM t_persdat WHERE PersName LIKE 'B%' AND year(Eintritt) IN (2001) Aufgabe 21a --hinzufügen: ALTER TABLE t_persdat ADD Spalte8 int --löschen: ALTER TABLE t_persdat DROP Spalte8 Aufgabe 21b ALTER TABLE t_persdat ADD PRIMARY KEY (PersNr) Aufgabe 21c ALTER TABLE t_persdat DROP PRIMARY KEY Aufgabe 22 SELECT PersName, AbtNr, Gehalt FROM t_persdat WHERE Gehalt >= ALL (SELECT Gehalt FROM t_persdat WHERE AbtNr = 14) Aufgabe 23 SELECT PersName, AbtNr, Gehalt FROM t_persdat WHERE Gehalt >= ANY (SELECT Gehalt FROM t_persdat WHERE AbtNr = 14) Aufgabe 24 CREATE TABLE copy_t_persdat SELECT * FROM t_persdat Aufgabe 25 INSERT INTO t_persdat2( PersName, PersVorName, Geschlecht, AbtNr, Eintritt, Gehalt ) SELECT PersName, PersVorName, Geschlecht, AbtNr, Eintritt, Gehalt FROM t_persdat Aufgabe 26 Delete from t_persdat2 (*) where PersNr = 15 DELETE FROM t_persdat2 Aufgabe 27 CREATE TABLE t_fammitglieder( `FamNr` int( 11 ) NOT NULL AUTO_INCREMENT , `PersNr` int( 11 ) NOT NULL , `FamName` varchar( 30 ) COLLATE latin1_general_ci default NULL , `Vorname` varchar( 30 ) COLLATE latin1_general_ci default NULL , `VWgrad` char( 1 ) COLLATE latin1_general_ci default NULL , `Zuschuss` int( 11 ) NOT NULL default '0', PRIMARY KEY ( `FamNr` ), FOREIGN KEY ( PersNr ) REFERENCES t_persdat( PersNr ) ) TYPE = InnoDB -- -- Daten für Tabelle `t_fammitglieder` -- INSERT INTO `t_fammitglieder` VALUES (1,2,'Behrend','Birgit', 'f', 50); INSERT INTO `t_fammitglieder` VALUES (2,5,'Eggers','Emil', 'm', 40); INSERT INTO `t_fammitglieder` VALUES (3,5,'Eggers','Erika', 't', 20); INSERT INTO `t_fammitglieder` VALUES (4,5,'Eggers','Elfi', 't', 20); INSERT INTO `t_fammitglieder` VALUES (5,6,'Fischbach', 'Felice', 't', 20); INSERT INTO `t_fammitglieder` VALUES (6,8, 'Hanspach', 'Hinnerk', 's', 30); Aufgabe 28 Inner-Join Vers. I SELECT t_persdat.PersNr, PersName, t_fammitglieder.FamName, FamNr FROM t_persdat, t_fammitglieder WHERE t_persdat.PersNr = t_fammitglieder.PersNr Inner-Join Vers. II SELECT t_persdat.PersNr, PersName, t_fammitglieder.FamName, FamNr FROM t_persdat INNER JOIN t_fammitglieder ON t_persdat.PersNr = t_fammitglieder.PersNr Outer-Left-Join SELECT t_persdat.PersNr, PersName, t_fammitglieder.FamName, FamNr FROM t_persdat LEFT JOIN t_fammitglieder ON t_persdat.PersNr = t_fammitglieder.PersNr Outer-Right-Join SELECT t_persdat.PersNr, PersName, t_fammitglieder.FamName, FamNr FROM t_persdat RIGHT JOIN t_fammitglieder ON t_persdat.PersNr = t_fammitglieder.PersNr Aufgabe 29 SELECT p.PersNr, PersName, PersVorName, f.FamName, Vorname, VWgrad, Zuschuss FROM t_persdat p INNER JOIN t_fammitglieder f ON p.PersNr = f.PersNr Aufgabe 30 SELECT p.PersNr, PersName, PersVorName, f.FamName, Vorname, VWgrad, Zuschuss FROM t_persdat p LEFT JOIN t_fammitglieder f ON p.PersNr = f.PersNr Ergänzung SELECT p.PersNr, PersName, PersVorName, f.FamName, Vorname, VWgrad, Zuschuss FROM t_persdat p LEFT JOIN t_fammitglieder f ON p.PersNr = f.PersNr WHERE f.famname is Null Aufgabe 31 SELECT p.PersName, PersVorName, f.FamName, Vorname, VWgrad FROM t_persdat p INNER JOIN t_fammitglieder f ON p.PersNr = f.PersNr WHERE p.PersName != f.FamName Aufgabe 32 SELECT p.PersName, PersVorName, f.FamName, Vorname, VWgrad FROM t_persdat p INNER JOIN t_fammitglieder f ON p.PersName = f.FamName WHERE p.PersNr =2 Aufgabe 33 CREATE TABLE t_abteilung( AbtNr int(11) PRIMARY KEY, AbtName varchar(30), LeiterPersNr int(11) )TYPE=InnoDB Aufgabe 34 INSERT INTO t_abteilung VALUES (10,'Verkauf',4); INSERT INTO t_abteilung VALUES (14,'Reparatur',8); Vorbereitung für Aufgabe 36 zeigt die Eigenschaften der Tabelle z.B. den Namen der Schlüssel (Foreign Key): SCHOW CREATE TABLE t_persdat Löscht den Foreign Key: ALTER TABLE t_persdat DROP FOREIGN KEY FK_t_persdat Aufgabe 35 der Schlüssel heisst key1: ALTER TABLE t_persdat ADD CONSTRAINT key1 FOREIGN KEY(abtnr)references t_abteilung(abtnr) Aufgabe 36 SELECT t_abteilung.AbtName, t_persdat.persname, t_fammitglieder.VWgrad, FamName FROM t_fammitglieder INNER JOIN ( t_persdat INNER JOIN t_abteilung ON t_persdat.abtNr = t_abteilung.abtNr ) ON t_persdat.persNr = t_fammitglieder.persnr WHERE t_fammitglieder.VWgrad = "f" OR t_fammitglieder.VWgrad = "m"