5-3 Just do 5-3 Here is the table used SQL> SELECT * 2 FROM rearp.Section; SECTION_ID COURSE_NUM SEMESTER YEAR ---------
Posted: Tue Jul 12, 2022 8:20 am
5-3
Just do 5-3
Here is the table used
SQL> SELECT *2 FROM rearp.Section;
SECTION_ID COURSE_NUM SEMESTER YEAR---------- ------------------------ ------------------ ------INSTRUCTOR BLDG ROOM------------------------------ ---------- ----------85 MATH2410 FALL 14KING 36 123
86 MATH5501 FALL 14EMERSON 36 123
87 ENGL3401 FALL 15HILLARY 13 101
SECTION_ID COURSE_NUM SEMESTER YEAR---------- ------------------------ ------------------ ------INSTRUCTOR BLDG ROOM------------------------------ ---------- ----------88 ENGL3520 FALL 16HILLARY 13 101
89 ENGL3520 SPRING 16HILLARY 13 101
90 COSC3380 SPRING 16HARDESTY 79 179
SECTION_ID COURSE_NUM SEMESTER YEAR---------- ------------------------ ------------------ ------INSTRUCTOR BLDG ROOM------------------------------ ---------- ----------91 COSC3701 FALL 1479 179
92 COSC1310 FALL 16ANDERSON 79 179
93 COSC1310 SPRING 16RAFAELT 79 179
SECTION_ID COURSE_NUM SEMESTER YEAR---------- ------------------------ ------------------ ------INSTRUCTOR BLDG ROOM------------------------------ ---------- ----------94 ACCT3464 FALL 15RODRIGUEZ 74
95 ACCT2220 SPRING 15RODRIQUEZ 74
96 COSC2025 FALL 17RAFAELT 79 179
5-2. Create a duplicate table (call it Stutab) in your account from the Student table, which contains all rows from the Student table. Hint: Use DESC Student to see the attributes of the Student table. Create the Stutab table with a CREATE TABLE command. INSERT INTO Stutab... SELECT to populate it. a. List student names and majors from the Stutab table for students who are juniors or seniors only. b. List student names and computer science (COSC) majors from the Stutab table. C. Create a view (call it vstu) containing student names and majors, but only for COSC majors. (Use CREATE OR REPLACE VIEW....) d. List the student names and majors from the vstu view in descending order by name. e. Modify a row in your view of your table so a student's major is changed. f. Re-execute the display of the view. Did modifying your vstu view also change the parent- table, Stutab? g. Try to modify the view again, but this time change the major to 'COMPSC' -- an obviously invalid field in the Stutab table because the attribute was defined as four characters. Can you do it? What happens? 5-3. a. Repeat the CREATE VIEW part of the exercise in 5-2c with a column name in the CREATE OR REPLACE VIEW statement like this: CREATE OR REPLACE VIEW vx (Na, Ma) AS SELECT ... using the Stutab table you created in exercise 5-2. Display the view. What is the effect of the (Na, Ma)? b. Repeat exercise 5-3a, but this time use column aliases in the view definition. Are there any differences noted between explicitly defining the view-columns in the heading part of the statement versus using a column alias?
- 5 3 Just Do 5 3 Here Is The Table Used Sql Select 2 From Rearp Section Section Id Course Num Semester Year 1 (436.47 KiB) Viewed 32 times
Here is the table used
SQL> SELECT *2 FROM rearp.Section;
SECTION_ID COURSE_NUM SEMESTER YEAR---------- ------------------------ ------------------ ------INSTRUCTOR BLDG ROOM------------------------------ ---------- ----------85 MATH2410 FALL 14KING 36 123
86 MATH5501 FALL 14EMERSON 36 123
87 ENGL3401 FALL 15HILLARY 13 101
SECTION_ID COURSE_NUM SEMESTER YEAR---------- ------------------------ ------------------ ------INSTRUCTOR BLDG ROOM------------------------------ ---------- ----------88 ENGL3520 FALL 16HILLARY 13 101
89 ENGL3520 SPRING 16HILLARY 13 101
90 COSC3380 SPRING 16HARDESTY 79 179
SECTION_ID COURSE_NUM SEMESTER YEAR---------- ------------------------ ------------------ ------INSTRUCTOR BLDG ROOM------------------------------ ---------- ----------91 COSC3701 FALL 1479 179
92 COSC1310 FALL 16ANDERSON 79 179
93 COSC1310 SPRING 16RAFAELT 79 179
SECTION_ID COURSE_NUM SEMESTER YEAR---------- ------------------------ ------------------ ------INSTRUCTOR BLDG ROOM------------------------------ ---------- ----------94 ACCT3464 FALL 15RODRIGUEZ 74
95 ACCT2220 SPRING 15RODRIQUEZ 74
96 COSC2025 FALL 17RAFAELT 79 179
5-2. Create a duplicate table (call it Stutab) in your account from the Student table, which contains all rows from the Student table. Hint: Use DESC Student to see the attributes of the Student table. Create the Stutab table with a CREATE TABLE command. INSERT INTO Stutab... SELECT to populate it. a. List student names and majors from the Stutab table for students who are juniors or seniors only. b. List student names and computer science (COSC) majors from the Stutab table. C. Create a view (call it vstu) containing student names and majors, but only for COSC majors. (Use CREATE OR REPLACE VIEW....) d. List the student names and majors from the vstu view in descending order by name. e. Modify a row in your view of your table so a student's major is changed. f. Re-execute the display of the view. Did modifying your vstu view also change the parent- table, Stutab? g. Try to modify the view again, but this time change the major to 'COMPSC' -- an obviously invalid field in the Stutab table because the attribute was defined as four characters. Can you do it? What happens? 5-3. a. Repeat the CREATE VIEW part of the exercise in 5-2c with a column name in the CREATE OR REPLACE VIEW statement like this: CREATE OR REPLACE VIEW vx (Na, Ma) AS SELECT ... using the Stutab table you created in exercise 5-2. Display the view. What is the effect of the (Na, Ma)? b. Repeat exercise 5-3a, but this time use column aliases in the view definition. Are there any differences noted between explicitly defining the view-columns in the heading part of the statement versus using a column alias?