Voucher z relační algebry 25/26

Random zápisky, úpravy welcome.

poradi random
bylo potřeba 14 bodů z 16

Schéma:
Band(_id_, name, foundingyear)
Musician(_id_, _name_, surname, professional)
BandMember(_idm, idb_)
idb in band.id; idm in musician.id

select max(foundingyear)
Band[FoundingYear] - (Band[FoundingYear<FY]Band<FoundingYear→FY>)[FoundingYear]

???
(Band<name→bname,id→idb>(FoundingYear=1960)*BandMember*Musician<name→mname,id→idm>)[bname, mname]

all attrs of bands with unique name
Band - (Band[id!=id2 and name=name2]Band<id→id2,name→name2,foundingyear→fy2>)[id, name, foundingyear]

musicians with the same name as surname or different name as surname
Musician
lmao

select name from band where id not in (select idb from bandmember)
aneb select jmena bands ktera nemaji membery
Band[Name] - (Band[id=idb]BandMember)[Name]

name and surname of musicians that appear more than once
(priserne zadana otazka, mysleno dvojice (name,surname) ktera nejsou unikatni)
(Musician[name=n2 and surname=s2 and id!=id2]Musician<name→n2,surname→s2,id→id2>)[name, surname]

select foundingyear from band where name='Beatles'
Band(name='Beatles')[foundingyear]

names of oldest bands
Band[name] - (Band[foundingyear>fy2]Band<foundingyear→fy2,name→n2>)[name]
(to name->n2 je tam aby nebyl konflikt (stejně pojmenované sloupce))

ids of musicians who play in all bands founded since 2000
(pry by se tu mohla hodit operace deleni, idfk)
(BandMember[idb=id]Band(FoundingYear>2000))[idm]
(joo, to moje vybere ty ktere hraji v NEJAKE nove band; my chceme membery kteri jsou cleni VSECH novych bands, ale byly tam špatný data takže fungovalo i to moje lol)

select name from band where name in (select name from musician)
aka select jmena ktera jsou zaroven jmeno kapely i hudebnika
(Band[name=mname]Musician<name→mname>)[name]
(opet, rename name->mname je potreba aby [name] bylo unikatni)
funguje treba band[name]*musician[name] nebo i musician[name] cap band[name]


groups where only professional musicians play (pozor ze bez name-nameb tak [] vybre musician.name :(((( )
Band - (Musician<id→idm>(professional=0)*BandMember*Band<id→idb,name→nameb>)[idb, nameb, foundingyear]

select distinct B.* from band b inner join bandmember on .=. inner join musician on .=. where m.professional = 1
(Band<id→idb>*BandMember*Musician<id→idm,name→namem>(professional=1))[idb, name, foundingyear]

bands where no professional musician plays (lol)
Band - (Band<id→idb>*BandMember*Musician<id→idm,name→namem>(professional=1))[idb,name,foundingyear]

name & surname of professional musicians without a band
(Musician - (Musician[id=idm]BandMember)[id, name, surname, professional])[name, surname]
(nejak mi tam chybi ten professional=1 filter lol)
alternativne: (wtf mam tam zavorku navic)
(Musician[name, surname] - (Musician[id=idm]BandMember)[name, surname]

name of bands where Rigo Starr worked
(Musician<name→namem,id→idb>(namem='Ringo' and surname='Starr')*BandMember*Band<id→idb>)[name]