Database SQL: You can download the files from this link: https://drive.google.co

Question

Database SQL:

You can download the files from this link: https://drive.google.com/drive/folders/0B0kVn308bqFud21IMVBfQUZqUEU?usp=sharing

Enter your SQL below each numbered question below.

1. Write an SQL 'create table' statement to create a ‘grade_audit’ table with attributes “ID”, name, dept_name, course_id, grade, and time. Use the appropriate types for each attribute, based on why you see in the rest of the courses schema.

2. Create a trigger so that if there is an insert into the ‘takes’ table with a grade of “F”, then the student’s ID, name, dept_name is inserted into the grade_audit table, along with the course_id and grade for the course, and the current time.

Hint: within the insert statement in the body of the trigger, you will need one or more joins.

3. Create another trigger, this time for updates to the ‘takes’ table. The trigger should remove the student’s record for a course in the grade_audit table if the updated grade is “C” or better.

Explanation / Answer

Part 1. SQL Query for creating table grade_audit

CREATE TABLE grade_audit

        (ID            varchar(8),

        name          varchar(20),

        dept_name     varchar(15),
         course_id      varchar(8),
         grade          varchar(4),
         time           time,

         primary key (ID, course_id)

        );

Part 2- Creating Trigger

CREATE Trigger update_grade_audit

AFTER INSERT ON takes

WHEN (new.grade = 'F')

FOR EACH ROW

BEGIN

        SELECT s.ID, s.name, s.dept_name, t.ID, t.grade

               FROM student AS s INNER JOIN takes AS t ON t.id = s.id;

        INSERT INTO grade_audit

               SET ID = s.ID,

               name = s.name,

               dept_name = s.dept_name,

               course_id = t.course_id,

               grade = t.grade,

               time = now();

END;

PART 3- Creating Trigger

CREATE TRIGGER delete_from_grade_audit

AFTER UPDATE ON takes

WHEN (new.grade = 'C' OR new.grade = 'B' OR new.grade = 'A')

BEGIN

        DELETE FROM grade_audit

               WHERE ID = old.ID;

END;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.