Bài Tập Quản Lý Sinh Viên Sql ), Bài Tập Quản Lí Sinh Viên Sql

(19) Nguyễn Minh Hiếu

#quan_ly_sinh_vien.sql

create database QLSV1create 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”,”“,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 add 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 and 20

Xem thêm: Game Người Khổng Lồ Xanh Noi Gian, Game Nguoi Khong Lo Xanh Noi Gian

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 Dai Nghia”,”“,”0123456789″,”nu”),(“003″,”Tran Van C”, 24,”Tran Dai Nghia”,”“,”0123456789″,”nam”),(“004″,”Tran Van D”, 26,”Tran Dai 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

#QuanLySinhVien.sql

create database quan_ly_sinh_vienuse quan_ly_sinh_viencreate 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”, ““, 01235464, “Nam”),(“002”, “Tran Van B”, 30, “ThaiBinh”, ““, 2352366, “Nam”),(“003”, “Tran Van C”, 21, “HaNoi”, ““, 26262626, “Nu”),(“004”, “Tran Van D”, 50, “HaTay”, “il.com”, 2626357, “Nu”),(“005”, “Tran Van E”, 25, “HCM”, ““, 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 SinhVienselect * from BangDiemselect * from MonHocselect * from LopHocselect * from PhongHocselect * from GioDay

Xem thêm: Cách Sử Dụng Format Painter Và Sao Chép Định Dạng Trong Excel

create database bai1763use bai1763create 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”, ““, “0969696969”, “nam”),(“R002”, “TRAN VAN B”, “18”, “Ha noi”, ““, “0969696969”, “nu”),(“R003”, “TRAN VAN C”, “18”, “Ha noi”, ““, “0969696969”, “nam”),(“R004”, “TRAN VAN D”, “18”, “Ha noi”, ““, “0969696969”, “nam”),(“R005”, “TRAN VAN E”, “18”, “Ha noi”, ““, “0969696969”, “nu”)select * from sinh_viencreate 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_diemcreate 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_hoccreate 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_hoccreate 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_hoccreate 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