Data Definition Language

CREATE SCHEMA hollywood;CREATE TABLE hollywood.films (title text, release date, awardstext[]);

CREATE TABLE personen(ID INTEGER PRIMARY KEY,Name CHAR(30) NOT NULL,Abteilung VARCHAR(5),Telefonnummer TEXT, Postleitzahl NUMERIC,Name_des_Wohnorts CHAR(30),Jahr_der_Einstellung DATE REFERENCES Abteilung); //secondary Key

Data Manipulation Language

insert into Vorlesungen (VorlNr, Titel, SWS, gelesenVon)values (5099, `Methaphysik, 4, 2137);

insert into hrenselect MatrNr, VorlNrfrom Studenten, Vorlesungenwhere Titel= `Logik ;

update Studenten set Semester= Semester + 1;
delete

Data Query Language

select

select distinct ..
SELECT a, a*100/c AS percentage
SELECT a "given as an A"
COUNT(*)with wildcard counts every line orCOUNT(name)counts every filled line,COUNT(distinct name)`

sort

select name from personen order by Rang desc,Name asc

Join

FROM (student s JOIN personen h ON (s.Matr_nr=h.Matr_nr and s.Name='Lovelace')
students JOIN lectures v USING Vorl_nr
students NATURAL JOIN lectures
students LEFT JOIN lectures also students without a lecture are in table

UNION, INTERSECT, EXCEPT

find all: select name from assistent) union (select name from professors)

LIKE

wildcard "%" ; ""
"%" any amound
"" exactly one char
where title like '%thik%';

notin, all, ect

ect: 'avg(age), max(age), min(age), count(*), sum(meter)`

where PersNr not in (select gelesenVon from Vorlesungen );
where Semester >= all ( select Semester from Studenten);
same as: where Semester >= ( select max(Semester) from Studenten);
`

group by


select gelesenVon, Name, avg(SWS)
from Vorlesungen, Professoren
where gelesenVon = PersNr
group by gelesenVon, Name
having avg(SWS) >= 3;


Query in FROM

FROM(SELECT * FROM Studenten s JOIN hoeren h USING (Matr_nr)
WHERE s.Name='Lovelace') lovelace_hoert`
has to have a name

declutter querys

With alias_name1 as(select ...from... where...),alias_name2 as(select ...from... where...)'

select * from Studenten where Semester between 1 and 4;
select * from Studenten where Semester in (1,2,3,4);

select MatrNr, (case when Note<1.5 then sehr gutwhen Note<2.5 then gutwhen Note<3.5 then befriedigendwhen Note <= 4 then ausreichendelse Nicht bestanden end)from pruefen;

select s.MatrNr, s.Name, avg(Note) as persDurchschnitt,(select avg(note) as Gesamtsschnitt from pruefen p)from Studenten s, pruefen pwhere s.MatrNr=p.MatrNrgroup by s.MatrNr, Namehaving persDurchschnitt < select avg(note) from pruefen p