Student database
1)Create database (DDL)

CREATE TABLE specialties(
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(100) NOT NULL,
    description VARCHAR2(300),
    PRIMARY KEY(id)
);

CREATE TABLE students(
    fc_number NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(100) NOT NULL,
	spec_id NUMBER NOT NULL,
	period NUMBER NOT NULL,
	email VARCHAR2(50) NOT NULL,
    PRIMARY KEY(fc_number),
	FOREIGN KEY(spec_id) REFERENCES specialties(id)
);

CREATE TABLE lecturers(
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(100) NOT NULL,
    scientific_title VARCHAR2(15) NULL,
	mobile VARCHAR2(25),
	email VARCHAR2(50) NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE disciplines(
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    description VARCHAR2(100) NOT NULL,
	semester NUMBER NOT NULL,
    lecturer_id NUMBER NULL,
    PRIMARY KEY(id),
	FOREIGN KEY(lecturer_id) REFERENCES lecturers(id)
);

CREATE TABLE exam_scores(
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
	fc_number NUMBER NOT NULL,
    discipline_id NUMBER NOT NULL,
	score NUMBER NOT NULL,
	exam_date TIMESTAMP (2) NOT NULL,
    PRIMARY KEY(id),
	FOREIGN KEY(fc_number) REFERENCES students(fc_number),
	FOREIGN KEY(discipline_id) REFERENCES disciplines(id)
);

2)INSERT DATA (DML)
-- Tuk te da si dobawqt primerni danni validni za tehnia sluchai wyw wsichki tablici
INSERT INTO specialties(name, description) VALUES('Informatics','description');

3)Tyrsene
SELECT *
FROM students
WHERE name LIKE '%Ivanov%';

SELECT *
FROM students
WHERE fc_number = 123456;

Tyrsene na ocenki na student
SELECT *
FROM exam_scores e
	JOIN students s ON e.fc_number = s.fc_number
	JOIN disciplines d ON e.discipline_id = d.id
WHERE s.fc_number = 123456
ORDER BY exam_date;

Tyrsene na ocenki po disciplina
SELECT *
FROM exam_scores e
	JOIN students s ON e.fc_number = s.fc_number
	JOIN disciplines d ON e.discipline_id = d.id
WHERE e.discipline_id = 123456
ORDER BY exam_date;

4)Spravki
4.1. Akademi4na spravka
SELECT *
FROM exam_scores e
	JOIN students s ON e.fc_number = s.fc_number
	JOIN disciplines d ON e.discipline_id = d.id
WHERE s.fc_number = 123456
ORDER BY d.semester, e.exam_date;

4.2.
SELECT e.fc_number, s.name, AVG(score)
FROM exam_scores e
	JOIN students s ON e.fc_number = s.fc_number
WHERE e.discipline_id = 123
	AND s.spec_id = 234
GROUP BY e.fc_number, s.name
ORDER BY 3 DESC, s.name;

4.3.
SELECT e.discipline_id, d.description, AVG(score)
FROM exam_scores e
	JOIN disciplines d ON e.discipline_id = d.id
GROUP BY e.discipline_id, d.description
ORDER BY 3 DESC, d.description;

4.4.SELECT e.fc_number, s.name, AVG(score)
FROM exam_scores e
	JOIN students s ON e.fc_number = s.fc_number
GROUP BY e.fc_number, s.name
ORDER BY 3 DESC, s.name LIMIT 10;