30/09/2018, 20:11
Lấy ra điều kiện sách quá hạn bằng sql
use master
go
if exists (select name from sys.databases where name= 'Quanlysach')
drop database Quanlysach
go
--1. Hãy tạo các bảng trên trong hệ quản trị cơ sở dữ liệu
create database Quanlysach
use Quanlysach
go
create table Sach
(
Masach int not null primary key identity,
Tensach nvarchar(50),
Namxuatban date,
Nhaxuatban nvarchar(50),
Loaisach nvarchar(50)
)
create table Sinhvien
(
Masv int not null primary key identity,
Tensv nvarchar(50),
Ngaysinh date,
Gioitinh nvarchar(10),
Que nvarchar(50),
Lop nvarchar(10)
)
create table Muonsach
(
Masach int,
Masv int,
Ngaymuon date,
Ngayhentra date,
--2. Tạo khóa chính (Primary key) và khóa ngoại liên kết giữa các bảng
constraint primary_key primary key (Masach, Masv),
constraint khoaNgoai_S_MS foreign key (Masach) references Sach (Masach),
constraint khoaNgoai_SV_MS foreign key (Masv) references Sinhvien (Masv)
)
--3. Thêm một trường datra (xem sinh viên đã trả hay chưa) trong bảng muonsach, kiểu dữ liệu là “bit” và mặc định là 0 (chưa trả sách)
alter table Muonsach
add Datra bit default 0
--4. Nhập dữ liệu cho 3 bảng trên mỗi bảng ít nhất 7 bản ghi.
insert Sach /*9*/
values (N'Trên đường băng', '1/3/2015', N'Trẻ', N'Cuộc sống'),
(N'Cà phê cùng Tony', '3/3/2013', N'Văn hóa thông tin', N'Kinh nghiệm'),
(N'Nhà giả kim', '4/3/2014', N'Văn học', N'Văn học'),
(N'Mặc kệ nó, làm tới đi', '5/7/2015', N'Lao động - xã hội', N'Chia sẻ'),
(N'Bí quyết thành công của Bill Gate', '5/3/1992', N'PandaBooks', N'Chia sẻ'),
(N'Tôi tài giỏi, bạn cũng thế', '12/3/2012', N'Phụ nữ', N'Kinh nghiệm'),
(N'Chìa khóa tư duy tích cực', '3/3/2013', N'Trẻ', N'Kinh nghiệm'),
('Sherlock Holmes', '3/5/2013', N'Nước ngoài', N'Trinh thám'),
('Conan', '4/5/2012', N'Nhật bản', N'Trinh thám')
insert Sinhvien (Tensv, Gioitinh, Ngaysinh, Que, Lop) /*13*/
values (N'Phạm Trung Tính', 'Nam', '03/30/1996', N'Quảng Ninh', 'L01'),
(N'Trần Bảo Trọng', 'Nam', '1995/12/14', N'Hà Giang', 'L02'),
(N'Lê Thùy Dung', N'Nữ', '05/12/1997', N'Hà Nội', 'L03'),
(N'Lê Trường An', N'Nam', '11/20/1995', N'Ninh Bình', 'L04'),
(N'Phạm Thị Hương Giang', N'Nữ', '02/21/1999', N'Hòa Bình', 'L02'),
(N'Đoàn Duy Thức', N'Nam', '04/16/1994', N'Hà Nội', 'L01'),
(N'Dương Tuấn Thông', N'Nam', '06/12/1991', N'Nam Định', 'L03'),
(N'Lê Thành Đạt', N'Nữ', '11/20/1997', N'Phú Thọ', 'L04'),
(N'Ngô Văn Tuấn', N'Nam', '04/15/1994', N'Hà Nội', 'L01'),
(N'Cao Bá Quát', N'Nam', '08/11/1995', N'Bắc Giang', 'L03'),
(N'Nguyễn Thị Minh Anh ', N'Nữ', '09/25/1992', N'Phú Thọ', 'L01'),
(N'Lê Minh Đạt', N'Nam', '01/12/1993', N'Thái Bình', 'L04'),
(N'Bá Văn Linh', N'Nam', '09/20/1996', N'Thái Bình', 'L02')
insert Muonsach
values (1, 1, '01/03/2016', '02/02/2016', 1),
(2, 2, '04/05/2016', '06/05/2016', 1),
(4, 3, '10/06/2015', '11/06/2015', 0),
(3, 4, '01/07/2015', '02/19/2015', 1),
(3, 5, '09/07/2016', '09/08/2016', 0),
(6, 11, '08/08/2016', '09/08/2016', 1),
(1, 7, '10/02/2014', '11/06/2015', 1),
(3, 3, '08/07/2016', '09/07/2016', 0),
(7, 9, '01/07/2013', '04/08/2016',0),
(6, 12, '01/08/2016', '11/08/2016', 0),
(2, 1, '10/02/2015', '11/06/2015', 1),
(9, 2, '08/07/2016', '09/07/2016', 0),
(8, 4, '01/07/2015', '04/08/2016',0),
(9, 6, '01/08/2015', '11/08/2016', 0)
select * from Sinhvien
select * from Sach
select * from Muonsach
--5. Thực hành cập nhật dữ liệu, xóa dữ liệu trong các bảng.
update Sinhvien set Gioitinh = 'Nam' where Masv = 1
delete Sinhvien where Gioitinh = N'Nữ'
dbcc checkident ('Sinhvien', reseed, 1)
--6. Lấy thông tin các bạn sinh viên quê ở Hà Nội đã mượn ít nhất một quyển sách.
select *
from Sinhvien
where Masv in (select Masv from Muonsach) and Que = N'Hà Nội'
--7. Lấy ra thông tin các bạn sinh viên chưa mượn quyển sách nào
select sv.Masv, Tensv, Ngaysinh, Gioitinh, Que, Lop
from (Sinhvien sv full join Muonsach ms on sv.Masv = ms.Masv)
where ms.Masv is null
-- Hoac
(select * from Sinhvien)
except
(select sv.Masv, Tensv, Ngaysinh, Gioitinh, Que, Lop
from Sinhvien sv, Muonsach ms
where sv.Masv = ms.Masv)
--8. Đưa ra thông tin các quyển sách được xuất bản tử năm 2014 tới hết năm 2015
select *
from Sach
where YEAR(Namxuatban) between 2014 and 2015
--9. Đưa ra thông tin sinh viên (masv, tensv), thông tin sách (tensach, nhaxuatban) của những bạn sinh viên dã mượn sách quá hạn mà chưa trả.
select sv.Masv, Tensv, Tensach, Nhaxuatban
from Sinhvien sv, Sach s, Muonsach ms
where sv.Masv = ms.Masv -- chua xong
--10. Đưa ra thông tin những sinh viên không mượn những sách mà sinh viên có mã 1 đã mượn.
select distinct sv.Masv, Tensv, Ngaysinh, Gioitinh, Que, Lop
from Sinhvien sv, Muonsach ms
where sv.Masv = ms.Masach and ms.Masach not in (select Masach from Muonsach ms where Masv = 1)
--11. Viết câu lệnh truy vấn Đưa ra thông tin các bạn sinh viên đã mượn sách trong năm 2016.
select distinct sv.Masv, Tensv, Ngaysinh, Gioitinh, Que, Lop
from Sinhvien sv, Muonsach ms
where sv.Masv = ms.Masv and YEAR(Ngaymuon) = 2016
--12. Viết câu lệnh truy vấn Đưa ra các bạn sinh viên lớp AT11 đã mượn sách có mã là 1 mà chưa trả.
select distinct sv.Masv, Tensv, Ngaysinh, Gioitinh, Que, Lop
from Sinhvien sv, Muonsach ms
where sv.Masv = ms.Masv and Lop = 'AT11C' and Masach = 1 and Datra = 0 --chua xong
--13. Viết câu lệnh truy vấn để biết xem mỗi lớp có bao nhiêu sinh viên.
select Lop, COUNT(*) as Soluong
from Sinhvien
group by Lop
--14. Viết câu lệnh truy vấn Đưa ra thông tin các quyển sách được xuất bản tháng 3 năm 2016 mà bạn sinh viên mã 1 đã mượn.
select *
from Sach
where MONTH(Namxuatban) = 3 and YEAR(Namxuatban) = 2016 and Masach in (select Masach from Muonsach where Masv = 1)
--15. Viết câu lệnh truy vấn Đưa ra thông tin về các bạn sinh viên nam và sách có thể loại trinh thám chưa được trả đúng hạn..
select sv.Masv, Tensv, Ngaysinh, Gioitinh, Que, Lop
from Sinhvien sv, Sach s, Muonsach ms
where sv.Masv = ms.Masv and s.Masach = ms.Masach and Loaisach = N'Trinh thám' and Datra = 0 /* Thiếu điều kiện quá hạn */
--16. Viết câu lệnh truy vấn để biết xem mỗi sinh viên mượn bao nhiêu sách trong năm 2015.
select Masv, COUNT(*) as Sosachmuon
from Muonsach
where YEAR(Ngaymuon) = 2015
group by Masv
--17. Tạo một View cho biết thông tin (Masv,Tensv, Lop, tensach, nhaxuatban, Ngaymuon, ngayhentra) của các bạn sinh viên đã mượn ít nhất 1 quyển sách trong thể loại trinh thám.
-- Viết câu lệnh cập nhật view vừa tạo để thay đổi ngayhentra, nhaxuatban của bạn sinh viên có mã là 1. Câu lệnh này chạy có bị lỗi hay không?
create view cau17
as
select sv.Masv, Tensv, Lop, Tensach, Nhaxuatban, Ngaymuon, Ngayhentra
from Sinhvien sv, Sach s, Muonsach ms
where sv.Masv = ms.Masv and s.Masach = ms.Masach and Loaisach = N'Trinh thám'
select * from cau17
update cau17 set Ngayhentra = '03/12/2015', Nhaxuatban = N'Nhật bản' where Masv = 4 /* Báo lỗi vì nó tác động đến 2 bảng Sach và Muonsach */
--18. Tạo một View cho biết thông tin (Masv,Tensv, Lop, tensach, nhaxuatban, Ngaymuon, ngayhentra) của các bạn sinh viên nữ đã mượn ít nhất 1 quyển sách.
-- Viết câu lệnh cập nhật view vừa tạo để thay đổi tensach, nhaxuatban của bạn sinh viên có mã là 1. Câu lệnh này chạy có bị lỗi hay không?
create view cau18
as
select sv.Masv, Tensv, Lop, Tensach, Nhaxuatban, Ngaymuon, Ngayhentra
from Sinhvien sv, Sach s, Muonsach ms
where sv.Masv = ms.Masv and s.Masach = ms.Masach and Gioitinh = N'Nữ'
select * from cau18
update cau18 set Tensach = N'Cái gì đấy', Nhaxuatban = N'Cái gì luôn' where Masv = 1 /* Ko báo lỗi vì n tác động trên cùng 1 bảng */
--19. Tạo một khung nhìn cho biết thông tin những bạn sinh viên chưa mượn quyển sách nào.
create view cau19
as
select sv.Masv, Tensv, Ngaysinh, Gioitinh, Que, Lop
from (Sinhvien sv left join Muonsach ms on sv.Masv = ms.Masv)
where ms.Masv is null
select * from cau19
--20. Tạo một khung nhìn cho biết thông tin những bạn sinh viên đã mượn sách mà quá hạn ít nhất 02 quyển.
create view cau19
as
select sv.Masv, Tensv, Ngaysinh, Gioitinh, Que, Lop /* Chua lam duoc */
--21. Tạo một thủ tục cho biết xem bạn sinh viên mượn những quyển sách nào.
create proc cau21 (@Tensv nvarchar(50))
as
select Tensach
from Sinhvien sv, Sach s, Muonsach ms
where sv.Masv = ms.Masv and s.Masach = ms.Masach and Tensv = @Tensv
cau21 N'Phạm Trung Tính'
--22. Tạo một thủ tục thống kê mỗi lớp mượn tổng là bao nhiêu quyển sách.
create proc cau22 (@Lop nvarchar(10))
as
select COUNT(*) as Soluong
from Sinhvien sv, Muonsach ms
where sv.Masv = ms.Masv and Lop = @Lop
cau22 'L01'
--23. Tạo một hàm cho biết tổng số sinh viên theo lớp.
create function cau23(@Lop nvarchar(10))
returns table
as
return
(
select Lop, COUNT(*) as Soluong
from Sinhvien
where Lop = @Lop
group by Lop
)
select * from dbo.cau23('L01')
--24. Tạo một thủ tục cho biết số lượng sách quá hạn của mỗi sinh viên.
--25. Tạo một thủ tục cho biết thông tin những quyển sách được xuất bản theo năm.
create proc cau25(@nam int)
as
select *
from Sach
where YEAR(Namxuatban) = @nam
cau25 2015
--26. Tạo một store procedure để xem bạn sinh viên này mượn những quyển quá hạn có tên sách là gì?
--27. Tạo một store procedure để xem 2 bạn sinh viên có cùng mượn quyển sách mã 1 hay không?
-- Nếu cùng mượn in ra “cả 2 cùng mượn sách mã 1” ngược lại in ra “ít nhất 1 sinh viên không mượn sách mã 1”.
create proc cau27(@masv1 int, @masv2 int)
as
begin
declare @tong int
select @tong = COUNT(*)
from Muonsach
where Masach = 1 and (Masv = @masv1 or Masv = @masv2)
if(@tong = 2)
print N'Cả 2 cùng mượn sách mã 1'
else
print N'ít nhất 1 sinh viên không mượn sách mã 1'
end
cau27 1, 7
Mình còn câu 15, 20, 24, 26 chưa làm được, tại mình không biết điều kiện ntn để lấy ra điều kiện quá hạn ntn. Mọi người giúp mình với.
@SoEmXui Giúp mình với
Bài liên quan
upppppppppppppppppppppppppppp ai giúp mình với