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 BT1763

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à < 20

Liên kết rút gọn:

https://gokisoft.com/1763

Bình luận

avatar
Nguyễn Minh Hiếu [community,C2010L]
2021-04-13 06:51:34


#quan_ly_sinh_vien.sql


create database QLSV1
create table svien
(
	 rollno nvarchar(20)  primary key not null, 
	 fullname varchar(20), 
	 age int, 
	 address nvarchar(100), 
	 email nvarchar(100), 
	 phoneNumber int, 
	 gender varchar(20)
)
create table sub
(
	subjectid int primary key,
	subjectname nvarchar(50)
)

create table diem
(
	mark int,
	rollno nvarchar(50),
	subjectid int
)

create table lophoc
(
	classid int primary key identity(1,1),
	classname nvarchar(20),
	rollno nvarchar(20)
)
create table roomclass
(
	roomname nvarchar(20),
	roomid int primary key identity(1,1),
	ban int,
	chair int,
	address nvarchar(50),
	check(ban>0),
	check(chair>0)
)
insert into svien values
	('R001','Nguyen Van A',20,'12 street ha noi','a@gmail.com',0921063488,'nam'),
	('R002','Nguyen Van B',20,'12 street ha noi','b@gmail.com',0921063488,'nam'),
	('R003','Nguyen Van C',20,'12 street ha noi','c@gmail.com',0921063488,'nam'),
	('R004','Nguyen Van D',20,'12 street ha noi','d@gmail.com',0921063488,'nam'),
	('R005','Nguyen Van E',20,'12 street ha noi','e@gmail.com',0921063488,'nam')
	
insert into sub values
	(1,'CNTT'),
	(2,'QTDL'),
	(3,'KTCT'),
	(4,'QTKD'),
	(5,'NNA')
	
insert into diem values
	(10,'R001',1),
	(7,'R001',1),
	(8,'R002',2),
	(9,'R003',3),
	(5,'R004',4)
	
insert into lophoc values
	('C2010L','R001'),
	('C2011L','R001'),
	('C2012L','R002'),
	('C2013L','R003'),
	('C2014L','R005')
	
insert into roomclass values
	('class1',7,20,'tang 5'),
	('class1',2,3,'tang 5'),
	('class1',10,30,'tang 5'),
	('class1',5,20,'tang 5'),
	('class1',6,18,'tang 5')
	
alter table diem add constraint fk_subjectid foreign key (subjectid) references sub(subjectid)
select roomclass.roomid,roomname,ban,chair
from roomclass
where roomclass.ban >5
and roomclass.chair >5

select roomclass.roomid,roomname,ban,chair
from roomclass 
where roomclass.ban  between 5 and 20
and roomclass.chair between 5 and 20


avatar
Võ Như Việt [C2010L]
2021-04-03 08:31:55



create table SinhVien(
	rollno nvarchar(4) primary key,
	fullname nvarchar(50),
	age int,
	adddress nvarchar(50),
	email nvarchar(50),
	phoneNumber nvarchar(10),
	gender nvarchar(5)
)
insert into SinhVien(rollno,fullname,age,adddress,email,phoneNumber,gender)
values
('001','Tran Van A', 20,'Tran Dai Nghia','a@gmail.com','0123456789','nam'),
('002','Tran Van B', 22,'Tran Dai Nghia','b@gmail.com','0123456789','nu'),
('003','Tran Van C', 24,'Tran Dai Nghia','c@gmail.com','0123456789','nam'),
('004','Tran Van D', 26,'Tran Dai Nghia','d@gmail.com','0123456789','nu'),
('005','Tran Van E', 28,'Tran Dai Nghia','e@gmail.com','0123456789','nam')

-- Mon Hoc--
create table MonHoc(
	object_Number int primary key identity(1,1),
	object_Names nvarchar(20)
)
insert into MonHoc(object_Names)
values
('HTML'),
('Lap Trinh SQL Server'),
('CSS/JS')
--bo sung mon hoc--
insert into MonHoc(object_Names)values
('Java'),
('Android')
select * from MonHoc
-- Bang Diem--
create table BangDiem(
	Mark int,
	rollno nvarchar(4),
	object_Number int,
	primary key(rollno,object_Number)
)
insert into BangDiem(Mark,rollno,object_Number)
values
(9,'001',1),
(7,'002',2),
(8,'003',3)
--bosung diem--
insert into BangDiem(Mark,rollno,object_Number)
values
(6,'004',4),
(10,'005',5)
select * from BangDiem
-- Bang Lop--
create table Class_C(
	Class_No int identity(1,1),
	Class_Names nvarchar(10),
	rollno nvarchar(4)
)
insert into Class_C(Class_Names,rollno)
values
('C2010L','001'),
('C2011L','002'),
('C2012L','003'),
('C2013L','004'),
('C2014L','005')
-- Phong Hoc--
create table Room_C(
	Room_Names nvarchar(10),
	Room_No int primary key identity(1,1),
	No_table int,
	No_chair int,
	Id_Class nvarchar(5)	
)
insert into Room_C(Room_Names,No_table,No_chair,Id_Class)
values
('Lab1',6,11,'CL10'),
('Lab2',7,13,'CL11'),
('Lab3',8,15,'CL12'),
('Lab4',9,17,'CL13'),
('Lab5',10,19,'CL14')
select * from Room_C


avatar
Vũ Trung Kiên [C2009I]
2021-01-24 10:04:16


#QuanLySinhVien.sql


create database quan_ly_sinh_vien
use quan_ly_sinh_vien

create table SinhVien (
	rollno nvarchar(20), 
	fullname nvarchar(50), 
	age int, 
	address nvarchar(50), 
	email nvarchar(50), 
	phoneNumber bigint, 
	gender nvarchar(20)
)

insert into SinhVien(rollno, fullname, age, address, email, phoneNumber, gender)
values
('001', 'Tran Van A', 20, 'HaNoi', 'TVA@gmail.com', 01235464, 'Nam'),
('002', 'Tran Van B', 30, 'ThaiBinh', 'TVB@gmail.com', 2352366, 'Nam'),
('003', 'Tran Van C', 21, 'HaNoi', 'TVC@gmail.com', 26262626, 'Nu'),
('004', 'Tran Van D', 50, 'HaTay', 'TVD@gmail.com', 2626357, 'Nu'),
('005', 'Tran Van E', 25, 'HCM', 'TVE@gmail.com', 3864583, 'Nam')

create table BangDiem (
	Diem int,
	rollno nvarchar(20),
	MaMonHoc nvarchar(20)
)

insert into BangDiem(Diem, rollno, MaMonHoc)
values
(5, '001', 'T'),
(10, '002', 'A'),
(6, '003', 'VL'),
(9, '004', 'S'),
(2, '005', 'D')

create table MonHoc (
	MaMonHoc nvarchar(20),
	TenMonHoc nvarchar(20)
)

insert into MonHoc(MaMonHoc, TenMonHoc)
values
('T', 'Toan'),
('A', 'Anh'),
('VL', 'Vat Ly'),
('S', 'Su'),
('D', 'Dia')

create table LopHoc (
	MaLopHoc nvarchar(20),
	TenLopHoc nvarchar(50),
	rollno nvarchar(20)
)

insert into LopHoc(MaLopHoc, TenLopHoc, rollno)
values
('TC', 'Toan Chieu', '001'),
('AC', 'Anh Chieu', '002'),
('SC', 'Su Chieu', '003'),
('TS', 'Toan Sang', '004'),
('DS', 'Dia Sang', '005')

create table PhongHoc (
	TenPhongHoc nvarchar(50),
	MaPhongHoc nvarchar(20),
	SoBanHoc int,
	SoGheHoc int,
	DiaChi nvarchar(50)
)

insert into PhongHoc(TenPhongHoc, MaPhongHoc, SoBanHoc, SoGheHoc, DiaChi)
values
('Phong 1 tang 1', '101', 50, 100, 'HaNoi'),
('Phong 2 tang 6', '206', 30, 60, 'HCM'),
('Phong 3 tang 5', '305', 60, 120, 'ThaiBinh'),
('Phong 4 tang 4', '404', 20, 40, 'HaTay'),
('Phong 5 tang 10', '510', 25, 50, 'HaNoi')

create table GioDay (
	MaLopHoc nvarchar(20),
	GioDay nvarchar(50),
	GioVe nvarchar(50),
	MaPhongHoc nvarchar(20)
)

insert into GioDay(MaLopHoc, GioDay, GioVe, MaPhongHoc)
values
('TC', '5', '6', '101'),
('AS', '4', '5', '202'),
('TS', '3', '4', '304'),
('SC', '6', '7', '404'),
('DS', '2', '3', '510')

select * from SinhVien
select * from BangDiem
select * from MonHoc
select * from LopHoc
select * from PhongHoc
select * from GioDay


avatar
Lê Trọng Nghĩa [community,C2009I]
2021-01-21 09:12:53



drop table test2
drop table test3
drop table test4
drop table test5
drop table test6
drop table test7
create table test2 (
	rollno nvarchar(50),
	fullname nvarchar(50),
	age int,
	address nvarchar(50),
	email nvarchar(50),
	phoneNumber nvarchar(50),
	gender nvarchar(50)
)
create table test3 (
	rollno nvarchar(50),
	ma nvarchar(50),
	diem int
)
create table test4 (
	ma_mon_hoc nvarchar(50),
	ten_mon nvarchar(50)
)
create table test5 (
	rollno nvarchar(50),
	ma_lop_hoc nvarchar(50),
	ten_lop_hoc nvarchar(50)
)
create table test6 (
	ten_phong nvarchar(50),
	ma_phong nvarchar(50),
	so_ban int,
	so_ghe int,
	dia_chi nvarchar(50)
)
create table test7 (
	ma_lop nvarchar(50),
	gio_bat_dau datetime,
	gio_tra datetime,
	ma_phong nvarchar(50)
	
)
insert into test2(rollno, fullname, age, address, email, phoneNumber, gender)
values
('1', 'tran van a', '18', 'ha noi', 'trana@gmail.com', '1234', 'nam'),
('2', 'tran van b', '18', 'ha noi', 'tranb@gmail.com', '12345', 'nam'),
('33', 'tran van c', '18', 'ha noi', 'tranc@gmail.com', '123456', 'nam'),
('44', 'tran van d', '18', 'ha noi', 'trand@gmail.com', '1234567', 'nam'),
('55', 'tran van e', '18', 'ha noi', 'trane@gmail.com', '12345678', 'nam')
select * from test2
 insert into test3(rollno, ma, diem)
values
('1', 'abc', '18'),
('2', 'bav', '18'),
('3', 'cbv', '18'),
('4', 'abv', '18'),
('5', 'dbv', '18')
select * from test3
 insert into test4(ma_mon_hoc, ten_mon)
values
('1', 'abc'),
('2', 'bav'),
('3', 'aav'),
('4', 'cav'),
('5', 'dav')
select * from test4
insert into test5(rollno, ma_lop_hoc, ten_lop_hoc)
values
('1', 'abc', 'a1'),
('2', 'bav', 'a2'),
('3', 'cbv', 'a3'),
('4', 'cav', 'a4'),
('5', 'cdv', 'a5')
 select * from test5
 insert into test6(ten_phong, ma_phong, so_ban, so_ghe, dia_chi)
values
('a', 'a1', '18', '18', 'aptech'),
('b', 'b1', '18', '18', 'aptech'),
('c', 'c1', '18', '18', 'aptech'),
('d', 'd1', '18', '18', 'aptech'),
('e', 'e1', '18', '18', 'aptech')
select * from test6
insert into test7(ma_lop, gio_bat_dau, gio_tra, ma_phong)
values
('a1', '1990-02-06 12:15:55', '1990-02-06 17:30:00', 'a'),
('b1', '1990-02-06 12:15:55', '1990-02-06 17:30:00', 'b'),
('c1', '1990-02-06 12:15:55', '1990-02-06 17:30:00', 'c'),
('d1', '1990-02-06 12:15:55', '1990-02-06 17:30:00', 'd'),
('e1', '1990-02-06 12:15:55', '1990-02-06 17:30:00', 'e')
select * from test7


avatar
Triệu Văn Lăng [T2008A]
2020-11-29 07:13:21



create database bai1763
use bai1763

create table sinh_vien (
	rollno nvarchar(20),
	fullname nvarchar(100),
	age int,
	address nvarchar(150),
	email nvarchar(100),
	phoneNumber nvarchar(16),
	gender nvarchar(10)
)
insert into sinh_vien(rollno, fullname, age, address, email, phoneNumber, gender)
values
('R001', 'TRAN VAN A', '18', 'Ha noi', 'R001@gmail.com', '0969696969', 'nam'),
('R002', 'TRAN VAN B', '18', 'Ha noi', 'R002@gmail.com', '0969696969', 'nu'),
('R003', 'TRAN VAN C', '18', 'Ha noi', 'R003@gmail.com', '0969696969', 'nam'),
('R004', 'TRAN VAN D', '18', 'Ha noi', 'R004@gmail.com', '0969696969', 'nam'),
('R005', 'TRAN VAN E', '18', 'Ha noi', 'R005@gmail.com', '0969696969', 'nu')
select * from sinh_vien

create table bang_diem (
	diem float,
	rollno nvarchar(20),
	ma_MH nvarchar(20)
)
insert into bang_diem(rollno, diem, ma_MH)
values
('R001', '7', 'LEBJF'),
('R002', '8', 'LEBJF'),
('R003', '9', 'LEBJF'),
('R004', '8.5', 'LEBJF'),
('R005', '9', 'LEBJF')
select * from bang_diem

create table mon_hoc (
	ma_MH nvarchar(20),
	ten_MH nvarchar(20)
)
insert into mon_hoc(ma_MH, ten_MH)
values
('LEBJF', 'HTML/CSS'),
('LEBJF', 'HTML/CSS'),
('LEBJF', 'HTML/CSS'),
('LEBJF', 'HTML/CSS'),
('LEBJF', 'HTML/CSS')
select * from mon_hoc

create table lop_hoc (
	ma_LH nvarchar(20),
	ten_LH nvarchar(20),
	rollno nvarchar(20)
)
insert into lop_hoc(ma_LH, ten_LH, rollno)
values
('P208', 'T2008A', 'R001'),
('P208', 'T2008A', 'R002'),
('P208', 'T2008A', 'R003'),
('P208', 'T2008A', 'R004'),
('P208', 'T2008A', 'R005')
select * from lop_hoc

create table phong_hoc (
	ten_PH nvarchar(20),
	ma_PH nvarchar(20),
	so_ban int,
	so_ghe int,
	dia_chi nvarchar(100)
)
insert into phong_hoc(ten_PH, ma_PH, so_ban, so_ghe, dia_chi)
values
('T200A', 'P208', '20', '40', 'so 8 ton that thuyet'),
('T200A', 'P208', '20', '40', 'so 8 ton that thuyet'),
('T200A', 'P208', '20', '40', 'so 8 ton that thuyet'),
('T200A', 'P208', '20', '40', 'so 8 ton that thuyet'),
('T200A', 'P208', '20', '40', 'so 8 ton that thuyet')
select * from phong_hoc

create table book_gio_day (
	ma_LH nvarchar(20),
	bat_dau time,
	ket_thuc time,
	ma_PH nvarchar(20)
)
insert into book_gio_day(ma_LH, bat_dau, ket_thuc, ma_PH)
values
('T2008A', '13:00:00', '17:00:00', 'P208'),
('T2008A', '13:00:00', '17:00:00', 'P208'),
('T2008A', '13:00:00', '17:00:00', 'P208'),
('T2008A', '13:00:00', '17:00:00', 'P208'),
('T2008A', '13:00:00', '17:00:00', 'P208')
select * from book_gio_day



avatar
Đức Sơn [T2008A]
2020-11-28 10:00:50



create datebase Quan_ly_sinh_vien
use Quan_ly_sinh_vien
create table sinh_vien (
   rollNo nvarchar(50),
   fullname nvarchar(50),
   age int,
   address nvarchar(200),
   email nvarchar(100),
   phoneNum nvarchar(20),
   gender nvarchar(10)
)

create table diem(
   rollNo nvarchar(50),
   diem int,
   ma_mon nvarchar(50),
)

create table mon_hoc(
   ma_mon nvarchar(50),
   ten_mon nvarchar(50)
)

create table lop_hoc(
   rollNo nvarchar(50),
   ma_lop nvarchar(50),
   ten_lop nvarchar(50)
)

create table phong_hoc(
   ten_phong nvarchar(50),
   ma_phong nvarchar(50),
   so_ban nvarchar(100),
   so_ghe nvarchar(100),
   dia_chi nvarchar(200),
)
create table gio_day(
   ma_lop nvarchar(50),
   gio_bat_dau nvarchar(20),
   gio_ket_thuc nvarchar(20),
   ma_phong nvarchar(50),
)

insert into sinh_vien (rollNo, fullname, age, address, email, phoneNum, gender)
values
('R1', 'Duc', '18', 'Thanh hoa', 'abcd@gmail.com', '0987123456', 'Nam'),
('R2', 'Duc', '18', 'Thanh hoa', 'abcd@gmail.com', '0987123456', 'Nam'),
('R3', 'Duc', '18', 'Thanh hoa', 'abcd@gmail.com', '0987123456', 'Nam'),
('R4', 'Duc', '18', 'Thanh hoa', 'abcd@gmail.com', '0987123456', 'Nam'),
('R5', 'Duc', '18', 'Thanh hoa', 'abcd@gmail.com', '0987123456', 'Nam')

insert into diem (rollNo, diem, ma_mon)
values
('R1', '5', '2001'),
('R2', '5', '2001'),
('R3', '5', '2001'),
('R4', '5', '2001'),
('R5', '5', '2001')
insert into mon_hoc (ma_mon, ten_mon)
values
('1001', 'HTML'),
('1001', 'Lap Trinh C'),
('1001', 'HTML'),
('1001', 'Lap Trinh C'),
('1001', 'HTML')

insert into lop_Hoc (rollNo, ma_lop, ten_lop)
values
('R1', 'M', 'NNNN'),
('R2', 'M', 'NNNN'),
('R3', 'M', 'NNNN'),
('R4', 'M', 'NNNN'),
('R5', 'M ','NNNN')

insert into phong_hoc (ten_phong, ma_phong, so_ban, so_ghe, dia_chi)
values 
('111', 'C', '25', '25', 'My dinh'),
('111', 'C', '25', '25', 'My dinh'),
('111', 'C', '25', '25', 'My dinh'),
('111', 'C', '25', '25', 'My dinh'),
('111', 'C', '25', '25', 'My dinh')

insert into gio_day (ma_lop, gio_bat_dau, gio_ket_thuc, ma_phong)
values
('M', '13h30', '17h30', 'C'),
('M', '13h30', '17h30', 'C'),
('M', '13h30', '17h30', 'C'),
('M', '13h30', '17h30', 'C'),
('M', '13h30', '17h30', 'C')
select * from sinh_vien
select * from diem
select * from mon_hoc
select * from lop_hoc
select * from phong_hoc
select * from gio_day


avatar
Nguyễn Anh Vũ [T2008A]
2020-11-27 09:26:41



create database Quan_Li_SV
use Quan_Li_SV

create table Sinh_Vien (
       rollno nvarchar(20),
	   fullname nvarchar(50),
	   age int,
	   address nvarchar(150),
	   email nvarchar(100),
	   phonenumber nvarchar(100),
	   gerder nvarchar(50),
)

select * from Sinh_Vien

create table Diem (
       diem int,
	   rollno nvarchar(40),
	   ma_mon_hoc nvarchar(30),
)

create table mon_hoc (
       ma_mon_hoc nvarchar(40),
	   ten_mon_hoc nvarchar(40)
)

create table lop_hoc (
       ma_lop_hoc nvarchar(40),
	   ten_lop_hoc nvarchar(40),
	   rollno nvarchar(50),
)

create table phong_hoc (
       ten_phong_hoc nvarchar(40),
	   ma_phong_hoc nvarchar(40),
	   so_ban_hoc int,
	   so_ghe_hoc int,
	   dia_chi_lop_hoc nvarchar (200),
)

create table book_gio_day (
       ma_lop_hoc nvarchar(40),
	   gio_bd_day nvarchar(40),
	   gio_tra_lop nvarchar(40),
	   ma_phong_hoc nvarchar(40),
)
select * from Sinh_Vien

insert into Sinh_Vien(rollno, fullname, age, address, email, phonenumber, gerder)
values
('R000', 'Nguyen Anh Vu', '18', 'HaNam', 'nguyenanhvu280402@gmail.com', '0964657860', 'Nam' ),
('R001', 'Nguyen Anh Vu', '18', 'HaNam', 'nguyenanhvu280402@gmail.com', '0964657860', 'Nam' ),
('R002', 'Nguyen Anh Vu', '18', 'HaNam', 'nguyenanhvu280402@gmail.com', '0964657860', 'Nam' ),
('R003', 'Nguyen Anh Vu', '18', 'HaNam', 'nguyenanhvu280402@gmail.com', '0964657860', 'Nam' ),
('R004', 'Nguyen Anh Vu', '18', 'HaNam', 'nguyenanhvu280402@gmail.com', '0964657860', 'Nam' )

select * from Sinh_Vien

insert into Diem(diem, rollno, ma_mon_hoc)
values
('9', 'R000' , '123'),
('9', 'R001' , '123'),
('9', 'R002' , '123'),
('9', 'R003' , '123'),
('9', 'R004' , '123')
select * from Diem

insert into mon_hoc(ma_mon_hoc, ten_mon_hoc)
values
('123', 'Toan'),
('123', 'Van'),
('123', 'Anh'),
('123', 'Li'),
('123', 'Hoa')
select * from mon_hoc

insert into lop_hoc(ma_lop_hoc, ten_lop_hoc, rollno)
values
('xyz', 'T2008A', '8A Ton That Thuyet' ),
('xyz', 'T2008A', '8A Ton That Thuyet' ),
('xyz', 'T2008A', '8A Ton That Thuyet' ),
('xyz', 'T2008A', '8A Ton That Thuyet' ),
('xyz', 'T2008A', '8A Ton That Thuyet' )
select * from lop_hoc

insert into phong_hoc(ten_phong_hoc, ma_phong_hoc, so_ban_hoc, so_ghe_hoc, dia_chi_lop_hoc)
values
('Thay Diep', '207', '1', '2', '8A Ton That Thuyet'),
('Thay Diep', '207', '1', '2', '8A Ton That Thuyet'),
('Thay Diep', '207', '1', '2', '8A Ton That Thuyet'),
('Thay Diep', '207', '1', '2', '8A Ton That Thuyet'),
('Thay Diep', '207', '1', '2', '8A Ton That Thuyet')
select * from phong_hoc

insert into book_gio_day(ma_lop_hoc, gio_bd_day, gio_tra_lop, ma_phong_hoc)
values
('T2008A', '13h30', '17h30', '207'),
('T2008A', '13h30', '17h30', '207'),
('T2008A', '13h30', '17h30', '207'),
('T2008A', '13h30', '17h30', '207'),
('T2008A', '13h30', '17h30', '207')
select * from book_gio_day


avatar
Bùi Văn Mạnh [T2008A]
2020-11-27 09:24:08



create database Quan_Ly_Sinh_Vien
use Quan_Ly_Sinh_Vien
create table Sinh_Vien(
       rollNo nvarchar(40),
	   fullname nvarchar(50),
	   age int,
	   address nvarchar(100),
	   email nvarchar(100),
	   phoneNum nvarchar(12),
	   gender nvarchar(40),
)
create table Diem(
	   rollNo nvarchar(40),
	   diem int,
	   ma_mon nvarchar(30),
)
create table Mon_Hoc(
	   Ma_Mon nvarchar(40),
	   Ten_Mon nvarchar(50),
)
create table Lop_Hoc(
	   rollNo nvarchar(40),
	   Ma_Lop nvarchar(50),
	   Ten_Lop nvarchar(50),
)
create table Phong_Hoc(
	   Ten_Phong nvarchar(50),
	   Ma_Phong nvarchar(50),
	   So_Ban nvarchar(100),
	   So_Ghe nvarchar(100),
	   Dia_Chi nvarchar(200),
)
create table Gio_Day(
	   Ma_Lop nvarchar(50),
	   Gio_Bat_Dau nvarchar(20),
	   Gio_Ket_Thuc nvarchar(20),
	   Ma_Phong nvarchar(50),
)
insert into Sinh_Vien (rollNo, fullname, age, address, email, phoneNum, gender)
values
('R1', 'Bui Van Manh', '18', 'Nam Dinh', 'Bvmlsr2002@gmail.com', '0915094500', 'Nam'),
('R2', 'Bui Van Manh', '18', 'Nam Dinh', 'Bvmlsr2002@gmail.com', '0915094500', 'Nam'),
('R3', 'Bui Van Manh', '18', 'Nam Dinh', 'Bvmlsr2002@gmail.com', '0915094500', 'Nam'),
('R4', 'Bui Van Manh', '18', 'Nam Dinh', 'Bvmlsr2002@gmail.com', '0915094500', 'Nam'),
('R5', 'Bui Van Manh', '18', 'Nam Dinh', 'Bvmlsr2002@gmail.com', '0915094500', 'Nam')
insert into Diem (rollNo, diem, ma_mon)
values
('R1', '7', '2002'),
('R2', '7', '2002'),
('R3', '7', '2002'),
('R4', '7', '2002'),
('R5', '7', '2002')
insert into Mon_Hoc (Ma_Mon, Ten_Mon)
values
('2002', 'HTML'),
('2002', 'Lap Trinh C'),
('2002', 'HTML'),
('2002', 'Lap Trinh C'),
('2002', 'HTML')
insert into Lop_Hoc (rollNo, Ma_Lop, Ten_Lop)
values
('R1', 'A', 'T2008A'),
('R2', 'A', 'T2008A'),
('R3', 'A', 'T2008A'),
('R4', 'A', 'T2008A'),
('R5', 'A', 'T2008A')
insert into Phong_Hoc (Ten_Phong, Ma_Phong, So_Ban, So_Ghe, Dia_Chi)
values 
('208', 'B', '30', '30', 'Ton That Thuyet'),
('208', 'B', '30', '30', 'Ton That Thuyet'),
('208', 'B', '30', '30', 'Ton That Thuyet'),
('208', 'B', '30', '30', 'Ton That Thuyet'),
('208', 'B', '30', '30', 'Ton That Thuyet')
insert into Gio_Day (Ma_Lop, Gio_Bat_Dau, Gio_Ket_Thuc, Ma_Phong)
values
('A', '13h30', '17h30', 'B'),
('A', '13h30', '17h30', 'B'),
('A', '13h30', '17h30', 'B'),
('A', '13h30', '17h30', 'B'),
('A', '13h30', '17h30', 'B')
select * from Sinh_Vien
select * from Diem
select * from Mon_Hoc
select * from Lop_Hoc
select * from Phong_Hoc
select * from Gio_Day


avatar
Nguyễn đình quân [T2008A]
2020-11-25 09:24:46



create table Student (
	rollNo nvarchar(30),
	fullname nvarchar(50),
	age int,
	address nvarchar(200),
	email nvarchar(100),
	phoneNum nvarchar(100),
	gender nvarchar(40),
)

create table Diem (
	rollNo nvarchar(30),
	diem int,
	ma_mon nvarchar(40),
)

create table Mon_hoc (
	 ma_mon nvarchar(40),
	 ten_mon nvarchar(50),
)

create table Lop_hoc (
	rollNo nvarchar(40),
	ma_lop nvarchar(40),
	ten_lop nvarchar(40),
)

create table Phong_hoc (
	ten_phong nvarchar(50),
	ma_phong nvarchar(50),
	so_ban int,
	so_ghe int,
	dia_chi nvarchar(200),
)

create table Gio_day (
	ma_lop nvarchar(40),
	gio_bat_dau nvarchar(50),
	gio_ket_thuc nvarchar(50),
	ma_phong nvarchar(50),
)

insert into Student (rollNo, fullname, age, address, email, phoneNum, gender)
values
('R100', 'Nguyen Quan', '20', 'Hanoi', 'dtnm@gmail.com', '0984406101', 'Male'),
('R100', 'Nguyen Quan', '20', 'Hanoi', 'dtnm@gmail.com', '0984406101', 'Male'),
('R100', 'Nguyen Quan', '20', 'Hanoi', 'dtnm@gmail.com', '0984406101', 'Male'),
('R100', 'Nguyen Quan', '20', 'Hanoi', 'dtnm@gmail.com', '0984406101', 'Male'),
('R100', 'Nguyen Quan', '20', 'Hanoi', 'dtnm@gmail.com', '0984406101', 'Male')


insert into Diem (rollNo, diem, ma_mon)
values
('R100', '8', 'ABC'),
('R100', '8', 'ABC'),
('R100', '8', 'ABC'),
('R100', '8', 'ABC'),
('R100', '8', 'ABC')


insert into Mon_hoc (ma_mon, ten_mon)
values
('ABC', 'Toan'),
('ABC', 'Toan'),
('ABC', 'Toan'),
('ABC', 'Toan'),
('ABC', 'Toan')


insert into Lop_hoc (rollNo, ma_lop, ten_lop)
values
('R100', 'A1', 'B1'),
('R100', 'A1', 'B1'),
('R100', 'A1', 'B1'),
('R100', 'A1', 'B1'),
('R100', 'A1', 'B1')

insert into Phong_hoc (ten_phong, ma_phong, so_ban, so_ghe, dia_chi)
values
('E1', 'C1', '12', '23', 'abcabc'),
('E1', 'C1', '12', '23', 'abcabc'),
('E1', 'C1', '12', '23', 'abcabc'),
('E1', 'C1', '12', '23', 'abcabc'),
('E1', 'C1', '12', '23', 'abcabc')

insert into Gio_day (ma_lop, gio_bat_dau, gio_ket_thuc, ma_phong)
values
('asdf', 'zxcv', 'bnmv', 'qwerty'),
('asdf', 'zxcv', 'bnmv', 'qwerty'),
('asdf', 'zxcv', 'bnmv', 'qwerty'),
('asdf', 'zxcv', 'bnmv', 'qwerty'),
('asdf', 'zxcv', 'bnmv', 'qwerty')

select * from Student
select * from Diem
select * from Mon_hoc
select * from Lop_hoc
select * from Phong_hoc
select * from Gio_day


avatar
Đặng Trần Nhật Minh [T2008A]
2020-11-25 03:54:52



create table Student (
	rollNo nvarchar(30),
	fullname nvarchar(50),
	age int,
	address nvarchar(200),
	email nvarchar(100),
	phoneNum nvarchar(100),
	gender nvarchar(40),
)

create table Diem (
	rollNo nvarchar(30),
	diem int,
	ma_mon nvarchar(40),
)

create table Mon_hoc (
	 ma_mon nvarchar(40),
	 ten_mon nvarchar(50),
)

create table Lop_hoc (
	rollNo nvarchar(40),
	ma_lop nvarchar(40),
	ten_lop nvarchar(40),
)

create table Phong_hoc (
	ten_phong nvarchar(50),
	ma_phong nvarchar(50),
	so_ban int,
	so_ghe int,
	dia_chi nvarchar(200),
)

create table Gio_day (
	ma_lop nvarchar(40),
	gio_bat_dau nvarchar(50),
	gio_ket_thuc nvarchar(50),
	ma_phong nvarchar(50),
)

insert into Student (rollNo, fullname, age, address, email, phoneNum, gender)
values
('R100', 'Minh Dang', '20', 'Hanoi', 'dtnm@gmail.com', '0912009992', 'Male'),
('R100', 'Minh Dang', '20', 'Hanoi', 'dtnm@gmail.com', '0912009992', 'Male'),
('R100', 'Minh Dang', '20', 'Hanoi', 'dtnm@gmail.com', '0912009992', 'Male'),
('R100', 'Minh Dang', '20', 'Hanoi', 'dtnm@gmail.com', '0912009992', 'Male'),
('R100', 'Minh Dang', '20', 'Hanoi', 'dtnm@gmail.com', '0912009992', 'Male')


insert into Diem (rollNo, diem, ma_mon)
values
('R100', '8', 'ABC'),
('R100', '8', 'ABC'),
('R100', '8', 'ABC'),
('R100', '8', 'ABC'),
('R100', '8', 'ABC')


insert into Mon_hoc (ma_mon, ten_mon)
values
('ABC', 'Toan'),
('ABC', 'Toan'),
('ABC', 'Toan'),
('ABC', 'Toan'),
('ABC', 'Toan')


insert into Lop_hoc (rollNo, ma_lop, ten_lop)
values
('R100', 'A1', 'B1'),
('R100', 'A1', 'B1'),
('R100', 'A1', 'B1'),
('R100', 'A1', 'B1'),
('R100', 'A1', 'B1')

insert into Phong_hoc (ten_phong, ma_phong, so_ban, so_ghe, dia_chi)
values
('E1', 'C1', '12', '23', 'abcabc'),
('E1', 'C1', '12', '23', 'abcabc'),
('E1', 'C1', '12', '23', 'abcabc'),
('E1', 'C1', '12', '23', 'abcabc'),
('E1', 'C1', '12', '23', 'abcabc')

insert into Gio_day (ma_lop, gio_bat_dau, gio_ket_thuc, ma_phong)
values
('asdf', 'zxcv', 'bnmv', 'qwerty'),
('asdf', 'zxcv', 'bnmv', 'qwerty'),
('asdf', 'zxcv', 'bnmv', 'qwerty'),
('asdf', 'zxcv', 'bnmv', 'qwerty'),
('asdf', 'zxcv', 'bnmv', 'qwerty')

select * from Student
select * from Diem
select * from Mon_hoc
select * from Lop_hoc
select * from Phong_hoc
select * from Gio_day