CST221 Fall 11, Zhen Jiang Quiz 3

Consider the following database with two tables: Fac and Student:

Fac
AdvNum / Name / Rank / Office / Yrs Worked
A10 / Jones / Full Prof / B406 / 12
A20 / Smith / Asst Prof / Y310 / 4
A30 / Bradly / Full Prof / X234 / 10
A40 / Anders / Instructor / X208 / 2
A50 / Baker / Asst Prof / B402 / 14
Student
StNum / StName / age / Major / class / gpa / AdvNum
s100 / Al / 20 / BIO / JR / 2.5 / A40
s18 / Matt / 21 / ENG / JR / 3.7 / A30
S24 / Nicole / 24 / ENG / SR / 3.2 / A10
s28 / Jenny / 25 / BIO / FR / 3.2 / A30
s34 / Bob / 20 / CSC / FR / 2.5 / A40
s39 / Amanda / 30 / CSC / JR / 2.6 / A20
s49 / Cindy / 30 / CSC / JR / 3.6 / A40
s54 / Fred / 20 / BIO / FR / 1.5 / A50
s55 / Pam / 30 / ENG / SO / 3.6 / A10
s56 / Sam / 22 / CSC / SO / 4 / A50
s65 / Alisha / 20 / BIO / SO / 2.9 / A10
s71 / Hal / 40 / CSC / SR / 2.8 / A40
s77 / John / 17 / CSC / FR / 3 / A50
s78 / Al / 19 / ENG / SR / 3 / A10
s88 / Ed / 25 / CSC / JR / 3.5 / A50
s96 / Lisa / 30 / ENG / FR / 3.5 / A20
s99 / Sam / 45 / BIO / JR / 2 / A30

FR represents freshman, SO- sophomore JR- Junior and SR-Senior

Create Sql queries directly for each of these.

Select (list of attributes)

From (table name)

where (some condition which can be applied to each row)

group by (group division)

having (any condition to be applied to select groups)

Order by (some attribute ) [asc/desc] ;

  1. Select display
  2. Table(s)
  3. Check unique attribute name
  4. Where condition: and, or, not; multiple table connection
  5. Group division
  6. Group selection
  7. Order

Q1) For each student who is a Freshman in Biology, list number, name, age, and gpa.

Q2) For each student who has a GPA <2 , list their gpa, age, number, name, the name and number of their advisor.

Q3)For each advNum, list advNum and the average gpa and count of their Freshman advisees.

Q4) For each student who is a senior, list stNum, name, gpa, advisor number, advisor name and rank.

Order your result stName.

Q5)List the advisor number, of any advisor who has more than one student with a GPA above 3. Include a count of these students.

Send to me (using digital drop box in blackboard ) your Access database(which contains your

SQL queries)

A)SELECT stnum, stname, age, gpa

FROM Student

WHERE class="FR" and major="BIO";

B)

SELECT student.gpa, student.age, student.stnum, student.stname, Fac.advNum, Fac.Name

FROM student, Fac

WHERE student.advnum=fac.advnum AND gpa<2;

C)

SELECT student.advNum, Avg(student.gpa) AS [avg gpa], Count(student.stnum) AS [count]

FROM student

WHERE class=”FR”

GROUP BY student.advNum;

D)

SELECT stnum, stname, gpa, fac.advNum, name, rank

FROM student, fac

WHERE student.advNum=fac.advnum

and class="SR"

ORDER BY stName;

E)

SELECT advnum, count(stnum) AS [count]

FROM student

WHERE gpa>3

GROUP BY advnum

HAVING count(stnum)>1;