LAB 4 – Simple SQL statements
Question 1:

Write SQL statements to create a database containing the following tables. Note: need to add appropriate primary and foreign keys.
1.Table: Departments
Name
DeptID
Name
NoOfStudents

Type
varchar
Nvarchar
int

Size

Type
varchar
Nvarchar
Nvarchar
varchar
Date
Nvarchar
Varchar
float
Numeric(4,2)

Size

Type

varchar
Nvarchar
tinyint

Size

Type
varchar
varchar
int
int
float
varchar

Size

4
50

Constraint: Name - not null.
2.Table: Students
Name
StudentID
LastName
FirstName
Sex
DateOfBirth
PlaceOfBirth
DeptID
Scholarship

AverageScore
Constraint: Sex should be ‘F’ or ‘M’

4
30
10
1
30
4

3.Table: Courses
Name
CourseID
Name
Credits

4
35

4.Table: Results
Name
StudentID
CourseID
Year
Semester
Mark
Grade
Write SQL statements to insert data to database as follow:
Table Departments:
DeptID

Name
IS
Information Systems
NC
Network and Communication
SE
Software Engineering
CE
Computer Engineering
CS
Computer Science
Table Students:

NoOfStudent
s

4
4
1
6


StudentID LastName

FirstName

Sex

DateOfBirth


PlaceOfBirth DeptID

Scholarship

S001

Lê

Kim Lan

F

23/02/1990

Hà nội

IS

130000

S002

Trần

Minh Chánh

M

24/12/1992


Bình Định

NC

150000

S003

Lê

An Tuyết

F

21/02/1991

Hải phịng

IS

170000

S004

Trần

Anh Tuấn

M


20/12/1993

TpHCM

NC

80000

S005

Trần

Thị Mai

F

12/08/1991

TpHCM

SE

0

S006

Lê

Thị Thu Thủy F


02/01/1991

An Giang

IS

0

S007

Nguyễn

Kim Thư

F

02/02/1990

Hà Nội

SE

180000

S008

Lê

Văn Long


M

08/12/1992

TpHCM

IS

190000

Table Courses:
CourseID Name
DS01
Database Systems
AI01
Artificial Intelligence
CN01
Computer Network
CN01
Computer Network
DSA1
Data Structures and Algorithms
Table Results:
StudentID
S001
S001
S001
S001
S001
S002

S002
S002
S002
S003
S003
S003
S003
S004
S004
S005
S005
S005
S006
S006

CourseID
DS01
DS01
AI01
AI01
CN01
DS01
DS01
CN01
DSA1
DS01
DS01
CN01
CN01
DS01

DSA1
DS01
CN01
CN01
AI01
CN01

Year
2017
2017
2017
2017
2017
2016
2017
2016
2016
2017
2017
2017
2017
2017
2018
2017
2017
2018
2018
2018

Credits

3
3
3
3
4

Semester
1
2
1
2
3
1
1
3
3
1
3
2
3
3
1
2
2
1
1
2

Mark
3

6
4.5
6
5
4.5
7
10
9
2
5
2.5
4
4.5
10
7
2.5
5
6
10

Grade

Write SQL statements to do bellow task.
Create Table Departments(
DeptID varchar(4) primary key,
name Nvarchar(50) Not null,
NoOfStudents int,
)
INSERT INTO Departments(DeptID,name,NoOfStudents)
VALUES('IS','Information Systems',NULL)

insert into Departments(DeptID,name,NoOfStudents)

AverageScore


values('NC','Network and Communication',NULL)
insert into Departments(DeptID,name,NoOfStudents)
values('SE','Software Engineering',NULL)
insert into Departments(DeptID,name,NoOfStudents)
values('CE','Computer Engineering',NULL)
insert into Departments(DeptID,name,NoOfStudents)
values('CS','Computer Science',NULL)
Create Table Students(
StudentID varchar(4) primary key,
LastName Nvarchar(30) ,
FirstName Nvarchar(20) ,
Sex varchar(1) NOT NULL CHECK (Sex IN ('F', 'M')),
DateOfBirth Date,
PlaceOfBirth Nvarchar(30) ,
DeptID varchar(4),
Scholarship float,
AverageScore Numeric(4,2)
)
INSERT INTO
Student
s(StudentID,LastName,FirstName,Sex,DateOfBirth,PlaceOfBirth,DeptID,Scholarship,AverageScore)
values('S001','Lê','Kim Lan','F','1990-02-23','Hà nội' ,'IS',130000,Null )
INSERT INTO
Student
s(StudentID,LastName,FirstName,Sex,DateOfBirth,PlaceOfBirth,DeptID,Scholarship,AverageScore)

values('S002' ,'Trầ
n' ,'Minh Chánh' ,'M' , '1992-12-24' ,
'Bình Định' ,
'NC' , 150000
,Null )
INSERT INTO
Student
s(StudentID,LastName,FirstName,Sex,DateOfBirth,PlaceOfBirth,DeptID,Scholarship,AverageScore)
values('S003' ,'Lê',
'An Tuyế
t' ,'F' ,'1991-02-21' ,'Hải phòng' ,'IS' ,170000 ,Null )
INSERT INTO
Student
s(StudentID,LastName,FirstName,Sex,DateOfBirth,PlaceOfBirth,DeptID,Scholarship,AverageScore)
values('S004' ,'Trầ
n' ,'Anh Tuấ
n',
'M ','1993-12-20','TpHCM','NC' ,80000 ,Null )
INSERT INTO
Student
s(StudentID,LastName,FirstName,Sex,DateOfBirth,PlaceOfBirth,DeptID,Scholarship,AverageScore)
values('S005' ,'Trầ
n','Thị Mai' ,'F' ,'1991-08-12' ,'TpHCM' ,'SE' , 0 ,Null )
INSERT INTO
Student
s(StudentID,LastName,FirstName,Sex,DateOfBirth,PlaceOfBirth,DeptID,Scholarship,AverageScore)
values('S006' ,'Lê' ,'Thị Thu Thủy' ,'F' ,'1991-01-02' ,'An Giang' ,'IS', 0 ,Null )
INSERT INTO
Student
s(StudentID,LastName,FirstName,Sex,DateOfBirth,PlaceOfBirth,DeptID,Scholarship,AverageScore)

values('S007' ,'Nguyễ
n', 'Kim Thư' ,'F' ,'1990-02-02' ,'Hà Nội', 'SE', 180000 ,Null )
INSERT INTO
Student
s(StudentID,LastName,FirstName,Sex,DateOfBirth,PlaceOfBirth,DeptID,Scholarship,AverageScore)
values('S008' ,'Lê','Văn Long' ,'M' ,'1992-12-08','TpHCM' ,'IS' ,190000 ,Null )
Create Table Courses(
CourseID varchar(4)primary key,
Name Nvarchar(35),
Credits tinyint,
)
INSERT INTO Courses(CourseID,Name,Credits)
values('DS01'
,'Database Systems' ,
3 )
INSERT INTO Courses(CourseID,Name,Credits)
values('AI01' ,
'Artificial Intelligence' ,

3 )


INSERT INTO Courses(CourseID,Name,Credits)
values('CN01',
'Computer Network' ,
3 )
INSERT INTO Courses(CourseID,Name,Credits)
values('CN01'
,'Computer Network' ,
3 )

INSERT INTO Courses(CourseID,Name,Credits)
values('DSA1' ,
'Data Structures and Algorithms' ,

4

)

Create Table Results(
StudentID varchar(4),
CourseID varchar(4),
Year int,
Semester int,
Mark float,
Grade varchar(6)
)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S001' ,
'DS01' ,
2017 ,1
,3 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S001' ,
'DS01' ,2017, 2
,6 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S001' ,
'AI01' ,
2017 , 1
,4.5 ,Null)

INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S001' ,'AI01'
,2017 ,2
,6 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S001 '
,'CN01'
,2017 ,
3,
5 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S002' ,'DS01',
2016 , 1,
4.5 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S002' ,
'DS01',
'2017',
1
,7 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S002', 'CN01'
,'2016' ,
3
,10 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S002' ,
'DSA1' ,
'2016' ,
3,

9 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S003' ,
'DS01' ,'2017'
,3
,5 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S003' ,
'DS01' ,'2017' ,
1,
2 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S003' ,
'CN01' ,2017 ,
2
,2.5 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S003' ,
'CN01',
2017 , 3,
4 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S004' ,
'DS01' ,2017 ,
3
,4.5 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S004'
,'DSA1',
2018 , 1

,10 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S005' ,
'DS01',
2017 ,2,
7 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S005' ,
'CN01' ,2017 ,
2,
2.5 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S005' ,
'CN01',
2018 , 1,
5 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S006'
,'AI01',
2018 , 1,
6 ,Null)
INSERT INTO Results(StudentID,CourseID,Year,Semester,Mark,Grade)
values('S006'
,'CN01'
,2018 ,
2
,10 ,Null)

Question 2.
Update NoOfStudents of each department in Departments table where NoOfStudents is the total number

of students of each departments. Note that for department that has no student, the NoOfStudents should be 0.


UPDATE Departments
SET NoOfStudents = ( SELECT COUNT(*)
FROM Students
WHERE

Departments.DeptID=Students.DeptID)

Question 3.
Update AverageScore for each student so that for each course, we take only his/her highest Mark and the
AverageScore of the student is calculated as the average mark of all the courses that the student joins.

UPDATE Students
SET AverageScore = ( SELECT Sum(Mark)/COUNT(StudentID)
FROM Results
WHERE Students.StudentID=Results.StudentID)
Question 4.



Update Grade in table Results so that:

Grade = ‘Passed’ if 5<= Mark <= 10
Grade = ‘Failed’ if 0<= Mark < 5

Update Results
Set Grade = case
when Mark <=10 and Mark >= 5 then 'Passed'

else 'Failed'
end
Question 5.

List (StudentID, Fullname, DateOfBirth, PlaceOfBirth, DeptID, Scholarship) of all students
having Scholarship not greater than 160000, in descending order of Scholarship. Note that FullName is the concatenation
of LastName and FirstName. For example, if LastName = ‘Lê’ and FirstName = ‘Kim Lan’, then Fullname should be
‘Kim Lan Lê’.

Select StudentID, CONCAT(FirstName,LastName) as
FullName, DateOfBirth, PlaceOfBirth, DeptID, Scholarship
from Students
where Scholarship<=160000
Question 6.

List (DeptID, DepartmentName, StudentID, LastName, FirstName) of all departments (KHOA)
so that we see also departments which have no students.

select Departments.DeptID , Name as DepartmentName,
StudentID, LastName, FirstName
from Departments


left join Students on
Departments.DeptID=Students.DeptID
ORDER BY Departments.DeptID
Question 7.
List (StudentID, LastName, FirstName, NumberOfCourses) of all students, show the results in ascending
order of NumberOfCourses where NumberOfCourses is the total number of courses studied by each student.


select Students.StudentID, LastName, FirstName,
count(Results.Semester)as NumberOfCourses
from Students
join Results on Students.StudentID=Results.StudentID
Group by Students.StudentID, LastName, FirstName
Question 8.

C1:

List (DeptID, DepartmentName, NumberOfFemaleStudents, NumberOfMaleStudents) of all departments.

select Departments.DeptID, name,
SUM(case when Sex like'M' then 1 else 0 end) as NumberOfMaleStudents,
SUM(case when Sex like'F' then 1 else 0 end) as NumberOfFemaleStudents
from Departments
left join Students on Students.DeptID=Departments.DeptID
group by Departments.DeptID, name

C2:

select Departments.DeptID, name, COUNT(Sex) as NumberOfFemaleStudents,null as
NumberOfMaleStudents
from Departments
join Students on Departments.DeptID=Students.DeptID
where Sex like'F'
group by Departments.DeptID, name
UNION
select Departments.DeptID, name,null as NumberOfFemaleStudents, COUNT(Sex) as
NumberOfMaleStudents
from Departments

left join Students on Departments.DeptID=Students.DeptID
where Sex like'M'
group by Departments.DeptID, name
Question 9.
Show the list of students which are not in the department ‘Information Systems’ but having Mark of
Database Systems greater than at least one student of department ‘Information Systems’.

select students.StudentID,LastName,FirstName,DeptID


from Students
join Results on Students.StudentID=Results.StudentID
where DeptID not like 'is' and Mark > (select min(mark) from
Students
join Results on
Students.StudentID=Results.StudentID
where DeptID like 'is')
Question 10. List (CourseID, CourseName, BestStudentFullName) where BestStudentFullName is the name of the
student who has the highest mark for this course.

Em nghĩ là sai nhưng
select courses.CourseID, Name, CONCAT(FirstName,LastName) as BestStudentFullName
from Courses
left join Results on Courses.CourseID=Results.CourseID
join Students on Students.StudentID=Results.StudentID
where
results.CourseID like 'AI01' and Mark = (select max(Mark) from Results )
UNION
select courses.CourseID, Name, CONCAT(FirstName,LastName) as BestStudentFullName
from Courses

left join Results on Courses.CourseID=Results.CourseID
join Students on Students.StudentID=Results.StudentID
where
results.CourseID like 'DS01' and Mark = (select max(Mark) from Results )
UNION
select courses.CourseID, Name, CONCAT(FirstName,LastName) as BestStudentFullName
from Courses
left join Results on Courses.CourseID=Results.CourseID
join Students on Students.StudentID=Results.StudentID
where Mark = (select max(Mark) from Results where CourseID like 'CN01')
UNION
select courses.CourseID, Name, CONCAT(FirstName,LastName) as BestStudentFullName
from Courses
left join Results on Courses.CourseID=Results.CourseID
join Students on Students.StudentID=Results.StudentID
where
results.CourseID like 'CG01' and Mark = (select max(Mark) from Results )
UNION
select courses.CourseID, Name, CONCAT(FirstName,LastName) as BestStudentFullName
from Courses
left join Results on Courses.CourseID=Results.CourseID
join Students on Students.StudentID=Results.StudentID
where results.CourseID like 'DSA1' and Mark = (select max(Mark) from Results where
results.CourseID like 'DSA1' )