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