SELECT concat(Prijmeni,", ",Jmeno) AS Zamestnanec, DatumObjednavky, Mezisoucet
FROM Zamestnanci JOIN Objednavky USING (CisloZamestnance)
WHERE DatumObjednavky>="1998-05-01";
SELECT Firma AS Dodavatel,NazevVyrobku AS Vyrobek
FROM Dodavatele JOIN Vyrobky USING (CisloDodavatele)
ORDER BY 1,2
LIMIT 20;
SELECT Firma AS Prepravce, CisloObjednavky
FROM Objednavky JOIN Prepravci ON (Preprava=CisloPrepravce)
WHERE DatumObjednavky>="1998-05-01";
SELECT NazevVyrobku,DatumObjednavky,Mnozstvi
FROM Vyrobky JOIN RozpisObjednavek USING (CisloVyrobku) JOIN Objednavky USING (CisloObjednavky)
LIMIT 30;
SELECT DISTINCT NazevVyrobku,ZemePrijemce
FROM Vyrobky JOIN RozpisObjednavek USING (CisloVyrobku) JOIN Objednavky USING (CisloObjednavky)
ORDER BY 1,2
LIMIT 30;
SELECT DISTINCT concat(Prijmeni,", ",Jmeno) AS Zamestnanec, Zeme
FROM Zamestnanci JOIN Objednavky USING (CisloZamestnance) JOIN Zakaznici USING (KodZakaznika)
ORDER BY 1,2
LIMIT 30;
SELECT DISTINCT Firma
FROM Prepravci JOIN Objednavky ON (Preprava=CisloPrepravce) JOIN Zakaznici z USING (KodZakaznika)
WHERE z.Firma="Around the Horn";
SELECT Firma, Mezisoucet
FROM Objednavky JOIN Zakaznici USING (KodZakaznika)
ORDER BY 1,2 DESC
LIMIT 30;
SELECT Firma
FROM Objednavky JOIN Zakaznici USING (KodZakaznika)
WHERE YEAR(DatumObjednavky)=1998
ORDER BY Mezisoucet DESC
LIMIT 1;
SELECT NazevVyrobku
FROM Vyrobky JOIN Kategorie USING (CisloKategorie)
WHERE Nazevkategorie="Nápoje"
ORDER BY JednotkovaCena DESC
LIMIT 1;
SELECT YEAR(DatumObjednavky) AS rok,MONTH(DatumObjednavky) AS mesic
FROM Vyrobky JOIN RozpisObjednavek USING (CisloVyrobku) JOIN Objednavky USING (CisloObjednavky)
WHERE NazevVyrobku="Côte de Blaye"
ORDER BY 1,2
LIMIT 1;
SELECT CisloZamestnance, sum(Mezisoucet) AS CelkemTrzba
FROM Objednavky
GROUP BY CisloZamestnance;
SELECT KodZakaznika, sum(Mezisoucet) AS CelkemNakup, avg(Mezisoucet) AS prumernyNakup, max(Mezisoucet) AS nejvetsiNakup
FROM Objednavky
GROUP BY KodZakaznika;
SELECT KodZakaznika,sum(Mezisoucet) AS Nakup
FROM Objednavky
WHERE DatumObjednavky>="1998-05-01"
GROUP BY KodZakaznika;
SELECT Zemeprijemce,sum(mezisoucet) AS Trzba
FROM Objednavky
WHERE CisloZamestnance=1
GROUP BY Zemeprijemce;
takto je to hloupé:
SELECT Prijmeni,Jmeno, sum(Mezisoucet) AS Trzba
FROM Zamestnanci JOIN Objednavky USING (CisloZamestnance)
WHERE DatumObjednavky>="1998-05-01"
GROUP BY Prijmeni,Jmeno ;
lépe takto:
SELECT Prijmeni,Jmeno, sum(Mezisoucet) AS Trzba
FROM Zamestnanci JOIN Objednavky USING (CisloZamestnance)
WHERE DatumObjednavky>="1998-05-01"
GROUP BY CisloZamestnance;
SELECT ZemePrijemce, YEAR(DatumObjednavky)as rok, sum(Mezisoucet) AS Trzba
FROM Objednavky
GROUP BY 1,2;
SELECT Prijmeni,Jmeno, sum(Mezisoucet) AS Trzba
FROM Zamestnanci JOIN Objednavky USING (CisloZamestnance)
WHERE DatumObjednavky>="1998-05-01"
GROUP BY CisloZamestnance
HAVING Trzba>100000;
SELECT sum(Mezisoucet), COUNT(*) FROM Objednavky;
SELECT Firma, min(datumObjednavky)
FROM Objednavky JOIN Zakaznici USING (KodZakaznika)
GROUP BY KodZakaznika;
SELECT NazevVyrobku,sum(VyslednaCena) AS trzba
FROM Vyrobky JOIN RozpisObjednavek USING (CisloVyrobku)
GROUP BY CisloVyrobku;
SELECT Firma AS Dodavatel, sum(VyslednaCena) AS Trzba
FROM Dodavatele JOIN Vyrobky USING (CisloDodavatele) JOIN RozpisObjednavek USING (CisloVyrobku)
GROUP BY CisloDodavatele;
SELECT Firma AS Dodavatel,NazevKategorie AS Kategorie,sum(VyslednaCena) AS Trzba
FROM Kategorie JOIN (Dodavatele JOIN Vyrobky USING (CisloDodavatele) JOIN RozpisObjednavek USING (CisloVyrobku)) USING (CisloKategorie)
GROUP BY CisloDodavatele, CisloKategorie;
SELECT Firma, sum(Mezisoucet) AS nakup
FROM Objednavky JOIN Zakaznici USING (KodZakaznika)
GROUP BY KodZakaznika
ORDER BY 2 desc
LIMIT 10;
SELECT Firma, sum(Mezisoucet) AS Nakup
FROM Objednavky JOIN Zakaznici USING (KodZakaznika)
GROUP BY KodZakaznika
HAVING nakup>1000000;
SELECT Firma AS dodavatel,sum(VyslednaCena) AS trzba,COUNT(*) AS x
FROM Dodavatele JOIN Vyrobky USING (CisloDodavatele) JOIN RozpisObjednavek USING (CisloVyrobku)
GROUP BY CisloDodavatele;
SELECT Firma, sum(Mezisoucet) AS nakup,COUNT(*) AS pocetObjednavek
FROM Objednavky JOIN Zakaznici USING (KodZakaznika)
GROUP BY KodZakaznika
HAVING pocetObjednavek>5
ORDER BY 3 DESC;
SELECT Zeme,COUNT(*), COUNT(fax), COUNT(distinct Mesto) FROM Zakaznici GROUP BY Zeme;
SELECT ZemePrijemce, COUNT(*) FROM Objednavky GROUP BY 1 ORDER BY 2 DESC;
SELECT ZemePrijemce, COUNT(*) FROM Objednavky GROUP BY 1 HAVING COUNT(*)>50;
SELECT NazevKategorie AS kategorie, Max(JednotkovaCena) nejvyssicena, avg(JednotkovaCena) prumernaCena, COUNT(*) AS pocetVyrobku
FROM Kategorie JOIN Vyrobky USING (CisloKategorie)
GROUP BY Cislokategorie
ORDER BY 4 desc;
SELECT Zeme,COUNT(*) pocetZak,COUNT(Fax) pocetFaxu, COUNT(distinct Mesto) pocetMest
FROM Zakaznici GROUP BY Zeme;
SELECT Zeme FROM Zakaznici GROUP BY Zeme HAVING COUNT(Fax) <
COUNT(*);
(Takto to jde v MySQL:)
SELECT Firma zak, COUNT(*) pocetObjed
FROM Zakaznici JOIN Objednavky USING (KodZakaznika)
GROUP BY KodZakaznika;
(Takto je to podle standardu - např v Oracle:)
SELECT KodZakaznika kod,Firma zak, COUNT(*) pocetObjed
FROM Zakaznici JOIN Objednavky USING (KodZakaznika)
GROUP BY KodZakaznika,Firma;
SELECT Year(DatumObjednavky), COUNT(*) pocetOb
FROM Objednavky
GROUP BY Year(DatumObjednavky);
SELECT DATEDIFF(DodatDne,DatumObjednavky) lhuta , COUNT(*) pocetOb
FROM Objednavky
GROUP BY lhuta;
SELECT MIN(DatumObjednavky) odkdy,NazevVyrobku, COUNT(*) pocetObj
FROM Objednavky JOIN RozpisObjednavek USING (CisloObjednavky) JOIN Vyrobky USING (CisloVyrobku)
GROUP BY CisloVyrobku
ORDER BY 2;
SELECT z1.Mesto, Firma zak, CONCAT(Prijmeni,", ",Jmeno) zam
FROM Zakaznici z1, Zamestnanci z2
WHERE z1.Mesto=z2.Mesto
ORDER BY 1;
SELECT z1.Mesto, COUNT(DISTINCT KodZakaznika) zakazniku,COUNT(DISTINCT CisloZamestnance) zamestancu
FROM Zakaznici z1, Zamestnanci z2
WHERE z1.Mesto=z2.Mesto
GROUP BY 1;
SELECT NazevVyrobku, CAST(AVG(Mnozstvi) AS DECIMAL(10,2)) Prumerne
FROM RozpisObjednavek JOIN Vyrobky USING (CisloVyrobku)
GROUP BY CisloVyrobku;
SELECT Firma Zakaznik, Nazevkategorie Kategorie, COUNT(DISTINCT CisloVyrobku) PocetDruhu
FROM Kategorie JOIN Vyrobky USING (CisloKategorie) JOIN RozpisObjednavek USING (CisloVyrobku) JOIN Objednavky USING (CisloObjednavky) JOIN Zakaznici USING (KodZakaznika)
GROUP BY KodZakaznika,CisloKategorie
LIMIT 30;
SELECT SUM(JednotkyNaSklade*JednotkovaCena) sklad, Firma dodavatel
FROM Dodavatele JOIN Vyrobky USING (CisloDodavatele)
GROUP BY CisloDodavatele
ORDER BY 1 DESC;
SELECT Firma dopravce, COUNT(DISTINCT ZemePrijemce) pocetZemi, SUM(Dopravne) celkemDopravne
FROM Prepravci JOIN Objednavky ON (CisloPrepravce=Preprava)
GROUP BY CisloPrepravce;
SELECT Firma
FROM Zakaznici JOIN Objednavky USING (Kodzakaznika)
GROUP BY Kodzakaznika
HAVING COUNT(*)<4;
SELECT NazevVyrobku
FROM Vyrobky JOIN RozpisObjednavek USING (CisloVyrobku) JOIN Objednavky USING (CisloObjednavky)
WHERE DatumObjednavky>="1998-01-01"
GROUP BY CisloVyrobku
HAVING sum(VyslednaCena)>500000;
SELECT NazevVyrobku,SUM(VyslednaCena) Trzba
FROM Vyrobky JOIN RozpisObjednavek USING (CisloVyrobku) JOIN Objednavky USING (CisloObjednavky)
WHERE DatumObjednavky>="1998-01-01"
GROUP BY CisloVyrobku
HAVING Trzba>500000
ORDER BY 2 DESC;
SELECT firma
FROM Zakaznici z LEFt JOIN Objednavky USING (KodZakaznika)
WHERE CisloObjednavky IS NULL;
SELECT DISTINCT Zeme
FROM Zakaznici LEFT JOIN Objednavky USING (KodZakaznika)
WHERE DatumObjednavky>="1998-04-01"
AND CisloObjednavky IS NULL;
)
Takto to lze:
SELECT DISTINCT Zeme
FROM Zakaznici LEFT JOIN (SELECT DISTINCT KodZakaznika FROM Objednavky WHERE DatumObjednavky>="1998-04-01") A USING (KodZakaznika)
WHERE A.KodZakaznika IS NULL;
nebo takto:
SELECT DISTINCT Zeme
FROM Zakaznici
WHERE KodZakaznika NOT IN (SELECT DISTINCT KodZakaznika FROM Objednavky WHERE DatumObjednavky>="1998-04-01");
nebo ještě jinak (ale tak je to méně efektivní):
SELECT DISTINCT Zeme
FROM Zakaznici z
WHERE NOT EXISTS (SELECT * FROM Objednavky o WHERE DatumObjednavky>="1998-04-01" AND o.KodZakaznika=z.KodZakaznika);
SELECT NazevVyrobku
FROM Vyrobky
WHERE JednotkovaCena>(SELECT AVG(JednotkovaCena) FROM Vyrobky);
SELECT Firma
FROM Zakaznici JOIN Objednavky USING (KodZakaznika)
WHERE Zeme="USA"
GROUP BY Kodzakaznika
HAVING sum(Mezisoucet)=
(
SELECT MAX(A.nakup) FROM
(SELECT SUM(Mezisoucet) nakup FROM Zakaznici JOIN Objednavky USING (KodZakaznika) WHERE Zeme="USA" GROUP BY Kodzakaznika) A
);
Takto to jde samozřejmě jednodušeji:
SELECT Firma, SUM(Mezisoucet) nakup
FROM Zakaznici JOIN Objednavky USING (Kodzakaznika)
WHERE Zeme="USA"
GROUP BY Kodzakaznika
ORDER BY 2 DESC
LIMIT 1;
SELECT Zeme,Firma
FROM Zakaznici z JOIN Objednavky USING (Kodzakaznika)
GROUP BY Kodzakaznika
HAVING sum(Mezisoucet)>=ALL
(SELECT SUM(Mezisoucet)
FROM Zakaznici z1 JOIN Objednavky USING (Kodzakaznika)
WHERE z1.Zeme=z.Zeme
GROUP BY Kodzakaznika);
Jinak:
CREATE TEMPORARY TABLE POM SELECT Firma, Zeme, SUM(Mezisoucet) nakup
FROM Zakaznici z JOIN Objednavky USING (Kodzakaznika)
GROUP BY KodZakaznika;
CREATE TEMPORARY TABLE POM2
SELECT Zeme,MAX(nakup) nejvic FROM POM GROUP BY Zeme;
SELECT Zeme, Firma
FROM POM JOIN POM2 USING (Zeme)
WHERE POM.nakup=POM2.nejvic;
Takto to v MySQL nejde, ale některé SŘBD to zvládnou:
SELECT A.ZemePrijemce,
(
SELECT Firma
FROM Zakaznici JOIN Objednavky USING (KodZakaznika)
WHERE ZemePrijemce=A.ZemePrijemce
GROUP BY ZemePrijemce, Firma
ORDER BY Sum(O.Mezisoucet) DESC
LIMIT 1
) AS NejvetsiZakaznik
FROM (SELECT DISTINCT ZemePrijemce FROM Objednavky) AS A;
Toto MySQL neumí optimalizovat:
SELECT DISTINCT Firma
FROM Zakaznici JOIN Objednavky USING (KodZakaznika) JOIN RozpisObjednavek USING (CisloObjednavky)
WHERE DatumObjednavky>="1998-01-01"
AND CisloVyrobku IN
(SELECT CisloVyrobku
FROM RozpisObjednavek JOIN Objednavky USING (CisloObjednavky)
WHERE DatumObjednavky>="1998-01-01"
GROUP BY CisloVyrobku
HAVING sum(VyslednaCena)>500000);
takže to uděláme takto:
CREATE TEMPORARY TABLE A SELECT CisloVyrobku
FROM RozpisObjednavek JOIN Objednavky USING (CisloObjednavky)
WHERE DatumObjednavky>="1998-01-01"
GROUP BY CisloVyrobku
HAVING sum(VyslednaCena)>500000;
SELECT DISTINCT Firma
FROM Zakaznici JOIN Objednavky USING (KodZakaznika) JOIN RozpisObjednavek USING (CisloObjednavky)
WHERE DatumObjednavky>="1998-01-01"
AND CisloVyrobku IN (SELECT CisloVyrobku FROM A);
Optimálnější je toto:
CREATE TEMPORARY TABLE B
SELECT CisloVyrobku,KodZakaznika,VyslednaCena
FROM RozpisObjednavek JOIN Objednavky USING (CisloObjednavky)
WHERE DatumObjednavky>="1998-01-01";
CREATE TEMPORARY TABLE C SELECT CisloVyrobku
FROM B
GROUP BY CisloVyrobku
HAVING sum(VyslednaCena)>500000;
SELECT DISTINCT Firma
FROM Zakaznici JOIN B USING (KodZakaznika)
WHERE CisloVyrobku IN (SELECT CisloVyrobku FROM C);
Takto by to bylo špatně:
SELECT Firma,DatumObjednavky,Mezisoucet
FROM Zakaznici LEFT JOIN Objednavky USING (KodZakaznika)
WHERE DatumObjednavky>="1998-04-01";
I takto by to bylo špatně:
SELECT Firma
FROM Zakaznici z LEFT JOIN Objednavky USING (KodZakaznika)
GROUP BY z.KodZakaznika
HAVING MAX(DatumObjednavky)<"1998-04-01";
Takto je to správně:
SELECT Firma, DatumObjednavky,Mezisoucet
FROM Zakaznici LEFT JOIN
(SELECT KodZakaznika, DatumObjednavky,Mezisoucet FROM Objednavky WHERE DAtumObjednavky>="1998-04-01") O1
USING (KodZakaznika);
SELECT KontaktniOsoba AS jmeno, "zakaznik" AS vztah
FROM Zakaznici
UNION
SELECT KontaktniOsoba, "dodavatel"
FROM Dodavatele
UNION
SELECT concat(Jmeno," ",Prijmeni), "zamestnanec"
FROM Zamestnanci
ORDER BY 1;
SELECT Firma,KontaktniOsoba FROM Zakaznici
WHERE KontaktniOsoba Like "% Batista"
SELECT Adresa
FROM Zakaznici
WHERE Firma = "Alfreds Futterkiste";
lépe:
SELECT Adresa
FROM Zakaznici
WHERE Firma LIKE "Alfreds Futterkiste%";
nejhůře:
SELECT Adresa
FROM Zakaznici
WHERE Firma LIKE "%Futterkiste%";
CREATE TEMPORARY TABLE Mesta (
SELECT DISTINCT Mesto
FROM Zakaznici JOIN Zamestnanci USING (Mesto)
);
SELECT Mesto,Kdo,Jmeno
FROM (SELECT Mesto, "Zakaznik" AS Kdo, Firma Jmeno
FROM Zakaznici
UNION
SELECT Mesto , "Zamestnanec", CONCAT(Prijmeni,", ",Jmeno)
FROM Zamestnanci) A
WHERE Mesto IN (SELECT Mesto FROM Mesta)
ORDER BY 1,2;