These eight original questions cover normalising, joins, simple totals and integrity. Try each before opening the answer.
This set belongs to database development. A teacher can go through your working in the one-hour trial class (from RM50), a taught lesson on the Computer Science topic you choose.
Questions
Use these tables. Student: (1, Aina, 1), (2, Bala, 2), (3, Chen, 1), (4, Dina, 2) with columns StudentID, Name, HouseID. House: (1, Merah), (2, Biru), (3, Hijau) with columns HouseID, HouseName.
Question 1. How many rows does SELECT Student.Name, House.HouseName FROM Student INNER JOIN House ON Student.HouseID = House.HouseID; return?
Answer
Four rows, one per student. Each student matches one house. Hijau has no students, so it does not appear.
Question 2. How many rows would the same query return if the ON line were left out?
Answer
4 students × 3 houses = 12 rows. Every student pairs with every house, which is not a meaningful result.
Question 3. Write a query to show the names of students in house 1.
Answer
SELECT Name
FROM Student
WHERE HouseID = 1;The output is Aina and Chen.
Question 4. What does SELECT COUNT(*) FROM Student WHERE HouseID = 2; return?
Answer
2. Bala and Dina are in house 2, so two rows pass the filter.
Question 5. An Orders table has columns OrderID, Customer and Item1, Item2. Name the normalisation problem.
Answer
Item1 and Item2 form a repeating group. Move items to a separate table with one row per order and item.
Question 6. Judge: insert a student with HouseID 5. Allowed or not?
Answer
Not allowed. House 5 does not exist in House, so the student’s foreign key would point to nothing.
Question 7. Judge: delete the House row for house 1 while Aina and Chen belong to it.
Answer
Not allowed. Two students still refer to house 1. Move or remove them first, then delete the house.
Question 8. Judge: insert a new house, HouseID 4, Kuning, with no students.
Answer
Allowed. A new parent row with no children breaks no rule.
If you got these wrong
For question 5, review normalising a small data set. For questions 1 to 3, read joining related tables.
For question 4, see using aggregation and filtering. For questions 6 to 8, use checking referential integrity.
For help repairing a weak step, see online one-to-one Computer Science tuition.