IT2003
Task Performance
Pivot
Objective:
At the end of the exercise, the students should be able to:
Execute OLAP-type operations using ROLLUP, CUBE, and PIVOT operator.
Software Requirements:
Microsoft SQL Server Management Studio 18.0 or higher
Microsoft SQL Server Express 2017
Instructions:
1. Go to https://sqliteonline.com. In the left-side corner, expand the MS SQL tab then click
the Click to connect.
Note: After 15 minutes of being idle, all data that have been created on the website will be
automatically deleted.
2. Open the text file named Data_Insert 2. Execute the following commands by copying the
contents of the file and pasting it to the query section of the website then click Run. The
commands that have been executed will automatically insert the data that will be used
for this exercise. See the table below.
Student Subject Grades Semester
Jamie Mathematic 82. 1
s 0
Jamie Science 83. 1
0
Jamie Programmin 91. 1
g 0
Jamie History 89. 1
0
04 Task Performance 1 *Property of STI
Page 1 of 4
IT2003
Jamie English 95. 1
0
Jenny Mathematic 95. 1
s 0
Jenny Science 95. 1
0
04 Task Performance 1 *Property of STI
Page 2 of 4
IT2003
Jenny Programmin 90. 1
g 0
Jenny History 92. 1
0
Jenny English 96. 1
0
Jerome Mathematic 96. 1
s 0
Jerome Science 92. 1
0
Jerome Programmin 93. 1
g 0
Jerome History 88. 1
0
Jerome English 94. 1
0
Jamie Mathematic 79. 2
s 0
Jamie Science 80. 2
0
Jamie Programmin 92. 2
g 0
Jamie History 95. 2
0
Jamie English 93. 2
0
Jenny Mathematic 78. 2
s 0
Jenny Science 79. 2
0
Jenny Programmin 82. 2
g 0
Jenny History 80. 2
0
Jenny English 79. 2
0
Jerome Mathematic 90. 2
s 0
Jerome Science 92. 2
0
Jerome Programmin 98. 2
g 0
Jerome History 93. 2
0
Jerome English 90. 2
0
3. Using the PIVOT operator, write queries that will (1) display the subjects into
columns; and (2) will display the computed average grade of the students for the
two (2) semesters. See the output below.
Output:
04 Task Performance 1 *Property of STI
Page 3 of 4
IT2003
GRADING RUBRIC:
CRITERIA PERFORMANCE INDICATORS POINTS
Correctness The code produces the expected result. 30
Logic The code meets the specifications of the problem. 30
Efficiency The code is concise without sacrificing correctness and logic. 20
Syntax The code adheres to the rules of the database management system. 20
Total 100
04 Task Performance 1 *Property of STI
Page 4 of 4
