ایجاد دیتابیس SQL Server کتابخانه با پروسیجر و ویو و فانکشن و تریگر

Books (کتاب‌ها)
BookID (شناسه کتاب): شناسه یکتای هر کتاب
Title (عنوان): عنوان کتاب
Author (نویسنده): نویسنده کتاب
PublicationYear (سال انتشار): سال انتشار کتاب
Genre (ژانر): ژانر یا دسته‌بندی کتاب

Authors (نویسندگان)
AuthorID (شناسه نویسنده): شناسه یکتای هر نویسنده
FirstName (نام): نام نویسنده
LastName (نام خانوادگی): نام خانوادگی نویسنده
BirthYear (سال تولد): سال تولد نویسنده

Customers (مشتریان)
CustomerID (شناسه مشتری): شناسه یکتای هر مشتری
FirstName (نام): نام مشتری
LastName (نام خانوادگی): نام خانوادگی مشتری
Email (ایمیل): آدرس ایمیل مشتری
PhoneNumber (شماره تلفن): شماره تلفن مشتری

BorrowedBooks (کتاب‌های امانی)
BorrowID (شناسه امانت): شناسه یکتای هر امانت کتاب به مشتری
CustomerID (شناسه مشتری): شناسه مشتری که کتاب را امانت گرفته است
BookID (شناسه کتاب): شناسه کتابی که به مشتری امانت داده شده است
BorrowDate (تاریخ امانت): تاریخی که کتاب به مشتری امانت داده شده است
ReturnDate (تاریخ بازگشت): تاریخی که کتاب از مشتری بازگشت داده شده است
حالا می توانیم کد SQL Server برای ایجاد این جداول را بنویسیم. در اینجا از دستورات T-SQL استفاده می کنیم:

کد:

-- Create Authors table

CREATE TABLE Authors (

    AuthorID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    BirthYear INT

);



-- Create Books table

CREATE TABLE Books (

    BookID INT PRIMARY KEY,

    Title VARCHAR(100),

    AuthorID INT,

    PublicationYear INT,

    Genre VARCHAR(50),

    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)

);



-- Create Customers table

CREATE TABLE Customers (

    CustomerID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Email VARCHAR(100),

    PhoneNumber VARCHAR(20)

);



-- Create BorrowedBooks table

CREATE TABLE BorrowedBooks (

    BorrowID INT PRIMARY KEY,

    CustomerID INT,

    BookID INT,

    BorrowDate DATE,

    ReturnDate DATE,

    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),

    FOREIGN KEY (BookID) REFERENCES Books(BookID)

);

کد:

-- افزودن رکوردهای نمونه به Authors table

INSERT INTO Authors (AuthorID, FirstName, LastName, BirthYear)

VALUES

    (1, 'John', 'Doe', 1980),

    (2, 'Jane', 'Smith', 1975),

    (3, 'Michael', 'Johnson', 1990);



-- افزودن رکوردهای نمونه به Books table

INSERT INTO Books (BookID, Title, AuthorID, PublicationYear, Genre)

VALUES

    (101, 'Sample Book 1', 1, 2010, 'Fiction'),

    (102, 'Sample Book 2', 2, 2015, 'Mystery'),

    (103, 'Sample Book 3', 3, 2020, 'Science Fiction');



-- افزودن رکوردهای نمونه به Customers table

INSERT INTO Customers (CustomerID, FirstName, LastName, Email, PhoneNumber)

VALUES

    (1001, 'Alice', 'Johnson', 'alice@example.com', '123-456-7890'),

    (1002, 'Bob', 'Smith', 'bob@example.com', '987-654-3210'),

    (1003, 'Carol', 'Davis', 'carol@example.com', '111-222-3333');



-- افزودن رکوردهای نمونه به BorrowedBooks table

INSERT INTO BorrowedBooks (BorrowID, CustomerID, BookID, BorrowDate, ReturnDate)

VALUES

    (10001, 1001, 101, '2023-09-01', '2023-09-15'),

    (10002, 1002, 102, '2023-09-05', '2023-09-20'),

    (10003, 1003, 103, '2023-09-10', '2023-09-25');

کد:

SELECT Customers.FirstName, Customers.LastName, Books.Title, BorrowedBooks.ReturnDate

FROM Customers

INNER JOIN BorrowedBooks ON Customers.CustomerID = BorrowedBooks.CustomerID

INNER JOIN Books ON BorrowedBooks.BookID = Books.BookID

ORDER BY Customers.FirstName, Customers.LastName;

کد:

SELECT Customers.FirstName, Customers.LastName, COUNT(BorrowedBooks.BorrowID) AS TotalBorrowedBooks

FROM Customers

LEFT JOIN BorrowedBooks ON Customers.CustomerID = BorrowedBooks.CustomerID

GROUP BY Customers.FirstName, Customers.LastName;

کد:

SELECT Authors.FirstName, Authors.LastName, COUNT(Books.BookID) AS TotalBooks,

       AVG(PublicationYear) AS AveragePublicationYear

FROM Authors

LEFT JOIN Books ON Authors.AuthorID = Books.AuthorID

GROUP BY Authors.FirstName, Authors.LastName;

کد:

CREATE PROCEDURE AddNewBook

    @Title VARCHAR(100),

    @AuthorID INT,

    @PublicationYear INT,

    @Genre VARCHAR(50)

AS

BEGIN

    INSERT INTO Books (Title, AuthorID, PublicationYear, Genre)

    VALUES (@Title, @AuthorID, @PublicationYear, @Genre);

END;

کد:

DECLARE @AuthorID INT;

DECLARE @FirstName VARCHAR(50);

DECLARE @LastName VARCHAR(50);

DECLARE @BirthYear INT;



DECLARE AuthorCursor CURSOR FOR

SELECT AuthorID, FirstName, LastName, BirthYear

FROM Authors

WHERE DATEDIFF(YEAR, BirthYear, GETDATE()) > 50;



OPEN AuthorCursor;



FETCH NEXT FROM AuthorCursor INTO @AuthorID, @FirstName, @LastName, @BirthYear;



WHILE @@FETCH_STATUS = 0

BEGIN

    PRINT 'Author ID: ' + CAST(@AuthorID AS VARCHAR) + ', Name: ' + @FirstName + ' ' + @LastName + ', Birth Year: ' + CAST(@BirthYear AS VARCHAR);

    FETCH NEXT FROM AuthorCursor INTO @AuthorID, @FirstName, @LastName, @BirthYear;

END;



CLOSE AuthorCursor;

DEALLOCATE AuthorCursor;

کد:

CREATE TRIGGER CheckBookGenre

ON Books

AFTER INSERT

AS

BEGIN

    DECLARE @Genre VARCHAR(50);

    DECLARE @PublicationYear INT;

    

    SELECT @Genre = inserted.Genre, @PublicationYear = inserted.PublicationYear

    FROM inserted;



    IF @Genre = 'Mystery' AND @PublicationYear < 2000

    BEGIN

        RAISEERROR('Books in the Mystery genre published before 2000 are not allowed.', 16, 1);

        ROLLBACK;

    END;

END;

تریگر CheckBookGenre هر زمان که یک کتاب به جدول Books اضافه می‌شود، بررسی می‌کند که آیا ژانر کتاب Mystery است و سال انتشار آن کمتر از 2000 است یا نه. اگر شرایط صدق کنند، یک خطای اجرایی (Error) ایجاد می‌کند و اضافه کردن کتاب را بازنگری می‌کند.

کد:

CREATE PROCEDURE UpdateAuthor

    @AuthorID INT,

    @NewFirstName VARCHAR(50),

    @NewLastName VARCHAR(50),

    @NewBirthYear INT

AS

BEGIN

    UPDATE Authors

    SET FirstName = @NewFirstName,

        LastName = @NewLastName,

        BirthYear = @NewBirthYear

    WHERE AuthorID = @AuthorID;

    

    IF @@ROWCOUNT = 0

    BEGIN

        RAISEERROR('Author with ID ' + CAST(@AuthorID AS VARCHAR) + ' not found.', 16, 1);

        RETURN;

    END;

    

    PRINT 'Author with ID ' + CAST(@AuthorID AS VARCHAR) + ' updated successfully.';

END;