exam sql

exam_sql.sql

CREATE DATABASE BarbershopDB;
GO
USE BarbershopDB;
GO


CREATE TABLE Barbers (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(200) NOT NULL,
    Gender NVARCHAR(10) NOT NULL CHECK (Gender IN ('Male','Female')),
    Phone NVARCHAR(20) NOT NULL,
    Email NVARCHAR(100) NOT NULL UNIQUE,
    BirthDate DATE NOT NULL,
    HireDate DATE NOT NULL,
    Position NVARCHAR(20) NOT NULL CHECK (Position IN ('Chief','Senior','Junior'))
);

CREATE TABLE Services (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    BarberId INT NOT NULL FOREIGN KEY REFERENCES Barbers(Id),
    Name NVARCHAR(100) NOT NULL,
    Price MONEY NOT NULL CHECK (Price > 0),
    DurationMinutes INT NOT NULL CHECK (DurationMinutes > 0)
);

CREATE TABLE Clients (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(200) NOT NULL,
    Phone NVARCHAR(20) NOT NULL,
    Email NVARCHAR(100) UNIQUE
);

CREATE TABLE Feedbacks (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    BarberId INT NOT NULL FOREIGN KEY REFERENCES Barbers(Id),
    ClientId INT NOT NULL FOREIGN KEY REFERENCES Clients(Id),
    Rating NVARCHAR(20) CHECK (Rating IN ('Very Bad','Bad','Normal','Good','Excellent')),
    Comment NVARCHAR(MAX)
);

CREATE TABLE Schedule (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    BarberId INT NOT NULL FOREIGN KEY REFERENCES Barbers(Id),
    ClientId INT NULL FOREIGN KEY REFERENCES Clients(Id),
    Date DATE NOT NULL,
    TimeSlot TIME NOT NULL,
    IsBooked BIT NOT NULL DEFAULT 0
);

CREATE TABLE Visits (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    ClientId INT NOT NULL FOREIGN KEY REFERENCES Clients(Id),
    BarberId INT NOT NULL FOREIGN KEY REFERENCES Barbers(Id),
    ServiceId INT NOT NULL FOREIGN KEY REFERENCES Services(Id),
    VisitDate DATE NOT NULL,
    TotalPrice MONEY NOT NULL,
    Rating NVARCHAR(20),
    Feedback NVARCHAR(MAX)
);






SELECT FullName FROM Barbers;

SELECT * FROM Barbers WHERE Position = 'Senior';

SELECT * FROM Barbers WHERE Id IN (
    SELECT BarberId FROM Services WHERE Name = 'Traditional Beard Shaving'
);

SELECT 
    (SELECT COUNT(*) FROM Barbers WHERE Position='Senior') AS SeniorCount,
    (SELECT COUNT(*) FROM Barbers WHERE Position='Junior') AS JuniorCount;


SELECT * FROM Barbers WHERE Id = (
    SELECT TOP 1 BarberId FROM Visits GROUP BY BarberId ORDER BY COUNT(ClientId) DESC
);


SELECT * FROM Barbers WHERE Id IN (
    SELECT BarberId FROM Visits WHERE MONTH(VisitDate)=MONTH(GETDATE())
    GROUP BY BarberId ORDER BY SUM(TotalPrice) DESC
);

SELECT * FROM Barbers WHERE Id IN (
    SELECT BarberId FROM Visits GROUP BY BarberId HAVING COUNT(*)>=10 ORDER BY AVG(
        CASE Rating WHEN 'Very Bad' THEN 1 WHEN 'Bad' THEN 2 WHEN 'Normal' THEN 3 WHEN 'Good' THEN 4 WHEN 'Excellent' THEN 5 END
    ) DESC
);

SELECT * FROM Clients WHERE Id NOT IN (SELECT ClientId FROM Feedbacks);

SELECT * FROM Clients WHERE Id NOT IN (
    SELECT ClientId FROM Visits WHERE VisitDate > DATEADD(YEAR,-1,GETDATE())
);

SELECT * FROM Clients WHERE Id = (
    SELECT TOP 1 ClientId FROM Visits GROUP BY ClientId ORDER BY COUNT(*) DESC
);

SELECT * FROM Clients WHERE Id = (
    SELECT TOP 1 ClientId FROM Visits GROUP BY ClientId ORDER BY SUM(TotalPrice) DESC
);









CREATE PROCEDURE GetTopBarberByDateRange @StartDate DATE, @EndDate DATE
AS
BEGIN
    SELECT * FROM Barbers WHERE Id = (
        SELECT TOP 1 BarberId FROM Visits 
        WHERE VisitDate BETWEEN @StartDate AND @EndDate
        GROUP BY BarberId ORDER BY COUNT(ClientId) DESC
    );
END;

CREATE FUNCTION GetLongestWorkingBarber()
RETURNS TABLE
AS RETURN (SELECT TOP 1 * FROM Barbers ORDER BY HireDate ASC);

CREATE FUNCTION GetLongestService()
RETURNS TABLE
AS RETURN (SELECT TOP 1 * FROM Services ORDER BY DurationMinutes DESC);

CREATE PROCEDURE GetScheduleForBarber @BarberId INT, @Day DATE
AS
BEGIN
    SELECT * FROM Schedule WHERE BarberId=@BarberId AND Date=@Day;
END;

CREATE PROCEDURE GetFreeSlotsForBarber @BarberId INT, @StartDate DATE
AS
BEGIN
    SELECT * FROM Schedule WHERE BarberId=@BarberId AND Date BETWEEN @StartDate AND DATEADD(DAY,7,@StartDate) AND IsBooked=0;
END;

CREATE TRIGGER PreventDoubleBooking ON Schedule
AFTER INSERT
AS
BEGIN
    IF EXISTS (
        SELECT 1 FROM Schedule s JOIN inserted i ON s.BarberId=i.BarberId AND s.Date=i.Date AND s.TimeSlot=i.TimeSlot AND s.IsBooked=1
    )
    BEGIN
        RAISERROR('This slot is already booked!',16,1);
        ROLLBACK TRANSACTION;
    END
END;

CREATE TRIGGER LimitJuniorBarbers ON Barbers
AFTER INSERT
AS
BEGIN
    IF (SELECT COUNT(*) FROM Barbers WHERE Position='Junior') > 5
    BEGIN
        RAISERROR('Too many junior barbers!',16,1);
        ROLLBACK TRANSACTION;
    END
END;

CREATE TRIGGER PreventChiefDelete ON Barbers
INSTEAD OF DELETE
AS
BEGIN
    IF EXISTS (SELECT * FROM deleted WHERE Position='Chief')
    BEGIN
        RAISERROR('Cannot delete Chief barber!',16,1);
        ROLLBACK TRANSACTION;
    END
    ELSE
        DELETE FROM Barbers WHERE Id IN (SELECT Id FROM deleted);
END;

CREATE TRIGGER PreventYoungBarbers ON Barbers
AFTER INSERT
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE YEAR(BirthDate) > 2005)
    BEGIN
        RAISERROR('Barber too young!',16,1);
        ROLLBACK TRANSACTION;
    END
END;









--данные


INSERT INTO Barbers (FullName, Gender, Phone, Email, BirthDate, HireDate, Position)
VALUES
('John Peterson','Male','+380501112233','john@barbershop.com','1985-03-12','2020-01-15','Chief'),
('Alex Smith','Male','+380502223344','alex@barbershop.com','1990-07-21','2021-05-10','Senior'),
('Michael Brown','Male','+380503334455','michael@barbershop.com','1995-11-05','2022-09-01','Junior'),
('Andrew Johnson','Male','+380504445566','andrew@barbershop.com','1998-02-17','2023-03-20','Junior');


INSERT INTO Services (BarberId, Name, Price, DurationMinutes)
VALUES
(1,'Classic haircut',300,45),
(1,'Traditional beard shaving',250,40),
(2,'Clipper haircut',200,30),
(2,'Beard grooming',220,35),
(3,'Youth haircut',180,25),
(4,'Fade haircut',250,40);

INSERT INTO Clients (FullName, Phone, Email)
VALUES
('George Miller','+380671112233','george@client.com'),
('Mary Johnson','+380672223344','mary@client.com'),
('David Wilson','+380673334455','david@client.com'),
('Anna Taylor','+380674445566','anna@client.com');

INSERT INTO Feedbacks (BarberId, ClientId, Rating, Comment)
VALUES
(1,1,'Excellent','Very professional barber!'),
(2,2,'Good','Nice service, but a bit slow.'),
(3,3,'Normal','Average haircut, nothing special.'),
(4,4,'Bad','Not satisfied.');


INSERT INTO Schedule (BarberId, ClientId, Date, TimeSlot, IsBooked)
VALUES
(1,1,'2026-06-11','10:00',1),
(2,NULL,'2026-06-11','11:00',0),
(3,2,'2026-06-12','12:00',1),
(4,NULL,'2026-06-12','13:00',0);


INSERT INTO Visits (ClientId, BarberId, ServiceId, VisitDate, TotalPrice, Rating, Feedback)
VALUES
(1,1,1,'2026-05-20',300,'Excellent','Great haircut'),
(2,2,4,'2026-05-25',220,'Good','Quality beard grooming'),
(3,3,5,'2026-06-01',180,'Normal','Okay service'),
(4,4,6,'2026-06-05',250,'Bad','Disappointed');