Consider the following database. It has two tables: Fac contains info about faculty Student contains info about students. see image.
FR represents freshman, SO- sophomore JR- Junior and SR-Senior
1)For each following query, provide your expected result table first. Then, create Sql program. After that, verify them in ACCESS. Please use sql view directly without touching the design view, and please use the connection of multiple tables that we discussed in class, please not to use join or inner join operation in your sql program. Otherwise, you will lose all points.
2)Submit a) your expected result table, and b) your sql program in a word file via D2L. Late submission is not acceptable.
QA) For each student who is a Freshman in Biology, list number, name, age, and gpa.
QB) For each student who has a GPA <2 , list their gpa, age, number, name, the name and number of their advisor.
QC) For each student who is a Freshman, list their number, name, age, and a value called Expected age as a Senior (which shows their age three years from now, expected age as a senior).
QD) List all faculties (number, and name) who reside in Building B (office number starts with the letter B).
QE) For each advNum, list advNum and the average gpa and count of their Freshman advisees.
QF) For each Major, list the Major and the avg age, min age and max age for that major.
QG) For each student who is a senior, list stNum, name, gpa, advisor number, advisor name and rank. Order your result stName.
QH) For each class, list class and the number of students under 25 years old, include the average gpa of these students.
QI) For each advisor, list number, name, rank, and list of their advisees (stNum , stName and gpa) who have gpa below 3.0.
QJ) List the advisor number, of any advisor who has more than one student with a GPA above 3. Include a count of these students.
QK) For the faculty member named Jones (list name, number and rank) and all their advisees (number and name and age) who are not seniors. (sort the result by advisee names)
QL) List the number, name and gpa, for all students who have a higher than average gpa.
QM) List the number, name, and age, for all students who are in Majors with more than 5 members
For example, suppose you were asked to list the number, name, and age of all students >25 You expected the result table as the following and it can be implemented in the corresponding sql program. For submission, you need see image.
Select stNum,stName,age
from Student
where age>25;