Question

All the questions are to run on the Application DATABASE Student(SID,SName,GPA,sizeHS); college(CName,State, Enroolment); Apply(SID,CName,Major,Decision) ;
PLEASE MAKE SURE THERE ARE NO DUPLICATES IN YOUR RESULTS

Q1.1: Get the names of all the students whose GPA>=3.8;

Q1.2:Get the names of all the students who applied to some college;

Q1.3: Get the names all the students who did not applied to any college;

Q1.4: Get the names and enrollment of all the colleges who received applications for a major involving bio;

Q1.5: Get all pairs of students who have the same GPA;

Q1.6: Get all the students who applied to both CS and EE majors;

Q7: Get all the students who applied to CS and not to EE;

Q1.8: Use subquery to answer Q1.6 & Q1.7;

Q1.9 :Get all colleges such that some other college is in the same state;

Q1.10: Get all students.with highest GPA--use the keyword EXISTS or NOT EXISTS to answer the query; hint; use a subquery

Q1.11: Now pair colleges with the name of their applicants.

Answer 1

Answer:

Check the explanation

Explanation:

Q1: SQL to list names of all the students whose GPA is above specific number:

select SName

from Student

where GPA >= 3.8;

Using the Student table we are able to get the details where the GPa is greater than the required value.

Q2: SQL to get list of names of all the students who applied to some college:

select SName from Student where SID in (

select distinct SID from Apply

);

Selecting the name of the students who are present in the Apply table.

Q3: SQL to get list of names all the students who did not apply to any college:

select SName from Student where SID not in (

select distinct SID from Apply

);

Selecting the name of the students who are not present in the Apply table.

Q4: SQL to get list of names and enrollment of all the colleges who received applications for a major involving bio:

select CName, Enrollment from College where CName in (

select CName from Apply where Major = 'Bio' and SID in (

select SID from Student

)

);