SQL Cheet Sheat

Data Definition Language

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

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 hören
select MatrNr, VorlNr
from Studenten, Vorlesungen
where 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 gut‘
when Note<2.5 then ‘gut‘
when Note<3.5 then ‘befriedigend‘
when Note <= 4 then ‘ausreichend‘
else ‘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 p
where s.MatrNr=p.MatrNr
group by s.MatrNr, Name
having persDurchschnitt < select avg(note) from pruefen p