By GokiSoft.com|
19:29 13/12/2023|
SQL Server/MySQL
Thiết kế CSDL quản lý sinh viên - lập trình sql server
Tạo bảng dữ liệu tên là : quan_ly_sinh_vien
Tạo ra các bảng sau
- Bảng sinh viên gồm các thuộc tính : rollno, fullname, age, address, email, phoneNumber, gender -> Trường khóa chính rollno
- Bảng môn học : mã môn học, tên môn học -> Trường khóa chính mã môn học tự tăng- Bảng điểm gồm các thuộc tinh sau : điểm, rollNo, mã môn học -> Trường khóa chính rollno, mã môn học
- Bảng lớp học : mã lớp học, tên lớp học -> Trường khóa chính mã lớp học tự tăng
- Bảng phòng học : tên phòng học, mã phòng học, sỗ bàn học, số ghế học, địa chỉ lớp học -> Trường khóa chính mã phòng học tự tăng
Thực hiện thêm dữ liệu vào từng bảng, mỗi bảng thêm tối thiểu 5 bản ghi.
Yêu cầu:
- Hiển thị dữ liệu trong các bảng
- Hiển thị phòng học có số bàn > 5 và số ghế > 5
- Hiển thị phòng học có số bàn > 5 và < 20 + số ghế > 5 và < 20Tags:
Phản hồi từ học viên
5
(Dựa trên đánh giá ngày hôm nay)
![Nguyên Phấn Đông [T2008A]](https://www.gravatar.com/avatar/c9c4f8f79ce35b9224637b6cc5fbe5c4.jpg?s=80&d=mm&r=g){kind=avatar}
Nguyên Phấn Đông
2020-11-24 16:49:51
create database quan_ly_sinh_vien
go
create table SinhVien (
rollno nvarchar(20) primary key,
fullname nvarchar(50),
age int,
address nvarchar(150),
email nvarchar(100),
phonenumber nvarchar(20),
gender nvarchar(10)
)
select * from SinhVien
insert into SinhVien(rollno, fullname, age, address, email, phonenumber, gender)
values
('R000','Nguyen Ba Hai', '18', 'Ha Noi', 'a@gmail.com', '123456789', 'nam'),
insert into SinhVien(rollno, fullname, age, address, email, phonenumber, gender)
values
('R001','Nguyên Phấn Đông', '18', 'Ha Noi', 'a@gmail.com', '123456789', 'nam'),
('R002','Nguyên Phấn Đông', '18', 'Ha Noi', 'a@gmail.com', '123456789', 'nam'),
('R003','Nguyên Phấn Đông', '18', 'Ha Noi', 'a@gmail.com', '123456789', 'nam'),
('R004','Nguyên Phấn Đông', '18', 'Ha Noi', 'a@gmail.com', '123456789', 'nam')
create table Marks (
marks float,
rollno nvarchar(20),
Mamonhoc nvarchar(20) primary key
)
select * from Marks
insert into Marks(marks, rollno, Mamonhoc)
values
('10', 'R001', 'MS000')
insert into Marks(marks, rollno, Mamonhoc)
values
('10', 'R001', 'MS001'),
('10', 'R001', 'MS002'),
('10', 'R001', 'MS003'),
('10', 'R001', 'MS004')
create table MonHoc (
Mamonhoc nvarchar(20) primary key,
Tenmonhoc nvarchar(30)
)
select * from MonHoc
insert into MonHoc(Mamonhoc, Tenmonhoc)
values
('MH001', 'Toan')
insert into MonHoc(Mamonhoc, Tenmonhoc)
values
('MH002', 'Van')
insert into MonHoc( Mamonhoc, Tenmonhoc)
values
('NH003', 'Sinh'),
('NH004', 'Ly'),
('NH005', 'Hoa')
create table LopHoc (
Malophoc nvarchar(10) primary key,
Tenlophoc nvarchar(20),
rollno nvarchar(10)
)
select * from LopHoc
insert into LopHoc(Malophoc, Tenlophoc, rollno)
values
('T2008', 'T2008A', 'ND1008'),
('T2004', 'T2008A', 'ND1008'),
('T2003', 'T2008A', 'ND1008'),
('T2002', 'T2008A', 'ND1008'),
('T2001', 'T2008A', 'ND1008')
create table PhongHoc (
Tenphonghoc nvarchar(50),
Maphong nvarchar(20) primary key,
Soban int,
Soghe int,
Diachi nvarchar(100)
)
select * from PhongHoc
insert into PhongHoc(Tenphonghoc, Maphong, Soban, Soghe, Diachi)
values
('T2008A', 'T2008', '15', '15', 'Ha noi'),
('T2008A', 'T2002', '15', '15', 'Ha noi'),
('T2008A', 'T2003', '15', '15', 'Ha noi'),
('T2008A', 'T2004', '15', '15', 'Ha noi'),
('T2008A', 'T2005', '15', '15', 'Ha noi')
create table Giogiay (
Malop nvarchar(10) primary key,
GioGiay Nvarchar(20),
Giotra nvarchar(20),
Maphong nvarchar(20)
)
select * from Giogiay
insert into Giogiay(Malop, GioGiay, Giotra, Maphong)
values
('T11', '1:30', '5:00', 'T2008A'),
('T12', '1:30', '5:00', 'T2008A'),
('T13', '1:30', '5:00', 'T2008A'),
('T14', '1:30', '5:00', 'T2008A'),
('T15', '1:30', '5:00', 'T2008A')![Trần Văn Lâm [T2008A]](https://www.gravatar.com/avatar/cfc15c8cb7781ad669b013e01f9f1a6b.jpg?s=80&d=mm&r=g){kind=avatar}
Trần Văn Lâm
2020-11-21 10:39:10
create database Quan_li_sinh_vien
go
create table Student(
rollno nvarchar(100) primary key,
fullname nvarchar(150),
age int,
address nvarchar(150),
email nvarchar(80),
phonenumber nvarchar(20),
gender nvarchar(10),
)
insert into Student(rollno,fullname,age,address,email,phonenumber,gender)
values
('T01','Tran Van Lam','18','Ha Nam','lam@gmail.com','1239999','nam')
select * from Student
insert into Student(rollno,fullname,age,address,email,phonenumber,gender)
values
('T02','Tran Van Lam','18','Ha Nam','lam@gmail.com','1239999','nam'),
('T03','Tran Van Lam','18','Ha Nam','lam@gmail.com','1239999','nam'),
('T04','Tran Van Lam','18','Ha Nam','lam@gmail.com','1239999','nam'),
('T05','Tran Van Lam','18','Ha Nam','lam@gmail.com','1239999','nam')
select * from Student
create table Marks(
mark float,
rollno nvarchar(20) primary key,
rollnoObject nvarchar(20)
)
insert into Marks(mark,rollno,rollnoObject)
values
('9','T01','S10'),
('10','T02','S20'),
('10','T03','S30')
select * from Marks
insert into Marks(mark,rollno,rollnoObject)
values
('9','T04','S40'),
('10','T05','S50')
select * from Marks
create table Object (
rollnoObject nvarchar(20) primary key,
nameObject nvarchar(50)
)
insert into Object(rollnoObject,nameObject)
values
('T01','Math'),
('T02','Oceian'),
('T03','Art'),
('T04','Vietnamese'),
('T05','History')
select * from Object
create table Class(
rollnoClass nvarchar(20) primary key,
nameClass nvarchar(100),
rollno nvarchar(20)
)
insert into Class(rollnoClass,nameClass,rollno)
values
('S10','10A1','T201'),
('S20','10A2','T202'),
('S30','10A3','T203'),
('S40','10A4','T204'),
('S50','10A5','T205')
select * from Class
create table RoomClass(
NameRoomClass nvarchar(100),
rollnoClass nvarchar(20) primary key,
tableNumber int,
chairNumber int,
addressRoomClass nvarchar(200)
)
insert into RoomClass(NameRoomClass,rollnoClass,tableNumber,chairNumber,addressRoomClass)
values
('MathRoom','S10','20','40','T1'),
('OceinRoom','S20','20','40','T2'),
('ArtRoom','S30','20','40','T3'),
('VietnameseRoom','S40','20','40','T4'),
('HistoryRoom','S50','20','40','T5')
select * from RoomClass
create table booktime(
rollnoRoomClass nvarchar(20) primary key,
startTeach datetime,
endTeach datetime,
rollnoClass nvarchar(20)
)
insert into booktime(rollnoRoomClass,startTeach,endTeach,rollnoClass)
values
('S1','2020-11-21 10:5:30','2020-11-21 11:5:30','T1'),
('S2','2020-11-21 10:5:30','2020-11-21 11:5:30','T2'),
('S3','2020-11-21 10:5:30','2020-11-21 11:5:30','T3'),
('S4','2020-11-21 10:5:30','2020-11-21 11:5:30','T4'),
('S5','2020-11-21 10:5:30','2020-11-21 11:5:30','T5')
select * from booktimeTrần Văn Lâm
2020-11-21 10:39:01
create database Quan_li_sinh_vien
go
create table Student(
rollno nvarchar(100) primary key,
fullname nvarchar(150),
age int,
address nvarchar(150),
email nvarchar(80),
phonenumber nvarchar(20),
gender nvarchar(10),
)
insert into Student(rollno,fullname,age,address,email,phonenumber,gender)
values
('T01','Tran Van Lam','18','Ha Nam','lam@gmail.com','1239999','nam')
select * from Student
insert into Student(rollno,fullname,age,address,email,phonenumber,gender)
values
('T02','Tran Van Lam','18','Ha Nam','lam@gmail.com','1239999','nam'),
('T03','Tran Van Lam','18','Ha Nam','lam@gmail.com','1239999','nam'),
('T04','Tran Van Lam','18','Ha Nam','lam@gmail.com','1239999','nam'),
('T05','Tran Van Lam','18','Ha Nam','lam@gmail.com','1239999','nam')
select * from Student
create table Marks(
mark float,
rollno nvarchar(20) primary key,
rollnoObject nvarchar(20)
)
insert into Marks(mark,rollno,rollnoObject)
values
('9','T01','S10'),
('10','T02','S20'),
('10','T03','S30')
select * from Marks
insert into Marks(mark,rollno,rollnoObject)
values
('9','T04','S40'),
('10','T05','S50')
select * from Marks
create table Object (
rollnoObject nvarchar(20) primary key,
nameObject nvarchar(50)
)
insert into Object(rollnoObject,nameObject)
values
('T01','Math'),
('T02','Oceian'),
('T03','Art'),
('T04','Vietnamese'),
('T05','History')
select * from Object
create table Class(
rollnoClass nvarchar(20) primary key,
nameClass nvarchar(100),
rollno nvarchar(20)
)
insert into Class(rollnoClass,nameClass,rollno)
values
('S10','10A1','T201'),
('S20','10A2','T202'),
('S30','10A3','T203'),
('S40','10A4','T204'),
('S50','10A5','T205')
select * from Class
create table RoomClass(
NameRoomClass nvarchar(100),
rollnoClass nvarchar(20) primary key,
tableNumber int,
chairNumber int,
addressRoomClass nvarchar(200)
)
insert into RoomClass(NameRoomClass,rollnoClass,tableNumber,chairNumber,addressRoomClass)
values
('MathRoom','S10','20','40','T1'),
('OceinRoom','S20','20','40','T2'),
('ArtRoom','S30','20','40','T3'),
('VietnameseRoom','S40','20','40','T4'),
('HistoryRoom','S50','20','40','T5')
select * from RoomClass
create table booktime(
rollnoRoomClass nvarchar(20) primary key,
startTeach datetime,
endTeach datetime,
rollnoClass nvarchar(20)
)
insert into booktime(rollnoRoomClass,startTeach,endTeach,rollnoClass)
values
('S1','2020-11-21 10:5:30','2020-11-21 11:5:30','T1'),
('S2','2020-11-21 10:5:30','2020-11-21 11:5:30','T2'),
('S3','2020-11-21 10:5:30','2020-11-21 11:5:30','T3'),
('S4','2020-11-21 10:5:30','2020-11-21 11:5:30','T4'),
('S5','2020-11-21 10:5:30','2020-11-21 11:5:30','T5')
select * from booktime![hainguyen [T2008A]](https://www.gravatar.com/avatar/32855ce6db55d60134d830aee06b41e5.jpg?s=80&d=mm&r=g){kind=avatar}
hainguyen
2020-11-20 16:44:03
create database quan_ly_sinh_vien
go
create table SinhVien (
rollno nvarchar(20) primary key,
fullname nvarchar(50),
age int,
address nvarchar(150),
email nvarchar(100),
phonenumber nvarchar(20),
gender nvarchar(10)
)
select * from SinhVien
insert into SinhVien(rollno, fullname, age, address, email, phonenumber, gender)
values
('R001','Nguyen Ba Hai', '18', 'Ha Noi', 'a@gmail.com', '123456789', 'nam'),
insert into SinhVien(rollno, fullname, age, address, email, phonenumber, gender)
values
('R002','Nguyen Ba Hai', '18', 'Ha Noi', 'a@gmail.com', '123456789', 'nam'),
('R003','Nguyen Ba Hai', '18', 'Ha Noi', 'a@gmail.com', '123456789', 'nam'),
('R004','Nguyen Ba Hai', '18', 'Ha Noi', 'a@gmail.com', '123456789', 'nam'),
('R005','Nguyen Ba Hai', '18', 'Ha Noi', 'a@gmail.com', '123456789', 'nam')
create table Marks (
marks float,
rollno nvarchar(20),
Mamonhoc nvarchar(20) primary key
)
select * from Marks
insert into Marks(marks, rollno, Mamonhoc)
values
('9', 'R001', 'MS001')
insert into Marks(marks, rollno, Mamonhoc)
values
('9', 'R001', 'MS002'),
('9', 'R001', 'MS003'),
('9', 'R001', 'MS004'),
('9', 'R001', 'MS005')
create table MonHoc (
Mamonhoc nvarchar(20) primary key,
Tenmonhoc nvarchar(30)
)
select * from MonHoc
insert into MonHoc(Mamonhoc, Tenmonhoc)
values
('MH001', 'Toan')
insert into MonHoc(Mamonhoc, Tenmonhoc)
values
('MH002', 'Van')
insert into MonHoc( Mamonhoc, Tenmonhoc)
values
('NH003', 'Anh'),
('NH004', 'Ly'),
('NH005', 'Hoa')
create table LopHoc (
Malophoc nvarchar(10) primary key,
Tenlophoc nvarchar(20),
rollno nvarchar(10)
)
select * from LopHoc
insert into LopHoc(Malophoc, Tenlophoc, rollno)
values
('T2008', 'T2008A', 'TH2008'),
('T2004', 'T2008A', 'TH2008'),
('T2003', 'T2008A', 'TH2008'),
('T2002', 'T2008A', 'TH2008'),
('T2001', 'T2008A', 'TH2008')
create table PhongHoc (
Tenphonghoc nvarchar(50),
Maphong nvarchar(20) primary key,
Soban int,
Soghe int,
Diachi nvarchar(100)
)
select * from PhongHoc
insert into PhongHoc(Tenphonghoc, Maphong, Soban, Soghe, Diachi)
values
('T2008A', 'T2008', '15', '15', 'Ha noi'),
('T2008A', 'T2002', '15', '15', 'Ha noi'),
('T2008A', 'T2003', '15', '15', 'Ha noi'),
('T2008A', 'T2004', '15', '15', 'Ha noi'),
('T2008A', 'T2005', '15', '15', 'Ha noi')
create table Giogiay (
Malop nvarchar(10) primary key,
GioGiay Nvarchar(20),
Giotra nvarchar(20),
Maphong nvarchar(20)
)
select * from Giogiay
insert into Giogiay(Malop, GioGiay, Giotra, Maphong)
values
('T11', '1:30', '5:00', 'T2008A'),
('T12', '1:30', '5:00', 'T2008A'),
('T13', '1:30', '5:00', 'T2008A'),
('T14', '1:30', '5:00', 'T2008A'),
('T15', '1:30', '5:00', 'T2008A')![vuong huu phu [T2008A]](https://www.gravatar.com/avatar/307a5cf29780afab49706dc8b15b86c6.jpg?s=80&d=mm&r=g){kind=avatar}
vuong huu phu
2020-11-20 13:15:09
create table Student (
rollno nvarchar(20) primary key ,
fullname nvarchar(100),
age int,
address nvarchar(200),
email nvarchar(150),
phoneNumber nvarchar(15),
gender nvarchar(20)
)
select * from Student
insert into Student( rollno, fullname, age, address, email, phoneNumber, gender)
values
('1001','A','19','18','A@gmail.com','123456789','nam'),
('1002','B','20','18','A@gmail.com','123456789','nam'),
('1003','C','19','18','A@gmail.com','123456789','nam'),
('1004','D','20','18','A@gmail.com','123456789','nam'),
('1005','E','19','18','A@gmail.com','123456789','nam')
create table Diem (
point float,
class nvarchar (10),
rollNo nvarchar (20) primary key
)
select * from Diem
insert into Diem(point,class,rollNo)
values
('1','t1','001'),
('9','t3','002'),
('5','t4','003'),
('10','t7','004'),
('7','t1','005')
create table monhoc (
ma_mon_hoc nvarchar (10) primary key,
ten_mon_hoc nvarchar (30)
)
insert into monhoc(ma_mon_hoc ,ten_mon_hoc )
values
('q111','toan'),
('q112','vat li'),
('q113','hoa hoc'),
('q114','sinh hoc'),
('q115','đia li')
select * from monhoc
create table class (
ma_lop_hoc nvarchar (20),
name nvarchar (30),
rollno nvarchar (20) primary key
)
insert into class(ma_lop_hoc,name,rollno)
values
('e111','qqq','123'),
('e113','ttt','114'),
('e111','eee','125'),
('e113','uuu','116'),
('e111','ooo','127')
select * from class
create table room (
name nvarchar (30),
rollno nvarchar (20) primary key,
table_room int,
chair int,
address nvarchar (200)
)
insert into room (name,rollno,table_room,chair,address)
values
('A','001','10','20','ha noi'),
('B','002','10','20','ha noi'),
('C','003','10','20','ha noi'),
('D','004','10','20','ha noi'),
('E','005','10','20','ha noi')
select * from room
create table book (
rollno nvarchar (20) primary key ,
start_ time,
end_ time,
room nvarchar (20),
)
insert into book(rollno,start_,end_,room)
values
('001','14:00:00','18:00:00','0123'),
('002','15:00:00','19:00:00','0124'),
('003','16:00:00','20:00:00','0125'),
('004','17:00:00','21:00:00','0126'),
('005','18:00:00','22:00:00','0127')
select * from book![Nguyễn Hữu Hiếu [T2008A]](https://www.gravatar.com/avatar/ca2884508b617fee77f000c7d99c219d.jpg?s=80&d=mm&r=g){kind=avatar}
Nguyễn Hữu Hiếu
2020-11-20 09:36:02
create table Student (
rollno nvarchar(20) primary key,
fullname nvarchar(30),
age int,
address nvarchar(150),
email nvarchar(40),
phoneNumber nvarchar(20),
gender nvarchar(10),
)
create table mark (
rollno nvarchar(20) primary key,
mamonhoc nvarchar(30),
diem float,
)
create table monhoc (
tenmonhoc nvarchar(20),
mamonhoc nvarchar(30) primary key,
)
create table lophoc (
malophoc nvarchar(20) primary key,
tenlophoc nvarchar(20),
)
create table phonghoc (
tenphonghoc nvarchar(20),
maphonghoc nvarchar(30) primary key,
sobanhoc int,
soghe int,
diachiphonghoc nvarchar(50)
)
create table book (
malophoc nvarchar(20) primary key,
giobatdau datetime,
gioketthuc datetime,
maphonghoc nvarchar(30),
)
select * from Student
select * from mark
select * from monhoc
select * from lophoc
select * from phonghoc
select * from book
insert into Student(rollno, fullname, age, address, email, phoneNumber, gender)
values
('R001', 'Nguyen Van A', 22, 'Ha Noi', 'sv1@gmail.com', '090232323', 'Nam'),
('R002', 'Nguyen Van A', 22, 'Ha Nam', 'sv1@gmail.com', '090232333', 'Nam'),
('R003', 'Nguyen Van A', 22, 'Ha Nam', 'sv1@gmail.com', '09023232323', 'Nam'),
('R004', 'Nguyen Van A', 22, 'Ha Noi', 'sv1@gmail.com', '09023232323', 'Nu'),
('R005', 'Nguyen Van A', 22, 'Ha Noi', 'sv1@gmail.com', '090232323', 'Nu')
insert into mark(rollno, mamonhoc, diem)
values
('R001', 'MH001', 8.5),
('R002', 'MH001', 8),
('R003', 'MH001', 8),
('R004', 'MH001', 8),
('R005', 'MH001', 8)
insert into monhoc(tenmonhoc, mamonhoc)
values
('SBVL', 'MH002'),
('CKC', 'MH003'),
('TKCT', 'MH004'),
('BTCT', 'MH005')
insert into lophoc(malophoc, tenlophoc, rollnoLophoc)
values
('LH001', 'CKC F2', '01'),
('LH002', 'CKC F2', '02'),
('LH003', 'CKC F2', '03'),
('LH004', 'CKC F2', '04'),
('LH005', 'CKC F2', '05')
insert into phonghoc(tenphonghoc, maphonghoc, sobanhoc, soghe, diachiphonghoc)
values
('Phong hoc Toan', 'P101A', 25, 25, 'Tang 1 Nha A'),
('Phong hoc Toan 2', 'P102A', 25, 25, 'Tang 1 Nha B'),
('Phong hoc Toan 3', 'P103A', 25, 25, 'Tang 1 Nha C'),
('Phong hoc Toan 4', 'P104A', 25, 25, 'Tang 1 Nha D'),
('Phong hoc Toan 5', 'P105A', 25, 25, 'Tang 1 Nha E')
insert into book(malophoc, giobatdau, gioketthuc, maphonghoc)
values
('LH001', '2020-08-20 15:02:22', '2020-08-20 15:02:22','P101A'),
('LH002', '2020-08-20 15:02:22', '2020-08-20 15:02:22','P101A'),
('LH003', '2020-08-20 15:02:22', '2020-08-20 15:02:22','P101A'),
('LH004', '2020-08-20 15:02:22', '2020-08-20 15:02:22','P101A'),
('LH005', '2020-08-20 15:02:22', '2020-08-20 15:02:22','P101A')
alter table lophoc
add rollnoLophoc nvarchar(10)
delete from lophoc![ĐINH VĂN MẠNH [community]](https://www.gravatar.com/avatar/1dafe1c52c029887c3afbc5164aa4dbb.jpg?s=80&d=mm&r=g){kind=avatar}
ĐINH VĂN MẠNH
2020-08-02 12:48:01
create database quan_ly_sinh_vien
go
use quan_ly_sinh_vien
go
create table student (
rollno nvarchar(20) primary key,
fullname nvarchar(50) not null,
age nvarchar(10),
email nvarchar(200),
gender nvarchar(16),
phone_number nvarchar(16),
address nvarchar(200)
)
go
create table monhoc (
monhoc_id nvarchar(20) primary key,
monhoc_name nvarchar(50) not null
)
go
create table diem (
diem nvarchar(20) primary key,
rollno nvarchar(20) not null,
monhoc_id nvarchar(20),
constraint fk_diem primary key (rollno, monhoc_id),
constraint fk_rollno foreign key (rollno) references student (rollno),
constraint fk_monhoc_id foreign key (ma_mon_hoc) references monhoc (monhoc_id)
)
go
create table class (
class_id nvarchar(20) primary key,
class_name nvarchar(50) not null,
rollno nvachar(20)
)
go
create table room (
room_id nvarchar(20) primary key,
room_name nvarchar(50) not null,
sobanhoc nvarchar(20),
soghehoc nvarchar(20),
address nvarchar(200)
)
go
create table book (
class_id nvarchar(20) primary key,
room_id nvarchar (20),
start nvarchar(20),
finnish nvarchar(20)
constraint fk_booking primary key (class_id, room_id),
constraint fk_class_id foreign key (class_id) references class (class_id),
constraint fk_room_id foreign key (room_id) references room (room_id)
)
go
select * from student
select * from diem
select * from monhoc
select * from class
select * from room
select * from book
insert into student(rollno, fullname, age, email , gender, phone_number, address)
values
('R001', 'TRAN VAN A', '26','tranvana@gmail.com', 'NAM', '+84967025996', '285 DOI CAN, BA DINH, HA NOI, VIET NAM'),
('R002', 'TRAN VAN B', '25','tranvanb@gmail.com', 'NAM', '+84967024996', '286 DOI CAN, BA DINH, HA NOI, VIET NAM'),
('R003', 'TRAN VAN C', '24','tranvanc@gmail.com', 'NU', '+84967025196', '215 DOI CAN, BA DINH, HA NOI, VIET NAM'),
('R004', 'TRAN VAN D', '23','tranvand@gmail.com', 'NAM', '+84967023996', '225 DOI CAN, BA DINH, HA NOI, VIET NAM')
insert into diem(diem,rollno,monhoc_id)
values
('8','R001','SQL1'),
('7','R002','SQL1'),
('9','R003','SQL1'),
('10','R004','SQL1')
insert into monhoc(monhoc_id,monhoc_name)
values
('SQl1','SQL'),
('SQl1','SQL'),
('SQl1','SQL'),
('SQl1','SQL')
insert into class(class_id,class_name,rollno)
values
('Cl01','C2002L','R001'),
('Cl01','C2002L','R002'),
('Cl01','C2002L','R003'),
('Cl01','C2002L','R004')
insert into room(room_id,room_name,sobanhoc,soghehoc,address)
values
('SQl01','MONSQL','24','48','285 DOI CAN, BA DINH, HA NOI, VIET NAM'),
('SQl01','MONSQL','24','48','285 DOI CAN, BA DINH, HA NOI, VIET NAM'),
('SQl01','MONSQL','24','48','285 DOI CAN, BA DINH, HA NOI, VIET NAM'),
('SQl01','MONSQL','24','48','285 DOI CAN, BA DINH, HA NOI, VIET NAM')
insert into book(class_id,room_id,start,finnish)
values
('Cl01','SQl01','6','10'),
('Cl01','SQl01','6','10'),
('Cl01','SQl01','6','10'),
('Cl01','SQl01','6','10')![Phạm Thế Hiền [community]](https://www.gravatar.com/avatar/f51f757b3b3e201171a15f388a427c1f.jpg?s=80&d=mm&r=g){kind=avatar}
Phạm Thế Hiền
2020-08-01 14:15:05
Pham The Hien C2002l
#SQLQuery1.sql
create database quan_ly_sinh_vien;
use quan_ly_sinh_vien;
create table student (
rollno varchar(20) primary key,
fullname nvarchar(50),
age int,
address nvarchar(200),
email nvarchar(100),
phoneNumber nvarchar(50),
gender varchar(10)
)
create table bang_mon_hoc (
ma_mon_hoc varchar(20) primary key,
ten_mon_hoc nvarchar(100)
)
create table bang_diem(
diem int,
rollNo varchar(20),
ma_mon_hoc varchar(20),
constraint fk_bang_diem primary key (rollno, ma_mon_hoc),
constraint fk_rollno foreign key (rollno) references student (rollno),
constraint fk_ma_mon_hoc foreign key (ma_mon_hoc) references bang_mon_hoc (ma_mon_hoc)
);
create table room (
room_name nvarchar(50),
room_id int primary key,
address nvarchar(200),
table_no int,
chair int
);
create table class (
class_id int primary key,
class_name nvarchar(50),
rollNo nvarchar(20)
);
create table booking (
class_id int,
start_time datetime,
end_time datetime,
room_id int,
constraint fk_booking primary key (class_id, room_id),
constraint fk_class_id foreign key (class_id) references class (class_id),
constraint fk_room_id foreign key (room_id) references room (room_id)
);
insert into student(rollno, fullname, age, address, email, phoneNumber, gender)
values
('R123', 'pham the hien', '18', '125 doi can, ba dinh, ha noi', 'hien.pt.739@aptechlearning.edu.vn', '+841256989636', 'Nam'),
('R002', 'pham the hien 1', '19', '125 doi can, ba dinh, ha noi', 'hien.pt.7391@aptechlearning.edu.vn', '+841256989637', 'Nam'),
('R003', 'pham the hien 2', '20', '125 doi can, ba dinh, ha noi', 'hien.pt.7392@aptechlearning.edu.vn', '+841256989638', 'Nam'),
('R004', 'pham the hien 3', '21', '125 doi can, ba dinh, ha noi', 'hien.pt.7393@aptechlearning.edu.vn', '+841256989639', 'Nam'),
('R005', 'pham the hien 4', '22', '125 doi can, ba dinh, ha noi', 'hien.pt.7394@aptechlearning.edu.vn', '+841256989630', 'Nam')
select * from student
insert into bang_mon_hoc (ma_mon_hoc, ten_mon_hoc)
values (1, 'Toan'),
(2, 'Hoa Hoc'),
(3, 'Ngu Van'),
(4, 'Anh Van'),
(5, 'Lich Su'),
(6, 'Dia Ly')
select * from bang_mon_hoc
insert into bang_diem (diem, rollNo, ma_mon_hoc)
values
(10, 'R123', 1),
(9, 'R002', 2),
(8, 'R004', 3),
(8, 'R005', 4),
(10, 'R003', 5),
(7, 'R123', 6)
select * from bang_diem
![Bùi Văn Hưởng [community]](https://www.gravatar.com/avatar/4a5de982b1f3b4d749d512ee55993f66.jpg?s=80&d=mm&r=g){kind=avatar}
Bùi Văn Hưởng
2020-08-01 14:14:45
em Bùi Văn Hưởng nộp bài
-- Tao CSDL
create database C2002L
go
-- active CSDL C2002L
use C2002L
go
-- THIET KE CAU TRUC TABLE TRONG CSDL
-- Tao bang sinh vien : Student
create table student (
rollno nvarchar(20) primary key,
fullname nvarchar(50) not null,
age nvarchar(10),
address nvarchar(50),
email nvarchar(50),
phoneNumber nvarchar(20),
gender nvarchar(50),
)
go
create table bangdiem (
roolNo nvarchar (50),
mamonhoc nvarchar (50),
)
go
create table bangmonhoc (
manomhoc nvarchar(50),
tenmonhoc nvarchar(50),
)
go
create table Banglophoc (
manomhoc nvarchar(50),
tenlophoc nvarchar(50),
rollNo nvarchar(50),
)
go
create table Bangphonghoc (
tenphonghoc nvarchar(50),
maphonghoc nvarchar(50),
sobanhoc nvarchar(50),
soghehoc nvarchar(50),
diachilophoc nvarchar(50),
)
go
create table Bangbookgioday (
malophoc nvarchar(50),
giobatdauhoc nvarchar(50),
giatralop nvarchar(50),
maphonghoc nvarchar(50),
)
go
select * from student
select * from bangdiem
select * from bangmonhoc
select * from Banglophoc
select * from Bangphonghoc
select * from Bangbookgioday
insert into student(rollno, fullname, age, address, email, phoneNumber, gender)
values
('ROO', 'TRANVANA', '1999-12-28','HANOI', 'TRANVANA@gmail.com', '+08453823723' , 'Nam'),
('RO1', 'TRANVANB', '1999-12-23','HANAM', 'TRANVANBgmail.com', '+08453823723' , 'Nam'),
('RO2', 'TRANVANC', '1999-07-22','TAHINGUYEN', 'TRANVANC@gmail.com', '+08453823723' , 'Nam'),
('RO3', 'TRANVAND', '1999-02-25', 'HAIPHONG', 'TRANVAND@gmail.com', '+08453823723', 'Nam'),
('RO4', 'TRANVANE', '1999-03-26', 'QUANGNINH', 'TRANVANE@gmail.com', '+08453823723' , 'Nam')
insert into bangdiem(roolNo,mamonhoc)
values
('ROO','1928'),
('RO1','1922'),
('RO2','1923'),
('RO3','1948'),
('RO4','1948')
insert into bangmonhoc(manomhoc,tenmonhoc)
values
('1928','TOAN'),
('1922','VAN'),
('1923','TIENGANH'),
('1948','LY'),
('1948','HOA')
insert into Banglophoc(manomhoc,tenlophoc,rollNo)
values
('1928','C2020L','ROO'),
('1922','C2022L','RO1'),
('1923','C20234L','RO2'),
('1948','C2023L','RO3'),
('1948','C2021L','RO4')
insert into Bangphonghoc(![Trần Trung Hiếu [community]](https://www.gravatar.com/avatar/feb52dec56ce389c110a97d594c64932.jpg?s=80&d=mm&r=g){kind=avatar}
Trần Trung Hiếu
2020-08-01 14:14:04
Em là Trần Trung Hiếu, ở lớp C2002l
-- Tạo CSDL
create database quan_ly_sinh_vien
go
-- Active CSDL
use quan_ly_sinh_vien
go
-- THIẾT KẾ CẤU TRÚC TABLE
-- Tạo bảng "Sinh Viên"
create table SinhVien (
rollno nvarchar(20) primary key,
fullname nvarchar(50) not null,
age int,
svaddress nvarchar(150),
email varchar(100),
phoneNumber nvarchar(16),
gender nvarchar(10)
)
go
-- Tạo bảng "Điểm"
create table Diem (
diem int not null,
rollno nvarchar(20) not null,
maMonHoc nvarchar(20) not null,
)
go
-- Tạo bảng "Môn học"
create table MonHoc (
maMonHoc nvarchar(20) primary key,
tenMonHoc nvarchar(20) not null
)
go
-- Tạo bảng "Lớp học"
create table LopHoc (
maLopHoc nvarchar(20) primary key,
tenLopHoc nvarchar(20) not null,
rollNo nvarchar(20) not null
)
go
-- Tạo bảng "Phòng học"
create table PhongHoc (
tenPhong nvarchar(20) not null,
maPhong nvarchar(20) primary key,
soBan int not null,
soGhe int not null,
diaChi nvarchar(50) not null
)
go
-- Tạo bảng "Book Giờ dạy"
create table newClass (
maLopHoc nvarchar(20),
gioBatDau time(0) not null,
gioTraLop time(0) not null,
maPhong nvarchar(20) not null
)
go
-- TEST DỮ LIỆU
select * from SinhVien
select * from Diem
select * from MonHoc
select * from LopHoc
select * from PhongHoc
select * from newClass
insert into SinhVien(rollno, fullname, age, svaddress, email, phoneNumber, gender)
values
('R001','TRAN VAN A','18','HOANG QUOC VIET, CAU GIAY, HA NOI','Atranvan@gmail.com','+843200534034','NAM'),
('R002','TRAN VAN B','20','NGUYEN TRAI, THANH XUAN, HA NOI','Btranvan@gmail.com','+843200534034','NU'),
('R003','TRAN VAN C','15','TRAN PHU, HA DONG, HA NOI','Ctranvan@gmail.com','+843200534034','NAM'),
('R004','TRAN VAN D','19','LE THANH NGHI, HOANG MAI, HA NOI','Dtranvan@gmail.com','+843200534034','NU'),
('R005','TRAN VAN E','17','KIM MA, BA DINH, HA NOI','Etranvan@gmail.com','+843200534034','NAM')
insert into Diem (diem, rollno, maMonHoc)
values
('10','R001','S01'),
('7','R002','S04'),
('9','R003','S02'),
('8','R004','S03'),
('5','R005','S05')
insert into MonHoc (maMonHoc, tenMonHoc)
values
('S01','TOAN'),
('S02','VAN'),
('S03','ANH'),
('S04','LY'),
('S05','HOA')
insert into LopHoc (maLopHoc, tenLopHoc, rollNo)
values
('C001','C2001L','R001'),
('C002','C2002M','R002'),
('C003','C2004A','R003'),
('C004','C2003L','R004'),
('C005','C2005L','R005')
insert into PhongHoc (tenPhong, maPhong, soBan, soGhe, diaChi)
values
('305','P305','40','40','TANG 3 - A2'),
('603','P603','70','70','TANG 6 - A2'),
('201','P201','50','50','TANG 2 - A2'),
('G01','PG01','25','25','TANG G - A2'),
('504','P504','30','30','TANG 5 - A2')
insert into newClass (maLopHoc, gioBatDau, gioTraLop, maPhong)
values
('C001','07:00','09:30','P504'),
('C002','09:30','11:00','PG01'),
('C003','13:30','15:30','P201'),
('C004','15:30','17:00','P603'),
('C005','17:00','18:30','P305')
