Dbs1 Cheat Sheet by tarinya

DELETE FROM angestellter WHERE persNr=1100;
INSERT INTO Abteilung (Name, AbtrNr) VALUES ('Entwicklung', 20);
UPDATE test SET name = "a" WHERE id=1;

SELECT PZ.PersNr, proj.bezeichnung, Zeitanteil, ang.Name FROM projektZuteilung AS PZ
INNER JOIN angestellter AS ang ON ang.PersNr = PZ.PersNr
INNER JOIN projekt AS proj ON PZ.ProjNr=proj.ProjNr
WHERE (PZ.ProjNr=25) OR (PZ.ProjNr=30)
ORDER BY proj.ProjNr, ang.Name;

SELECT Name FROM Angestellter A WHERE EXISTS
( SELECT * FROM ProjektZuteilung WHERE PersNr = A.PersNr );

SELECT ang.Name, Salaer
FROM Angestellter as ang INNER JOIN Abteilung as abt ON abt.AbtNr=ang.AbtNr WHERE abt.Name='Entwicklung' AND Salaer =
( SELECT MIN (Salaer)
FROM Angestellter as ang INNER JOIN Abteilung as abt ON abt.AbtNr=ang.AbtNr WHERE abt.Name='Entwicklung');

SELECT MAX( Salaer ) FROM Angestellter;
SELECT MIN( Salaer ) FROM Angestellter;
SELECT AVG( Salaer ) FROM Angestellter;
SELECT SUM( Salaer ) AS "Salaersumme" FROM Angestellter; SELECT SELECT name, COUNT(projnr) from projektzuteilung inner join angestellter on projektzuteilung.persnr = angestellter.persnr group by name;

CREATE ROLE user WITH LOGIN PASSWORD 'pw';
ALTER ROLE user CREATEROLE, CREATEDB;
DROP ROLE AngProj;

//alle namen mit salär&differenz zum nächsten
SELECT name, salaer, (salaer - lead(salaer, 1) OVER(ORDER BY salaer desc)) AS "differenz" FROM angestellter ORDER BY 2 DESC LIMIT 5;
//alle Vor-&Nachnamen mit ihrer Anzahl
SELECT nachname, vorname, COUNT(*) OVER (PARTITION BY vorname) AS Anzahl FROM person;

WITH angestelltemitprojekten AS (
  SELECT a.name, proj.bezeichnung
  FROM angestellter a
  JOIN projzut pz ON a.persnr=pz.persnr
  JOIN projekt proj ON pz.projnr=proj.projnr
)
SELECT * FROM angestelltemitprojekten;

CREATE VIEW AngPublic (Persnr, Name, Tel, Wohnort) AS
SELECT Persnr, Name, Tel, Wohnort FROM Angestellter;
SELECT * FROM AngPublic ORDER BY Name;
DROP VIEW AngPublic;

BEGIN ISOLATION LEVEL SERIALIZABLE;
SAVEPOINT one;
ROLLBACK TO one;
COMMIT;

Tabellenname (id INTEGER PK,
name TEXT(20) NOT NULL,
abteilung NOT NULL REFERENCES abteilung);