DEKT.sql

CREATE DATABASE QLTV
USE QLTV

CREATE TABLE SACH
(
MASACH CHAR(10) PRIMARY KEY NOT NULL,
TENSACH NVARCHAR(30) NOT NULL,
SOTRANG INT NOT NULL,
SLTON INT NOT NULL
)

CREATE TABLE PM
(
MAPM CHAR(10) PRIMARY KEY NOT NULL,
NGAYM DATETIME NOT NULL,
HOTENDG NVARCHAR(30) NOT NULL
)

CREATE TABLE SACHMUON
(
MAPM CHAR(10) NOT NULL,
MASACH CHAR(10) NOT NULL,
SONGAYMUON INT NOT NULL,
PRIMARY KEY(MAPM, MASACH),
CONSTRAINT FK1 FOREIGN KEY(MAPM) REFERENCES PM(MAPM),
CONSTRAINT FK2 FOREIGN KEY(MASACH) REFERENCES SACH(MASACH)
)

INSERT INTO SACH VALUES
('S1', N'SÁCH VĂN', 10, 20),
('S2', N'SÁCH TOÁN', 8, 25)

INSERT INTO PM VALUES
('P1', '1/22/2021', N'ĐỨC'),
('P2', '1/20/2021', N'ĐỖ')




INSERT INTO SACHMUON VALUES
('P1', 'S1', '20'),
('P2', 'S2', '24'),
('P1', 'S2', '17'),
('P2', 'S1', '22')

SELECT * FROM SACH
SELECT * FROM PM
SELECT * FROM SACHMUON


CREATE VIEW CAU2
AS
SELECT SACHMUON.MASACH, HOTENDG, NGAYM, (NGAYM + SONGAYMUON) AS 'NGAY TRA'
FROM SACH INNER JOIN SACHMUON ON SACH.MASACH = SACHMUON.MASACH
		  INNER JOIN PM ON PM.MAPM = SACHMUON.MAPM

SELECT * FROM CAU2


ALTER FUNCTION CAU3(@MASACH CHAR(10))
RETURNS INT
AS
BEGIN
	DECLARE @TONGSO INT
	SELECT @TONGSO = COUNT(SACHMUON.MASACH)
	FROM PM INNER JOIN SACHMUON
	ON PM.MAPM = SACHMUON.MAPM
	WHERE MASACH = @MASACH AND
	DATEDIFF(DAY, NGAYM, GETDATE()) > SONGAYMUON
	GROUP BY MASACH
	RETURN @TONGSO
END


SELECT DBO.CAU3('S2')

SELECT DATEDIFF(DAY, NGAYM, GETDATE())
FROM PM



SELECT * FROM PM