Consider the following schema: Students ( bannerID : string, lName: string, fNam

Question

Consider the following schema:

Students(bannerID: string, lName: string, fName: string, email: string, address: string, phoneNum: string, GPA: real)
Courses( cNum:string, cname:string, creditHrs: integer, prerequisites: string)
Enroll(bannerID:string, cNum: string, semester: string, section:string, grade:string)

write the following queries in SQL statements.

a. “Find the names of Students.”
b. “Find the Banner IDs of students who enrolled in CS101.”
c. “Find the names and addresses of students who enrolled in CS101.”
d. “Find the names and addresses of students who enrolled in CS101Fall 2010.”
e. “Find the Banner IDs of students who have enrolled in both CS101 and CS102”.
f. “Find the Banner IDs of students who have enrolled in CS101 or CS102”.
g. “Find the Banner IDs of students who have enrolled in CS101 but not CS102”.
8. “Find the Banner IDs of students who have enrolled in at least one course”.
h. “Find the Banner IDs of students who have enrolled in at least two courses”.
i. “Find the Banner IDs of students who have not enrolled in any course yet Fall 2010”.

Explanation / Answer

Hi, I hope these queries help answer your question. If for some reason they're not doing what you need them to do you can send me a PM and we can take another look at them again. Otherwise, if this does the trick then please remember to rate :)

a. “Find the names of Students.”
select lName, fName from Students;

b. “Find the Banner IDs of students who enrolled in CS101.”
select bannerID from Enroll where cNum = 'CS101';

c. “Find the names and addresses of students who enrolled in CS101.”
select lName, fName, address from Students where bannerID IN ( select bannerID from Enroll where cNum='CS101');

d. “Find the names and addresses of students who enrolled in CS101Fall 2010.”
select lName, fName, address from Students where bannerID IN ( select bannerID from Enroll where cNum='CS101' AND semester='Fall 2010');

e. “Find the Banner IDs of students who have enrolled in both CS101 and CS102”.
select bannerID from Enroll where cNum='CS101'
intersect
select bannerID from Enroll where cNum='CS102'

f. “Find the Banner IDs of students who have enrolled in CS101 or CS102”.
select bannerID from Enroll where cNum='CS101'
union
select bannerID from Enroll where cNum='CS102'

g. “Find the Banner IDs of students who have enrolled in CS101 but not CS102”.
select bannerID from Enroll where cNum='CS101' and bannerID NOT IN (select bannerID from Enroll where cNum='CS102')
another way to write this could be
select bannerID from Enroll where cNum='CS101'
except
select bannerID from Enroll where cNum='CS102'

8. “Find the Banner IDs of students who have enrolled in at least one course”.
a student being enrolled in at least 1 course just means that their code shows up somewhere in the enroll table, right
select bannerID from Students where bannerID IN (select bannerID from Enroll)

h. “Find the Banner IDs of students who have enrolled in at least two courses”.
select bannerID from Enroll group by BannerID Having count(cNum) >= 2

i. “Find the Banner IDs of students who have not enrolled in any course yet Fall 2010”.
select bannerID from Students where bannerID NOT IN (select bannerID from Enroll where semester='Fall 2010')
another way could be
select bannerID from Students
except
select bannerID from Enroll where semester='Fall 2010'

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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