SQL in Oracle. Write, exicute and print a query to list student names and grades
ID: 3871242 • Letter: S
Question
SQL in Oracle.
Write, exicute and print a query to list student names and grades (just two attributes) using the table alias feature. Restrict the list to students that have either an A or B in courses with ACCT prefixes only.
Here is how to complete this problem:
Get the statement to work as a COUNT of a join of the three tables, stdnt, grdrpt, and sctn. Use table aliases in the join condition (remember to use the /*comments*/). Note that a join of n tables requires (n-1) join conditions, so here you have to have two join conditions - one to join the stdnt and grdrpt tables, and one to join the grdrpt and sctn tables.
Next, modify the query and put the accounting (ACCT) condition in the WHERE clause.
Finally, modify the query and add the frade constraints. The number of rows should decrease. Note that if you have WHERE x and y or z, parenthies are optional, but the criteria will be interpretated according to precedence rules.
Here are the tables. Only use the three requested in 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)
Explanation / Answer
Hi,
Given we have to use 3 tables stdnt,grdrpt and sctn . lets see what are the common columns and how we can join them,
stdnt and grdrpt both have student number so they will be joined on that
grdrpt and sctn both have section_id so they will be joined based on that column
now we can write the query like,
1.select count(*) from stdnt a,grdrpt b ,sctn c where a.stno=b.student_number and b.section_id=c.section_id;
next is add one where clause for ACCT, since we have to use where on course name, we join one more table CRS to get course name
2.select count(*) from stdnt a,grdrpt b, sctn c,crs d where a.stno=b.student_number and b.section_id=c.section_id and c.course_num=d.course_number and d.course_name like 'ACCT%';
finally, given we have to add some more wher clauses to this,
therefore it becomes
select a.sname,b.grade /*selectin only 2 columns as desired */ from stdnt a,grdrpt b ,sctn c,crs d where a.stno=b.student_number and b.section_id=c.section_id and c.course_num=d.course_number and d.course_name like 'ACCT%' /* cource should have ACCT prefix */ and b.grade IN ('A','B') /*grade condition as given */ ;
Thumbs up if this was helpful, otherwisr let me know in comments.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.