1 / 16

Isti upit iskazan na različite načine

Isti upit iskazan na različite načine. 1 - Zamena IN (SELECT ...) izraza. www.baze-podataka.net. 1.1 - Struktura. CREATE TABLE partneri ( sifra_partnera INTEGER NOT NULL, ime_partnera VARCHAR(50) NOT NULL, CONSTRAINT pk_par PRIMARY KEY (sifra_partnera) ); CREATE TABLE adrese (

lexine
Download Presentation

Isti upit iskazan na različite načine

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Isti upit iskazanna različite načine 1 - Zamena IN (SELECT ...) izraza www.baze-podataka.net

  2. 1.1 - Struktura CREATE TABLE partneri ( sifra_partnera INTEGER NOT NULL, ime_partnera VARCHAR(50) NOT NULL, CONSTRAINT pk_par PRIMARY KEY (sifra_partnera) ); CREATE TABLE adrese ( sifra_partnera INTEGER NOT NULL, opis_adrese VARCHAR(20) NOT NULL, CONSTRAINT pk_tra PRIMARY KEY (sifra_partnera, opis_adrese), CONSTRAINT fk_tra_par FOREIGN KEY (sifra_partnera) REFERENCES partneri ON DELETE CASCADE ON UPDATE CASCADE ); www.baze-podataka.net

  3. 1.2 - Podaci INSERT INTO partneri (sifra_partnera, ime_partnera) VALUES (1, 'Mika str'); INSERT INTO partneri (sifra_partnera, ime_partnera) VALUES (2, 'Pera doo'); INSERT INTO partneri (sifra_partnera, ime_partnera) VALUES (3, 'MELANIJA'); INSERT INTO partneri (sifra_partnera, ime_partnera) VALUES (4, 'Joca doo'); INSERT INTO partneri (sifra_partnera, ime_partnera) VALUES (5, 'Doo ZIKA i SONS'); www.baze-podataka.net

  4. 1.2 - Podaci INSERT INTO adrese (sifra_partnera, opis_adrese) VALUES (1, 'prodavnica'); INSERT INTO adrese (sifra_partnera, opis_adrese) VALUES (2, 'prodavnica br 1'); INSERT INTO adrese (sifra_partnera, opis_adrese) VALUES (2, 'prodavnica br 2'); INSERT INTO adrese (sifra_partnera, opis_adrese) VALUES (2, 'Kafana kod Pere'); INSERT INTO adrese (sifra_partnera, opis_adrese) VALUES (4, 'uprava'); INSERT INTO adrese (sifra_partnera, opis_adrese) VALUES (4, 'skladiste'); INSERT INTO adrese (sifra_partnera, opis_adrese) VALUES (4, 'prodavnica'); INSERT INTO adrese (sifra_partnera, ime_partnera) VALUES (5, 'Kafana Sinovi'); www.baze-podataka.net

  5. 1.3 - Upit Pitanje: Treba prikazati partnere koji imaju barem dve adrese. Rezultat: sifra_partnera ime_partnera -------------- ------------ 2 Pera doo 4 Joca doo www.baze-podataka.net

  6. 1.4 - Rešenje sa IN SELECT p.sifra_partnera, p.ime_partnera FROM partneri AS p WHERE p.sifra_partnera IN (SELECT a.sifra_partnera FROM adrese AS a GROUP BY a.sifra_partnera HAVING COUNT(a.sifra_partnera) > 1 ) ORDER BY p.sifra_partnera www.baze-podataka.net

  7. 1.5 - Pretvaranje IN u EXISTS SELECT p.sifra_partnera, p.ime_partnera FROM partneri AS p WHERE p.sifra_partnera IN (SELECT a.sifra_partnera FROM adrese AS a GROUP BY a.sifra_partnera HAVING COUNT(a.sifra_partnera) > 1 ) ORDER BY p.sifra_partnera www.baze-podataka.net

  8. 1.5 - Pretvaranje IN u EXISTS SELECT p.sifra_partnera, p.ime_partnera FROM partneri AS p WHERE p.sifra_partneraIN (SELECT a.sifra_partnera FROM adrese AS a GROUP BY a.sifra_partnera HAVING COUNT(a.sifra_partnera) > 1 ) ORDER BY p.sifra_partnera www.baze-podataka.net

  9. 1.5 - Pretvaranje IN u EXISTS SELECT p.sifra_partnera, p.ime_partnera FROM partneri AS p WHERE EXISTS (SELECT a.sifra_partnera FROM adrese AS a WHEREp.sifra_partnera=a.sifra_partnera GROUP BY a.sifra_partnera HAVING COUNT(a.sifra_partnera) > 1 ) ORDER BY p.sifra_partnera www.baze-podataka.net

  10. 1.6 - Rešenje sa EXISTS SELECT p.sifra_partnera, p.ime_partnera FROM partneri AS p WHERE EXISTS (SELECT a.sifra_partnera FROM adrese AS a WHERE p.sifra_partnera = a.sifra_partnera GROUP BY a.sifra_partnera HAVING COUNT(a.sifra_partnera) > 1 ) ORDER BY p.sifra_partnera www.baze-podataka.net

  11. 1.7 - Pretvaranje EXISTS u JOIN SELECT p.sifra_partnera, p.ime_partnera FROM partneri AS p WHERE EXISTS (SELECT a.sifra_partnera FROM adrese AS a WHERE p.sifra_partnera = a.sifra_partnera GROUP BY a.sifra_partnera HAVING COUNT(a.sifra_partnera) > 1 ) ORDER BY p.sifra_partnera www.baze-podataka.net

  12. 1.7 - Pretvaranje EXISTS u JOIN SELECT p.sifra_partnera, p.ime_partnera FROM partneri AS p WHERE EXISTS (SELECT a.sifra_partnera FROM adrese AS a WHEREp.sifra_partnera = a.sifra_partnera GROUP BY a.sifra_partnera HAVING COUNT(a.sifra_partnera) > 1 ) ORDER BY p.sifra_partnera www.baze-podataka.net

  13. 1.7 - Pretvaranje EXISTS u JOIN SELECT p.sifra_partnera, p.ime_partnera FROM partneri AS p INNER JOIN (SELECT a.sifra_partnera FROM adrese AS a GROUP BY a.sifra_partnera HAVING COUNT(a.sifra_partnera) > 1 ) AS pa ONp.sifra_partnera = pa.sifra_partnera ORDER BY p.sifra_partnera www.baze-podataka.net

  14. 1.7 - Pretvaranje EXISTS u JOIN SELECT p.sifra_partnera, p.ime_partnera FROM partneri AS p INNER JOIN (SELECT a.sifra_partnera FROM adrese AS a GROUP BY a.sifra_partnera HAVING COUNT(a.sifra_partnera) > 1 ) AS pa ON p.sifra_partnera = pa.sifra_partnera ORDER BY p.sifra_partnera www.baze-podataka.net

  15. 1.8 - Rešenjesa JOIN SELECT p.sifra_partnera, p.ime_partnera FROM partneri AS p INNER JOIN (SELECT a.sifra_partnera FROM adrese AS a GROUP BY a.sifra_partnera HAVING COUNT(a.sifra_partnera) > 1 ) AS pa ON p.sifra_partnera = pa.sifra_partnera ORDER BY p.sifra_partnera www.baze-podataka.net

  16. 1.9 - Zaključak Prikazana su tri načina da se iskaže jedan isti upit. Brzina izvršavanja prikazanih verzija zavisi od: - konkretnog RDBMS-a, - njegovog SQL optimizatora i - okolnosti pod kojima se upit izvršava (količinapodataka, indeksi, dodatna ograničenja) Svaku od verzija treba isprobati, a koristiti onukoja se pokaže kao najbolja (najbrža). www.baze-podataka.net

More Related