Can someone please help me solve this SQL question. Please answer with copyable
ID: 3598905 • Letter: C
Question
Can someone please help me solve this SQL question. Please answer with copyable code.
Find the names, grades, and course numbers of students who have earned A's in computer science or math courses. Join the Section and Grade_report tables (be careful to not create the Cartesian product). Then, UNION the set of "course numbers COSC% and A" with the set of "course number MATH% and A."
Hint: Start with the query to get names, grades, and course numbers for COSC% and A, and then turn this into a view. Do the same for MATH% and A, and then execute the UNION statement as follows (using your view names):
SELECT *
FROM view1a
UNION
SELECT *
FROM view1b
Here is the database to use for the question:
The Student Course Database
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)
-----------------------------------------------------------------------------------------------------------------------------------------------------
I've been trying this and it doesn't seem to be correct: SQL> CREATE VIEW view1a AS
SELECT stdnt.sname, sctn.course_num, grdrpt.grade
FROM [sctn] INNER JOIN [grdrpt]
ON sctn.section_id = grdrpt.section_id
INNER JOIN [stdnt]
ON grdrpt.student_number = stdnt.stno
WHERE grade = 'A' AND course_num LIKE 'cosc%'; FROM [sctn] INNER JOIN [grdrpt]
* ERROR at line 3: ORA-00903: invalid table name
Explanation / Answer
Try with outer join between ON statements.
try this below code once.
CREATE VIEW view1a AS
SELECT stdnt.sname, sctn.course_num, grdrpt.grade
FROM [sctn] INNER JOIN [grdrpt]
ON sctn.section_id = grdrpt.section_id
OUTER JOIN [stdnt]
ON grdrpt.student_number = stdnt.stno
WHERE grade = 'A' AND course_num LIKE 'cosc%'; FROM [sctn] INNER JOIN [grdrpt]