SQL questions. Suppose that set A is the set of student names who have made an A
ID: 3596897 • Letter: S
Question
SQL questions.
Suppose that set A is the set of student names who have made an A or B in computer science (COSC) courses. Suppose further that set B is the set of students who have taken math courses (regardless of what grade they earned).
Then, set A minus set B would contain names of students who have made an A or B in computer science courses, less those who have taken math courses. Similarly, set B minus set A would be the set of students who took math courses, less those who took COSC courses and made an A or a B in some COSCxxxx. Build these queries into set difference queries as views based on student numbers and execute them, as follows:
a. Write a query that gives the student number, name, course, and grade for each set. Save each query as Q65a and Q65b.
b. After saving each query, reconstruct each query into a view of just student numbers, verify that it works, and then use CREATE VIEW to create set A and set B. Verify that you have the same number of tuples in set A as you have in Q65a, and the same number of tuples in set B as you have in Q65b.
c. Finally, display the student numbers of students in each set difference - show (set A minus set B) and (set B minus set A). Look at the original queries, Q65a and Q765b, to verify your result.
Here is the student table called 'stutable' in our Oracle program that we use to complete these questions:
The Student Course Database (stutable):
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)
Explanation / Answer
a)
Q65a
Select stno,sname,course_name,grade from stutable inner join grdrpt on stutable.stno = grdrpt.student_number inner join sctn on grdrpt.section_id = sctn.section_id inner join crs on sctn.course_num = crs.course_number where grade = 'A' or grade = 'B' and course_name LIKE 'COSC%';
Q65b
Select stno,sname,course_name,grade from stutable inner join grdrpt on stutablestno = grdrpt.student_number inner join sctn on grdrpt.section_id = sctn.section_id inner join crs on sctn.course_num = crs.course_number where course_name LIKE 'MATH%';
b.Views
1. Create view setA as Select stno,sname,course_name,grade from stutable inner join grdrpt on stutable.stno = grdrpt.student_number inner join sctn on grdrpt.section_id = sctn.section_id inner join crs on sctn.course_num = crs.course_number where grade = 'A' or grade = 'B' and course_name LIKE 'COSC%';
Select * from setA;
2. Create view setB as Select stno,sname,course_name,grade from stutable inner join grdrpt on stutable.stno = grdrpt.student_number inner join sctn on grdrpt.section_id = sctn.section_id inner join crs on sctn.course_num = crs.course_number where course_name LIKE 'MATH%';
Select * from setB;
c.
1. Select stno from setA minus Select stno rom setB;
2. Select stno from setB minus Select stno from setA;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.