TEXTBOOK SOLUTIONS EXPERT Q&A Now that you have all the information you need in
ID: 3869384 • Letter: T
Question
TEXTBOOK SOLUTIONS EXPERT Q&A Now that you have all the information you need in your system, you need to write a procedure to automate the grading of students. Assignment: 1. Create a PL/SQL Procedure to assign grades to your registered students. Use the following guidelines for providing a grade scale for your students: Rule: A - Class Title ends with a character between A-F B - Class Title ends with a character between G-K C - Class Title ends with a character between L-P D - Class Title ends with a character between Q-T E - Class Title ends with a character between U-Z Hint: Use a PL/SQL Procedure (cursor to loop through the records, check the class title, then update the grade table). Below is the table I created earlier. DROP TABLE sTUDENT; CREATE TABLE STUDENT ( StudentID NUMBER(10), FirstName VARCHAR2(20), LastName VARCHAR2(20), MI CHAR(1), Address VARCHAR2(20), City VARCHAR2(20), State CHAR(2), Zip VARCHAR2(10), HomePhone VARCHAR2(10), WorkPhone VARCHAR2(10), Email VARCHAR2(20) , DOB DATE, PIN VARCHAR2(10), Status CHAR(1) ); DROP TABLE FACULTY; CREATE TABLE FACULTY( FacultyID Number(10), FIRSTNAME VARCHAR2(20), LASTNAME VARCHAR2(20), MI CHAR(1), WORKPHONE VARCHAR2(10), CellPhone VARCHAR2(10), Rank VARCHAR2(20), Experience VARCHAR2(20), Status CHAR(10) ); DROP TABLE COURSE; cREATE TABLE COURSE( CourseID Number(10), CourseNumber Varchar2(20), CourseName Varchar(20), Description Varchar(20), CreditHours Number(4), Status Char(1) ); DROP TABLE SECTION; cREATE TABLE SECTION( SectionID Number(10), courseID Number(10), sectionNumber Varchar2(10), Days Varchar2(10), StartTime Date, Endtime Date, LocationID Number(10), SeatAvailable Number(3), Status Char(1) ); DROP TABLE LOCATION; CREATE TABLE LOCATION ( LocationID Number(10), Building Varchar2(20), Room Varchar2(5), capacity Number(5), Status char(1) ); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (1, 'Booky', 'Phillips', 'L', 'PO BOX 339', 'Pickerington', 'OH', '43147-9998', '6142812811', '6142541254', 'bphillips@email.com', '12-JAN-1963', '15246', 'F'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (2, 'Denise', 'Miller', 'S', '172 E Main St', 'Columbus', 'OH', '43215-9997', '6145412544', '6142541254', 'dsmith@email.com', '01-FEB-1964', '45246', 'S'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (3, 'Rocky', 'Smith', 'R', '45 Broad St', 'Westerville', 'OH', '43081-8888', '6145542211', '6142541254', 'rsmith@email.com', '02-APR-1950', '99999', 'J'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (4, 'Tammy', 'Stone', 'B', '88 E Broad St', 'Columbus', 'OH', '43215-7777', '6142812811', '6142541254', 'Tstone@email.com', '04-MAR-1958', '88855', 'R'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (5, 'Anne', 'Thomas', 'P', '172 E State St', 'Columbus', 'OH', '43215-7777', '6144667245', '6142541254', 'athomas@email.com', '05-MAY-1971', '99911', 'F'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (6, 'Paula', 'Miller', 'R', '250 Civic Center Dr', 'Columbus', 'OH', '43215-8888', '6142812811', '6142541254', 'Pthomas@email.com', '06- JuN-1972', '44555', 'R'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (7, 'Melissa', 'Harrison', 'G', 'PO BOX 154', 'Obetz', 'OH', '43147-4444', '6142812811', '6142541254', 'mHarrison@email.com', '07-JUL-1974', '77898', 'J'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (8, 'Ellen', 'Andrews', 'M', '17 E State St', 'Columbus', 'OH', '43215-5555', '6145415411', '6142541254', 'EAndrews@email.com', '08-AUG-1964', '11512', 'F'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (9, 'Mary', 'Green', 'S', '10 Broad Meadows Ct', 'Columbus', 'OH', '43214-6666', '6142812811', '6142541254', 'mGreen@email.com', '09-SEP-1963', '04455', 'S'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (10, 'Michael', 'Jones', 'W', '23 E Main St', 'Circleville', 'OH', '43113-4444', '6145415455', '6142541254', 'mjones@email.com', '10-OCT-1973', '55246', 'J'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (11, 'Timothy', 'Johnson', 'T', '45 S Scioto', 'Circleville', 'OH', '43113-3333', '4281281122', '6142541254', 'tjohnson@email.com', '11-NOV-1975', '95246', 'F'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (12, 'Ryan', 'Moore', 'E', '90 N Court St', 'Pickerington', 'OH', '43147-6666', '6145454455', '6142541254', 'Rmoore@email.com', '12-DEC-1978', '65246', 'R'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (13, 'Theresa', 'Anderson', 'C', '8 W High St', 'Columbus', 'OH', '43207-7777', '6145414455', '6142541254', 'tanderson@email.com', '03-APR-1973', '5746', 'S'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (14, 'Cathy', 'Ream', 'A', '123 Weber Rd', 'Worthington', 'OH', '43085-6666', '6148845544', '6142541254', 'Cream@email.com', '01-AUG-1968', '28246', 'R'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (15, 'Toni', 'Baloney', 'R', '2210 Trent Rd', 'Columbus', 'OH', '43229-8888', '6142812811', '6142541254', 'Tbaloney@email.com', '07-OCT-1969', '09090', 'J'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (16, 'Daphne', 'Miller', 'R', '10 Main St', 'Obetz', 'OH', '43101-4444', '6145415477', '6142541254', 'dmiller@email.com', '02-MAR-1971', '34343', 'F'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (17, 'Keith', 'Palmer', 'W', '205 Court St', 'Circleville', 'OH', '43113-9909', '7402812811', '6142541254', 'kpalmer@email.com', '22-JAN-1974', '84575', 'F'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (18, 'Renee', 'Pierson', 'S', '4578 Heatherton Dr', 'Columbus', 'OH', '43229-1234', '6148461415', '6142541254', 'rpierson@email.com', '04-JAN-1978', '98789', 'J'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (19, 'Maria', 'Gordon', 'P', '5515 Briarcliff', 'Reynoldsburg', 'OH', '43068-5644', '6142812811', '6142541254', 'mgordons@email.com', '12-Feb-1975', '43422', 'R'); INSERT INTO STUDENT ( studentid, firstname, lastname, mi, address, city, state, zip, homephone, workphone, email, dob, pin, status ) VALUES (20, 'Randy', 'Thompson', ' ', '1459 Northtown Blvd', 'Columbus', 'OH', '43229-4551', '6148891544', '6142541254', 'rthompson@email.com', '12-JAN-1963', '65454', 'S'); /* These are the insert statements for the Faculty table. Be sure to use a sequence for one of the tables */ INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (1, 'sam', 'cook', 'p', '6142812811', '6142541254', 'newbie', '5 YEARS', 'A'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (2, 'mike', 'jones', 'l', '6142812811', '6142541254', 'historian', '3 YEARS', 'T'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (3, 'denise', 'smith', 'h', '6142812811', '6142541254', 'professor', '25 YEARS', 'A'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (4, 'tim', 'miller', 'o', '6142812811', '6142541254', 'Adjunct', '5 YEARS', 'L'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (5, 'ann', 'meek', 'p', '6142812811', '6142541254', 'Professor', '35 YEARS', 'R'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (6, 'betty', 'lane', 'w', '6142812811', '6142541254', 'associate', '5 YEARS', 'A'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (7, 'greg', 'morris', 's', '6142812811', '6142541254', 'Professor', '5 YEARS', 'L'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (8, 'wayne', 'grill', 'a', '6142812811', '6142541254', 'associate', '15 YEARS', 'R'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (9, 'mika', 'wiley', 'q', '6142812811', '6142541254', 'associaTe', '10 YEARS', 'A'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (10, 'debbie', 'wine', 'y', '6142812811', '6142541254', 'Adjunct', '5 YEARS', 'L'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (11, 'sonya', 'fellows', 'z', '6142812811', '6142541254', 'associate', '5 YEARS', 'A'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (12, 'leslie', 'merritt', 'p', '6142812811', '6142541254', 'professor', '35 YEARS', 'R'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (13, 'thomas', 'prist', 'l', '6142812811', '6142541254', 'associate', '5 YEARS', 'A'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (14, 'rodney', 'reynolds', 'm', '6142812811', '6142541254', 'TA', '2 YEARS', 'A'); INSERT INTO FACULTY ( facultyid, firstname, lastname, mi, workphone, cellphone, rank, experience, status ) VALUES (15, 'cathy', 'frazier', 'n', '6142812811', '6142541254', 'associate', '5 YEARS', 'A'); /* These are the insert statements for the Course table. Be sure to use a sequence for one of the tables */ INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status ) VALUES (1, 'ACT-1211', 'Financial Accounting', 'Financial Accounting', '3', 'E'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (2, 'CSCI-2370', 'Database Systems', 'Database Systems', '3', 'I'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (3, 'CSCI-2489', 'Mobile Development', 'Mobile Development', '4', 'D'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours , status) VALUES (4, 'CSCI-2447', 'JavaScript Fundamentals', 'JavaScript Fundamentals', '4', 'B'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (5, 'CSCI-2412', 'Web Database', 'Web Database', '3', 'A'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (6, 'GIS-2100', 'Intro to GIS Data', 'Intro to GIS Data', '3', 'E'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (7, 'GIS-1102', 'GIS in Industry', 'GIS in Industry', '4', 'I'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (8, 'GIS-1101', 'Acquire GIS Data', 'Acquire GIS Data', '4', 'E'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (9, 'HOSP-1153', 'Nutrition', 'Nutrition', '3', 'D'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (10, 'HOSP-1109', 'Food Principles', 'Food Principles', '3', 'S'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (11, 'IMM-2601', 'Game Development', 'Game Development', '4', 'R'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours , status) VALUES (12, 'IMM-1530', 'Screenwriting', 'Screenwriting', '4', 'Z'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (13, 'MKTG-1110', 'Marketing Principles', 'Marketing Principles', '3', 'U'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (14, 'SES-1006', 'Intro to Golf', 'Intro to Golf', '3', 'Y'); INSERT INTO COURSE ( courseid, coursenumber, coursename, description, credithours, status) VALUES (15, 'SES-2216', 'Basics of Golf', 'Basics of Golf', '4', 'B'); /* These are the insert statements for the Location table. Be sure to use a sequence for one of the tables */ INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (1, 'Eibling Hall', ' 209', 00030, 'A'); INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (2, 'Delaware Hall', ' 210', 00030, 'B'); INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (3, 'Nestor Hall', ' 211', 00030, 'C'); INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (4, 'Delaware', ' 212', 00030, 'D'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (5, 'Eibling Hall', ' 213', 00030, 'E'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (6, 'Acquinas Hall', ' 214', 00030, 'F'); INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (7, 'Nestor Hall', ' 215', 00030, 'G'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (8, 'Eibling Hall', ' 216', 00030, 'H'); INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (9, 'Nestor Hall', ' 217', 00030, 'I'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (10, 'Eibling Hall', ' 218', 00030, 'J'); INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (11, 'Delaware Hall', ' 220', 00030, 'K'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (12, 'Eibling Hall', ' 221', 00030, 'L'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (13, 'Eibling Hall', ' 222', 00030, 'M'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (14, 'Eibling Hall', ' 223', 00030, 'N'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (15, 'Eibling Hall', ' 224', 00030, 'O'); INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (16, 'Delaware Hall', ' 220', 00030, 'P'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (17, 'Eibling Hall', ' 221', 00030, 'Q'); INSERT INTO Location ( locationid, Building, Room, Capacity, status) VALUES (18, 'Delaware Hall', ' 222', 00030, 'R'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (19, 'Acquinas Hall', ' 223', 00030, 'S'); INSERT INTO Location ( locationid , Building, Room, Capacity, status) VALUES (20, 'Eibling Hall', ' 224', 00030, 'T'); /* These are the insert statements for the Section table. Be sure to use a sequence for one of the tables */ INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (1, 99, 'B1-H', 'Tue', '22-MAY-13', '3-AUG-13', 29999, 23, 'A'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (2, 999, 'C1-H', 'Wed', '22-MAY-13', '3-AUG-13',39999, 23, 'C'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (3, 999, 'D1-W', 'Thur', '22-MAY-13', '3-AUG-13',49999, 23, 'F'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (4, 99999, 'E1-2', 'Fri', '22-MAY-13', '3-AUG-13',59999, 23, 'D'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (5, 999, 'F1-B', 'Sat', '22-MAY-13', '3-AUG-13',69999, 23, 'E'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (6, 99999, 'G1-B', 'Mon', '22-MAY-13', '3-AUG-13',79999, 23, 'E'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (7, 9999, 'H1-HWB', 'Tue', '22-MAY-13', '3-AUG-13',89999, 23, 'R'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (8, 999, 'I1-HWB', 'Wed', '22-MAY-13', '3-AUG-13',99999, 23, 'T'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (9,9999, 'J1-HWB', 'Thur', '22-MAY-13', '3-AUG-13',9999, 23, 'U'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (10, 99999, 'K1-HWB', 'Fri', '22-MAY-13', '3-AUG-13',229999, 23, 'A'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (11, 9999, 'L1-HWB', 'Sat', '22-MAY-13', '3-AUG-13',2439870000, 23, 'G'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (12, 999, 'M1-W', 'Tue', '22-MAY-13', '3-AUG-13',2249870000, 23, 'A'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (13, 9999, 'N1-W', 'Thur', '22-MAY-13', '3-AUG-13',2259870000, 23, 'Y'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (14, 999, 'O1-W', 'Mon', '22-MAY-13', '3-AUG-13',2269870000, 23, 'A'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (15, 99999, 'P1-H', 'Fri', '22-MAY-13', '3-AUG-13',2279870000, 23, 'R'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (16, 99999, 'Q1-H', 'Sat', '22-MAY-13', '3-AUG-13',2289870000, 23, 'E'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (17, 9999, 'R1-H', 'Tue', '22-MAY-13', '3-AUG-13',2299870000, 23, 'W'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (18, 9999, 'S1-WE', 'Tue', '22-MAY-13', '3-AUG-13',2309870000, 23, 'I'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (19, 999, 'T1-WE', 'Tue', '22-MAY-13', '3-AUG-13',2319870000, 23, 'W'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (20, 99999, 'U1-LE', 'Tue', '22-MAY-13', '3-AUG-13',229999, 23, 'N'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (21, 9999, 'V1-LE', 'Tue', '22-MAY-13', '3-AUG-13',2439870000, 23, 'W'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (22, 999, 'W1-LE', 'Tue', '22-MAY-13', '3-AUG-13',2249870000, 23, 'A'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (23, 99999, 'X1-BTW', 'Tue', '22-MAY-13', '3-AUG-13',2259870000, 23, 'A'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (24, 9999, 'Y1-BTW', 'Tue', '22-MAY-13', '3-AUG-13',2269870000, 23, 'I'); INSERT INTO SECTION ( sectionid, courseid, SectionNumber, Days, Starttime, endtime, locationid, SeatsAvailable, Status) VALUES (25, 999, 'A1-BTW', 'Mon', '22-MAY-13', '3-AUG-13', 19999, 23, 'O'); DROP SEQUENCE S_id; CREATE SEQUENCE S_ID MINVALUE 1 MAXVALUE 99999 START WITH 1 INCREMENT BY 1 CACHE 20; DROP INDEX i_sTUDENT_first_Name; CREATE INDEX i_Student_first_Last_Name on Student(lastname); Lab3 Starts here create table Registration ( RegNumber varchar(10), StudentID number(10), SectionID number(10), courseID number(10), primary key(StudentID,SectionID,courseID), foreign key (StudentID) references dbo.STUDENT(STUDENTid), foreign key (SectionID) references dbo.SECTION(SECTIONid), foreign key (courseID) references dbo.COURSE(COURSEid) ) create table Grade ( StudentID number(10), SectionID number(10), courseID number(10), Grade varchar(10) null, primary key(StudentID,SectionID,courseID), foreign key (StudentID) references dbo.STUDENT(STUDENTid), foreign key (SectionID) references dbo.SECTION(SECTIONid), foreign key (courseID) references dbo.COURSE(COURSEid) ) create table CourseInstructor ( FacultyID number(10), SectionID number(10), courseID number10) primary key(FacultyID,SectionID,courseID) foreign key (FacultyID) references dbo.Faculty(FacultyID), foreign key (SectionID) references dbo.SECTION(SECTIONid), foreign key (courseID) references dbo.COURSE(COURSEid) ) insert into Registration (RegNumber,StudentID, SectionID, courseID) values('Booky123',1,2,3) insert into Registration (RegNumber,StudentID, SectionID, courseID) values('Denise123',2,2,3) insert into Registration (RegNumber,StudentID, SectionID, courseID) values('Rocky123',3,5,3) insert into Registration (RegNumber,StudentID, SectionID, courseID) values('Tammy123',1,2,6) insert into CourseInstructor (FacultyID, SectionID, courseID) values(1,6,3) insert into CourseInstructor (FacultyID, SectionID, courseID) values(2,1,4) insert into CourseInstructor (FacultyID, SectionID, courseID) values(4,2,5) insert into CourseInstructor (FacultyID, SectionID, courseID) values(6,5,6) insert into CourseInstructor (FacultyID, SectionID, courseID) values(3,4,1) Create View RegisteredDetails select Student ID, Name,CourseName, SectionID, CreditHours, Days, StartTime,Endtime from Registration r join student s on r.studentid=s.studentid join dbo.COURSE c on c.courseid=r.courseid join dbo.SECTION s on s.SectionID=s.SectionID Create View as select facultyname, coursename, SectionID, CreditHours, Days, StartTime,Endtime from CourseInstructor join dbo.COURSE c on c.courseid=r.courseid join dbo.SECTION s on s.SectionID=s.SectionID
Explanation / Answer
Here, the structure of the class table is missing.
But, to get this thing done, you can write a query, update table student set grade = 'a' where class_id = (select class_id from class where name like %{A-E})
To get your target achieved, what you have to is, fire an update query which will update the grade of a student where the class Idname will be equal to the Class Id. And this class ID will be fetched using another sub-query with the condition of matching the last letter as per your requirement.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.