SQL 06.01.2022

CREATE DATABASE uzd_3;
USE uzd_3;
CREATE TABLE specialitates (
ID INT AUTO_INCREMENT,
kods CHAR(30) NOT NULL,
nosaukums CHAR(50) NOT NULL,
PRIMARY KEY(ID)
);

CREATE TABLE grupas (
ID INT AUTO_INCREMENT,
nosaukums CHAR(30) NOT NULL,
vards CHAR(20) NOT NULL,
uzvards CHAR(30) NOT NULL,
PRIMARY KEY(ID),
specialitates INT,
FOREIGN KEY (specialitates) REFERENCES specialitates(ID)
);

CREATE TABLE audzekni (
ID INT AUTO_INCREMENT,
per_kods CHAR(12) NOT NULL,
vards CHAR(20) NOT NULL,
uzvards CHAR(30) NOT NULL,
talr_numurs CHAR(8) NOT NULL,
dzim_diena DATE NOT NULL,
vecums INT(3) NOT NULL,
PRIMARY KEY(ID),
grupas INT,
FOREIGN KEY (grupas) REFERENCES grupas(ID)
);

CREATE TABLE prieksmeti (
ID INT AUTO_INCREMENT,
nosaukums CHAR(50) NOT NULL,
st_skaits INT(10) NOT NULL,
nod_skaits INT(10) NOT NULL,
PRIMARY KEY(ID)
);

CREATE TABLE skolotaji (
ID INT AUTO_INCREMENT,
vards CHAR(20) NOT NULL,
uzvards CHAR(30) NOT NULL,
talr_numurs CHAR(8) NOT NULL,
staza INT(3) NOT NULL,
PRIMARY KEY(ID)
);

CREATE TABLE met_komisijas (
ID INT AUTO_INCREMENT,
nosaukums CHAR(50) NOT NULL,
PRIMARY KEY(ID)
);

CREATE TABLE mac_plans (
ID INT AUTO_INCREMENT,
semestris CHAR(10) NOT NULL,
teor_skaits INT(10) NOT NULL,
prak_skaits INT(10) NOT NULL,
PRIMARY KEY(ID),
skolotaji INT,
prieksmeti INT,
grupas INT,
FOREIGN KEY (skolotaji) REFERENCES skolotaji(ID),
FOREIGN KEY (prieksmeti) REFERENCES prieksmeti(ID),
FOREIGN KEY (grupas) REFERENCES grupas(ID)
);

CREATE TABLE dip_vertejums (
ID INT AUTO_INCREMENT,
vertejums CHAR(3) NOT NULL,
PRIMARY KEY(ID),
prieksmeti INT,
audzekni INT,
FOREIGN KEY (prieksmeti) REFERENCES prieksmeti(ID),
FOREIGN KEY (audzekni) REFERENCES audzekni(ID)
);

ALTER TABLE skolotaji
ADD COLUMN met_komisijas INT,
ADD CONSTRAINT met_komisijas FOREIGN KEY (met_komisijas)
REFERENCES met_komisijas(ID) ON UPDATE CASCADE;


INSERT INTO specialitates VALUES
(1, "33 484 011", "Programmesana"),
(2, "33 484 011", "Datorsistemas, datubazes un datortikli");

INSERT INTO met_komisijas VALUES
(1, "Informacijas un komunikacijas tehnologijas"),
(2, "Matematika un fizika");

INSERT INTO prieksmeti VALUES
(1, "Datu bazu programmesana", 93, 280),
(2, "Lokalo tiklu ierikosana un uzturesana", 56, 40),
(3, "Matematika", 456, 0),
(4, "Fizika", 205, 16);



INSERT INTO skolotaji VALUES
(1, "Aleksandrs", "Skitov", 28700968, 9, 2),
(2, "Dmitrijs", "Yagodnik", 26863051, 8, 1),
(3, "Olegs", "Hramovs", 29670322, 12, 2),
(4, "Shadow", "Fiend", 25256623, 25, 2);

INSERT INTO grupas VALUES
(1, "DP22", "Nikolay", "Kononov", 1),
(2, "DP31", "Anton", "Hramov", 1),
(3, "DP32", "Stanislav", "Minaev", 1),
(4, "DT12", "Yaroslav", "Pchelkin", 2),
(5, "DT22", "Marc", "Stark", 2);

INSERT INTO audzekni VALUES
(1, "220498-32013", "Nikolay", "Kononov", 29853035, "1998-04-22", 18, 1),
(2, "190200-68102", "Anton", "Hramov", 26769008, "2000-02-19", 20, 2),
(3, "040872-36791", "Stanislav", "Minaev", 26397315, "1972-08-04", 19, 3),
(4, "011185-30219", "Yaroslav", "Pchelkin", 22473533, "1985-11-01", 20, 4),
(5, "161201-61523", "Marc", "Stark", 28535471, "2001-12-16", 18, 5);

INSERT INTO mac_plans VALUES
(1, 2, 456, 0, 1, 3, 1),
(2, 4, 205, 16, 1, 4, 2),
(3, 8, 205, 16, 1, 4, 3),
(4, 6, 93, 280, 2, 1, 4),
(5, 2, 56, 40, 2, 2, 5);

INSERT INTO dip_vertejums VALUES
(1, "9", 1, 1),
(2, "10", 2, 1),
(3, "n/v", 3, 1),
(4, "9", 4, 1),

(5, "5", 1, 2),
(6, "5", 2, 2),
(7, "2", 3, 2),
(8, "2", 4, 2),

(9, "3", 1, 3),
(10, "10", 2, 3),
(11, "4", 3, 3),
(12, "n/v", 4, 3),

(13, "5", 1, 4),
(14, "1", 2, 4),
(15, "9", 3, 4),
(16, "9", 4, 4),

(17, "8", 1, 5),
(18, "4", 2, 5),
(19, "4", 3, 5),
(20, "7", 4, 5);