BPII - Parcijalni I

/*1. Kreirati novu bazu podataka i imenovati imenovati vašim brojem indexa.
Postavke za data fajl:
 Lokacija: C:\BP2\Data
 Veličina: inicijalno 5 MB,
 Maksimalna veličina: Bez ograničenja
 Uvećanje: 10%
Postavke za log fajl:
 Lokacija: C:\BP2\Log
 Veličina: inicijalno 2 MB, maksimalna veličina neograničena
 Maksimalna veličina: Bez ograničenja
 Uvećanje: 5%*/

CREATE DATABASE ParcijalniI
ON
(NAME=IB140261_dat, FILENAME='C:\BP2\DATA\ParcijalniI.mdf', SIZE=5MB, MAXSIZE=UNLIMITED, FILEGROWTH=10%)
LOG ON
(NAME=IB140261_log, FILENAME='C:\BP2\LOG\ParcijalniI.ldf', SIZE=2 MB, MAXSIZE=UNLIMITED, FILEGROWTH=5%)

/*2. U bazi podataka kreirati sljedeće tabele:
 Kandidati
 Ime, polje za unos 30 karaktera (obavezan unos),
 Prezime, polje za unos 30 karaktera (obavezan unos),
 JMBG, polje za unos 13 karaktera (obavezan unos i jedinstvena vrijednost),
 DatumRodjenja, polje za unos datuma (obavezan unos),
 MjestoRodjenja, polje za unos 30 karaktera,
 Telefon, polje za unos 20 karaktera,
 Email, polje za unos 50 karaktera (jedinstvena vrijednost).*/

USE ParcijalniI
GO

CREATE TABLE Kandidati
(
KandidatID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Ime nvarchar (30) NOT NULL,
Prezime nvarchar (30) NOT NULL,
JMBG nvarchar (13) UNIQUE NOT NULL,
DatumRodjenja date NOT NULL,
MjestoRodjenja nvarchar(30),
Telefon nvarchar (20),
Email nvarchar (50) UNIQUE
);
/*Testovi
 Datum, polje za unos datuma i vremena (obavezan unos),
 Naziv, polje za unos 50 karaktera (obavezan unos),
 Oznaka, polje za unos 10 karaktera (obavezan unos i jedinstvena vrijednost),
 Oblast, polje za unos 50 karaktera (obavezan unos),
 MaxBrojBodova, polje za unos cijelog broja (obavezan unos),
 Opis, polje za unos 250 karaktera.*/
CREATE TABLE Testovi
(
TestID int IDENTITY (1,1) PRIMARY KEY NOT NULL,
Datum datetime NOT NULL,
Naziv nvarchar (50) NOT NULL,
Oznaka nvarchar (10) UNIQUE NOT NULL,
Oblast nvarchar (50) NOT NULL,
MaxBrojBodova int NOT NULL,
Opis nvarchar (250)
);
/*RezultatiTesta
 Polozio, polje za unos ishoda testiranja – DA/NE (obavezan unos)
 OsvojeniBodovi, polje za unos decimalnog broja (obavezan unos),
 Napomena, polje za unos dužeg niza karaktera.
Napomena: Kandidat može da polaže više testova i za svaki test ostvari određene rezultate, pri čemu kandidat ne može
dva puta polagati isti test. Također, isti test može polagati više kandidata.*/
CREATE TABLE RezultatiTesta
(
KandidatID int FOREIGN KEY REFERENCES Kandidati (KandidatID),
TestID int FOREIGN KEY REFERENCES Testovi (TestID),
Polozio bit NOT NULL,
OsvojeniBodovi decimal NOT NULL,
Napomena text
)

/*3. Kreirati referentnu tabelu Gradovi i vezati je sa tabelom Kandidati. Također, u tabelu Kandidati dodati polje Adresa.*/
CREATE TABLE Gradovi
(
GradID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Naziv nvarchar(50)
)

ALTER TABLE Kandidati ADD GradID INT CONSTRAINT FK_Kandidati_Gradovi FOREIGN KEY (GradID) REFERENCES Gradovi (GradID)

ALTER TABLE Kandidati
ADD Adresa nvarchar (30)


/*4. U tabelu Kandidati importovati 10 kupaca iz baze podataka AdventureWorks2014 i to sljedeće kolone:
 FirstName (Person) -> Ime,
 LastName (Person) -> Prezime,
 Zadnjih 13 karaktera kolone rowguid iz tabele Customer (Crticu zamijeniti brojem 0) -> JMBG,
 ModifiedDate (Customer) -> DatumRodjenja,
 City (Address) -> MjestoRodjenja,
 PhoneNumber (PersonPhone) -> Telefon,
 EmailAddress (EmailAddress) -> Email.*/

INSERT INTO Kandidati (Ime, Prezime, JMBG, DatumRodjenja, MjestoRodjenja, Telefon, Email)
SELECT TOP 10 pp.FirstName AS [Ime],
        pp.LastName AS [Prezime],
        REPLACE(RIGHT(pp.rowguid, 13), '-', '0') AS [JMBG],
        pp.ModifiedDate AS [Datum rodjenja],
        ppa.City AS [Mjesto rodjenja],
        ppp.PhoneNumber AS [Telefon],
        pea.EmailAddress AS [Email]
FROM AdventureWorks2014.Person. Person AS pp
INNER JOIN AdventureWorks2014.Person.PersonPhone AS ppp ON pp.BusinessEntityID=ppp.BusinessEntityID
INNER JOIN AdventureWorks2014.Person.EmailAddress AS pea ON pp.BusinessEntityID=pea.BusinessEntityID
INNER JOIN AdventureWorks2014.Person.Address AS ppa ON pp.BusinessEntityID=ppa.AddressID

SELECT * FROM Kandidati

--SELECT REPLACE(RIGHT(pp.rowguid, 13), '-', '0'),CONVERT(varchar, pp.ModifiedDate, 104)  FROM AdventureWorks2014.Person.Person AS pp

/*5. U tabelu Testovi unijeti minimalno 3 zapisa, a zatim u tabelu RezultatiTesta unijeti minimalno 10 zapisa.*/
INSERT INTO Testovi (Datum, Naziv, Oznaka, Oblast, MaxBrojBodova, Opis)
VALUES
('2011-11-11 11:11:11', 'Bosansi', 'Bos', 'Jezicka', '100' , 'Ovo je test iz bosanskog jezika'),
('2010-10-10 10:10:10', 'Matematika', 'Mat', 'Tehnicka', '80', 'Ovo je test iz matematike'),
('2012-12-12 12:12:12', 'Priroda', 'Pri', 'Prirodna', '60', 'Ovo je test iz prirode')
SELECT * FROM Testovi
SELECT * FROM Kandidati

INSERT INTO RezultatiTesta (KandidatID, TestID, Polozio, OsvojeniBodovi, Napomena)
VALUES
('8', '1', '1', '60', 'Polozio'),
('9', '1', '0', '25', 'Pao'),
('10', '1', '1', '50', 'Polozio'),
('11', '2', '1', '75', 'Polozio'),
('12', '2', '0', '10', 'Pao'),
('13', '2', '1', '50', 'Polozio'),
('14', '3', '1', '40', 'Polozio'),
('15', '3', '0', '13', 'Pao'),
('16', '3', '1', '35', 'Polozio'),
('17','1','1','70', 'Polozio')
SELECT * FROM RezultatiTesta

/*6. Kreirati upit koji prikazuje rezultate testiranja za određeni test (oznaka testa kao filter).
Kao rezultat upita prikazati sljedeće kolone: ime i prezime, jmbg, telefon i email kandidata,
zatim datum, naziv, oznaku, oblast i maksimalan broj bodova na testu, te polje položio,
osvojene bodove i procentualni rezultat testa.*/

SELECT k.Ime, k.Prezime, k.JMBG, k.Telefon, k.Email,
        t.Datum, t.Naziv, t.Oznaka, t.Oblast, t.MaxBrojBodova,
        rt.Polozio, rt.OsvojeniBodovi, ROUND((rt.OsvojeniBodovi/t.MaxBrojBodova)/100,2) AS [Procentualni rezultat testa]
FROM RezultatiTesta AS rt
INNER JOIN Kandidati AS k ON rt.KandidatID=k.KandidatID
INNER JOIN Testovi AS t ON rt.TestID=t.TestID
WHERE t.Oznaka='Bos'

SELECT * FROM Testovi

/*7. Kreirati upit koji prikazuje naziv testa, datum održavanja, ukupan broj studenata koji su položili i ukupan broj studenata koji
nisu položili test. Uzeti u obzir samo testove na kojima su minimalno dva kandidata položila.*/
SELECT t.Naziv, t.Datum, COUNT(rt.KandidatID) AS [Broj polozenih]
FROM Testovi AS t
INNER JOIN RezultatiTesta AS rt ON rt.TestID=t.TestID
WHERE rt.Polozio=1
GROUP BY t.Naziv, t.Datum
HAVING COUNT(rt.KandidatID) >=2

/*8. Izmijeniti rezultate testiranja svim kandidatima koji su položili određeni test (oznaka testa kao filter).
Svim kandidatima koji su položili test broj osvojenih bodova uvećati za 5.*/
UPDATE RezultatiTesta
SET OsvojeniBodovi+=5
FROM RezultatiTesta AS rt
INNER JOIN Testovi AS t ON rt.TestID=t.TestID
WHERE t.Oznaka='Mat' AND rt.Polozio=1

/*9. Obrisati jedan test i ostvarene rezultate na testu (oznaka testa kao filter).*/

SELECT * FROM Testovi
GO
DELETE FROM RezultatiTesta
FROM RezultatiTesta AS rt
INNER JOIN Testovi AS t ON rt.TestID=t.TestID
WHERE t.Oznaka LIKE 'Pri'

DELETE FROM Testovi
WHERE Testovi.Oznaka='Pri'

/*10.Obrisati tabelu Gradovi i ukloniti polja GradID i Adresa u tabeli Kandidati.*/
ALTER TABLE Kandidati
DROP CONSTRAINT FK_Kandidati_Gradovi

ALTER TABLE Kandidati
DROP COLUMN GradID, Adresa

DROP TABLE Gradovi