Zad 26 DZI 2023 may

CREATE DATABASE university

USE university

CREATE TABLE students
(
    Admission_no INT PRIMARY KEY NOT NULL,
    First_name NVARCHAR(25) NOT NULL,
    Last_name NVARCHAR(25) NOT NULL,
    City NVARCHAR(25) NOT NULL
)

CREATE TABLE fee
(
    ID INT PRIMARY KEY IDENTITY,
    Admission_no INT FOREIGN KEY REFERENCES students(Admission_no) NOT NULL,
    Course NVARCHAR(25) NOT NULL,
    Amount_paid INT NOT NULL
)

INSERT INTO students
VALUES
    (3354, N'Георги', N'Георгиев', N'Варна'),
    (4321, N'Милена', N'Красимирова', N'Стара Загора'),
    (8345, N'Михаил', N'Мартинов', N'Пловдив'),
    (7555, N'Антонио', N'Тачев', N'Стара Загора'),
    (2135, N'Мартин', N'Иванов', N'София')

INSERT INTO fee (Admission_no, Course, Amount_paid)
VALUES
    (3354, 'Java', 200),
    (7555, 'C#', 1800),
    (4321, 'SQL', 1600),
    (4321, 'Java', 2000),
    (8345, 'C++', 1700)

SELECT City
FROM students
WHERE Admission_no = 8345

SELECT AVG(Amount_paid)
FROM fee

SELECT *
FROM fee

UPDATE fee
SET Course = 'Java'
WHERE Amount_paid = 1800

SELECT Course, COUNT(Admission_no) AS [Assigned students]
FROM fee
GROUP BY Course

SELECT s.First_name, s.Last_name, f.Course
FROM students s
LEFT JOIN fee f ON f.Admission_no = s.Admission_no

SELECT s.First_name, s.Last_name, STRING_AGG(f.Course, ', ')
FROM students s
LEFT JOIN fee f ON f.Admission_no = s.Admission_no
GROUP BY s.First_name, s.Last_name