This question covers Sections and 2 of Block 3. It assesses your ability to interpret and to write simple SQL queries (see Section 2 of Block 3). This question wes the University nabase (a) For each of the following give a request for problem statement, see page 12 of Block 3) that the query can be used to answer. For each of the following requests (or problem statements) Write a simple query (see Section 2 of Block 3) that will answer that request. All of the queries in this question can be written without using composite queries. In your answer, include a copy of the result of evaluating your query SELECT student id, address FROM student (i) Give the name and staff number of each staff member who has a Contract with region SELECT region_number, COUNT/staff_number) AS how_many FROM staff GROUP BY region number HAVING COUNT(staff_number) >= 2 (ii) For each course where at least one student has sat the examination for the course, give the course code and highest examination mark for that course. (ii) Give the staff number and staff menber's name for each staff member who tutors at least one studentenrolled in a 30 point course. SELECT assignment.", staff.staff_number, staff.name FROM staff, assignment, tutors WHERE staff.stall number = tutors staff number AND assignment student tutors.student id AND assignment course oodetutors.course code (iv) For each course that bas a quota limit, give the course code and the number of students enrolled on that course. (Note: Your solution should include those courses that have a quota limit, but that do not have any students.) (iv) SELECT student student id, student.name, COUNTexamination.course_code) AS how many FROM student, examination, assignment Includes, exam Includes WHERE student student id = examination student id AND student student_id = assignment includes.student_id AND student.student id exam includes.student id AND examination.course code assignment includes course_code AND cxamination course code exam includes course_code GROUP BY student student ld, student.name This question covers Sections and 2 of Block 3. It assesses your ability to interpret and to write simple SQL queries (see Section 2 of Block 3). This question wes the University nabase (a) For each of the following give a request for problem statement, see page 12 of Block 3) that the query can be used to answer. For each of the following requests (or problem statements) Write a simple query (see Section 2 of Block 3) that will answer that request. All of the queries in this question can be written without using composite queries. In your answer, include a copy of the result of evaluating your query SELECT student id, address FROM student (i) Give the name and staff number of each staff member who has a Contract with region SELECT region_number, COUNT/staff_number) AS how_many FROM staff GROUP BY region number HAVING COUNT(staff_number) >= 2 (ii) For each course where at least one student has sat the examination for the course, give the course code and highest examination mark for that course. (ii) Give the staff number and staff menber's name for each staff member who tutors at least one studentenrolled in a 30 point course. SELECT assignment.", staff.staff_number, staff.name FROM staff, assignment, tutors WHERE staff.stall number = tutors staff number AND assignment student tutors.student id AND assignment course oodetutors.course code (iv) For each course that bas a quota limit, give the course code and the number of students enrolled on that course. (Note: Your solution should include those courses that have a quota limit, but that do not have any students.) (iv) SELECT student student id, student.name, COUNTexamination.course_code) AS how many FROM student, examination, assignment Includes, exam Includes WHERE student student id = examination student id AND student student_id = assignment includes.student_id AND student.student id exam includes.student id AND examination.course code assignment includes course_code AND cxamination course code exam includes course_code GROUP BY student student ld, student.name
