USE master;

CREATE DATABASE NML01;
GO
USE NML01;

CREATE TABLE sailors (
    Sid int,
    Sname varchar(50),
    rating int,
    age int,
    sex bit,
	constraint pk_sailors Primary key(Sid)
);

CREATE TABLE boats (
    Bid int,
    Bname varchar(50),
    Color varchar(10),
    Price decimal,
	constraint pk_boats Primary key(Bid)
);

CREATE TABLE reserves (
	Sid int,
    Bid int,
    ReservesDate date,
	constraint pk_reserves Primary key(Sid, Bid, ReservesDate)
);

ALTER TABLE reserves
	ADD CONSTRAINT fk_sailors_reserves FOREIGN KEY (Sid) REFERENCES sailors(Sid),
		CONSTRAINT fk_boats_reserves FOREIGN KEY (Bid) REFERENCES boats(Bid);

INSERT INTO sailors VALUES(22, 'Dustin', 7, 45, 1);
INSERT INTO sailors VALUES(29, 'Sara', 1, 33, 0);
INSERT INTO sailors VALUES(31, 'Lubber', 8, 55, 1);
INSERT INTO sailors VALUES(32, 'Andy', 8, 25, 1);
INSERT INTO sailors VALUES(58, 'Adele', 10, 35, 0);
INSERT INTO sailors VALUES(64, 'Horatio', 7, 35, 1);
INSERT INTO sailors VALUES(71, 'Amy', 10, 16, 0);
INSERT INTO sailors VALUES(74, 'Hora', 9, 35, 1);
INSERT INTO sailors VALUES(85, 'Jane', 3, 25, 0);
INSERT INTO sailors VALUES(95, 'Bob', 3, 63, 1);
INSERT INTO sailors VALUES(96, 'Frodo', 3, 25, 1);

INSERT INTO boats VALUES(101, 'Interlake', 'Blue', 30000);
INSERT INTO boats VALUES(102, 'Interlake', 'Red', 29000);
INSERT INTO boats VALUES(103, 'Clipper', 'Green', 42000);
INSERT INTO boats VALUES(104, 'Marine', 'Red', 18000);

INSERT INTO reserves VALUES(22, 101, '2019-10-10');
INSERT INTO reserves VALUES(22, 102, '2019-10-10');
INSERT INTO reserves VALUES(22, 103, '2019-10-08');
INSERT INTO reserves VALUES(22, 104, '2019-10-07');
INSERT INTO reserves VALUES(31, 102, '2019-11-10');
INSERT INTO reserves VALUES(31, 103, '2019-11-06');
INSERT INTO reserves VALUES(31, 104, '2019-11-12');
INSERT INTO reserves VALUES(64, 101, '2019-09-05');
INSERT INTO reserves VALUES(64, 102, '2019-09-08');
INSERT INTO reserves VALUES(74, 103, '2019-09-08');

--Câu 1 Tìm tên thuỷ thủ đặt thuyền màu đỏ
SELECT DISTINCT Sname FROM sailors s 
	INNER JOIN reserves r ON s.Sid like r.Sid
	INNER JOIN boats b ON r.Bid like b.Bid
	WHERE b.Color like 'Red';

SELECT DISTINCT Sname FROM sailors s, reserves r, boats b
	WHERE s.Sid like r.Sid AND r.Bid like b.Bid AND b.Color like 'Red';

--Câu 2 Tìm tên thuỷ thủ đặt thuyền màu đỏ hoặc xanh
SELECT DISTINCT Sname FROM sailors s 
	INNER JOIN reserves r ON s.Sid like r.Sid
	INNER JOIN boats b ON r.Bid like b.Bid
	WHERE b.Color IN ('Red', 'Blue', 'Green');
	
SELECT DISTINCT Sname FROM sailors s, reserves r, boats b
	WHERE s.Sid like r.Sid AND r.Bid like b.Bid AND b.Color IN ('Red', 'Blue', 'Green');
	
--Câu 3 Tìm tên thuỷ thủ đặt tất cả thuyền
SELECT Sname FROM sailors s 
	INNER JOIN reserves r ON s.Sid like r.Sid
	GROUP BY Sname
	HAVING COUNT(*) >= (SELECT COUNT(*) FROM boats);	
	
SELECT Sname FROM sailors s, reserves r
	WHERE s.Sid like r.Sid
	GROUP BY Sname
	HAVING COUNT(*) >= (SELECT COUNT(*) FROM boats);	
	
--Câu 4 Tìm tên thuỷ thủ chưa đặt bao giờ
SELECT DISTINCT Sname FROM sailors s 
	LEFT JOIN reserves r ON s.Sid like r.Sid
	WHERE r.Sid IS NULL;
	
SELECT DISTINCT Sname FROM sailors
	WHERE Sid NOT IN (SELECT DISTINCT Sid FROM reserves);
	
--Câu 5 Tìm tên thuyền buồm có nhiều thuỷ thủ đặt nhất
SELECT Bname FROM boats b
	INNER JOIN reserves r ON b.Bid like r.Bid
	GROUP BY Bname
	HAVING COUNT(*) like (SELECT TOP 1 COUNT(*) FROM reserves GROUP BY Bid ORDER BY COUNT(*) DESC);
	
SELECT Bname FROM boats b, reserves r
	WHERE b.Bid like r.Bid
	GROUP BY Bname
	HAVING COUNT(*) like (SELECT TOP 1 COUNT(*) FROM reserves GROUP BY Bid ORDER BY COUNT(*) DESC);

--Câu 6 Tìm ngày tháng năm có thuỷ thủ đặt nhiều nhất
SELECT ReservesDate FROM reserves
	GROUP BY ReservesDate
	HAVING COUNT(*) like (SELECT TOP 1 COUNT(*) FROM reserves GROUP BY ReservesDate ORDER BY COUNT(*) DESC);

--Câu 7 Tìm các loại màu của các thuyền buồm được thuỷ thủ Dustin đặt
SELECT DISTINCT Color FROM boats b
	INNER JOIN reserves r ON b.Bid like r.Bid
	INNER JOIN sailors s ON r.Sid like s.Sid
	WHERE s.Sname like 'Dustin';
	
SELECT DISTINCT Color FROM boats b, reserves r, sailors s
	WHERE b.Bid like r.Bid AND r.Sid LIKE s.Sid AND s.Sname like 'Dustin';
	
--Câu 8 Tìm tất cả các thuỷ thủ (Sid) có ít nhất là hạng 8 và đặt thuyền buồm (Bid) 103
SELECT s.Sid FROM sailors s
	INNER JOIN reserves r ON r.Sid like s.Sid
	WHERE rating >= 8 AND r.Bid like 103;
	
SELECT s.Sid FROM sailors s, reserves r
	WHERE r.Sid like s.Sid AND rating >= 8 AND r.Bid like 103;
	
--Câu 9 Tìm tên thuỷ thủ không đặt thuyền nào mà trong tên có chứa chuỗi 'do', sắp xếp tăng dần
SELECT Sname FROM sailors s 
	LEFT JOIN reserves r ON s.Sid like r.Sid
	WHERE r.Sid IS NULL AND Sname LIKE '%do%'
	ORDER BY Sname DESC;
	
SELECT Sname FROM sailors
	WHERE Sid NOT IN (SELECT DISTINCT Sid FROM reserves) AND Sname LIKE '%do%'
	ORDER BY Sname DESC;

--Câu 10 Tìm thuỷ thủ (Sid) không đặt thuyền nào có tuổi >= 20
SELECT s.Sid FROM sailors s 
	LEFT JOIN reserves r ON s.Sid like r.Sid
	WHERE r.Sid IS NULL AND age >= 20;
	
SELECT Sid  FROM sailors
	WHERE Sid NOT IN (SELECT DISTINCT Sid FROM reserves) AND age >= 20;
	
--Câu 11 Tìm tên thuỷ thủ có đặt ít nhất 2 thuyền buồm
SELECT Sname FROM sailors s 
	INNER JOIN reserves r ON s.Sid like r.Sid
	GROUP BY r.Sid, Sname
	HAVING COUNT(r.Bid) >= 2

SELECT Sname FROM sailors s, reserves r 
	WHERE s.Sid like r.Sid
	GROUP BY r.Sid, Sname
	HAVING COUNT(r.Bid) >= 2

--Câu 12 Tìm tên thuỷ thủ có đặt thuyền buồm trong ngày 10/10/2019
SELECT DISTINCT Sname FROM sailors s 
	INNER JOIN reserves r ON s.Sid like r.Sid
	WHERE ReservesDate like '2019-10-10';
	
SELECT DISTINCT Sname FROM sailors s, reserves r
	WHERE s.Sid like r.Sid AND ReservesDate like '2019-10-10';