UL/FRI/VSP-RI/PB1/Seminarska/2004/05

Skoči na: navigacija, iskanje

Vsebina 1 Izpiši vse podatke o trgovcih, ki živijo v mestu Portorož 2 Izpiši priimke trgovcev, ki prodajajo kupcu s šifro 'K2' artikel s šifro 'A3' 3 Izpiši barve artiklov, ki jih prodaja trgovec s šifro 'T2' 4 Izpiši šifre trgovcev, ki prodajajo tako kupcu 'K1' kot tudi kupcu 'K2' (torej obema) 5 Izpiši šifre artiklov, ki jih prodajajo trgovci kupcem iz istega mesta 6 Izpiši šifre kupcev, ki jim prodaja vsaj en trgovec, ki ni iz istega mesta 7 Izpiši šifre kupcev, ki jim prodajajo samo trgovci iz istega mesta 8 Izpiši šifre kupcev, ki ne kupujejo artiklov modre barve od trgovcev iz Ankarana 9 Izpiši šifre kupcev, ki kupujejo samo od trgovca 'T2' 10 Izpiši imena in težo artiklov z najmanjšo težo 11 Izpiši priimke trgovcev urejenih v padajočem vrstnem redu po številu kupčij, ki so jih sklenili 12 Izpiši kolikšno je število kupcev, ki jim prodaja trgovec 'T3' 13 Izračunaj in izpiši število parov nogavic, ki jih je prodal trgovec 'Adamic' 14 Izpiši šifre trgovcev, ki so prodali manj kot 2000 kosov kakršnih koli izdelkov 15 Za vsakega kupca izpiši ime njemu najbližje živečega trgovca 16 Izpiši imena trgovcev, ki prodajajo najbolj oddaljenim kupcem 17 Izpiši imena trgovcev, ki prodajajo v povprečju najbolj oddaljenim kupcem 18 Ugotovi, ali za vsaki dve mesti M1 in M2 velja, da je razdalja med M1 in M2 enaka razdalji med M2 in M1 19 Izpiši imena trgovcev, ki so prodali nadpovprečno veliko količino izdelkov 20 Izpiši imena trgovcev z najvišjim razmerjem med številom prodanih izdelkov in vsoto razdalj do kupcev

Izpiši vse podatke o trgovcih, ki živijo v mestu Portorož

SELECT *
FROM oseba o, trgovec t, mesto m
WHERE m.ime = 'Portoroz'
AND o.oid = t.oid
AND m.mid = o.mid;

+-----+---------+----------+-----+------+-----------+
| oid | priimek | mesto    | tid | oid  | provizija |
+-----+---------+----------+-----+------+-----------+
| O2  | Jazbec  | Portoroz | T2  | O2   |      10.0 |
| O3  | Bajec   | Portoroz | T3  | O3   |      30.0 |
+-----+---------+----------+-----+------+-----------+

Izpiši priimke trgovcev, ki prodajajo kupcu s šifro 'K2' artikel s šifro 'A3'

SELECT priimek
FROM kupcija, trgovec, oseba
WHERE kupcija.kid='K2'
AND kupcija.aid='A3'
AND kupcija.tid = trgovec.tid
AND oseba.oid = trgovec.oid;

+---------+
| priimek |
+---------+
| Jazbec  |
+---------+

Izpiši barve artiklov, ki jih prodaja trgovec s šifro 'T2'

SELECT DISTINCT barva
FROM kupcija, artikel
WHERE tid='T2'
AND kupcija.aid = artikel.aid;

+-------+
| barva |
+-------+
| Modra |
+-------+       

Izpiši šifre trgovcev, ki prodajajo tako kupcu 'K1' kot tudi kupcu 'K2' (torej obema)

SELECT tid
FROM kupcija
WHERE kid = 'K1'
intersect
SELECT tid
FROM kupcija
WHERE kid = 'K2';

+-----+
| tid |
+-----+
| T2  |
| T3  |
+-----+

Razlaga: Intersect naredi presek nad rezultati, ki so vsebovani v obeh poizvedbah.

Izpiši šifre artiklov, ki jih prodajajo trgovci kupcem iz istega mesta

SELECT DISTINCT artikel.aid
FROM artikel, kupcija, kupec, oseba
WHERE kupcija.aid = artikel.aid
AND kupcija.kid = kupec.kid
AND kupec.oid = oseba.oid
AND artikel.mesto = oseba.mesto;

+-----+
| aid |
+-----+
| A3  |
| A6  |
+-----+

Razlaga: Izpiše šifre brez ponavljanja tistih artiklov, ki imajo kupca in prodajalca v istem mestu.

Izpiši šifre kupcev, ki jim prodaja vsaj en trgovec, ki ni iz istega mesta

SELECT DISTINCT kupcija.kid FROM kupcija, kupec, oseba
WHERE kupcija.kid = kupec.kid
AND kupec.oid = oseba.oid
AND mesto NOT IN ( SELECT mesto FROM trgovec
               WHERE kupcija.tid = trgovec.tid
               AND oseba.oid = trgovec.oid );

+------+
| kid  |
+------+
| K1   |
| K4   |
| K2   |
| K3   |
| K5   |
| K6   |
| K7   |
+------+

Razlaga: Izpiše šifre brez ponavljanja tistih kupcev, kateri imajo drugačno mesto kot prodajalec.

Izpiši šifre kupcev, ki jim prodajajo samo trgovci iz istega mesta

SELECT DISTINCT kp.kid FROM kupcija kp, kupec k, trgovec t
WHERE 
(SELECT mesto
 FROM oseba
 WHERE kp.kid = k.kid
 AND k.oid = oseba.oid)
=
(SELECT mesto
 FROM oseba
 WHERE kp.tid = t.tid
 AND t.oid = oseba.oid)
AND kp.kid NOT IN 
(
SELECT DISTINCT kp2.kid FROM kupcija kp2, kupec k2, trgovec t2
WHERE 
(SELECT mesto
 FROM oseba
 WHERE kp2.kid = k2.kid
 AND k2.oid = oseba.oid)
<>
(SELECT mesto
 FROM oseba
 WHERE kp2.tid = t2.tid
 AND t2.oid = oseba.oid)
);

+------+
| kid  |
+------+
+------+ (prazen seznam, ker vsi kupujejo pri najmanj enemu iz drugega mesta)

Razlaga: Izpiše kupce v seznamu kupčij, ki so iz istega mesta kot njihovi trgovci in filtrira vse, ki kupujejo pri kateremkoli trgovcu iz drugega mesta.

Izpiši šifre kupcev, ki ne kupujejo artiklov modre barve od trgovcev iz Ankarana

SELECT DISTINCT kid
FROM kupcija
WHERE aid NOT IN (SELECT aid FROM artikel
                  WHERE barva = 'Modra')
AND tid NOT IN (SELECT tid FROM trgovec,oseba
                WHERE trgovec.oid = oseba.oid
                AND oseba.mesto = 'Ankaran');

+------+
| kid  |
+------+
| K1   |
| K4   |
| K2   |
| K3   |
| K7   |
| K7   |
+------+

Razlaga: Izpiše šifre kupcev brez ponavljanja iz kupčij, ki nimajo artikla modre barve in prodajalca iz ankarana.

Izpiši šifre kupcev, ki kupujejo samo od trgovca 'T2'

SELECT kid
FROM kupcija
WHERE tid = 'T2'
AND kid NOT IN (SELECT kid
                FROM kupcija
                WHERE tid <> 'T2');

+------+
| kid  |
+------+
| K6   |
+------+

Razlaga: Izpiše šifre kupcev, ki kupujejo od trgovca T2 in izloči vse, ki kupujejo od kogarkoli drugega.

Izpiši imena in težo artiklov z najmanjšo težo

SELECT ime,teza
FROM artikel
WHERE teza = (SELECT min(teza)
              FROM artikel);

+----------+------+
| ime      | teza |
+----------+------+
| Srajca   |   12 |
| Nogavice |   12 |
+----------+------+

Razlaga: Izpiše podatke artiklov, katerih teža se ujema z minimalno težo artikla.

Izpiši priimke trgovcev urejenih v padajočem vrstnem redu po številu kupčij, ki so jih sklenili

SELECT priimek FROM ( SELECT kp.tid, count(kp.tid) AS stevilo, o.priimek
                      FROM kupcija kp, trgovec t, oseba o
                      WHERE kp.tid = t.tid
                      AND t.oid = o.oid
                      GROUP BY kp.tid
                      ORDER BY stevilo DESC) AS foo;

+---------+
| priimek |
+---------+
| Trcek   |
| Smolar  |
| Bajec   |
| Cokan   |
| Jazbec  |
| Adamic  |
+---------+

Razlaga: Izpiše priimke iz subselecta, ki uredi vrstice po padajočem vrstnem redu glede na count števila kupčij vsakega kp.tid posebej. Alias foo je zaradi mysql specifikacij.

Izpiši kolikšno je število kupcev, ki jim prodaja trgovec 'T3'

SELECT count(DISTINCT kid) AS stevilo
FROM kupcija
WHERE tid = 'T3';

+---------+
| stevilo |
+---------+
|       2 |
+---------+

Razlaga: Izbere unikatne kupce, katerim prodaja T3 in jih prešteje.

Izračunaj in izpiši število parov nogavic, ki jih je prodal trgovec 'Adamic'

SELECT sum(kosov) AS stevilo
FROM kupcija
WHERE tid IN (SELECT tid
              FROM trgovec,oseba
              WHERE trgovec.oid = oseba.oid
              AND priimek = 'Adamic')
AND aid IN (SELECT aid
            FROM artikel
            WHERE ime = 'Nogavice');

+---------+
| stevilo |
+---------+
|    1000 |
+---------+

Razlaga: Sešteje vse zapise v stolpcu kosov, ki izpolnjujejo dana pogoja.

Izpiši šifre trgovcev, ki so prodali manj kot 2000 kosov kakršnih koli izdelkov

SELECT tid
FROM kupcija
GROUP BY tid
HAVING sum(kosov) < 2000;

+------+
| tid  |
+------+
| T1   |
| T3   |
| T4   |
| T6   |
+------+

Razlaga: Pri grupiranju izbere šifre, katere izpolnjujejo dani pogoj.

Za vsakega kupca izpiši ime njemu najbližje živečega trgovca

SELECT k.kid, t.tid, o1.priimek AS kupec, o2.priimek AS trgovec, razdalja
FROM kupec k, trgovec t, oseba o1, oseba o2, razdalja r
WHERE k.oid = o1.oid
AND t.oid = o2.oid
AND r.mesto1 = o1.mesto
AND r.mesto2 = o2.mesto
AND razdalja = 
(
SELECT min(razdalja)
FROM kupec k1, trgovec t1, oseba o11, oseba o21, razdalja r1
WHERE k1.kid = k.kid
AND k1.oid = o11.oid
AND t1.oid = o21.oid
AND r1.mesto1 = o11.mesto
AND r1.mesto2 = o21.mesto
GROUP BY k1.kid
)
ORDER BY k.kid;

+-----+-----+----------+---------+----------+
| kid | tid | kupec    | trgovec | razdalja |
+-----+-----+----------+---------+----------+
| K1  | T2  | Stanic   | Jazbec  |        0 |
| K1  | T3  | Stanic   | Bajec   |        0 |
| K2  | T1  | Pintar   | Smolar  |       90 |
| K2  | T6  | Pintar   | Trcek   |       90 |
| K2  | T4  | Pintar   | Cokan   |       90 |
| K3  | T5  | Rovtar   | Adamic  |        0 |
| K4  | T5  | Crnek    | Adamic  |        0 |
| K5  | T1  | Cingolic | Smolar  |        0 |
| K5  | T6  | Cingolic | Trcek   |        0 |
| K5  | T4  | Cingolic | Cokan   |        0 |
| K6  | T4  | Trubar   | Cokan   |      152 |
| K6  | T1  | Trubar   | Smolar  |      152 |
| K6  | T6  | Trubar   | Trcek   |      152 |
| K7  | T4  | Trcek    | Cokan   |        0 |
| K7  | T1  | Trcek    | Smolar  |        0 |
| K7  | T6  | Trcek    | Trcek   |        0 |
+-----+-----+----------+---------+----------+

Razlaga: Iz tabele vseh razdalj med kupci in trgovci s pomočjo subselecta izbere samo zapise, ki vsebujejo najmanjšo razdaljo za določenega kupca. Nekateri kupci imajo na isti razdalji na voljo več trgovcev, zato jih je izpisanih več.

Izpiši imena trgovcev, ki prodajajo najbolj oddaljenim kupcem

SELECT os1.priimek, razdalja
FROM kupcija kp, trgovec t, kupec k, oseba AS os1, oseba AS os2, razdalja
WHERE kp.tid = t.tid
AND t.oid = os1.oid
AND kp.kid = k.kid
AND k.oid = os2.oid
AND os1.mesto = mesto1
AND os2.mesto = mesto2
ORDER BY razdalja DESC;

+---------+----------+
| priimek | razdalja |
+---------+----------+
| Jazbec  |      271 |
| Jazbec  |      215 |
| Jazbec  |      215 |
| Bajec   |      215 |
| Adamic  |      199 |
| Adamic  |      199 |
| Smolar  |      123 |
| Jazbec  |      123 |
| Jazbec  |      123 |
| Adamic  |      107 |
| Smolar  |      107 |
| Adamic  |      107 |
| Cokan   |      107 |
| Jazbec  |       22 |
| Jazbec  |       22 |
| Jazbec  |        0 |
| Adamic  |        0 |
| Adamic  |        0 |
| Adamic  |        0 |
| Adamic  |        0 |
| Adamic  |        0 |
| Trcek   |        0 |
| Bajec   |        0 |
| Cokan   |        0 |
| Adamic  |        0 |
+---------+----------+

Razlaga: Izpiše v padajočem vrstnem redu razdaljo vsake kupčije posebej in pripadajoči priimek trgovca tako, da poišče iz kupčije lokacijo kupca in lokacijo trgovca, ter ti dve lokaciji primerja v tabeli razdalj.

Izpiši imena trgovcev, ki prodajajo v povprečju najbolj oddaljenim kupcem

SELECT os1.priimek, avg(razdalja) AS razdalja
FROM kupcija kp, trgovec t, kupec k, oseba AS os1, oseba AS os2, razdalja
WHERE kp.tid = t.tid
AND t.oid = os1.oid
AND kp.kid = k.kid
AND k.oid = os2.oid
AND os1.mesto = mesto1
AND os2.mesto = mesto2
GROUP BY kp.tid
ORDER BY razdalja DESC;

+---------+----------+
| priimek | razdalja |
+---------+----------+
| Jazbec  | 123.8750 |
| Smolar  | 115.0000 |
| Bajec   | 107.5000 |
| Adamic  |  61.2000 |
| Cokan   |  53.5000 |
| Trcek   |   0.0000 |
+---------+----------+

Razlaga: Izpiše v padajočem vrstnem redu glede na povprečje vse trgovce tako, da poišče lokacije prodajalcev in kupcev, iz tega potegne podatek o razdalji, kateri se upošteva pri funkciji avg ob grupiranju.

Ugotovi, ali za vsaki dve mesti M1 in M2 velja, da je razdalja med M1 in M2 enaka razdalji med M2 in M1

SELECT max(r1.razdalja-r2.razdalja) FROM
razdalja r1, razdalja r2
WHERE r1.mesto1 = r2.mesto2
AND r2.mesto1 = r1.mesto2;

+------------------------------+
| max(r1.razdalja-r2.razdalja) |
+------------------------------+
|                            0 |
+------------------------------+

Razlaga: Maksimalna razlika je nič, torej razlike med obratno napisanimi mesti ni.

Izpiši imena trgovcev, ki so prodali nadpovprečno veliko količino izdelkov

Izpiši imena trgovcev, ki so prodali nadpovprečno veliko količino (število kosov) kakršnih koli izdelkov (nadpovprečno veliko glede na vse trgovce)

SELECT o.priimek
FROM kupcija k, oseba o, trgovec t
WHERE k.tid = t.tid
AND t.oid = o.oid
GROUP BY k.tid
HAVING sum(kosov) > (SELECT sum(kosov)/count(DISTINCT tid)
                      FROM kupcija);

+---------+
| priimek |
+---------+
| Jazbec  |
| Adamic  |
+---------+

Razlaga: S subselectom ugotovi povprečje in potem z njim pri grupiranju filtrira ven podpovprečne.

Izpiši imena trgovcev z najvišjim razmerjem med številom prodanih izdelkov in vsoto razdalj do kupcev

SELECT os1.priimek, sum(kosov)/sum(razdalja) razmerje
FROM kupcija kp, trgovec t, kupec k, oseba AS os1, oseba AS os2, razdalja
WHERE kp.tid = t.tid
AND t.oid = os1.oid
AND kp.kid = k.kid
AND k.oid = os2.oid
AND os1.mesto = mesto1
AND os2.mesto = mesto2
GROUP BY kp.tid
ORDER BY razmerje DESC;

+---------+----------+
| priimek | razmerje |
+---------+----------+
| Adamic  |     8.17 |
| Cokan   |     5.61 |
| Smolar  |     3.91 |
| Bajec   |     3.26 |
| Jazbec  |     3.23 |
| Trcek   |     NULL |
+---------+----------+

Razlaga: Izpiše v padajočem vrstnem redu glede na razmerje vse trgovce tako, da poišče lokacije prodajalcev in kupcev, iz tega potegne podatek o razdalji, kateri se upošteva pri funkciji sum ob grupiranju, hkrati upošteva sum vseh kosov v kupčijah trgovcev in s pomočjo teh 2 funkcij izračuna razmerje. Deljenje z nič povzroči NULL.