Untitled

use master
go
create database MarkManagement
go
use MarkManagement
go
create table Students(
	StudentID nvarchar(12) 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) 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 Students values('AV0807005', N'Mai Trung Hiếu', '10-11-1989','trunghieu@yahoo.com','0904115116','AV1')
insert into Students values('AV0807006', N'Nguyễn Quý Hùng', '12-2-1988','quyhung@yahoo.com','0955667787','AV2')
insert into Students values('AV0807007', N'Đỗ Đắc Huỳnh', '1-2-1990','dachuynh@yahoo.com','0988574747','AV2')
insert into Students values('AV0807009', N'An Đăng Khuê', '3-6-1986','dangkhue@yahoo.com','0986757463','AV1')
insert into Students values('AV0807010', N'Nguyễn Thị Tuyết Lan', '7-12-1989','tuyetlan@gmail.com','0983310342','AV2')
insert into Students values('AV0807011', N'Đinh Phụng Long', '12-2-1990','phunglong@yahoo.com','0292282882','AV1')
insert into Students values('AV0807012', N'Nguyễn Tuấn Nam', '3-2-1990','tuannam@yahoo.com','0972727722','AV1')

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

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

select *from Students
select * from Subjects
select * from Mark
select *from Students where Class = 'AV1'
update Students set Class = 'AV2' where StudentID = 'AV0807012' 

select class,count(*) as 'Tổng số sinh viên' 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 ='S001' and Theory < 10 and Date = '5-6-2008'

select count(*) as 'Tổng sinh viên không đạt lý thuyết' from Students inner join Mark on Students.StudentID=Mark.StudentID
where SubjectID = 'S001' and Theory <10

select * from Students where Class = 'AV1' and DateofBirth > 1-1-1980

delete from Mark where StudentID ='AV0807011'
delete from Students where StudentID ='AV0807011'

select Mark.StudentID, StudentName, SubjectName, Theory, Practical, Date from Students inner join Mark on Students.StudentID= Mark.StudentID
inner join Subjects on Subjects.SubjectID = Mark.SubjectID
where Mark.SubjectID = 'S001' and Date = '5-6-2008'