2021-04-09 13:09:41

#quanlysinhvien.sql

create database quanlysinhvien
create table sinhvien
(
	rollno nvarchar(20) primary key,
	fullname varchar(20),
	age int,
	address nvarchar(50),
	email nvarchar(50),
	phoneNumber int,
	gender varchar(10)
)
create table monhoc
(
	maMH nvarchar(20) primary key,
	tenMH varchar(20)
)
create table mark
(
	mark int,
	rollNo nvarchar(20) references sinhvien(rollno),
	maMH nvarchar(20) references monhoc(maMH)
)
create table class
(
	maclass nvarchar(20) primary key,
	tenclass nvarchar(20)
)
create table quanlyclass
(
	maclass nvarchar(20) references class(maclass),
	rollNo nvarchar(20) references sinhvien(rollNo),
)


create table phonghoc
(
	tenphong nvarchar(20),
	maphong nvarchar(20) primary key,
	soban int,
	soghe int,
	address nvarchar(50),
	check(soban>1),
	check (soghe>1)
)
create table timestudy
(
	maclass nvarchar(20),
	timestart int,
	timeend int,
	maphong nvarchar(20) references phonghoc(maphong)
)

insert into sinhvien(rollno,fullname,age,address,email,phoneNumber,gender) values
	('R001','Nguyen Van A',20,'30 thanh xuan,Ha noi','A@gmail.com','0921063488','nam'),
	('R002','Nguyen Van B',20,'30 thanh xuan,Ha noi','B@gmail.com','0921063488','nam'),
	('R003','Nguyen Van C',20,'30 thanh xuan,Ha noi','C@gmail.com','0921063488','nu'),
	('R004','Nguyen Van D',20,'30 thanh xuan,Ha noi','D@gmail.com','0921063488','nam'),
	('R005','Nguyen Van E',20,'30 thanh xuan,Ha noi','E@gmail.com','0921063488','nu')
	
insert into monhoc(maMH,tenMH) values
	('C201','CNTT'),
	('C202','QTKD'),
	('C203','KTCT'),
	('C204','NNA'),
	('C205','TRIET')
	
insert into mark(mark,rollNo,maMH) values
	(10,'R001','C201'),
	(9,'R002','C202'),
	(8,'R003','C203'),
	(7,'R004','C204'),
	(6,'R005','C205')
	
insert into class(maclass,tenclass) values
	('C2010L','CNTT'),
	('C2011L','QTKD'),
	('C2012L','KTCT'),
	('C2013L','NNA'),
	('C2014L','TRIET')
	
insert into quanlyclass(maclass)values
	('C2010L'),
	('C2011L'),
	('C2012L'),
	('C2013L'),
	('C2014L')
	
insert into phonghoc(tenphong,maphong,soban,soghe,address) values
	('P-CNTT','cntt',5,20,'tang 5'),
	('P-QTKD','qtkd',5,20,'tang 6'),
	('P-KTCT','ktct',5,20,'tang 7'),
	('P-NNA','nna',5,20,'tang 8'),
	('P-TRIET','triet',5,20,'tang 9')
	
insert into timestudy(maclass,timestart,timeend,maphong) values
	('C2010L',6,8,'cntt'),
	('C2011L',6,8,'qtkd'),
	('C2012L',6,8,'ktct'),
	('C2013L',6,8,'nna'),
	('C2014L',6,8,'triet')

select * from sinhvien
select * from monhoc
select * from mark
select * from class
select * from quanlyclass
select * from phonghoc
select * from timestudy

select * from sinhvien
where sinhvien.address = 'Nam Dinh'

select * from class	
where tenclass like '%T%'

select sinhvien.rollno,fullname,mark
from sinhvien left join mark on sinhvien.rollno = mark.rollNo
	
where sinhvien.fullname = 'Nguyen Van A'

select sinhvien.rollno,fullname,mark,tenMH
from sinhvien left join mark on sinhvien.rollno = mark.rollNo
	left join monhoc on mark.maMH = monhoc.maMH
			

2021-01-26 07:34:50

-- Tao database
create database BT1764

-- Active database BT1764
use BT1764
go

-- Create tables
create table Student (
	rollno nvarchar(20) primary key, 
	fullname nvarchar(50), 
	age int, 
	address nvarchar(200), 
	email nvarchar(150), 
	phoneNumber nvarchar(20), 
	gender nvarchar(20)
)
go

create table Subject (
	subject_code nvarchar(20) primary key,
	subject_name nvarchar(50)
)
go

create table Marks (
	-- Chu y: subject_code trong bang Marks va Subject phai co cung length
	subject_code nvarchar(20) references Subject(subject_code),
	rollno nvarchar(20) references Student(rollno),
	marks float,
	constraint pk_marks primary key (subject_code, rollno)
)
go

create table Class (
	class_code nvarchar(20) primary key,
	class_name nvarchar(50)
)
go

create table GroupClass (
	class_code nvarchar(20) references Class(class_code),
	rollno nvarchar(20) references Student(rollno),
	constraint pk_group_class primary key (class_code, rollno)
)
go

create table ClassRoom (
	room_no nvarchar(20) primary key,
	room_name nvarchar(50),
	seats int default 0,
	desk int default 0,
	address nvarchar(200)
)
go

create table Booking (
	class_no nvarchar(20) references Class(class_code),
	room_no nvarchar(20) references ClassRoom(room_no),
	start_date datetime,
	end_date datetime
)
go

-- Insert Data
insert into Student(rollno, fullname, age, address, email, phoneNumber, gender)
values
('R001', 'TRAN VAN A', 20, 'Ha Noi', 'tranvana@gmail.com', '123456', 'Nam'),
('R002', 'NGUYEN THI B', 19, 'Ha Noi', 'nguyenthib@gmail.com', '4534534', 'Nu'),
('R003', 'PHAM THI C', 21, 'Ha Noi', 'phamthic@gmail.com', '123456', 'Nu'),
('R004', 'TRAN VAN A', 20, 'Ha Noi', 'tranvand@gmail.com', '34534543', 'Nam'),
('R005', 'TRAN VAN A', 22, 'Ha Noi', 'tranvane@gmail.com', '123456', 'Nam')

insert into Subject(subject_code, subject_name)
values
('EPC', 'LAP TRINH C'),
('HTML5', 'HTML/CSS/JS')

insert into Class(class_code, class_name)
values
('C01', 'Lop A8'),
('C02', 'Lop A7')

insert into Marks(subject_code, rollno, marks)
values
('EPC', 'R001', 8.6),
('EPC', 'R002', 5.6),
('EPC', 'R003', 7.6),
('HTML5', 'R001', 9.6),
('HTML5', 'R002', 2.6)

-- Hien thi du lieu
select * from Student
select * from Subject
select * from Marks

select * from Student where address = 'Nam Dinh'

select * from Class where class_name like '%A8%'

select Student.rollno, Student.fullname, Subject.subject_name, Marks.marks
from Student, Marks, Subject
where Student.rollno = Marks.rollno
	and Subject.subject_code = Marks.subject_code
	and Student.fullname = 'TRAN VAN A'

select Student.rollno, Student.fullname, Subject.subject_name, Marks.marks
from Student, Marks, Subject
where Student.rollno = Marks.rollno
	and Subject.subject_code = Marks.subject_code

select Student.rollno, Student.fullname, Subject.subject_name, Marks.marks
from Student left join Marks on Student.rollno = Marks.rollno
	left join Subject on Subject.subject_code = Marks.subject_code

2020-12-15 08:16:37

create database bt1764
use bt1764

--Create table
create table student (
	rollno int primary key identity(1,1),
	fullname nvarchar(50) not null,
	dob date,
	email nvarchar(50),
	phoneNumber nvarchar(25) not null,
	gender nvarchar(10)
)

create table class(
	id int primary key identity(1,1),
	name_class nvarchar(50)
)

create table subject(
	id int primary key identity(1,1),
	namesubject nvarchar(50),
)

create table mark(
	mark int,
	id_rollno int references student(rollno),
	id_subject int references subject(id)
)

create table classroom(
	id int primary key identity(1,1),
	name nvarchar(50),
	numTable int,
	numChair int,
	address nvarchar(250)
)

create table managerClass (
	id int primary key identity(1,1),
	id_class int references class(id),
	id_rollno int references student(rollno),
	id_subject int references subject(id)
)

create table bookStudy (
	id_class int references class(id),
	study_in datetime,
	study_out datetime,
	id_classroom int references classroom(id)
	CONSTRAINT PK_book PRIMARY KEY (id_class,id_classroom)
)

insert into student (fullname,dob,email,phoneNumber,gender)
values
('Nguyen Huu Hieu','1990-09-20','hieuuct209@gmail.com','0389945947','Nam'),
('Nguyen Van A','1991-09-20','hieuuct209@gmail.com','0389945947','Nam'),
('Nguyen Van B','1992-09-20','hieuuct209@gmail.com','0389945947','Nu'),
('Nguyen Van C','1993-09-20','hieuuct209@gmail.com','0389945947','Nam'),
('Nguyen Van D','1994-09-20','hieuuct209@gmail.com','0389945947','Nu'),
('Nguyen Van E','1995-09-20','hieuuct209@gmail.com','0389945947','Nam')
select * from student

insert into class(name_class)
values
('T2008A'),
('Cau Ham K49'),
('T2009B'),
('T2010E'),
('T2007M')
select * from class

insert into subject(namesubject)
values
('Javascript'),
('SQL Server'),
('PHP'),
('C# nang cao'),
('Suc ben vat lieu')
select * from subject

insert into mark(mark, id_rollno, id_subject)
values
(10,2,3),
(8,2,1),
(6,1,3),
(5,4,2),
(7,5,4),
(9,2,1),
(8,5,2)

select * from mark

insert into classroom(name,numTable,numChair,address)
values
('P202A2',20,20,'Tang 2 nha A2'),
('P302A2',20,20,'Tang 3 nha A2'),
('P402A3',30,30,'Tang 4 nha A3'),
('P202A1',210,210,'Tang 2 nha A1'),
('P202A4',50,50,'Tang 2 nha A4')

select * from classroom

insert into managerClass (id_class,id_rollno,id_subject)
values
(2,1,3),
(2,1,4),
(3,2,2),
(3,3,2),
(1,2,2),
(1,2,3),
(1,3,3)
select * from managerClass


insert into bookStudy (id_class,study_in,study_out,id_classroom)
values
(1,'2020-10-22 12:30:45', '2020-10-22 14:00:00', 2),
(2,'2020-10-22 12:30:45', '2020-10-22 14:00:00', 1),
(3,'2020-10-22 12:30:45', '2020-10-22 14:00:00', 3),
(4,'2020-10-22 12:30:45', '2020-10-22 14:00:00', 2),
(4,'2020-10-22 12:30:45', '2020-10-22 14:00:00', 4),
(5,'2020-10-22 12:30:45', '2020-10-22 14:00:00', 1)
select * from bookStudy
--Hien thi class co chu 'K'
select * from class
where class.name_class like '%K%'

--Hien thi (RollNo, tên, và điểm thi) cua SV co ten 'Nguyen Van A'
select student.rollno 'Ma Sinh Vien', student.fullname 'Ten SV', 
		mark.mark 'Diem thi', subject.namesubject 'Ten Mon Hoc'
from student, mark,subject
where student.rollno = mark.id_rollno and subject.id = mark.id_subject 
		and student.fullname = 'Nguyen Van A'

--- Hiển thị thông tin điểm thi (RollNo, tên, điểm thi) của tất cả sinh viên

select student.rollno, student.fullname, mark.mark
from student,mark
where student.rollno = mark.id_rollno

--- Hiển thị thông tin điểm thì (RollNo, tên, điểm thi, môn học) của tất cả sinh viên
create proc show_fullname_mark_subject
	@fullname nvarchar(50)
as
begin
	select student.rollno 'Ma Sinh Vien', student.fullname 'Ten SV', 
		mark.mark 'Diem thi', subject.namesubject 'Ten Mon Hoc'
from student, mark,subject
where student.rollno = mark.id_rollno and subject.id = mark.id_subject 
		and student.fullname = @fullname
end

exec show_fullname_mark_subject 'Nguyen Huu Hieu'

2020-11-25 08:40:07

-- Tao database
create database BT1764

-- Active database
use BT1764

-- Tao tables
create table student (
	rollno nvarchar(20) primary key,
	fullname nvarchar(50) not null,
	birthday date,
	address nvarchar(200),
	email nvarchar(150),
	phone_number nvarchar(16),
	gender nvarchar(10)
)

create table class (
	id int primary key identity(1,1),
	name nvarchar(100) not null
)

create table class_group (
	class_id int references class(id),
	rollno nvarchar(20) references student(rollno),
	constraint pk_class_id_rollno primary key (class_id, rollno)
)

alter table class_group
drop constraint pk_class_id_rollno

alter table class_group
add constraint pk_class_id_rollno primary key (class_id, rollno)

create table subject (
	id int primary key identity(1,1),
	name nvarchar(50) not null
)

create table marks (
	subject_id int,
	rollno nvarchar(20),
	marks float,
	constraint fk_subject_id foreign key (subject_id) references class(id)
)

alter table marks
add constraint fk_rollno foreign key (rollno) references student(rollno)

create table classroom (
	id int primary key identity(1,1),
	name nvarchar(50) not null,
	table_num int,
	desk_num int,
	address nvarchar(200)
)

create table book (
	id int primary key identity(1,1),
	class_id int references class(id),
	start_date datetime,
	end_date datetime,
	classroom_id int references classroom(id)
)

--- Them du lieu cho tung bang
------ student, subject, class, classroom
insert into student (rollno,fullname,birthday,email,phone_number,gender, address)
values 
('R001','Do Mac Nam','2020-09-20','nammac2k2@gmail.com','0993938890','nam', 'Ha noi'),
('R002','Do Mac A','2020-09-20','nammac2k2@gmail.com','0993938890','nam', 'Ha noi'),
('R003','Do Mac B','2020-09-20','nammac2k2@gmail.com','0993938890','nam', 'Nam Dinh'),
('R004','Do Mac C','2020-09-20','nammac2k2@gmail.com','0993938890','nam', 'Ha noi'),
('R005','Do Mac D','2020-09-20','nammac2k2@gmail.com','0993938890','nam', 'Nam Dinh')
insert into subject (name)
values
('LAP TRINH C'),
('HTML/CSS/JS'),
('BOOTSTRAP/JQUERY'),
('SQL SERVER'),
('PHP')
insert into classroom (name,table_num,desk_num,address)
values
('T2008A',15,15,'8A Ton That Thuyet'),
('T2008A',15,15,'8A Ton That Thuyet'),
('T2008A',15,15,'8A Ton That Thuyet'),
('T2008A',15,15,'8A Ton That Thuyet'),
('T2008A',15,15,'8A Ton That Thuyet')

insert into class( name)
values
( 'TRAN VAN A'),
( 'TRAN VAN B'),
( 'TRAN VAN C'),
( 'TRAN VAN D'),
( 'TRAN VAN E')

select * from student
select * from subject
select * from classroom
select * from class

------ class_group, marks, book
insert into class_group(class_id, rollno)
values 
(1, 'R001'),
(2, 'R002'),
(3, 'R003'),
(4, 'R004'),
(5, 'R005')
select * from class_group

insert into marks(subject_id, rollno, marks)
values
(1, 'R001', 9.9),
(2, 'R001', 9.9),
(3, 'R001', 9.9),
(4, 'R001', 9.9),
(5, 'R001', 9.9)
select * from marks


insert into book( class_id, start_date, end_date, classroom_id)
values
(1, '2020-11-23 1:30:00', '2020-11-23 5:00:00', 1),
(2, '2020-11-24 1:30:00', '2020-11-24 5:00:00', 2),
(3, '2020-11-25 1:30:00', '2020-11-25 5:00:00', 3),
(4, '2020-11-26 1:30:00', '2020-11-26 5:00:00', 4),
(5, '2020-11-27 1:30:00', '2020-11-27 5:00:00', 5)
select *from book

----- Hien thi sinh vien o 'Nam Dinh'
select * from student where address = 'Nam Dinh'
----- Hien thi sinh vien que quan bat dau bang chu Nam
select * from student where address like 'Nam%'
----- Hien thi sinh vien quen quan ket thuc bang chu Dinh
select * from student where address like '%Dinh'
---- Hien thi sinh vien que quan co chua chu N
select * from student where address like '%N%'

select * from class where name like '%A8%'

select * from student
select * from marks

--- Hien thi thong tin diem gom : rollno, name, marks
select student.rollno, student.fullname, marks.marks
from student, marks
where student.rollno = marks.rollno

-- tuong tu cau lenh sau
select student.rollno, student.fullname, marks.marks
from student inner join marks on student.rollno = marks.rollno

-- Hien thi thong tin tat ca sinh vien => diem
select student.rollno, student.fullname, marks.marks
from student left join marks on student.rollno = marks.rollno

select student.rollno, student.fullname, marks.marks
from student right join marks on student.rollno = marks.rollno

---- Hien thi thong tin diem thi: rollno, fullname, subject name, marks
select student.rollno, student.fullname, subject.name subject_name, marks.marks
from student, subject, marks
where student.rollno = marks.rollno
	and subject.id = marks.subject_id

---- Hien thi tat ca thong tin sinh vien
select student.rollno, student.fullname, subject.name subject_name, marks.marks
from student left join marks on student.rollno = marks.rollno
left join subject on subject.id = marks.subject_id