and SELECT: nested SELECT statement will in SELECT and FROM Subqueries in FROM c

Question

and SELECT: nested SELECT statement will in SELECT and FROM Subqueries in FROM clause: 7. SELECT A1.Az.A FROM R1. R 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 (sizeHS/ 1000) as scaledGPA FROM STUDENT WHERE abs(GPA (sizeHS/1000) - GPA)>1.0; SELECT FROM (SELECT sID, sName, GPA, GPA WHERE abs(G.scaledGPA - GPA)> 1.0 (sizeHS/1000) as scaledGPA FROM STUDENT) G Subquery in SELECT clause will generate a value: Q2. 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 Apply.sID Student.sID and GPA >= ALL ( SELECT GPA FROM Student, Apply WHERE Students|D ApplysID and Apply.cName - College.cName); SELECT cName, state, (SELECT distinct GPA FROM Apply, Student WHERE College.cName Apply.cName and ApplyslD·Student·sID and GPA >= ALL ( SELECT GPA FROM Student, Apply WHERE StudentsID-ApplysID and Apply.cName-College·cName)) as GPA from College

Explanation / Answer

Hi,
1. Both the queries are correct but written differently,
in the first query, only a where is used like
select sID,sNAME,GPA,GPA*(sizeHS/1000) as scaledGPA from STUDENT where abs(GPA*(sizeHS/1000)-GPA)>1.0;
here we are only collecting data from student subject to the conditio that abs(GPA*(sizeHS/1000)-GPA)>1.0;
Now, this can also be written using subquery like the one given in below/or in question
select * from(select sID,sNAME,GPA,GPA*(sizeHS/1000) as scaledGPA from STUDENT)G where abs(G.scaledGPA-GPA)>1.0;
this query creates a temp table G in the memory with columns sID,sNAME,GPA,scaledGPA, and then applied the filter specified in where condition
2.In this query, the subquery generates a value instead of a FULL table like the above one,
like in the given query,
select DISTINCT College.cName,state,GPA FROM College,Apply,Student WHERE College.cName=Apply.cName
and Apply.sID=Student.sID and GPA >=(SELECT GPA FROM Student,Apply where Student.sID=Apply.sID and Apply.cName=College.cName)
now the nested query is
SELECT GPA FROM Student,Apply where Student.sID=Apply.sID and Apply.cName=College.cNamewhich generates a single value of GPA for every student which is then used in the where condition
Simlarly in the next query,ALL() returns true if all of the subquery values satisfy the condition defined outside

Thumbs up if this was helpful, otherwise let me know in comments

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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