Create table filmy (
ID_filmu CHAR(6) Primary Key,
Tytul Varchar(60),
Kraj_produkcji Varchar(30),
Gatunek Varchar(15),
Cena Decimal(5,2)
);
Create table klienci (
pesel Char(11) Primary Key,
imie Char(15),
nazwisko Char(25)
);
Create table wypozyczenia (
Id_wyp Integer Primary Key Auto_Increment,
Data_wyp Date,
ID_filmu CHAR(6),
pesel Char(11),
foreign key (Id_filmu) references filmy(Id_filmu),
foreign key (pesel) references klienci(pesel)
);
1
SELECT gatunek, COUNT()
FROM filmy LEFT JOIN wypozyczenia ON filmy.ID_filmu = wypozyczenia.ID_filmu
GROUP BY gatunek
ORDER BY COUNT()
LIMIT 1;
2
SELECT ID_filmu, Tytul, Kraj_produkcji
FROM filmy
WHERE ID_filmu LIKE '%2008'
AND gatunek = 'wojenny'
ORDER BY Tytul ASC;
3
SELECT imie, nazwisko, SUM(filmy.cena)
FROM klienci
INNER JOIN wypozyczenia ON klienci.pesel = wypozyczenia.pesel
INNER JOIN filmy ON filmy.ID_filmu = wypozyczenia.ID_filmu
GROUP BY klienci.pesel
HAVING SUM(filmy.cena) 50;
4
SELECT Tytul, (
SELECT COUNT()FROM filmy
WHERE ID_filmu NOT IN (SELECT ID_filmu FROM wypozyczenia)
) AS ilosc
FROM filmy
WHERE ID_filmu NOT IN (SELECT ID_filmu FROM wypozyczenia);
5
SELECT SUBSTR(ID_filmu, 3) as rok, COUNT()
FROM filmy
WHERE SUBSTR(ID_filmu, 3) BETWEEN 2001 AND 2003
GROUP BY rok;
{"html5":"htmlmixed","css":"css","javascript":"javascript","php":"php","python":"python","ruby":"ruby","lua":"text\/x-lua","bash":"text\/x-sh","go":"go","c":"text\/x-csrc","cpp":"text\/x-c++src","diff":"diff","latex":"stex","sql":"sql","xml":"xml","apl":"apl","asterisk":"asterisk","c_loadrunner":"text\/x-csrc","c_mac":"text\/x-csrc","coffeescript":"text\/x-coffeescript","csharp":"text\/x-csharp","d":"d","ecmascript":"javascript","erlang":"erlang","groovy":"text\/x-groovy","haskell":"text\/x-haskell","haxe":"text\/x-haxe","html4strict":"htmlmixed","java":"text\/x-java","java5":"text\/x-java","jquery":"javascript","mirc":"mirc","mysql":"sql","ocaml":"text\/x-ocaml","pascal":"text\/x-pascal","perl":"perl","perl6":"perl","plsql":"sql","properties":"text\/x-properties","q":"text\/x-q","scala":"scala","scheme":"text\/x-scheme","tcl":"text\/x-tcl","vb":"text\/x-vb","verilog":"text\/x-verilog","yaml":"text\/x-yaml","z80":"text\/x-z80"}