Given the following relational schema: a. List the name of every student who has

Question

Given the following relational schema:

a. List the name of every student who has taken a course twice and earned the same grade.

b. List the CourseNumber of every course that meets or met on TTH (Tuesday and Thursday).

c. List distinct course numbers and student names who earned an A in the corresponding course and order them on CourseNumber Ascending, student name Descending.

d. List every course number meets or met in the same rom during at least two quarters.

e. List the name of every student, course number he or she has taken, course title, quarter, and the grade.

f. List every attribute of every student whose name starts with the letter 'C'.

g. List the name of every course which has a number of units greater than or equal 4.

Student (SSN, Name, Major) Course = ( CourseNumber. Ouarter, Course Title, Number(fUnits, RoomNumber, DayTime), where Day Time is of the form MW 1:0-2:00PM. Enrollment-SSN.C Grade) Write an SQL statement for each one of the following queries: ourseNumber, Quarter,

Explanation / Answer

Hi,

Please find below the answers-

Considering Oracle Database-
Ans a) select name from Student where SSN in(select pp.SSN from(
select SSN,rank() over(partition by SSN,CourseNumber,Grade order by SSN)rnk from Enrollment)pp
where pp.rnk=2);

Ans b) select CourseNumber from Course where substr(DayTime,1,3)='TTH';

Ans c) select distinct CourseNumber,Name from Enrollment e join Student s
on e.SSN=s.SSN
and e.grade='A'
order by CourseNumber asc,Name desc;

Ans d) select CourseNumber from Course where SSN in(
select CourseNumber ,rank() over(partition by CourseNumber,Quarter,RoomNumber order by Quarter)rnk from Course)pp
where pp.rnk>=2';

Ans e) select Name,coursenumber,coursetitle,quarter,grade from student s join enrollment e
on s.ssn=e.ssn
join course c
on e.coursenumber=c.course number;

Ans f) select ssn,name,major from student
where substr(name,1,1)='C';

Ans g) select coursetitle from course where numberofunits>=4;

Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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