z. Subqueries in FROM and SELECT: nested SELECT statement will in SELECT and FRO

Question

z. Subqueries in FROM and SELECT: nested SELECT statement will in SELECT and FROM clause SELECT A A.An FROM R1-R2-...,Rm WHERE condition Subquery in FROM clause will generate a table: Q1. Students whose scaled GPA changes GPA by more than 1. SELECT SID, sName, GPA, GPA FROM STUDENT WHERE abs(GPA (sizeHS/1000) GPA)>1.0 (sizeHS/ 1000) as scaledGPA SELECT FROM (SELECT sID, sName, GPA, GPA (sizeHS/1000) as scaledGPA FROM STUDENT) G WHERE abs(G.scaledGPA- GPA)>1.0; Subquery in SELECT clause will generate a value: 02. Colleges paired with the highest GPA of their applicants SELECT DISTINCT College.cName, state, GPA FROM College, Apply, Student WHERE College.cName Apply.cName and ApplysID Students|D and GPA ALL SELECT GPA FROM Student, Apply WHERE Student·sID = Apply.slD and ApplycName = College-cName) SELECT cName, state (SELECT distinct GPA FROM Apply, Student WHERE College.cName Apply.cName and Apply ID Student.sID and GPA>ALL( SELECT GPA FROM Student, Apply WHERE Student.sID Apply.sID and Apply.cName College.cName)) as GPA from College;

Explanation / Answer

SELECT siD, sName, GPA, GPA * (sizeHS / 1000) as scaledGPA
FROM STUDENT
WHERE abs(GPA * (sizeHS/1000)-GPA) > 1.0;

This is using subquery:

SELECT *
FROM ( SELCT siD, sName, GPA, GPA * (sizeHS / 1000) as scaledGPA FROM STUDENT) G
WHERE abs(G.scaledGPA-GPA) > 1.0;

2)

SELECT DISTINCT College.cName, state, GPA

FROM College, Apply, Student

WHERE College.cName = Apply.cName

and Apply.sID=Student.sID

and GPA>=ALL(

SELECT GPA

FROM Student, Apply

WHERE Student.sID= Apply.sID

and Apply.cName = College.cName);

SEELCT cName, state,

(SELECT distinct GPA

FROM Applu, Student

WHERE College.cName = Apply.cName

and Apply.sID = Student.sID

and GPA >= All(

SELECT GPA

FROM Student, Apply

WHERE Student.sID= Apply.sID

and Apply.cName = College.cName)) as GPA

from College;

Related Questions

Navigate

• Browse (All)
• Browse Z
• Subjects

---

---

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