Practicle Guide to Using SQL in Oracle. Chapter 5 exercises. Develop and execute
ID: 3872578 • Letter: P
Question
Practicle Guide to Using SQL in Oracle. Chapter 5 exercises.
Develop and execute a query to find the names of students who had HERMANO as an instructor and earned a grade of B or better in the class.
Develop the query by first finding sections where HERMANO was the instructor.
Save this query, then recall (EDIT) it and modify the query to join the sctn table with the grdrpt table.
Then add the grade constraint. Save this query (with the REPLACE option).
Then, recall it and go from there. Show all the steps of your output.
Use the tables from the student data ta
stdnt
stno NOT NULL NUMBER (3)
PRIMARY KEY NOT NULL
sname VARCHAR2(20)
major CHAR(4)
class NUMBER(1)
bdate DATE
grdrpt
student_number NOT NULL NUMBER(3)
section_id NOT NULL NUMBER(6)
grade CHAR(1)
PRIMARY KEY (student_number, section_id)
sctn
section_id NOT NULL NUMBER(6)
PRIMARY KEY NOT NULL
course_num CHAR(8)
semester VARCHAR2(6)
year CHAR(2)
instructor CHAR(10)
bldg. NUMBER(3)
room NUMBER(3)
dptmaj
dcode NOT NULL CHAR(4)
PRIMARY KEY NOT NULL
dname CHAR(20)
crs
course_name CHAR(20)
course_number NOT NULL CHAR(8)
PRIMARY KEY NOT NULL
credit_hours NUMBER(2)
offering_dept CHAR(4)
rm
bldg NOT NULL NUMBER(3)
room NOT NULL NUMBER(3)
capacity NUMBER(4)
ohead CHAR(1)
PRIMARY KEY (bldg., room)
preq
course_number CHAR(8)
prereq CHAR(8)
PRIMARY KEY (course_number,prereq)
ble below:
Explanation / Answer
SELECT section_id
FROM sctn
WHERE instructor = “HERMANO ”
SELECT sctn.section_id, grdrpt. student_number
FROM sctn
INNER JOIN grdrpt ON sctn.section_id = grdrpt.section_id
WHERE sctn.instructor = “HERMANO ”
SELECT sctn.section_id ,grdrpt. student_number
FROM sctn
INNER JOIN grdrpt ON sctn.section_id = grdrpt.section_id
WHERE sctn.instructor = “HERMANO ” AND (grdrpt.grade = “B” OR grdrpt.grade = “A”)
SELECT sname
FROM stdnt
WHERE stno = (SELECT grdrpt. student_number
FROM sctn
INNER JOIN grdrpt ON sctn.section_id = grdrpt.section_id
WHERE sctn.instructor = “HERMANO ” AND (grdrpt.grade = “B” OR grdrpt.grade = “A”) )
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.