Students && Subjects

--Създаване на база данни
CREATE DATABASE StudentsPP_12v

--Избор на база данни с която да работим
USE StudentsPP_12v

--Създаване на таблицата Градове с колони Id, Име(до 15 символа)
CREATE TABLE Towns
(
    Id INT PRIMARY KEY IDENTITY NOT NULL,
    [Name] NVARCHAR(15) NOT NULL
)

--Добавяне на записи в таблицата с градовете
INSERT INTO Towns ([Name])
VALUES ('Видин'), ('София'), ('Пловдив'), ('Бургас')

--Извеждане съдържанието на таблицата с градовете
SELECT *
FROM Towns

--Създаване на таблицата Предмети с колони Id, Име(до 30 символа)
CREATE TABLE Subjects
(
    Id INT PRIMARY KEY IDENTITY NOT NULL,
    [Name] NVARCHAR(30) NOT NULL
)

--Добавяне на записи в таблицата с предметите
INSERT INTO Subjects ([Name])
VALUES
    ('Математика'),
    ('Информатика'),
    ('Биология и здравно образование'),
    ('География и икономика'),
    ('Английски език'),
    ('Информационни технологии')

--Извеждане съдържанието на таблицата с предметите
SELECT *
FROM Subjects

--Създаване на таблицата Ученици с колони Id, Име, Клас, Град, Предмети(2бр)
CREATE TABLE Students
(
    Id INT PRIMARY KEY IDENTITY NOT NULL,
    [Name] NVARCHAR(30) NOT NULL,
    [Class] NVARCHAR(3) NOT NULL,
    TownId INT FOREIGN KEY REFERENCES Towns(Id),
    Subject1 INT FOREIGN KEY REFERENCES Subjects(Id),
    Subject2 INT FOREIGN KEY REFERENCES Subjects(Id),
)

--Добавяне на записи в таблицата с учениците
INSERT INTO Students([Name], [Class], TownId, Subject1, Subject2)
VALUES
    ('Иван Иванов', '12а', NULL, 3, NULL),
    ('Петър Петров', '12а', 1, 1, 3),
    ('Таня Ивова', '12б', 3, 2, NULL),
    ('Георги Илиянов', '12в', 2, 5, NULL),
    ('Петко Мишев', '12б', NULL, 3, 2),
    ('Дияна Асенова', '12г', 2, 4, 1)

--Извеждане съдържанието на таблицата с учениците
SELECT *
FROM Students


--Извеждане на всички ученици от град с Id 2
SELECT *
FROM Students
WHERE TownId = 2


--Извеждане на всички ученици които нямат втори профилиращ предмет
SELECT *
FROM Students
WHERE Subject2 IS NULL


--Извеждане на всички ученици с избрани два профилиращи предмета
SELECT *
FROM Students
WHERE Subject1 IS NOT NULL AND Subject2 IS NOT NULL


--Извеждане на всички ученици за които е въведен град с имената на градовете
SELECT s.[Name], s.[Class], t.[Name]
FROM Students s
JOIN Towns t ON s.TownId = t.Id
WHERE s.TownId IS NOT NULL


--Извеждане на всички ученици с имената първия профилиращ предмет
SELECT st.[Name] AS 'Име на ученик',
       st.[Class] AS 'Клас',
       sub.[Name] AS 'Профилиращ предмет'
FROM Students st
JOIN Subjects sub ON st.Subject1 = sub.Id
WHERE st.Subject1 IS NOT NULL


--Извеждане на всички ученици с имената на градовете и предметите, за тези които са Null да се изведе текста 'липсва'
SELECT st.[Name] AS 'Име на ученик',
       st.[Class] AS 'Клас',
       t.[Name] AS 'Град',
       sub1.[Name] AS 'I-ви Профилиращ предмет',
       sub2.[Name] AS 'II-ри Профилиращ предмет'
FROM Students st
LEFT JOIN Towns t ON st.TownId = t.Id
LEFT JOIN Subjects sub1 ON st.Subject1 = sub1.Id
LEFT JOIN Subjects sub2 ON st.Subject2 = sub2.Id