Menu Bài Viết:

Nguyễn Hữu Hiếu [T2008A]

Ngày viết: 15:16 15/12/2020

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','[email protected]','0389945947','Nam'),
('Nguyen Van A','1991-09-20','[email protected]','0389945947','Nam'),
('Nguyen Van B','1992-09-20','[email protected]','0389945947','Nu'),
('Nguyen Van C','1993-09-20','[email protected]','0389945947','Nam'),
('Nguyen Van D','1994-09-20','[email protected]','0389945947','Nu'),
('Nguyen Van E','1995-09-20','[email protected]','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'

Do Trung Duc [T2008A]

Ngày viết: 15:40 25/11/2020

-- 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','[email protected]','0993938890','nam', 'Ha noi'),
('R002','Do Mac A','2020-09-20','[email protected]','0993938890','nam', 'Ha noi'),
('R003','Do Mac B','2020-09-20','[email protected]','0993938890','nam', 'Nam Dinh'),
('R004','Do Mac C','2020-09-20','[email protected]','0993938890','nam', 'Ha noi'),
('R005','Do Mac D','2020-09-20','[email protected]','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