By GokiSoft.com| 21:15 13/12/2023|
SQL Server/MySQL

Thiết kế hệ quản trị CSDL - Quản lý sinh viên (create, alter, update, insert, select) - Lập trình SQL Server

Phản hồi từ học viên

5

(Dựa trên đánh giá ngày hôm nay)

Hoàng Thái Sơn [C2010L]
Hoàng Thái Sơn

2021-04-10 13:33:26


#C2010L-testing.sql


create database StudentManagementSystem3767
use StudentManagementSystem3767

create table Class(
	ClassId INT NOT NULL,
	ClassCode NVARCHAR(50) 
)
create table Student(
	StudentId INT NOT NULL,
	StudentName NVARCHAR(50),
	BirthDate DATETIME,
	ClassId INT
)

create table Subjects(
	SubjectId INT NOT NULL,
	SubjectName NVARCHAR(100),
	SessionCount INT
)

create table Result (
	SubjectId INT NOT NULL,
	StudentId INT NOT NULL,
	Mark INT
)
 
alter table Result 
alter column MARK float;

ALTER TABLE Class
ADD CONSTRAINT PK_Class PRIMARY KEY (ClassId);

ALTER TABLE Student
ADD CONSTRAINT PK_Student PRIMARY KEY (StudentId);

ALTER TABLE Subjects
ADD CONSTRAINT PK_Subject PRIMARY KEY (SubjectId);

ALTER TABLE Result
ADD CONSTRAINT PK_Result Primary Key (StudentId, SubjectId);

ALTER TABLE Student
ADD CONSTRAINT FK_Student_Class 
FOREIGN KEY (ClassId) REFERENCES Class(ClassId);

ALTER TABLE Result
ADD CONSTRAINT FK_Result_Student
FOREIGN KEY (StudentId) REFERENCES Student(StudentId);

ALTER TABLE Result
ADD CONSTRAINT FK_Result_Subject
FOREIGN KEY (SubjectId) REFERENCES Subjects(SubjectId);

ALTER TABLE Subjects
ADD CONSTRAINT CK_Subject_SessionCount CHECK (SessionCount>0);

insert into Class (ClassId, ClassCode)
values
('1', 'C1106KV'),
('2', 'C1108GV'),
('3', 'C1108IV'),
('4', 'C1108HV'),
('5', 'C1109GV')

insert into Student (StudentId, StudentName, BirthDate, ClassId)
values
('1', 'Phạm Tuấn Anh', '1993-08-05', '1'),
('2', 'Phan Văn Huy', '1992-06-10', '1'),
('3', 'Nguyễn Hoàng Minh', '1992-09-07', '2'),
('4', 'Trần Tuấn Tú', '1992-09-07', '2'),
('5', 'Đỗ Anh Tài', '1992-06-06', '3')

insert into Subjects (SubjectId, SubjectName, SessionCount)
values
('1', 'C Programming', 22),
('2', 'Web Design', 18),
('3', 'Database Mangement', 23)

insert into Result (StudentId, SubjectId, Mark)
values 
('1', '1', 8),
('1', '2', 7),
('2', '3', 5),
('3', '2', 6),
('4', '3', 9),
('5', '2', 8)

Select * from Student

select StudentId 'Ma_Sinh_Vien', StudentName 'Ten_Sinh_Vien', BirthDate 'Ngay_Sinh' from Student
where BirthDate between '1992-10-10' and '1993-11-10'

select * from Class

select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'Si_So_Lop'
from Class inner join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode

select * from Result

select Student.StudentId, Student.StudentName, SUM(Result.Mark) 'Tong_DieM'
from Student left join Result on Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
having sum(Result.mark) > 10
order by 'Tong_DieM' desc



TRẦN VĂN ĐIỆP [Teacher]
TRẦN VĂN ĐIỆP

2021-03-24 04:07:41



-- Create Database
create database StudentManagementSystem

-- Active Database
use StudentManagementSystem

-- Create Table
create table Class (
	ClassId int not null,
	ClassCode nvarchar(50)
)

create table Student (
	StudentId int not null,
	StudentName nvarchar(50),
	BirthDate datetime,
	ClassId int
)

create table Subject (
	SubjectId int not null,
	SubjectName nvarchar(100),
	SessionCount int
)

create table Result (
	StudentId int not null,
	SubjectId int not null,
	Mark int
)

-- Alter
alter table Result
alter column Mark float

-- Constraint
alter table Class
add constraint PK_Class primary key (ClassId)

alter table Student
add constraint PK_Student primary key (StudentId)

alter table Subject
add constraint PK_Subject primary key (SubjectId)

alter table Result
add constraint PK_Result primary key (StudentId, SubjectId)

-- Foreign Key
alter table Student
add constraint FK_Student_Class foreign key (ClassId) references Class (ClassId)

alter table Result
add constraint FK_Result_Student foreign key (StudentId) references Student (StudentId)

alter table Result
add constraint FK_Result_Subject foreign key (SubjectId) references Subject (SubjectId)

alter table Result
drop constraint FK_Result_Subject

alter table Subject
add constraint CK_Subject_SessionCount check (SessionCount > 0)

-- INSERT Data
insert into Class (ClassId, ClassCode)
values
('1', 'C1106KV'),
('2', 'C1108GV'),
('3', 'C1108IV'),
('4', 'C1108HV'),
('5', 'C1109GV')

insert into Student (StudentId, StudentName, BirthDate, ClassId)
values
('1', 'Phạm Tuấn Anh', '1993-08-05', '1'),
('2', 'Phan Văn Huy', '1992-06-10', '1'),
('3', 'Nguyễn Hoàng Minh', '1992-09-07', '2'),
('4', 'Trần Tuấn Tú', '1992-09-07', '2'),
('5', 'Đỗ Anh Tài', '1992-06-06', '3')

insert into Subject (SubjectId, SubjectName, SessionCount)
values
('1', 'C Programming', 22),
('2', 'Web Design', 18),
('3', 'Database Mangement', 23)

insert into Result (StudentId, SubjectId, Mark)
values 
('1', '1', 8),
('1', '2', 7),
('2', '3', 5),
('3', '2', 6),
('4', '3', 9),
('5', '2', 8)

select * from Class
select * from Student
select * from Subject
select * from Result


select StudentId 'MSV', StudentName 'Ten SV', BirthDate 'Ngay Sinh' from Student
where BirthDate between '1992-10-10' and '1993-10-10'

select Class.ClassId, Class.ClassCode, count(Student.StudentId) 'Si So Lop'
from Class left join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode

select Student.StudentId, Student.StudentName, sum(Result.Mark) 'Tong Diem'
from Student left join Result on Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
having sum(Result.Mark) > 10
order by 'Tong Diem' desc



Hiếu Vũ [community,C2009I]
Hiếu Vũ

2021-01-26 10:19:12



create database StudentManagementSystem

create table Class (
	ClassId int NOT NULL,
	ClassCode nvarchar(50)
)

create table Student (
	StudentId int NOT NULL,
	StudentName nvarchar(50),
	BirthDate datetime,
	ClassId int 
)

create table Subject (
	SubjectId int NOT NULL,
	SubjectName nvarchar(100),
	SessionCount int
)

create table Result (
	StudentId int NOT NULL,
	SubjectId int NOT NULL,
	Mark int
)

create nonclustered index NCI_Student_StudentName on Student(StudentName)

alter table Result
	alter column Mark float

alter table Class
	add constraint PK_Class primary key (ClassId)

alter table Student
	add constraint PK_Student primary key (StudentId)

alter table Subject 
	add constraint PK_Subject primary key (SubjectId)

alter table Result
	add constraint PK_Result primary key (StudentId, SubjectId)

alter table Student
	add constraint FK_Student_Class foreign key (ClassId) references Class(ClassId)

alter table Result
	add constraint FK_Result_Student foreign key (StudentId) references Student(StudentId)
	
alter table Result
	add constraint FK_Result_Subject foreign key (SubjectId) references Subject(SubjectId)

alter table Subject 
	add constraint CK_Subject_SessionCount check (SessionCount>0)

insert into Class (ClassId, ClassCode)
values
('1', 'C1106KV'),
('2', 'C1108GV'),
('3', 'C1108IV'),
('4', 'C1108HV'),
('5', 'C1109GV')

insert into Student (StudentId, StudentName, BirthDate, ClassId)
values
('1', 'Phạm Tuấn Anh', '1993-08-05', '1'),
('2', 'Phan Văn Huy', '1992-06-10', '1'),
('3', 'Nguyễn Hoàng Minh', '1992-09-07', '2'),
('4', 'Trần Tuấn Tú', '1992-09-07', '2'),
('5', 'Đỗ Anh Tài', '1992-06-06', '3')

insert into Subject (SubjectId, SubjectName, SessionCount)
values
('1', 'C Programming', 22),
('2', 'Web Design', 18),
('3', 'Database Mangement', 23)

insert into Result (StudentId, SubjectId, Mark)
values 
('1', '1', 8),
('1', '2', 7),
('2', '3', 5),
('3', '2', 6),
('4', '3', 9),
('5', '2', 8)

select Student.StudentId, Student.StudentName, Student.BirthDate from Student




Lê Trọng Nghĩa [community,C2009I]
Lê Trọng Nghĩa

2021-01-26 09:59:32



Create database StudentManagementSystem
use StudentManagementSystem

go

create table Class (
	Class_id int primary key,
	Class_code nvarchar(50)
)
go

insert into Class(Class_id, Class_code)
values
('1', 'C1106KV'),
('2', 'C1108GV'),
('3', 'C1108IV'),
('4', 'C1108HV'),
('5', 'C1109GV')

Select * from Class

create table Student(
	Student_id int primary key,
	Student_Name nvarchar(50),
	Birth_Date datetime,
	Class_id int references Class(Class_id),
)
go

insert into Student(Student_id, Student_Name, Birth_Date, Class_id)
values
('1', 'Pham Tuan Anh', '1993-08-05', '1'),
('2', 'Phan Van Huy', '1992-06-10', '1'),
('3', 'Nguyen Hoang Minh', '1992-09-07', '2'),
('4', 'Tran Tuan Tu', '1993-10-10', '2'),
('5', 'Do Anh Tai', '1992-06-06', '3')

Select * from Student

create table Subject(
	Subject_id int primary key,
	Subject_name nvarchar(50),
	Session_Count int
)
go

insert into Subject(Subject_id, Subject_name, Session_Count)
values
('1', 'C Programming', '22'),
('2', 'Web Design', '18'),
('3', 'Database Management', '23')

Select * from Subject

create table Result(
	Student_id int references Student(Student_id),
	Subject_id int references Subject(Subject_id),
	Mark int
	constraint pk_Result primary key (Student_id, Subject_id)	
)
go

insert into Result(Student_id, Subject_id, Mark)
values
('1', '1', '8'),
('1', '2', '7'),
('2', '3', '3'),
('3', '2', '6'),
('4', '3', '9'),
('5', '2', '8')

Select * from Result

select Student.Student_id, Student.Student_Name, Student.Birth_Date from Student
	where Student.Birth_Date >= '1992-10-10' and Student.Birth_Date <= '1993-10-10'

Select Class.Class_id, Class.Class_code, count(Student.Class_id)
	from Class left join Student on Class.Class_id = Student.Class_id
	group by Class.Class_id, Class.Class_code

Select Student.Student_id, Student.Student_Name, sum(Result.Mark) 'highest' 
	from Student, Result 
		where Student.Student_id = Result.Student_id
			group by Student.Student_id, Student.Student_Name
				having sum(Result.Mark) > 10



Vũ Trung Kiên [C2009I]
Vũ Trung Kiên

2021-01-26 09:27:25


#QuanLySinhVienNangCao.sql


create database QuanLySinhVien
use QuanLySinhVien

create table Class_Table 
(
	ClassId int not null primary key,
	ClassCode nvarchar(50)
)

create table Student_Table 
(
	StudentId int not null primary key,
	StudentName nvarchar(50),
	BirthDate datetime,
	ClassId int
)

create table Subject_Table
(
	SubjectId int primary key,
	SubjectName nvarchar(100),
	SessionCount int
)

create table Result_Table
(
	StudentId int not null,
	SubjectId int not null,
	Mark int
)

alter table Student_Table 
add constraint fk_Student_Table foreign key (ClassId) references Class_Table  (ClassId)

alter table Result_Table
add constraint fk_Result_Table foreign key (StudentId) references Student_Table (StudentId)

alter table Result_Table
add constraint fk_ResultTable foreign key (SubjectId) references Subject_Table (SubjectId)

alter table Result_Table
alter column Mark float

insert into Class_Table(ClassId, ClassCode)
values 
('1', 'C1106KV'),
('2', 'C1108GV'),
('3', 'C1108IV'),
('4', 'C1108HV'),
('5', 'C1109GV')

insert into Student_Table(StudentId, StudentName, BirthDate, ClassId)
values
('1', 'Pham Tuan Anh', '1993-08-05', '1'),
('2', 'Phan Van Huy', '1992-06-10', '1'),
('3', 'Nguyen Hoang Minh', '1992-09-07', '2'),
('4', 'Tran Tuan Tu', '1993-10-10', '2'),
('5', 'Do Anh Tai', '1992-06-06', '3')

insert into Subject_Table(SubjectId, SubjectName, SessionCount)
values
('1', 'C', '22'),
('2', 'HTML', '18'),
('3', 'SQL', '23')

insert into Result_Table(StudentId, SubjectId, Mark)
values 
('1', '1', '8'),
('1', '2', '7'),
('2', '3', '5'),
('3', '2', '6'),
('4', '3', '9'),
('5', '2', '8')

select Student_Table.StudentId, Student_Table.StudentName, Student_Table.BirthDate from Student_Table
	where Student_Table.BirthDate >= '1992-10-10' and Student_Table.BirthDate <= '1993-10-10'

select Class_Table.ClassId, Class_Table.ClassCode, count (Student_Table.ClassId)'Total Students'
	from Class_Table left join Student_Table on Class_Table.ClassId = Student_Table.ClassId
		group by Class_Table.ClassId, Class_Table.ClassCode

select Student_Table.StudentId, Student_Table.StudentName, sum(Result_Table.Mark)'Highest'
	from Student_Table, Result_Table
		where Student_Table.StudentId = Result_Table.StudentId
			group by Student_Table.StudentId, Student_Table.StudentName
				having sum(Result_Table.Mark) > 10



Trinh Huy Hung [community,C2009I]
Trinh Huy Hung

2021-01-26 08:57:35



create database StudentManagementSystem

use StudentManagementSystem

create table Class(
	ClassId int primary key,
	ClassCode nvarchar(50)
)
create table Student(
	StudentId int primary key,
	StudentName nvarchar(50),
	BirthDate Datetime,
	ClassId int references Class(ClassId)
)

alter table Student
alter column BirthDate date

create table Subject(
	SubjectId int primary key,
	SubjectName nvarchar(100),
	SessionCount int
)
create table Result(
	StudentId int references Student(StudentId),
	SubjectId int references Subject(SubjectId),
	Mark int
	primary key (StudentId, SubjectId)
)

create unique index SCI_Student_StudentName
on Student(StudentName)

alter table Result
alter column Mark float

alter table Subject
add constraint CK_Subject_SessionCount check (SessionCount > 0)

insert into Class(ClassId, ClassCode)
values
(1,'C1106KV'),
(2,'C1108GV'),
(3,'C1108IV'),
(4,'C1108HV'),
(5,'C1109GV')

insert into Student(StudentId, StudentName, BirthDate, ClassId)
values
(1,'Pham Tuan Anh','1993-08-05',1),
(2,'Phan Van Huy','1992-06-10',1),
(3,'Nguyen Hoang Minh','1993-09-07',2),
(4,'Tran Tuan Tu','1993-10-10',2),
(5,'Do Anh Tai','1992-06-06',3)

insert into Subject(SubjectId, SubjectName,SessionCount)
values
(1,'C Programming', 22),
(2,'Web Design', 18),
(3,'Database Danagement', 23)

insert into Result(StudentId, SubjectId, Mark)
values 
(1, 1, 8),
(1, 2, 7),
(2, 3, 5),
(3, 2, 6),
(4, 3, 9),
(5, 2, 8)


select Student.StudentId, Student.StudentName, Student.BirthDate
from Student
where Student.BirthDate between '1992-10-10' and '1993-10-10'

select Class.ClassId, Class.ClassCode, COUNT(Student.ClassId)'CountStudent'
from Class left join Student on Class.ClassId=Student.ClassId
group by Class.ClassId, Class.ClassCode

select Student.StudentId, Student.StudentName,SUM(Result.Mark)'SumMark'
from Student, Result
where Student.StudentId=Result.StudentId 
group by Student.StudentId, Student.StudentName
having SUM(Result.Mark) > 10



Do Trung Duc [T2008A]
Do Trung Duc

2020-12-04 03:43:22



create DATABASE "QuanLySinhVien-gokisoft-buoi6"
use "QuanLySinhVien-gokisoft-buoi6"

create table Class(
ClassID int not null,
ClassCode nvarchar(50)
constraint PK_Class  primary key (ClassID)
)

create table Student(
StudentId int not null,
StudentName Nvarchar(50),
BirthDate datetime,
ClassID int 
constraint PK_Student primary key (StudentId)
constraint FK_Student_Class foreign key (ClassID) references Class(ClassID)
)

create table Subject(
SubjectId int not null,
SubjectName nvarchar(50),
SessionCount int,
constraint PK_Subject primary key (SubjectId)
)

create table Result(
StudentId int not null,
SubjectId int not null,
Mark int
)

alter table Result 
add constraint PK_Resault primary key (StudentId, SubjectId)

alter table Subject
add constraint CK_Subject_SessionCount check (SessionCount > 0)


alter table Result
add constraint FK_Result_Student foreign key (StudentId) references Student(StudentId)

alter table Result
add constraint FK_Result_Subject foreign key (SubjectId) references Subject(SubjectId)

select * from Class
select * from Student
select * from Subject
select * from Result

insert into Class(ClassID,ClassCode)
values
('1','C1106KV'),
('2','C1108GV'),
('3','C1108IV'),
('4','C1108HV'),
('5','C1109GV')

insert into Student(StudentId,StudentName,BirthDate,ClassID)
values
('1',N'Phạm Tuấn Anh','1993-08-05',1),
('2',N'Phạm Văn Huy','1992-06-10',1),
('3',N'Nguyễn Hoàng Minh','1992-09-07',2),
('4',N'Trần Tuấn Tú','1993-10-10',2),
('5',N'Đỗ Anh Tài','1992-06-06',3)

insert into Subject(SubjectId,SubjectName,SessionCount)
values
('1','C Programming','22'),
('2','Web Design','18'),
('3','Database Management','23')

insert into Result(StudentId,SubjectId,Mark)
values
('1','1','8'),
('1','2','7'),
('2','3','5'),
('3','2','6'),
('4','3','9'),
('5','2','8')

select Student.StudentId, Student.StudentName, Student.BirthDate from Student
where Student.BirthDate between '1992-10-10'and'1993-10-10'

select Student.ClassID, CLass.ClassCode, count(Student.StudentId) as N'Số học sinh trong lớp'
FROM Class inner join Student on Class.ClassID = Student.ClassID
group by Student.ClassID, CLass.ClassCode
 
select Result.StudentId, Student.StudentName, sum(Result.Mark) as 'Total Mark'
from Result inner join Student on Result.StudentId =  Student.StudentId 
group by Result.StudentId, Student.StudentName
having sum(Result.Mark) > 10





nguyễn Sử [T2008A]
nguyễn Sử

2020-12-03 10:27:04



create database StudentManagementSystem
use StudentManagementSystem

create table class_2 (
ClassID int NOT NULL,
ClassCode nvarchar(50)
)
go 
insert into class_2(ClassID,ClassCode)
values
('1','C1106KV'),
('2','C1108GV'),
('3','C1108IV'),
('4','C1106HV'),
('5','C1109GV')

create table Student_3(
StudentID int NOT NULL,
StudentName nvarchar(50),
BirthDate datetime,
ClassID int
)
go 
insert into Student_3(StudentID,StudentName,BirthDate,ClassID)
values 
('1','Phạm Tuấn Anh','1993-08-05','1'),
('2','Phạm Tuấn Anh','1992-06-10','1'),
('3','Phạm Tuấn Anh','1992-09-07','2'),
('4','Phạm Tuấn Anh','1993-10-10','2'),
('5','Phạm Tuấn Anh','1992-06-06','3')

create table Subject_4 ( 
SubjectID int NOT NULL,
SubjectName nvarchar(100),
SessionCount int
)
go 
insert into Subject_4(SubjectID,SubjectName,SessionCount)
values
('1','C Programming','22'),
('2','Wed Design','18'),
('3','Database Managenent','23')

create table result_5(
SudentID int NOT NULL,
SubjectID int NOT NULL,
Mark int
)
go 

insert into  result_5(SudentID,SubjectID,Mark)
values
('1','1','8'),
('1','2','7'),
('2','3','5'),
('3','2','6'),
('4','3','9'),
('5','4','8')

--- primary key --- 
alter table class_2
add constraint pk_class primary key (ClassID)
alter table student_3
add constraint pk_student primary key (StudentID)
alter table Subject_3
add constraint pk_Subject primary key (SubjectID)
alter table  result_4
add constraint pk_result primary key (StudentID,SubjectID)

---- foreign key ----
alter table Student 
add constraint FK_Student_class
foreign key (ClassID) references Class(ClassID)

alter table Result
add constraint FK_Result_Student
foreign key (StudentID) references Student(StudentID)

alter table Result
add constraint FK_Result_Subject
foreign key (SubjectID) references Subject(SubjectID)

--- check ---
alter table Subject
add constraint ck_Subject_SessionCount check (SessionCount>0)

--- inserting data ---
select *from class_2
select *from Student_3
select *from Subject_4
select *from result_5

---query Operations---
select StudentID 'ma sinh vien' StudentName 'ten sinh vien' BirthDate 'ngay sinh'
from Student_3 BirthDate between '1992-10-10' and '1993-10-10'

select class_2.classId 'Ma lop', class_2.classCode 'Ten lop', count(Student_3.studentId) 'Si so lop'
from  class_2, Student_3
where class_2.classId=Student_3.studentId
group by class_2.classId, class_2.classCode

select Student_3.studentId 'Ma sinh vien', studentName 'Ten sinh vien', sum(result.mark) 'Tong diem'
from Student_3, result_5
where Student_3.studentId=result_5.studentId
group by Student_3.studentId, Student_3.studentName
having sum(result_5.mark) >10



Triệu Văn Lăng [T2008A]
Triệu Văn Lăng

2020-12-02 09:48:11



create database StudentManagementSystem
use StudentManagementSystem 

create table class (
	classId int not null,
	classCode nvarchar(50)
)

create table student (
	studentId int not null,
	studentName nvarchar(50),
	birthDate datetime,
	classId int,
)

create table subject (
	subjectId int not null,
	subjectName nvarchar(50),
	sessioncount int
)

create table result (
	studentId int not null,
	subjectId int not null,
	mark int
)

alter table class
add constraint pk_class primary key (classId)

alter table student 
add constraint pk_student primary key (studentId)

alter table subject 
add constraint pk_subject primary key (subjectId)

alter table result 
add constraint pk_result primary key (studentId, subjectId)

alter table subject 
add constraint ck_subject_sessioncount check (sessioncount > 0)

alter table student
add constraint fk_student_class 
foreign key (classId) references class(classId)

alter table result 
add constraint fk_result_student 
foreign key (studentId) references student(studentId)

alter table result
add constraint fk_result_subject 
foreign key (subjectId) references subject(subjectId)

insert into class(classId, classCode)
values
('1', 'C1106KV'),
('2', 'C1108GV'),
('3', 'C1108IV'),
('4', 'C1108HV'),
('5', 'C1109GV')

insert into student(studentId, studentName, birthDate, classId)
values
(1, 'Pham Tuan Anh', '1993-08-05', 1),
(2, 'Phan Van Huy', '1992-06-10', 1),
(3, 'Nguyen Hoang Minh', '1992-09-07', 2),
(4, 'Tran Tuan Tu', '1993-10-10', 2),
(5, 'Do Anh Tai', '1993-08-05', 3)

insert into subject(subjectId, subjectName, sessioncount)
values
(1, 'C Programming', 22),
(2, 'Web Design', 18),
(3, 'Database Management', 23)

insert into result(studentId, subjectId, mark)
values
(1, 1, 8),
(1, 2, 7),
(2, 3, 5),
(3, 2, 6),
(4, 3, 9),
(5, 2, 8)

select * from class
select * from student
select * from subject
select * from result

select studentId 'Ma sinh vien', studentName 'Ten sinh vien', birthDate 'Ngay sinh'
from student where birthDate between '1992-10-10' and '1993-10-10'

select class.classId 'Ma lop', class.classCode 'Ten lop', count(student.studentId) 'Si so lop'
from class, student
where class.classId=student.studentId
group by class.classId, class.classCode

select student.studentId 'Ma sinh vien', studentName 'Ten sinh vien', sum(result.mark) 'Tong diem'
from student, result
where student.studentId=result.studentId
group by student.studentId, student.studentName
having sum(result.mark) >10



Trần Thị Khánh Huyền [T2008A]
Trần Thị Khánh Huyền

2020-12-02 09:27:47


#lession 6.sql


CREATE DATABASE StudentManagementSystem
USE StudentManagementSystem

CREATE TABLE Class(
	ClassId int  not null,
	ClassCode nvarchar(50)
)
CREATE TABLE Student(
	StudentId int not null,
	StudentName nvarchar(50),
	BirthDate datetime,
	ClassId int
)
CREATE TABLE Subject(
	SubjectId int not null,
	SubjectName nvarchar(100),
	SessionCount int 
)
CREATE TABLE Result(
	StudentId int not null,
	SubjectId int not null,
	Mark int
)

create nonclustered index NCI_Student_StudentName on Student (StudentName)

ALTER TABLE Result
ALTER COLUMN Mark float

ALTER TABLE Class
ADD constraint PK_Class primary key ClassId

ALTER TABLE Student
ADD constraint PK_Student primary key StudentId

ALTER TABLE Class
ADD constraint PK_Subject primary key SubjectId

ALTER TABLE Class
ADD constraint PK_Result primary key (StudentId, SubjectId)

alter table Student
add constraint FK_Student_Class foreign key (ClassId) references Class(ClassId)

alter table Result
add constraint FK_Result_Student foreign key (StudentId) references Student(StudentId)

alter table Result
add constraint FK_Result_Subject foreign key (SubjectId) references Subject(SubjectId)

alter table Subject
add constraint CK_Subject_SessionCount check (SessionCount > 0) 

INSERT INTO Class(Classid, ClassCode)
Values(1, C1106KV),
(2, C1108GV),
(3, C1108IV),
(4, C1108HV),
(5, C1109GV)

INSERT INTO Student(Studentid, StudentName, BirthDate, Classid)
VaLUES(1, Pham Tuan Anh, 1993-08-05,1),
(2, Phan Van Huy, 1992-06-19,1),
(3, Nguyen Hoang Minh, 1992-09-07,2),
(4, Tran Tuan Tu, 1993-10-10,2),
(5, Do Anh Tai, 1992-06-06,3)

INSERT INTO Subject (Subjectid, SubjectName, SessionCount)
VALUES(1, C Programming,22),
(2, Web Design,18),
(3,Database Management,23)

INSERT INTO Result(Studentid, Subjectid, Mark)
VALUES(1,1,8),
(1,2,7),
(2,3,5),
(3,2,6),
(4,3,9),
(5,2,8)

Select StudentId, StudentName, BirthDate
from Student
where BirthDate between '1992-10-10'and '1993-10-10'

select Class.ClassId, Class.ClassCode, COUNT(Student.StudentId) TotalStudent
from Class, Student
where Class.ClassId = Student.StudentId
group by Class.ClassId, Class.ClassCode

select Student.StudentId, Student.StudentName, SUM(Result.Mark) TotalMark
from Student left join Result on Student.StudentId = Result.StudentId
group by Student.StudentId, Student.StudentName
having SUM(Result.Mark) > 10
order by TotalMark desc