Untitled

use master
go
create database MarkManagement
go
use MarkManagement
go
create table Students(
	studentID nvarchar(12) not null primary key,
	studentName nvarchar(25) not null,
	dateofBirth datetime not null,
	email nvarchar(40),
	phone nvarchar(12),
	class nvarchar(10)
)

create table Subjects(
	subjectID nvarchar(10) not null primary key,
	subjectName nvarchar(25) not null
)

create table Mark(
	studentID nvarchar(12) not null,
	subjectID nvarchar(10) not null,
	date datetime,
	theory tinyint,
	practical tinyint
)

alter table Mark add constraint pk_Mark primary key (studentID, subjectID)
alter table Mark add constraint fk_Mark_Students foreign key (studentID)
	references Students(studentID)
alter table Mark add constraint fk_Mark_Subjects foreign key (subjectID)
	references Subjects(subjectID)

insert into Subjects values(N'S001', N'SQL')
insert into Subjects values(N'S002', N'Java Simplefield')
insert into Subjects values(N'S003', N'Active Server Page')

insert into Students values(N'AV0807005', N'Mai Trung Hiếu', '10/11/1989', N'trunghieu@yahoo.com', N'0904115116', N'AV1')
insert into Students values(N'AV0807006', N'Nguyễn Quý Hùng', '12/2/1988', N'quyhung@yahoo.com', N'0955667787', N'AV2')
insert into Students values(N'AV0807007', N'Đỗ Đắc Huỳnh', '1/2/1990', N'dachuynh@yahoo.com', N'0988574747', N'AV2')
insert into Students values(N'AV0807009', N'An Đăng Khuê', '3/6/1986', N'dangkhue@yahoo.com', N'0986757463', N'AV1')
insert into Students values(N'AV0807010', N'Nguyễn T. Tuyết Lan', '7/12/1989', N'tuyetlan@yahoo.com', N'0983310342', N'AV2')
insert into Students values(N'AV0807011', N'Đinh Phụng Long', '12/2/1990', N'phunglong@yahoo.com', N'0292282882', N'AV1')
insert into Students values(N'AV0807012', N'Nguyễn Tuấn Nam', '3/2/1990', N'tuannam@yahoo.com', N'0972727722', N'AV1')

insert into Mark values(N'AV0807005', N'S001','5/6/2008',8,25)
insert into Mark values(N'AV0807006', N'S002','5/6/2008',16,30)
insert into Mark values(N'AV0807007', N'S001','5/6/2008',10,25)
insert into Mark values(N'AV0807009', N'S003','5/6/2008',7,13)
insert into Mark values(N'AV0807010', N'S003','5/6/2008',9,16)
insert into Mark values(N'AV0807011', N'S002','5/6/2008',8,30)
insert into Mark values(N'AV0807012', N'S001','5/6/2008',7,31)
insert into Mark values(N'AV0807009', N'S002','5/6/2008',12,11)
insert into Mark values(N'AV0807010', N'S001','5/6/2008',7,6)

select *from Students

select *from Students 
where class='AV1'

update Students set class=N'AV2'  where studentID=N'AV0807012'

select class, count(*) as 'Tong so sinh vien' from Students 
group by class

select *from Students where class='AV2'
order by studentName asc

select *from  Students 
inner join Mark on Students.studentID=Mark.studentID
where subjectID=N'S001' and theory<10 and date='5/6/2008'

select count(*) as 'Tong so sinh vien khong dat'
from Mark 
where subjectID=N'S001' and theory<10

select *from Students 
where class=N'AV1' and dateofBirth>'5/6/2008'

delete from Mark where studentID=N'AV0807011'
delete from Students where studentID=N'AV0807011'

select Mark.studentID, studentName, subjectName, theory, practical, date
from Students inner join Mark on Students.studentID=Mark.studentID
inner join Subjects on Mark.subjectID=Subjects.subjectID
where Mark.subjectID=N'S001' and date=N'5/6/2008'