1) STUDENT (Last4SSN, LastName, FirstName, Email, MajorID, GroupID, SQLServerUserID)

The University of Texas

CS 327e

Spring 2002

Test 2

#### NAME: _______________________________ LAST 4 DIGITS of SSN: __________________

Date: 2002-03-25

Duration: 60 minutes

Total: 100 points. Additionally, there are 2 bonus Qs in the end (10 pts).

For all questions, assume the schema as shown on pages 7 and 8. This schema is same as in lab3, with a subset of the data shown in the sample data section.

1.a. (20 pts) Write a SQL query to list, for each ExamID, for each GroupName, the highest ExamScore achieved by any student within that group, for that exam. Write the query such that the list is ordered by ExamID (in ascending order), followed by GroupName (in descending order).

Answer:

SELECT STUDSCORE.ExamID, PROJECTGROUP.GroupName, MAX (STUDSCORE.ExamScore) AS MAXSCORE

FROM STUDENT, STUDSCORE, PROJECTGROUP

WHERE

STUDENT.Last4SSN = STUDSCORE.Last4SSN AND
STUDENT.GroupID = PROJECTGROUP.GroupID

GROUP BY STUDSCORE.ExamID, PROJECTGROUP.GroupName

ORDER BY STUDSCORE.ExamID ASC, PROJECTGROUP.GroupName DESC

1.b. (10 pts) For the data shown draw the resulting table as obtained by the SQL above. The resulting table should have 3 columns.

Answer:

ExamID GroupName MaxScore

Lab1 Events Tracker 1.0 92.00

Lab1 Connect2UT 84.00

Lab1 BevoTickets 105.00

Test1 Events Tracker 1.0 84.00

Test1 Connect2UT 94.00

Test1 BevoTickets 90.00

2.

Assume the following view exists:

CREATE VIEW AVGEXAMSCORE (ExamID, AvgScore) AS

SELECT ExamID, AVG (ExamScore)

FROM STUDSCORE

GROUP BY EXAMID;

And the following SQL

SELECT * FROM AVGEXAMSCORE;

Produces (based on the data shown) the following result set:

ExamID | AvgScore
Lab1 | 71.27
Test1 | 82.00

2.a. (15 pts) Write in English, what the following SQL query is trying to achieve:

SELECT

STUDENT.FirstName, STUDENT.LastName, STUDSCORE.ExamID, STUDSCORE.ExamScore

FROM

STUDENT, STUDSCORE, AVGEXAMSCORE

WHERE

STUDENT.Last4SSN = STUDSCORE.Last4SSN AND

STUDSCORE.ExamID = AVGEXAMSCORE.ExamID AND

STUDSCORE.ExamScore > AVGEXAMSCORE.AvgScore

ORDER BY

STUDSCORE.ExamID, STUDSCORE.ExamScore DESC, STUDENT.LastName, STUDENT.FirstName;

Answer:

For each exam, give the names of all students who have got more than the class average for that exam. For each exam, list the names in decreasing/descending order of their exam scores.

2.b. (15 pts) For the data shown, draw the resulting table as obtained by the SQL above.

Answer:

FirstName LastName ExamID ExamScore

Dhruv | Gajaria | Lab1 | 105.00
Emily | Starnes | Lab1 | 92.00
Olin | Bjork | Lab1 | 84.00
Derek | Burgess | Lab1 | 81.00
James | Rice | Lab1 | 80.00
Matthew | Greenfield | Lab1 | 72.00
Vinu | Kuriakose | Lab1 | 72.00
James | Rice | Test1 | 94.00
Amir | Thapa | Test1 | 90.00
Vinu | Kuriakose | Test1 | 88.00
John | Ward | Test1 | 88.00
Emily | Starnes | Test1 | 84.00

3.0 (15 pts) Write OLTP or OLAP against each statement.

Answer:

3.a. Optimized to support transactions _OLTP_______

3.b. Typically, a large snapshot of data _OLAP_______

3.c. Is typically Normalized _OLTP_______

3.d. Is Multi-dimensional in nature _OLAP_______

3.e. Real-time Queries, Process focused _OLTP_______

4. (25 pts) Write a stored procedure (using ANSI standard or SQL Server syntax) that takes as input: Last4SSN, ExamID; and returns the associated grade for that exam based on the following grading scheme.

>=90 “A”

>=80 and <=89 “B”

>=70 and <=79 “C”

>=60 and <=69 “D”

<=59 “F”

It must also return a status variable that has one of the following values:

0: Everything was successful.

1: Couldn’t find the associated student (in the STUDENT table) having the “Last4SSN”

2: Couldn’t find the associated exam (in the EXAM table) having the “ExamID”

3: Couldn’t find the associated entry in the STUDSCORE table

4: Any other kind of error.

If the status to be returned is not 0, then the associated grade returned should be “U”.

CLEARLY STATE YOUR ASSUMPTIONS. Choose appropriate data types. Use sample data (shown at the end of the test paper) for guidance.

Answer: Using SQL Server 2000 Syntax:

CREATE PROCEDURE sp_getGrade_for_student_exam

@LAST4SSN INTEGER,

@EXAMID VARCHAR(40),

@GRADE CHAR(1) OUTPUT,

@STATUS INTEGER OUTPUT

AS

-- Declare a temporary variable to hold the score

DECLARE @EXAMSCORE DECIMAL (8,2)

-- Set an initial value

SET @EXAMSCORE = 0.00

SET @GRADE = 'U'

SET @STATUS = 4

-- Verify if NULL value is passed in for input parameters

IF @LAST4SSN IS NULL

RETURN

IF @EXAMID IS NULL

RETURN

-- Verify if student @Last4SSN exists

IF NOT EXISTS (SELECT Last4SSN FROM STUDENT

WHERE Last4SSN = @LAST4SSN)

BEGIN

SET @STATUS = 1

RETURN

END

ELSE

-- Verify if exam @ExamID exists

IF NOT EXISTS (SELECT ExamID FROM EXAM

WHERE ExamID = @ExamID)

BEGIN

SET @STATUS = 2

RETURN

END

ELSE

-- Verify if (student, exam) entry exists

IF NOT EXISTS (SELECT ExamScore FROM STUDSCORE

WHERE ExamID = @ExamID AND Last4SSN = @Last4SSN)

BEGIN

SET @STATUS = 3

RETURN

END

-- Set everything to OK

SET @STATUS = 0

-- Get the Exam Score

SELECT @EXAMSCORE = ExamScore

FROM STUDSCORE

WHERE

ExamID = @ExamID AND

Last4SSN = @Last4SSN

-- Convert the ExamScore to Grade

IF @EXAMSCORE >= 90.00

SET @GRADE = 'A'

ELSE IF @EXAMSCORE >= 80.00

SET @GRADE = 'B'

ELSE IF @EXAMSCORE >= 70.00

SET @GRADE = 'C'

ELSE IF @EXAMSCORE >= 60.00

SET @GRADE = 'D'

ELSE

SET @GRADE = 'F'

;

-- After creating the procedure go to SQL QUERY ANALYZER in SQL Server -- 2000 using the ENTERPRISE MANAGER and type the following commands

-- and execute

DECLARE @grade char (1)

DECLARE @status integer

EXECUTE sp_getgrade_for_student_exam 1625, 'Lab1', @grade output, @status output

PRINT @grade

PRINT @status

GO

BONUS QUESTION 1(5 pts.): For the data shown in STUDMAJOR table, how many rows will the following query return:

SELECT ‘English’ FROM STUDMAJOR WHERE MajorID <> 393;

Answer: 9

BONUS QUESTION 2(5 pts.): Draw the resultant table for the query above:

Answer:

‘English’

English

English

English

English

English

English

English

English

English

SCHEMA FOR ALL QUESTIONS

Relationship Number | Parent Table | Child Table | Relationship Type
1 | STUDENT | STUDSCORE | 1:M
2 | EXAM | STUDSCORE | 1:M
3 | SQLSERVERACCT | STUDENT | 1:1
4 | SQLSERVERACCT | PROJECTGROUP | 1:1
5 | PROJECTGROUP | STUDENT | 1:M
6 | STUDMAJOR | STUDENT | 1:M

## Sample DATA

#### EXAM

ExamID | ExamDesc | ExamMaxScore | ExamExtraCredits | ExamPcntOfTotal
Lab1 |  | 100.00 | 20.00 | 6.00
Test1 | First Test | 100.00 | 2.00 | 15.00

#### STUDENT

Last4SSN | LastName | FirstName | Email | MajorID | GroupID | SQLServerUserID
7464 | Starnes | Emily | emmerlina@onebox.com | 124 | Group10 | User42
2034 | Gallardo | Nicholas | nickgallardo@yahoo.com | 315 | Group10 | User12
1247 | Burgess | Derek | swig@mail.utexas.edu | 443 | Group10 | User6
2159 | Greenfield | Matthew | mgreenfield@mail.utexas.edu | 659 | Group10 | User14
1625 | Gajaria | Dhruv | dhruv@mail.utexas.edu | 659 | Group11 | User11
4310 | Kuriakose | Vinu | vinukuriakose@usa.net | 736 | Group11 | User22
7496 | Thapa | Amir | athapa@mail.utexas.edu | 736 | Group11 | User44
1505 | Fisk | David | DFShotgun@aol.com | 315 | Group12 | User9
1166 | Bjork | Olin | orbjork@mail.utexas.edu | 393 | Group12 | User5
8941 | Ward | John | johnward@mail.utexas.edu | 393 | Group12 | User50
6373 | Rice | James | jimmyrice@mail.utexas.edu | 640 | Group12 | User35

#### STUDMAJOR

MajorID | MajorDesc
124 | Japanese
315 | Economics
393 | English
443 | Geography
640 | Management Information Systems
659 | Mechanical Engineering
664 | Mathematics/Mechanical Engineering
736 | Operations Research
852 | RTF
924 | Spanish

STUDSCORE SQLSERVERACCT

Last4SSN | ExamID | ExamScore |  | SQLServerID | SQLServerPswd
1166 | Lab1 | 84.00 |  | Group10 | Group10
1247 | Lab1 | 81.00 |  | Group11 | Group11
1505 | Lab1 | 0.00 |  | Group12 | Group12
1625 | Lab1 | 105.00 |  | User11 | User11
2034 | Lab1 | 58.00 |  | User12 | User12
2159 | Lab1 | 72.00 |  | User14 | User14
4310 | Lab1 | 72.00 |  | User22 | User22
6373 | Lab1 | 80.00 |  | User35 | User35
7464 | Lab1 | 92.00 |  | User42 | User42
7496 | Lab1 | 70.00 |  | User44 | User44
8941 | Lab1 | 70.00 |  | User5 | User5
1166 | Test1 | 70.00 |  | User50 | User50
1247 | Test1 | 78.00 |  | User6 | User6
1505 | Test1 | 82.00 |  | User9 | User9
1625 | Test1 | 70.00 |  |
2034 | Test1 | 78.00 |  |
2159 | Test1 | 80.00 |  |
4310 | Test1 | 88.00 |  |
6373 | Test1 | 94.00 |  |
7464 | Test1 | 84.00 |  |
7496 | Test1 | 90.00 |  |
8941 | Test1 | 88.00 |  |

#### PROJECTGROUP

GroupID | GroupName | ProjectDesc | SQLServerGroupID
Group10 | Events Tracker 1.0 | The focus of this project is to build a database and interface that will facilitate management of events for non-profit organizations. | Group10
Group11 | BevoTickets | To provide a database for buying and selling tickets for a wide range of recreational and art events. | Group11
Group12 | Connect2UT | A database to track the fields of study, areas of expertise, and contact information of faculty at UT and graduate degree holders from UT. | Group12