SQL Server Management Studio | Report

Running head: SQL SERVER MANAGEMENT STUDIO
SQL SERVER MANAGEMENT STUDIO
Name of the Student
Name of the University
Author Note

1
SQL SERVER MANAGEMENT STUDIO
Table of Contents
Enrolment System............................................................................................................................2
Entity Relationship Diagram...........................................................................................................2
Description.......................................................................................................................................3
Procedures....................................................................................................................................3
Trigger.........................................................................................................................................3
View.............................................................................................................................................4
Strength and Weakness....................................................................................................................4
Query...............................................................................................................................................4
Bibliography....................................................................................................................................6

2
SQL SERVER MANAGEMENT STUDIO
Enrolment System
The Enrolment system is a Database management system where the students’ enrolment
for the courses have been recorded. The Database records the student information consisting of
student name, contact, age, gender, email and contact. On other hand the course details are
recorded in form of course name, duration and fees. The enrolment is stored with the help of
student, course and enrolment date. The system is a relational system; hence the entities have
relationship between them. Each student can enroll to one or many courses, where each course
can have one or many students enrolled.
Entity Relationship Diagram
Figure 1: Entity Relationship Diagram of Enrolment System
Source: created by author

3
SQL SERVER MANAGEMENT STUDIO
Description
Procedures
The first stored procedure, shows the list of the student in the enrolment system. The procedure
shows the first name and last name combinedly as full name which is sorted in alphabetical
order.
CREATE PROCEDURE studentlist
AS
BEGIN
SELECT
FirstName +' ' + Lastname
as Fullname
FROM
students
ORDER BY
FirstName;
END;
The second procedure shows the number of students enrolled in each course. It returns the course
name, count which is sorted in ascending order.
CREATE PROCEDURE coursedetails
AS
BEGIN
select c.coursename, count(enrolmentid)as TotalStudents
from
courses c inner join enrolment e on
e.courseid=c.courseid group by (c.coursename)
order by TotalStudents;
END;
Trigger
The trigger bypasses the delete command on the students table. It will show the message ‘Action
Not Allowed’ every time when a delete command will be used on Students table.
--trigger

4
SQL SERVER MANAGEMENT STUDIO
CREATE TRIGGER Newtrigger ON students
INSTEAD OF DELETE
AS
BEGIN
Select 'Action Not Allowed' as [Message]
END
View
The view stores the student id, full name (first name and last name together), enrollment id and
enrolment date for the students who have enrolled to any courses.
create view studentEnrolment as
select s.studentid, s.FirstName +' ' + s.Lastname
as Fullname, e.enrolmentdate from enrolment e
inner join students s on s.StudentID=e.studentid
;
Strength and Weakness
The strengths of the enrollment system are that it has a proper relationship established
between the entities along with the trigger to bypass delete on the students table. Apart from that,
the important keys of the database are well defined and related to the appropriate entities. The
datatypes and their sizes are defined according to attributes and their requirements. The amount
attribute has set for the decimal data type for better precision.
On other hand the weakness of the database is that there are no check constraints for data
validation. The SQL server does not support ‘Before Insert/update/delete’ trigger. However, the
‘instead of’ trigger is used. The size of the database is also not appropriate for managing a
enrollment system.

5
SQL SERVER MANAGEMENT STUDIO
Query
--query 1 (Subquery)
It uses the subquery to show the student detail who is not enrolled to any courses.
select Studentid, firstname, lastname from students
where studentid not in (select s.studentid from students s
inner join enrolment e on e.studentid=s.studentid);
-query 2 (Aggregate Function)
The below query uses subquery and aggregate function ‘MAX’ too show the course which have
the highest fees amount.
select coursename, fees as highest from courses where fees in (select
max(fees) from courses);
--query 3
The query returns all the basic information of the students who is more than 20 years old and a
male student.
select FirstName +' ' + Lastname as Fullname, Contact, Email, Age,
Gender
FROM students where Age> 20 and Gender='Male' ORDER BY FirstName;
--query 4
The query shows the courses who are related to the technology.
select courseid, coursename, duration, fees from courses where
coursename like '%tech%';
--query 5
The query uses inner join to show the student name along with their enrolment date and course
name in descending order of the date to show the latest enrolments top of the result.

6
SQL SERVER MANAGEMENT STUDIO
select s.firstname, s.lastname, e.enrolmentdate, c.coursename from
courses c
inner join enrolment e on e.courseid=c.courseid inner join students s
on s.studentid=e.studentid
order by enrolmentdate desc;

7
SQL SERVER MANAGEMENT STUDIO
Bibliography
Corlăţan, C. G., Lazăr, M. M., Luca, V., & Petricică, O. T. (2014). Query optimization
techniques in Microsoft SQL Server. Database Systems Journal, 5(2), 33-48.
Foster, E. C., & Godbole, S. (2016). Overview of Microsoft SQL Server. In Database Systems
(pp. 461-467). Apress, Berkeley, CA.
Mistry, R., & Misner, S. (2014). Introducing Microsoft SQL Server 2014. Microsoft Press.
Rossi, B. (2014). Entity relationship diagram.