Question
Need to Create an Anonymous block that uses the If construct to test if each student is male or female and the IF Construct to
Need to Create an Anonymous block that uses the If construct to test if each student is male or female and the IF Construct to determine if the major is a Math, English, Comp Sci, or Geography major.
CREATE TABLE Student1 ( Stu_ID INT, Lname VARCHAR2(25), Fname VARCHAR2(25), Mi VARCHAR2(2), Sex VARCHAR2(2), Major VARCHAR2(10), Home_State VARCHAR2(5), CONSTRAINT student_pk PRIMARY KEY(Stu_ID) );
INSERT INTO Student1 (Stu_ID, Lname, Fname, Mi, Sex, Major, Home_State) VALUES (10001, 'Smith', 'Sam', 'M', 'M', 'Math', 'Tx'); INSERT INTO Student1 (Stu_ID, Lname, Fname, Mi, Sex, Major, Home_State) VALUES (10002, 'Jones', 'Peter', 'A', 'M', 'English', 'TX'); INSERT INTO Student1 (Stu_ID, Lname, Fname, Mi, Sex, Major, Home_State) VALUES (10003, 'Peters', 'Anne', 'A', 'F', 'English', 'ME'); INSERT INTO Student1 (Stu_ID, Lname, Fname, Mi, Sex, Major, Home_State) VALUES (10004, 'Johnson', 'John', 'J', 'M', 'CompSci', 'Ca'); INSERT INTO Student1 (Stu_ID, Lname, Fname, Mi, Sex, Major, Home_State) VALUES (10005, 'Penders', 'Alton', 'P', 'F', 'Math', 'GA'); INSERT INTO Student1 (Stu_ID, Lname, Fname, Mi, Sex, Major, Home_State) VALUES (10006, 'Allen', 'Diane', 'J', 'F', 'Geography', 'Minn'); INSERT INTO Student1 (Stu_ID, Lname, Fname, Mi, Sex, Major, Home_State) VALUES (10007, 'Gill', 'Jennifer', NULL, 'F', 'CompSci', 'Tx'); INSERT INTO Student1 (Stu_ID, Lname, Fname, Mi, Sex, Major, Home_State) VALUES (10008, 'Johns', 'Roberta', NULL, 'F', 'CompSci', 'Tx'); INSERT INTO Student1 (Stu_ID, Lname, Fname, Mi, Sex, Major, Home_State) VALUES (10009, 'Wier', 'Paul', NULL, 'M', 'Math', 'Ala'); INSERT INTO Student1 (Stu_ID, Lname, Fname, Mi, Sex, Major, Home_State) VALUES (10010, 'Evans', 'Richard', NULL, 'M', 'English', 'Tx');
CREATE TABLE Course ( Course_ID VARCHAR2(25), Section# VARCHAR2(5), C_Name VARCHAR2(25), C_Description VARCHAR2(35), CONSTRAINT course_pk PRIMARY KEY(Course_ID) );
INSERT INTO Course (Course_ID, Section#, C_Name, C_Description) VALUES ('COSC1300', 001, 'Intro to Comp.', 'First Computer Course'); INSERT INTO Course (Course_ID, Section#, C_Name, C_Description) VALUES ('ITSE2309', 001, 'Intro to DB', 'First Database Course'); INSERT INTO Course (Course_ID, Section#, C_Name, C_Description) VALUES ('GEOG1791', 002, 'World Geography', 'Second Geography Course'); INSERT INTO Course (Course_ID, Section#, C_Name, C_Description) VALUES ('COSC1315', 001, 'Intro to Prog.', 'Second Computer Course'); INSERT INTO Course (Course_ID, Section#, C_Name, C_Description) VALUES ('ITSE1345', 001, 'Intro to DB Prog.', 'Second Database Course'); INSERT INTO Course (Course_ID, Section#, C_Name, C_Description) VALUES ('ENGL2617', 002, 'English Literature', 'Second English Course'); INSERT INTO Course (Course_ID, Section#, C_Name, C_Description) VALUES ('MATH1101', 001, 'Calculus 1', 'Second Math Course'); INSERT INTO Course (Course_ID, Section#, C_Name, C_Description) VALUES ('ENGL1001', 001, 'American Literature', 'First English Course'); INSERT INTO Course (Course_ID, Section#, C_Name, C_Description) VALUES ('MATH1011', 001, 'Trig. and Algebra', 'First Math Course'); INSERT INTO Course (Course_ID, Section#, C_Name, C_Description) VALUES ('GEOG1010', 001, 'Texas Geography', 'First Geography Course');
CREATE TABLE Student_Course ( Stu_ID INT, Course_ID VARCHAR2(25), Section# VARCHAR2(5), CONSTRAINT student_fk FOREIGN KEY(Stu_ID) REFERENCES Student1(Stu_ID), CONSTRAINT course_fk FOREIGN KEY(Course_ID) REFERENCES Course(Course_ID) );
INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10001, 'MATH1101', 001); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10002, 'ENGL2617', 002); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10003, 'ENGL1001', 001); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10003, 'ENGL2617', 002); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10003, 'GEOG1010', 001); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10004, 'COSC1315', 001); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10005, 'MATH1101', 001); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10006, 'GEOG1010', 001); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10006, 'GEOG1791', 002); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10007, 'COSC1315', 001); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10007, 'ITSE2309', 001); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10008, 'COSC1315', 001); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10009, 'ITSE2309', 001); INSERT INTO Student_Course (Stu_ID, Course_ID, Section#) VALUES (10010, 'ENGL2617', 002);
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started