20/21: Baze podataka IT 3

Evo kako je izgledao zadatak koji je zadat za blic proveru:

Kako se rešava:

1. Treba kreirati bazu i tabele:

CREATE DATABASE TEST;
USE TEST;

CREATE TABLE Model (
  ModelID int PRIMARY KEY, 
  Naziv VARCHAR(50)
);

CREATE TABLE Boja (
  BojaID INT PRIMARY KEY,
  Naziv VARCHAR(25), 
  Opis TEXT 

);

Ovde je za opis moglo da ide i VARCHAR i TEXT.

CREATE TABLE Vozilo (
  VoziloID int PRIMARY KEY, 
  Registracija varchar(20), 
  GodinaProizvodnje Date, 
  PredjenoKM int, 
  ModelID int, 
  BojaID int, 
  CONSTRAINT fk1 FOREIGN KEY (ModelID) REFERENCES Model(ModelID), 
  CONSTRAINT fk2 FOREIGN KEY (BojaID) REFERENCES Boja(BojaID)
)

Mislim da je ovo gore jasno ... ali ako nije, kontaktirajte me na uobičajen način, pa će biti postavljeno dodatno objašnjenje

2. Popuniti svaku od tabela sa nekim podacima:

INSERT INTO boja (BojaID, Naziv, Opis) 
   VALUES (1, 'Bela', 'nema opis'),
          (2, 'Crna', 'black'),
          (3, 'siva', 'gray');

INSERT INTO model (ModelID, Naziv) VALUES (1, 'Ford Eskort');
INSERT INTO model (ModelID, Naziv) VALUES (2, 'Opel'), (3, 'Fiat');

INSERT INTO vozilo (VoziloID, Registracija, GodinaProizvodnje, PredjenoKM, ModelID, BojaID) 
  VALUES (1, '23455555', '2020-12-12', 1000, 3, 1),
         (2, 'fdgrdgsaergrag', '2019-12-12', 2000, 1, 1),
         (3, 'fdsgvadgvasg', '2010-12-12', 200000, 3, 1);

Kod unosa podataka možete unositi red po red, a možete i više redova odjednom. Da ne bude zabune, uvek se preporučuje da se navedu kolone u redosledu u kojem će se unositi podaci.

3. UPIT BR 1

Treba ispisati sve modele koje imamo: Prvo je navedeno kako da se ispišu svi nazivi modela koje imamo

  SELECT Naziv FROM Model;

Mada je čest slučaj da se ispiše sve što se ima u tabeli Model:

  SELECT * FROM Model;

4. UPIT BR 2

Traći se - Ispis svih registracija za sva vozila koja su bele boje:

1. način:

  SELECT vozilo.Registracija 
  FROM vozilo NATURAL JOIN boja
  WHERE boja.Naziv = 'Bela'

Ovde se koristi NATURAL JOIN a pošto je zajednička kolona za ove dve tabele BojaID, gledaće se za samo one redove gde je BojaID jednake vrednosti u obe tabele, a dodatni uslov je tu da se  osigura da se ispišu samo oni koji su beli.

2. način

  SELECT DISTINCT vozilo.Registracija 
  FROM vozilo, boja
  WHERE vozilo.BojaID = (SELECT BojaID FROM boja WHERE Naziv = 'Bela')

Ovde se koristi CROSS JOIN sa kojim se kreiraju sve moguće kombinacije svega iz obe tabele. Iako ne piše eksplicitno, to je zarez između naziva tabela ... Prvo se selektuje koji je BojaID za Belu boju, a onda se ispišu sve jednistvene registracije. Ako ne postavite DISTINCT, imaćete duplikate u vašim rezultatima.

3. način

  SELECT vozilo.Registracija 
  FROM vozilo, boja
  WHERE vozilo.BojaID = boja.BojaID
        AND boja.Naziv = 'Bela'

Ovo je još jedan od mogućih načina, gde se koristi logički uslov da se iz gomile podataka u obe tabele izdvoji samo ono što zadovoljava date uslove.

5. UPIT BR 3

Ispisati sve modele za koje postoji bar jedno registrovano vozilo

1. način - koristi se NATURAL JOIN

  SELECT model.Naziv, vozilo.Registracija
  FROM model NATURAL JOIN vozilo
  WHERE vozilo.Registracija IS NOT NULL;

ILI - pošto su različiti nazivi u tabelama, od onih koje se prikazuju, ne mora se navoditi i ime tabele:

 SELECT Naziv, Registracija
 FROM model NATURAL JOIN vozilo
 WHERE Registracija IS NOT NULL;

2. način: kao u u prethodnom upitu, može i ovako:

SELECT model.Naziv, vozilo.Registracija
FROM model, vozilo
WHERE model.ModelID = vozilo.ModelID
    AND vozilo.Registracija IS NOT NULL;

3. način: sa podupitom

SELECT model.Naziv, vozilo.Registracija
FROM model, vozilo
WHERE model.ModelID = (SELECT vozilo.ModelID
                       WHERE vozilo.Registracija IS NOT NULL);

POSTOJI ZA SVE VIŠE OD JEDNOG NAČINA DA SE URADI I VIŠE OD BROJA NAČINA KOJI VAM PREDSTAVIM, ALI OVO JE ONO ŠTO JE NAJČEŠĆE OČEKIVANO ZA OVU VRSTU UPITA .

6. Poslednji upit:

Ispisati model za koji postoji najviše registrovanih belih vozila:

SELECT Model.Naziv, COUNT(vozilo.VoziloID) as broj
FROM model, vozilo, boja
WHERE
   model.ModelID = vozilo.ModelID
   and vozilo.BojaID = boja.BojaID
   and boja.Naziv = 'Bela'
GROUP BY model.Naziv
ORDER BY broj DESC LIMIT 1

U ovom slučaju je trebalo da se pronađu svi modeli za koje ima belih vozila, onda se grupišu po nazivu modela, pa se za svaki od naziva modela pronađe broj tih vozila. Ono ORDER BY na kraju će da ispiše u opadajućem redosledu počev od naziva modela za kojeg ima najviše vozila, a LIMIT će da ograniči ispis samo jednog - što bukvalno znači da se ispisuje naziv modela i broj vozila samo za model koji ima najviše belih vozila.

Za vas ostavljam da za vašu vežbu pokušate da ovo uradite na drugi način.