create database asm_csdl;
create table TacGia (MaTacGia varchar(12) primary key,
TenTacGia varchar(50) not null);
create table NXB (MaNXB varchar(12) primary key,
TenNXB varchar(50) not null);
create table TheLoai (MaTheLoai varchar(12) primary key,
TenTheLoai varchar(50) not null);
create table KhuVuc (MaKhuVuc varchar(12),
TenKhuVuc varchar(50) not null,
MaTheLoai varchar(12),
primary key(MaKhuVuc),
FOREIGN KEY (MaTheLoai) REFERENCES theloai(matheloai)
);
create table ChuyenNganh (
MaCN varchar(12) PRIMARY key,
TenChuyenNganh varchar(20)
);
create table Sach(
MaSach varchar(12) primary key,
TieuDe varchar(20),
SoTrang int,
SoLuongBanIn int(100),
GiaTien int,
NgayNhap date,
MaNXB varchar(12),
MaTacGia varchar(12),
MaKhuVuc varchar(12),
MaTheLoai varchar(12),
FOREIGN KEY (MaTacGia) REFERENCES TacGia(MaTacGia),
FOREIGN KEY (MaNXB) REFERENCES NXB(MaNXB),
FOREIGN KEY (MaKhuVuc) REFERENCES KhuVuc(MaKhuVuc),
FOREIGN KEY (MaTheLoai) REFERENCES TheLoai(MaTheLoai)
);
create table ThuThu (
MSThuThu char(12) primary key,
Ten varchar(50) not null,
NgaySinh date,
DiaChi varchar(100),
SDD varchar(15),
Email varchar(50)
);
create table SinhVien (
MSSV varchar(12) primary key,
TenSV varchar(12),
ngaySinh date,
DiaChi varchar(100),
SDD varchar(15),
Email varchar(50),
MaCN varchar(12),
FOREIGN KEY (MaCN) references ChuyenNganh(MaCN)
);
create table TheSinhVien (
MSSV varchar(12),
Ho varchar(50),
Ten varchar(50),
ThoiHan date,
MaCN varchar(12),
primary key(MSSV,MaCN),
FOREIGN KEY (MSSV) REFERENCES SinhVien(MSSV),
FOREIGN KEY (MaCN) REFERENCES SinhVien(MaCN)
);
create table PhieuMuon (
MaPM varchar(12) primary key,
ngaythue date,
MSThuThu char(12),
MSSV char(12),
MaSach varchar(12),
TrangThai varchar(10),
FOREIGN KEY (MSThuThu) REFERENCES ThuThu(MSThuThu),
FOREIGN KEY (MSSV) REFERENCES SinhVien(MSSV),
FOREIGN KEY (MaSach) REFERENCES Sach(MaSach)
);
create table ChiTietPhieuMuon (
MaPM varchar(12),
MaSach varchar(12),
NgayTra date not null,
primary key(MaPM,MaSach),
FOREIGN KEY (MaPM) REFERENCES PhieuMuon(MaPM),
FOREIGN KEY (MaSach) REFERENCES Sach(MaSach)
);
insert into tacgia values('TG001','Nguyen Van A');
insert into tacgia values('TG002','Tran Quoc B');
insert into tacgia values('TG003','Nguyen Hue');
insert into tacgia values('TG004','Le Quy Don');
insert into tacgia values('TG005','Tran Hung Dao');
insert into tacgia values('TG006','Bill Gates');
insert into nxb values('NXB001','Tuoi Tre');
insert into nxb values('NXB002','Viet Luan');
insert into nxb values('NXB003','Thanh Nien');
insert into nxb values('NXB004','Kim Dong');
insert into nxb values('NXB005','Fahasa');
insert into theloai values('TL001','Kinh di');
insert into theloai values('TL002','Hanh dong');
insert into theloai values('TL003','Trinh tham');
insert into theloai values('TL004','Van hoc');
insert into theloai values('TL005','Vat ly');
insert into theloai values('TL006','Tin Hoc');
insert into khuvuc values('001','Khu vuc A', 'TL001');
insert into khuvuc values('002','Khu vuc B', 'TL002');
insert into khuvuc values('003','Khu vuc C', 'TL003');
insert into khuvuc values('004','Khu vuc D', 'TL004');
insert into khuvuc values('005','Khu vuc E', 'TL005');
insert into khuvuc values('006','Khu vuc T', 'TL006');
insert into chuyennganh values('Poly001','xay dung website');
insert into chuyennganh values('Poly002','lap trinh mobile');
insert into chuyennganh values('Poly003','to chuc su kien');
insert into chuyennganh values('Poly004','ung dung phan mem');
insert into chuyennganh values('Poly005','ky su');
insert into sach values('sach001A','Ky Su Ve Mien Tay', '200', '8', '100000', '2010-11-02','NXB001','TG001','003','TL001');
insert into sach values('sach002B','Ky Nang Song', '180', '10', '120000', '2011-08-04','NXB002','TG002','001','TL002');
insert into sach values('sach003C','Huong Dan Du Lich', '300', '20', '400000', '2012-07-06','NXB003','TG003','002','TL003');
insert into sach values('sach004D','Tu Tuong Mac Lenin', '220', '15', '190000', '2013-12-08','NXB004','TG004','004','TL004');
insert into sach values('sach005E','Truyen Hai Huoc', '400', '9', '230000', '2014-08-23','NXB005','TG005','005','TL005');
insert into sach values('sachIT6F','Tu Van May Tinh', '250', '10', '190000', '2013-12-08','NXB002','TG006','006','TL006');
insert into sach values('sachIT7G','Lap Trinh', '300', '10', '280000', '2014-05-23','NXB003','TG006','006','TL006');
insert into thuthu values('thu001','thuthu A','1994-01-02','HCM','0914823777','
[email protected]');
insert into thuthu values('thu002','thuthu B','1993-02-01','Ha Noi','0914673217','
[email protected]');
insert into thuthu values('thu003','thuthu C','1992-03-03','Tien Giang','0914823777','
[email protected]');
insert into thuthu values('thu004','thuthu D','1991-04-06','Lam Dong','0913423907','
[email protected]');
insert into thuthu values('thu005','thuthu E','1990-05-07','Dak Lak','0943823377','
[email protected]');
insert into sinhvien values('SV001','sinhvien A','1998-04-14','HCM','0914763757','
[email protected]','poly001');
insert into sinhvien values('SV002','sinhvien B','1997-07-12','Long Giang','0914763757','
[email protected]','poly002');
insert into sinhvien values('SV003','sinhvien C','1996-08-08','Tien Giang','09124753757','
[email protected]','poly003');
insert into sinhvien values('SV004','sinhvien D','1995-09-22','HCM','0914775757','
[email protected]','poly004');
insert into sinhvien values('SV005','sinhvien E','1994-10-30','Ha Noi','0945763757','
[email protected]','poly005');
insert into thesinhvien values('SV001','A','AA','2020-01-03','poly001');
insert into thesinhvien values('SV002','B','BB','2020-02-04','poly002');
insert into thesinhvien values('SV003','C','CC','2020-01-03','poly003');
insert into thesinhvien values('SV004','D','DD','2020-02-04','poly004');
insert into thesinhvien values('SV005','E','EE','2020-01-03','poly005');
insert into phieumuon values('P001','2018-02-22','thu001','sv002','sach001A','no');
insert into phieumuon values('P002','2017-12-24','thu004','sv001','sach003C','no');
insert into phieumuon values('P003','2018-01-01','thu003','sv003','sach002B','yes');
insert into phieumuon values('P004','2017-11-23','thu002','sv004','sach001A','yes');
insert into phieumuon values('P005','2017-12-25','thu004','sv005','sach004D','no');
insert into phieumuon values('P006','2018-01-09','thu001','sv001','sach005E','no');
insert into chitietphieumuon values('P001','sach001A','2018-03-11');
insert into chitietphieumuon values('P002','sach003c','2018-01-04');
insert into chitietphieumuon values('P003','sach002B','2018-02-12');
insert into chitietphieumuon values('P004','sach001A','2017-12-29');
insert into chitietphieumuon values('P005','sach004D','2017-12-29');
insert into chitietphieumuon values('P006','sach005E','2018-01-13');
SELECT sach.MaSach as 'Mã sách',TieuDe, GiaTien,TacGia.MaTacGia as 'Mã tác gi?',tacgia.TenTacGia as 'Tên tác gỉa'
From sach,tacgia
WHERE sach.MaTacGia=tacgia.MaTacGia AND MaSACH like '%IT%';
SELECT phieumuon.MaPM as 'Mã phiếu mượn',Sach.MaSach as 'Mã sách',phieumuon.ngaythue as 'Ngày mượn sách',TheSinhVien.MSSV as 'Tên sinh viên'
FROM phieumuon,Sach,TheSinhVien
WHERE phieumuon.MaSach=Sach.MaSach AND phieumuon.MSSV=TheSinhVien.MSSV and year(ngaythue)=2017 and month(ngaythue)=1;
SELECT MaPM,MaSach,ngaythue,trangThai
from phieumuon
WHERE trangThai="No"
ORDER BY ngaythue ASC;
SELECT Theloai.Matheloai , theloai.TentheLoai ,COUNT(MaSach) AS 'Tổng số sách'
FROM theloai , Sach
WHERE Sach.Matheloai=theloai.Matheloai
GROUP BY Sach.matheloai;
SELECT COUNT(DISTINCT MSSV) AS 'lượt sinh viên đã mượn sách'
FROM phieumuon
ORDER BY phieumuon.MSSV;
SELECT Sach.MaSach , Sach.tieude
FROM Sach
WHERE tieude like "%SQL%";
SELECT phieumuon.MSSV, Ho, Ten, MaPM, sach.tieude, phieumuon.NgayThue
FROM phieumuon, thesinhvien, sinhvien,sach
where phieumuon.mssv=sinhvien.mssv and phieumuon.masach = sach.masach and thesinhvien.mssv=sinhvien.mssv
GROUP BY NgayThue;
SELECT MaSach,count(MaSach) AS 'số lượng'
FROM phieumuon
GROUP BY MaSach HAVING count(MaSach) >= 2;
UPDATE Sach SET GiaTien=Giatien-(Giatien*0.3)
WHERE YEAR(NgayNhap) < 2018;
UPDATE phieumuon SET
Trangthai='Yes'
WHERE MSSV='SV001';
SELECT MaPM,tenSV,TieuDe,NgayThue
FROM phieumuon, sach, sinhvien
WHERE trangThai='No' and phieumuon.mssv=sinhvien.mssv and phieumuon.masach=sach.masach;
UPDATE sach
SET SoLuongBanIn=SoLuongBanIn+5
WHERE MaSach IN(
SELECT MaSach
FROM phieumuon
GROUP BY phieumuon.MaSach HAVING COUNT(phieumuon.MaSach)>10);
?
Đại bàng
Gửi bởi Kyou
còn một vấn đề nữa là cái Mushishi Zoku Shou nó chạy đến 2 cour rồi mà có một số bạn vẫn bảo cái này nó không phải ss2 của Mushishi, vậy cho mình hỏi nó là cái gì của Mushishi
.
cậu đã khẳng định vậy thì yên tâm rồi
)
Quyền viết bài
Đánh dấu