Find the student numbers of students who have received an A or B in courses taug

Question

Find the student numbers of students who have received an A or B in courses taught in the fall semester. Do this in two ways: First using a subquery, and then using a join.

(To be done with SQLplus)

The database is as follows (bold is table name, italics are the PK followed by all attributes of the table)

Student - stno NUMBER(3), sname VARCHAR2(20), major CHAR(4), class NUMBER(1), bdate DATE

Grade_report - student_number NUMBER(3), section_id NUMBER(6), grade CHAR(1)

Section - section_id NUMBER(6), course_num CHAR(8), semester VARCHAR2(6), year CHAR(2), instructor NUMBER(3), room NUMBER(3)

Department_to_major - dcode CHAR(4), dname CHAR(20)

Course - course_number CHAR(8), course_name CHAR(20), credit_hours NUMBER(2), offering_dept CHAR(4)

Room - bldg NUMBER(3), room NUMBER(3), capacity NUMBER(4), ohead CHAR(1)

Prereq - course_number CHAR(8), prereq CHAR(8)

Explanation / Answer

write a query for find the number of students who have received A or B

Sub Query:

Table 1 : student

stno NUMBER(3),
sname VARCHAR2(20),
major CHAR(4),
class NUMBER(1),
bdate DATE

Table 2: Grade_Report

student_number NUMBER(3),
section_id NUMBER(6),
grade CHAR(1)

Table 3: Course

course_number CHAR(8),
course_name CHAR(20),
credit_hours NUMBER(2),
offering_dept CHAR(4)

By using student,grade report sub query

select stno,sname from student
where stno in(select student_number from grade_report where grade='A' or grade='B')
order by stno;

By using student,grade report sub query with joins

select st.stno,st.sname,gr.student_number,c.course_name,c.course_number
from student st,
grade_report gr,
course c
where st.stno in(
select gr.student_number from grade_report gr where gr.grade='A' or gr.grade='B')
order by st.stno;

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.