Tutorial 7 – Questions
Q1. How many rows are returned by this query?
SELECT COUNT(*)
FROM MOVIE
Q2. How many rows are returned by this query if there are NO rows in the MOVIE table?
SELECT COUNT(*)
FROM MOVIE
Q3. How many rows would you expect are returned by this query?
SELECT GENDER, COUNT(*)
FROM ACTOR
GROUP BY GENDER
Q4. What is wrong with this statement?
SELECT BIRTHCOUNTRY, COUNT(*)
FROM ACTOR
Q5. This statement will execute perfectly. Why is this statement next to useless?
SELECT COUNT(*)
FROM ACTOR
GROUP BY BIRTHCOUNTRY
Q6. What is wrong with this statement?
SELECT BIRTHCOUNTRY, COUNT(*)
FROM ACTOR
GROUP BY GENDER
Q7. Provide a couple of example rows for the result set produced by this statement.
SELECT BIRTHCOUNTRY, GENDER, COUNT(*)
FROM ACTOR
GROUP BY BIRTHCOUNTRY, GENDER
Q8. Describe the purpose of the MIN aggregate function?
Q9. How many rows in the result for this statement?
SELECT MIN(BirthDate) FROM ACTOR
Consider this table named STUDENT
StuId Name Gender BirthYear Degree SubjectsPassed
1 Fred M 1991 BIT 0
2 Sue F 1993 ICT 4
3 Emma F 1992 BIS 2
4 Dave M 1993 BIT 4
5 Leah F 1991 ICT 6
6 Linda F 1991 BIS 6
7 Harry M 1992 BIS 4
Q10. Write a single SQL statement that counts the total number of students
Q11. Write a single SQL statement that counts the total number of students that are female
Q12. Write a single SQL statement that counts the total number of students of each gender Hint:
Use a group by clause
Q13. Write a single SQL statement that counts the total number of students of each degree Hint:
Use a group by clause
Q14. Write a single SQL statement that counts the total number of students of each degree.
Does not list any total values less than 2
Hint: Use a Having clause
Q15. Write a single SQL statement that calculates the average number of subjects passed by each
Degree.
Only include Female students (ignore all males)
Does not list any total values less than 3
Hint: Use a Where, Group By and Having clause
Q16. Consider the following ERD
EmpNo TaskNo
EmployeeName Description
EMPLOYEE ALLOCATION TASK
HAS
TO
a. What is the identifier of the Allocation entity?
b. How many foreign keys will exist in the Allocation table?
c. Write the Relational Schema. Indicate all PKs & FKs.
d. Write SQL statements to
i. create employees Jim Black and Perry White
ii. create tasks Bake Cake and Chop Onions
iii. allocate Jim Black to the Chop Onions task
iv. allocate Perry White to the Bake Cake task
Q17. Consider the following ERD JobName
WID Description
WName Agreed Rate Date negotiated
WORKER ATTACHED JOB
IS
TO
a. What is the identifier of the ATTACHED entity?
b. How many foreign keys will exist in the ATTACHED table?
c. Write the Relational Schema. Indicate all PKs & FKs.
Q18. Consider the following business case and diagram:
HouseNo
BuilderCode
SuburbName StreetName
BuilderName
DateBuilt
Population
built by
SUBURB has HOUSE BUILDER
a. What is the Identifier of each entity
b. Convert the above ERD to a relational schema.
