关键词 > COMP2350/6350
COMP2350/6350 Sample Quiz 2 S2, 2022
发布时间:2022-09-07
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
COMP2350/6350 Sample Quiz 2
S2, 2022
1. Consider a relation:
Student (StudentNo, FamilyName, FamilyNameInitial, GivenName, Address) where:
FamilyName → FamilyNameInitial
StudentNo → FamilyName, GivenName, Address
Therefore:
StudentNo → FamilyNameInitial
Which Armstrong's axiom(s) is(are) utilised?
Choose one or more:
a. reflexivity
b. augmentation
c. transitivity
d. composition
2. Let R(A, B, C, D) be a relation with AB as a composite primary key and CD as non-key attributes. Assume that D → B is an FD on R. What can we say about the relation R?
a. R is in BCNF since it has exactly two non-key attributes.
b. R is not in 1NF since B is part of the primary key.
c. R could possibly be in 3NF. Though attribute B transitively depends on the primary key via
the non-key D, attribute B is a key attribute.
d. R is not in 3NF since transitive dependency of attribute B on the primary key via the non- key D exists.
3. Consider the following instance of the relation R (X, Y, Z)
X |
Y |
Z |
1 |
aaa |
xxx |
2 |
aaa |
yyy |
3 |
bbb |
zzz |
4 |
bbb |
zzz |
5 |
ccc |
xxx |
Which of the following is definitely correct?
a. X → Y is an FD on the relation R
b. X → Y is not an FD on the relation R
c. Y → Z is an FD on the relation R
d. Y → Z is not an FD on the relation R
4. Consider the SQL query on the Student table:
SELECT FirstName
FROM Student
WHERE FirstName LIKE 'Eve%';
What will it return?
a. All First names start with “Eve” .
b. All four-letter First names start with “Eve” .
c. First name of all students who like Eve.
d. All First names that have “Eve” occurring in them .
5. Consider the SQL query on the Student table with StudentID as Primary Key:
SELECT LastName
FROM Student
WHERE StudentID = (
SELECT StudentID
FROM Student
WHERE FirstName = 'Eve'
);
What does this query assume?
a. It assumes that there is at least one student with the first name “Eve” .
b. It assumes that there is at most one student with the first name “Eve” .
c. It assumes that there is exactly one student with the first name “Eve” .
d. It assumes that all students with the first name “Eve” have the same last name.
6. Assume the table
Student (StudentID, FirstName, LastName, UnitID, WAM)
where WAM stands for Weighted Average Mark.
SELECT UnitID, AVG(WAM)
FROM Student
GROUP BY UnitID
HAVING AVG(WAM) < SOME (
SELECT AVG(WAM)
FROM Student
GROUP BY UnitID
);
Assuming that some (but not all) entries under WAM are null entries, what will the query above do?
a. Give an error message since there is no WHERE clause in the outer query.
b. Display the unit(s) with an average WAM that is not the highest (among unit averages).
c. Give an error message since both the inner and the outer queries have the same GROUP BY clause
d. Give an error message since some rows of the Students table have null entries under WAM.
7. Consider a table Student (SID, Age, Gender, Mark), with possible values of Gender: ‘F’ for Female and ‘M’ for male. Assume that every student in the class is 16+ years old. The query
SELECT AVG(Mark)
FROM Student
WHERE Gender = 'M' AND Age < 20;
will:
a. Display the average mark obtained by male students
b. Display the average mark obtained by teen-age male students
c. Display two average marks: one for male students and another for teen-age students.
d. Display the average mark obtained by all teen-age students since the second condition (on Age) will override the first condition (on Gender).
8. Consider a table Student (SID, Name, Age, Gender) and table LocalStudent (SID, ATAR, HighSchool). The university would like to see a list of all student with their ATAR score, if available.
SELECT s.SID, s.Name, ls.ATAR
FROM ________________;
Which one will be the best option to complete the query above?
a. Student INNER JOIN LocalStudent USING(SID)
b. Student LEFT JOIN LocalStudent USING(SID)
c. Student s LEFT JOIN LocalStudent ls ON s.SID = ls.SID
d. Student s RIGHT JOIN LocalStudent ls ON s.SID = ls.SID
9. Consider a table Student (SID, Age, Gender, Mark) where one of the records is for StudentID 1111. UPDATE Student
SET Mark = 72.3;
The SQL statement above will:
a. update StudentID 1111 in the Student table with the mark of 72.3.
b. update all rows in the Student table with the mark of 72.3.
c. update all rows that do not have a mark (null) in the Student table with the mark of 72.3.
d. not update any rows in the Student table since the syntax is incorrect.
10. A tutor would like to see who are the top five students based on their results. Which query will be suitable for this?
a. SELECT SID, Mark FROM Student LIMIT 5;
b. SELECT Mark FROM Student
ORDER BY Mark DESC
LIMIT 5;
c. SELECT SID, Mark FROM Student
ORDER BY Mark
LIMIT 5;
d. None of the options provided is suitable for the purpose.