You are required to use OpenOffice software to build tables, write, and execute
ID: 3758373 • Letter: Y
Question
You are required to use OpenOffice software to build tables, write, and execute queries.
Step 1
Build a Database named DBMS Course Project. The database should include following tables.
First Table – Students
ID
First Name
Last Name
Address
City
State
Zip Code
Phone Number with area code
Second Table – GPA
ID
Current GPA
Class
Freshman
Sophomore
Junior
Senior
Graduate Student
Third Table – Scholarships
ID
Major course of study
Academic Scholarship (Yes or No)
Athletic Scholarship (Yes or No)
Step 2
Extract the First and Last Name, Address, City, State, Zip Code and Phone Number of each senior on the database. Sort by Last Name, then First Name (1 sort).
Step 3
Extract the First and Last Name, and GPA of each student who qualifies for the Dean’s List. Sort by GPA, then Last Name, then First Name (1 sort). A GPA of 3.25 is required to make the Dean’s List.
Explanation / Answer
Trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.
Syntaxes of triggers:
Storeprocedures queries in Sql :
CREATE PROCEDURE [dbo].[TEST]
@Stu_ID VARCHAR(20)
AS
DECLARE
@Grade VARCHAR(2),
@Credits int,
@Points NUMERIC(4,0) = 0,
@Total_Credit_Hrs NUMERIC(4,0) = 0,
@Points_Times_CreditHrs NUMERIC(4,0) = 0,
@TotalPoints_Times_CreditHrs NUMERIC(4,0) = 0,
@GPA NUMERIC(4,0)
DECLARE GPA_Cursor CURSOR
FOR
SELECT ENROLLMENT.Grade, CLASSES.Credit
FROM STUDENTS INNER JOIN ENROLLMENT ON STUDENTS.StuID = ENROLLMENT.StuID INNER JOIN CLASSES ON ENROLLMENT.Course# = CLASSES.Course#
WHERE STUDENTS.StuID = @Stu_ID
OPEN GPA_Cursor
FETCH NEXT FROM GPA_Cursor INTO @Grade, @Credits
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM GPA_Cursor INTO @Grade, @Credits
IF (@Grade = 'A')
SET @Points = 4;
ELSE IF (@Grade = 'B')
SET @Points = 3;
ELSE IF (@Grade = 'C')
SET @Points = 2;
ELSE IF(@Grade = 'D')
SET @Points = 1;
ELSE IF(@Grade = 'F')
SET @Points = 0;
END;
SET @Total_Credit_Hrs += @Credits;
SET @Points_Times_CreditHrs = @Points * @Credits;
SET @GPA = @TotalPoints_Times_CreditHrs / @Total_Credit_Hrs
PRINT '--Calculated GPA of ' + convert(varchar(10),@Stu_ID) + ' is ' + convert(varchar(10),@GPA);
DEALLOCATE GPA_Cursor;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.