Baze podataka 3

Kreiranje baze Skola1

-- Kreiranje baze podataka
CREATE DATABASE Skola1;
GO

-- Prelazak na novokreiranu bazu
USE Skola1;
GO

Treba da se kreiraju tabele:

• Student – podaci o studentima

• Ucenik – podaci o učenicima

• Predmet – podaci o predmetima

• Ocena – podaci o ocenama učenika iz predmeta

🔹 Kreiranje tabele Ucenik

-- Ako već postoji tabela, brišemo je
IF OBJECT_ID('Ucenik', 'U') IS NOT NULL
    DROP TABLE Ucenik;
GO

-- Kreiranje tabele UCENIK
CREATE TABLE Ucenik (
    UcenikID INT IDENTITY(1,1) PRIMARY KEY,
    Ime NVARCHAR(30) NOT NULL,
    Prezime NVARCHAR(30) NOT NULL,
    Razred INT NOT NULL,
    ProsecnaOcena DECIMAL(3,2) NULL,
    DatumRodjenja DATE NULL
);
GO

🔹 Ubacivanje podataka

Svi podaci su primeri koji pokrivaju više razreda i različite situacije (poznata/nepoznata ocena, različiti datumi).

INSERT INTO Ucenik (Ime, Prezime, Razred, ProsecnaOcena, DatumRodjenja) VALUES
(N'Ana',     N'Petrović',  3, 4.50, '2007-03-12'),
(N'Miloš',   N'Nikolić',   3, 3.80, '2007-05-08'),
(N'Jelena',  N'Pavlović',  4, 4.20, '2006-11-21'),
(N'Nikola',  N'Simić',     4, 5.00, '2006-07-03'),
(N'Ivana',   N'Marković',  4, NULL, '2007-02-17'),
(N'Danilo',  N'Ristić',    3, 2.90, '2007-10-15'),
(N'Sara',    N'Đorđević',  2, 4.75, '2008-01-28'),
(N'Mina',    N'Popović',   2, 4.00, '2008-04-10'),
(N'Petar',   N'Ilić',      2, NULL, '2008-06-22'),
(N'Andrej',  N'Jovanović', 3, 3.20, '2007-09-05');
GO

🔹 Kreiranje tabela Student, Predmet, Ocena

CREATE TABLE Student (
    UcenikID INT PRIMARY KEY IDENTITY(1,1),
    Ime NVARCHAR(30),
    Prezime NVARCHAR(30),
    DatumRodjenja DATE,
    Mesto NVARCHAR(30)
);

CREATE TABLE Predmet (
    PredmetID INT PRIMARY KEY IDENTITY(1,1),
    Naziv NVARCHAR(50),
    FondNedeljno INT
);

CREATE TABLE Ocena (
    OcenaID INT PRIMARY KEY IDENTITY(1,1),
    UcenikID INT,
    PredmetID INT,
    Ocena INT,
    DatumOcene DATE,
    FOREIGN KEY (UcenikID) REFERENCES Ucenik(UcenikID),
    FOREIGN KEY (PredmetID) REFERENCES Predmet(PredmetID)
);

🔹 Unos podataka

za tabelu Student:

INSERT INTO Student (Ime, Prezime, DatumRodjenja, Mesto)
VALUES 
('Marko', 'Jovanović', '2007-03-15', 'Beograd'),
('Milica', 'Petrović', '2007-07-22', 'Novi Sad'),
('Nikola', 'Ilić', '2007-01-09', 'Niš'),
('Jelena', 'Stanković', '2007-05-30', 'Kragujevac'),
('Ana', 'Milenković', '2007-09-18', 'Subotica');

za tabelu Predmet:

INSERT INTO Predmet (Naziv, FondNedeljno)
VALUES
('Matematika', 4),
('Srpski jezik', 3),
('Informatika', 2),
('Fizika', 2),
('Hemija', 2);

zab tabelu Ocena:

INSERT INTO Ocena (UcenikID, PredmetID, Ocena, DatumOcene)
VALUES
(1, 1, 5, '2024-09-12'),
(1, 3, 4, '2024-09-20'),
(2, 1, 3, '2024-09-12'),
(2, 2, 5, '2024-09-18'),
(3, 3, 5, '2024-09-22'),
(3, 4, 4, '2024-09-25'),
(4, 2, 2, '2024-09-14'),
(4, 5, 3, '2024-09-16'),
(5, 1, 5, '2024-09-10'),
(5, 2, 5, '2024-09-12'),
(5, 3, 4, '2024-09-20');

• Izvedena kolona: kolona koja nije fizički u tabeli, već se dobija pomoću izraza ili funkcije.
  Primer:

  SELECT Ime, Prezime, ProsecnaOcena * 20 AS Poeni
  FROM Ucenik;
  

• Redosled izračunavanja:

  1. Zagrade

  2. Množenje / Deljenje

  3. Sabiranje / Oduzimanje

• Funkcije:

  • String: LEN, LEFT, RIGHT, UPPER, LOWER

  • Datum: GETDATE(), YEAR(), MONTH(), DAY()

  • Numeričke: ROUND, ABS, POWER

  • Agregacione: SUM, AVG, MIN, MAX, COUNT

• Izvedene kolone (npr. izračunavanje godina učenika iz datuma rođenja)

• Funkcije za stringove (npr. UPPER(Ime) + ' ' + UPPER(Prezime))

• Funkcije za datume (npr. YEAR(GETDATE()) - YEAR(DatumRodjenja))

• Agregacione funkcije (AVG(Ocena), COUNT(*), MAX(Ocena), itd.)

• Grupisanje podataka po predmetima, učenicima, mestima

• Filtriranje grupa pomoću HAVING

🔸 Za slabije učenike

1. Prikazati imena svih učenika i njihovu godinu rođenja.
   👉 Hint: koristi YEAR()

2. Prikazati naziv i fond časova svakog predmeta.

3. Prikazati naziv predmeta i fond časova u minutima ako čas traje 45 minuta.

4. Prikazati sve učenike čije prezime ima više od 7 slova.

5. Prikazati sve učenike rođene u mesecu maju.

🔸 Za srednje učenike

1. Prikazati ime i prezime učenika, i broj dana od rođenja do danas (DATEDIFF).

2. Prikazati prosečnu ocenu po učeniku i zaokružiti je na 2 decimale (ROUND).

3. Prikazati sve predmete gde je fond časova manji od proseka svih fondova.

4. Prikazati sve učenike iz Niša i njihove prosečne ocene.

5. Prikazati koliko učenika ima ocenu 5.

🔸 Za bolje učenike

1. Prikazati ime učenika i prosečnu ocenu, sortirano od najboljeg do najlošijeg.

2. Prikazati učenike koji imaju barem 2 ocene u tabeli Ocena.

3. Prikazati predmete kod kojih nijedna ocena nije manja od 4.

4. Prikazati mesto i prosečnu ocenu učenika iz tog mesta (grupisanje po Mesto).

5. Prikazati ime učenika i dan u nedelji kada je poslednji put ocenjen.