1. Loo uus kasutaja

Selle pildi alt-atribuut on tühi. Failinimi on 4FDAEB90-3058-4263-8AB3-B6A07B58B10C.png

2. Loo tabelid

CREATE TABLE Isik (
    id INT PRIMARY KEY identity(1,1),
    eesnimi VARCHAR(50),
    perenimi VARCHAR(50),
	sugu char (1),
    synniaeg DATE, 
    address VARCHAR(100),
    email VARCHAR(50)
)

create table Õppeaine(
õppeaine_id int primary key identity(1,1),
nimi varchar(100),
vastutav_õpetaja int FOREIGN KEY REFERENCES Isik(id),
aine_kestus VARCHAR(50)
);

CREATE TABLE Oppimine (
    id INT IDENTITY(1,1) PRIMARY KEY,
    isiku_id INT,
    oppeaine_id INT,
    hinne INT,
    FOREIGN KEY (isiku_id) REFERENCES Isik(id),
    FOREIGN KEY (oppeaine_id) REFERENCES Õppeaine(õppeaine_id)
);
CREATE TABLE Isik (
    id INT PRIMARY KEY identity(1,1),
    eesnimi VARCHAR(50),
    perenimi VARCHAR(50),
	sugu char (1),
    synniaeg DATE, 
    address VARCHAR(100),
    email VARCHAR(50)
)

create table Õppeaine(
õppeaine_id int primary key identity(1,1),
nimi varchar(100),
vastutav_õpetaja int FOREIGN KEY REFERENCES Isik(id),
aine_kestus VARCHAR(50)
);

CREATE TABLE Oppimine (
    id INT IDENTITY(1,1) PRIMARY KEY,
    isiku_id INT,
    oppeaine_id INT,
    hinne INT,
    FOREIGN KEY (isiku_id) REFERENCES Isik(id),
    FOREIGN KEY (oppeaine_id) REFERENCES Õppeaine(õppeaine_id)
);

CREATE TABLE Isik ( id INT PRIMARY KEY identity(1,1), eesnimi VARCHAR(50), perenimi VARCHAR(50), sugu char (1), synniaeg DATE, address VARCHAR(100), email VARCHAR(50) ) create table Õppeaine( õppeaine_id int primary key identity(1,1), nimi varchar(100), vastutav_õpetaja int FOREIGN KEY REFERENCES Isik(id), aine_kestus VARCHAR(50) ); CREATE TABLE Oppimine ( id INT IDENTITY(1,1) PRIMARY KEY, isiku_id INT, oppeaine_id INT, hinne INT, FOREIGN KEY (isiku_id) REFERENCES Isik(id), FOREIGN KEY (oppeaine_id) REFERENCES Õppeaine(õppeaine_id) );

3. Koosta tabeli logi

CREATE TABLE logi (
    id INT IDENTITY(1,1) PRIMARY KEY,
    kasutaja VARCHAR(50),
    kuupaev DATETIME DEFAULT GETDATE(),
    sisestatudAndmed VARCHAR(500)
);
CREATE TABLE logi (
    id INT IDENTITY(1,1) PRIMARY KEY,
    kasutaja VARCHAR(50),
    kuupaev DATETIME DEFAULT GETDATE(),
    sisestatudAndmed VARCHAR(500)
);

CREATE TABLE logi ( id INT IDENTITY(1,1) PRIMARY KEY, kasutaja VARCHAR(50), kuupaev DATETIME DEFAULT GETDATE(), sisestatudAndmed VARCHAR(500) );

4. Uuendamine Trigger

CREATE TRIGGER trg_Insert_Oppimine
ON Oppimine
AFTER INSERT
AS
BEGIN
    DECLARE @kasutaja NVARCHAR(50) = SUSER_NAME();
    DECLARE @kuupaev DATETIME = GETDATE();
    DECLARE @andmed NVARCHAR(500);

    SELECT @andmed = 
        'Lisatud kirje: ID=' + 
        CONVERT(NVARCHAR(10), i.id) + 
        ', isiku_id=' + CONVERT(NVARCHAR(10), i.isiku_id) +
        ', oppeaine_id=' + CONVERT(NVARCHAR(10), i.oppeaine_id) +
        ', hinne=' + CONVERT(NVARCHAR(10), i.hinne)
    FROM inserted i;

    INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed)
    VALUES (@kasutaja, @kuupaev, @andmed);
END;
CREATE TRIGGER trg_Insert_Oppimine
ON Oppimine
AFTER INSERT
AS
BEGIN
    DECLARE @kasutaja NVARCHAR(50) = SUSER_NAME();
    DECLARE @kuupaev DATETIME = GETDATE();
    DECLARE @andmed NVARCHAR(500);

    SELECT @andmed = 
        'Lisatud kirje: ID=' + 
        CONVERT(NVARCHAR(10), i.id) + 
        ', isiku_id=' + CONVERT(NVARCHAR(10), i.isiku_id) +
        ', oppeaine_id=' + CONVERT(NVARCHAR(10), i.oppeaine_id) +
        ', hinne=' + CONVERT(NVARCHAR(10), i.hinne)
    FROM inserted i;

    INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed)
    VALUES (@kasutaja, @kuupaev, @andmed);
END;

CREATE TRIGGER trg_Insert_Oppimine ON Oppimine AFTER INSERT AS BEGIN DECLARE @kasutaja NVARCHAR(50) = SUSER_NAME(); DECLARE @kuupaev DATETIME = GETDATE(); DECLARE @andmed NVARCHAR(500); SELECT @andmed = ‘Lisatud kirje: ID=’ + CONVERT(NVARCHAR(10), i.id) + ‘, isiku_id=’ + CONVERT(NVARCHAR(10), i.isiku_id) + ‘, oppeaine_id=’ + CONVERT(NVARCHAR(10), i.oppeaine_id) + ‘, hinne=’ + CONVERT(NVARCHAR(10), i.hinne) FROM inserted i; INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed) VALUES (@kasutaja, @kuupaev, @andmed); END;

5. Lisamine Trigger

CREATE TRIGGER trg_Update_Oppimine
ON Oppimine
AFTER UPDATE
AS
BEGIN
    DECLARE @kasutaja NVARCHAR(50) = SUSER_NAME();
    DECLARE @kuupaev DATETIME = GETDATE();
    DECLARE @andmed NVARCHAR(500);

    SELECT @andmed = 
        'Uuendatud kirje: ID=' + 
        CONVERT(NVARCHAR(10), i.id) + 
        ', uus hinne=' + CONVERT(NVARCHAR(10), i.hinne)
    FROM inserted i;

    INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed)
    VALUES (@kasutaja, @kuupaev, @andmed);
END;
CREATE TRIGGER trg_Update_Oppimine
ON Oppimine
AFTER UPDATE
AS
BEGIN
    DECLARE @kasutaja NVARCHAR(50) = SUSER_NAME();
    DECLARE @kuupaev DATETIME = GETDATE();
    DECLARE @andmed NVARCHAR(500);

    SELECT @andmed = 
        'Uuendatud kirje: ID=' + 
        CONVERT(NVARCHAR(10), i.id) + 
        ', uus hinne=' + CONVERT(NVARCHAR(10), i.hinne)
    FROM inserted i;

    INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed)
    VALUES (@kasutaja, @kuupaev, @andmed);
END;

CREATE TRIGGER trg_Update_Oppimine ON Oppimine AFTER UPDATE AS BEGIN DECLARE @kasutaja NVARCHAR(50) = SUSER_NAME(); DECLARE @kuupaev DATETIME = GETDATE(); DECLARE @andmed NVARCHAR(500); SELECT @andmed = ‘Uuendatud kirje: ID=’ + CONVERT(NVARCHAR(10), i.id) + ‘, uus hinne=’ + CONVERT(NVARCHAR(10), i.hinne) FROM inserted i; INSERT INTO logi (kasutaja, kuupaev, sisestatudAndmed) VALUES (@kasutaja, @kuupaev, @andmed); END;

6. Kontrolli trigerite tööd

INSERT INTO Oppimine (isiku_id, oppeaine_id, hinne)
VALUES (3, 2, 5);

UPDATE Oppimine SET hinne = 4 WHERE id = 1;

SELECT * FROM logi;
INSERT INTO Oppimine (isiku_id, oppeaine_id, hinne)
VALUES (3, 2, 5);

UPDATE Oppimine SET hinne = 4 WHERE id = 1;

SELECT * FROM logi;

INSERT INTO Oppimine (isiku_id, oppeaine_id, hinne) VALUES (3, 2, 5); UPDATE Oppimine SET hinne = 4 WHERE id = 1; SELECT * FROM logi;

Selle pildi alt-atribuut on tühi. Failinimi on 7CA090F5-98D4-4C64-98B8-B9CAF8FFFAF6.png

7. Koosta transaktsioon, mis võimaldab sisestatud andmeid tagasi võtta, kasuta SAVEPOINT.

EGIN TRANSACTION;
INSERT INTO Oppimine (isiku_id, oppeaine_id, hinne)
VALUES (4, 3, 3);
SAVE TRANSACTION sisestatud;
UPDATE Oppimine SET hinne = 5 WHERE id = 2;
COMMIT TRANSACTION;

SELECT * FROM Oppimine;
SELECT * FROM logi;

ROLLBACK TRANSACTION InsertedData;
EGIN TRANSACTION;
INSERT INTO Oppimine (isiku_id, oppeaine_id, hinne)
VALUES (4, 3, 3);
SAVE TRANSACTION sisestatud;
UPDATE Oppimine SET hinne = 5 WHERE id = 2;
COMMIT TRANSACTION;

SELECT * FROM Oppimine;
SELECT * FROM logi;

ROLLBACK TRANSACTION InsertedData;

EGIN TRANSACTION; INSERT INTO Oppimine (isiku_id, oppeaine_id, hinne) VALUES (4, 3, 3); SAVE TRANSACTION sisestatud; UPDATE Oppimine SET hinne = 5 WHERE id = 2; COMMIT TRANSACTION; SELECT * FROM Oppimine; SELECT * FROM logi; ROLLBACK TRANSACTION InsertedData;

Selle pildi alt-atribuut on tühi. Failinimi on AE77E7F1-1DA3-4EB0-9166-162D098E6400.png

8. Lisa oma andmebaasi tegevus, mis erineb eelnevalt tehtud ülesannetes

See protseduur näitab keskmist hinnangut

CREATE PROCEDURE KeskmineHinne
AS
BEGIN
    SELECT 
        o.nimi AS Oppeaine,
        AVG(op.hinne) AS KeskmineHinne
    FROM Oppimine op
    JOIN Õppeaine o ON op.oppeaine_id = o.õppeaine_id
    GROUP BY o.nimi;
END;
CREATE PROCEDURE KeskmineHinne
AS
BEGIN
    SELECT 
        o.nimi AS Oppeaine,
        AVG(op.hinne) AS KeskmineHinne
    FROM Oppimine op
    JOIN Õppeaine o ON op.oppeaine_id = o.õppeaine_id
    GROUP BY o.nimi;
END;

CREATE PROCEDURE KeskmineHinne AS BEGIN SELECT o.nimi AS Oppeaine, AVG(op.hinne) AS KeskmineHinne FROM Oppimine op JOIN Õppeaine o ON op.oppeaine_id = o.õppeaine_id GROUP BY o.nimi; END;

Selle pildi alt-atribuut on tühi. Failinimi on 33453795-3350-443F-8812-5F666CB733B8.png
  • Pilt