create database QLSV1create table svien( rollno nvarchar(20) primary key not null, fullname varchar(20), age int, address nvarchar(100), thư điện tử 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","",0921063488,"nam"),("R002","Nguyen Van B",20,"12 street ha noi","",0921063488,"nam"),("R003","Nguyen Van C",20,"12 street ha noi","",0921063488,"nam"),("R004","Nguyen Van D",20,"12 street ha noi","",0921063488,"nam"),("R005","Nguyen Van E",20,"12 street ha noi","",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 địa chỉ cửa hàng constraint fk_subjectid foreign key (subjectid) references sub(subjectid)select roomclass.roomid,roomname,ban,chairfrom roomclasswhere roomclass.ban >5and roomclass.chair >5select roomclass.roomid,roomname,ban,chairfrom roomclass where roomclass.ban between 5 and 20and roomclass.chair between 5 & 20
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","","0123456789","nam"),("002","Tran Van B", 22,"Tran dẻo Nghia","","0123456789","nu"),("003","Tran Van C", 24,"Tran dẻo Nghia","","0123456789","nam"),("004","Tran Van D", 26,"Tran dẻo Nghia","","0123456789","nu"),("005","Tran Van E", 28,"Tran dai Nghia","","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