TK Study
HomeMemoriseFlashcardsNotesMaster answersPast questionsMultiple choiceMock examPractice & marker

Multiple choice › SQL

SQL

1. SQL

Tables. Students(StudentID, Name, Year, House): (1,Ava,12,Red) (2,Ben,11,Blue) (3,Cleo,12,Blue) (4,Dan,12,Red) (5,Eli,11,Red). Scores(StudentID, Subject, Mark): (1,SE,80) (1,Maths,70) (2,SE,60) (3,SE,90) (4,SE,50) (5,Maths,75).

What does this query return?

SELECT Name FROM Students WHERE Year = 12 AND House = 'Red';

  1. Ben, Eli
  2. Ava
  3. Ava, Dan
  4. Ava, Cleo, Dan
Answer

C

Year 12 AND House Red: Ava (12, Red) and Dan (12, Red). Cleo is Blue.

2. SQL

Tables. Students(StudentID, Name, Year, House): (1,Ava,12,Red) (2,Ben,11,Blue) (3,Cleo,12,Blue) (4,Dan,12,Red) (5,Eli,11,Red). Scores(StudentID, Subject, Mark): (1,SE,80) (1,Maths,70) (2,SE,60) (3,SE,90) (4,SE,50) (5,Maths,75).

What is the first row returned by this query?

SELECT Name FROM Students WHERE Year = 12 ORDER BY Name DESC;

  1. Cleo
  2. Dan
  3. Eli
  4. Ava
Answer

B

Year 12 students are Ava, Cleo and Dan. Sorted DESC (Z to A) the first is Dan.

3. SQL

Tables. Students(StudentID, Name, Year, House): (1,Ava,12,Red) (2,Ben,11,Blue) (3,Cleo,12,Blue) (4,Dan,12,Red) (5,Eli,11,Red). Scores(StudentID, Subject, Mark): (1,SE,80) (1,Maths,70) (2,SE,60) (3,SE,90) (4,SE,50) (5,Maths,75).

What does this query return?

SELECT House, COUNT(*) FROM Students GROUP BY House ORDER BY House;

  1. Blue 2, Red 3
  2. Blue 3, Red 2
  3. Red 5
  4. Blue 1, Red 1
Answer

A

GROUP BY House counts students per house: Blue (Ben, Cleo) = 2, Red (Ava, Dan, Eli) = 3.

4. SQL

Tables. Students(StudentID, Name, Year, House): (1,Ava,12,Red) (2,Ben,11,Blue) (3,Cleo,12,Blue) (4,Dan,12,Red) (5,Eli,11,Red). Scores(StudentID, Subject, Mark): (1,SE,80) (1,Maths,70) (2,SE,60) (3,SE,90) (4,SE,50) (5,Maths,75).

What does this query return?

SELECT Students.Name, Scores.Mark FROM Students, Scores WHERE Students.StudentID = Scores.StudentID AND Scores.Subject = 'SE' AND Scores.Mark >= 80 ORDER BY Students.Name;

  1. Ava 80
  2. Ava 80, Ava 70, Cleo 90
  3. Cleo 90, Dan 50
  4. Ava 80, Cleo 90
Answer

D

The join links students to scores. SE marks of at least 80 belong to Ava (80) and Cleo (90).

5. SQL

Tables. Students(StudentID, Name, Year, House): (1,Ava,12,Red) (2,Ben,11,Blue) (3,Cleo,12,Blue) (4,Dan,12,Red) (5,Eli,11,Red). Scores(StudentID, Subject, Mark): (1,SE,80) (1,Maths,70) (2,SE,60) (3,SE,90) (4,SE,50) (5,Maths,75).

What does this query return?

SELECT AVG(Mark) FROM Scores WHERE Subject = 'SE';

  1. 68.75
  2. 80
  3. 75
  4. 70
Answer

D

SE marks are 80, 60, 90, 50 = 280, over 4 rows = 70.