NEW
Font size
WorksheetsDAY 23-MySQL: Subqueries & Stored Procedures 10th July 24
Total questions: 10
Worksheet time: 5mins
Which query retrieves the names of students who have enrolled in 'Mathematics' using a subquery?
SELECT name FROM students WHERE course = 'Mathematics';
SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments
WHERE course = 'Mathematics');
SELECT name FROM students WHERE id = (SELECT student_id FROM enrollments
WHERE course = 'Mathematics');
SELECT name FROM students WHERE course_id = (SELECT id FROM courses WHERE
name = 'Mathematics');
Which query creates a stored procedure named 'add_student' to insert a new student record?
CREATE PROCEDURE add_student (IN id INT, IN name VARCHAR(100), IN age INT)
BEGIN INSERT INTO students (id, name, age) VALUES (id, name, age); END;
CREATE PROCEDURE add_student (id INT, name VARCHAR(100), age INT) INSERT
INTO students (id, name, age) VALUES (id, name, age);
CREATE PROCEDURE add_student (IN id INT, name VARCHAR(100), age INT) INSERT
INTO students (id, name, age) VALUES (id, name, age);
CREATE PROCEDURE add_student (IN id INT, IN name VARCHAR(100), IN age INT)
INSERT INTO students (id, name, age) VALUES (id, name, age);
Which query uses a subquery to find the average age of students and retrieves names of students older than this average age?
SELECT name FROM students WHERE age > (SELECT AVG(age) FROM students);
SELECT name FROM students WHERE age >AVG(age);
SELECT name FROM students WHERE age >(SELECT AVERAGE(age) FROM students);
SELECT name FROM students WHERE age >(SELECT MEAN(age) FROM students);
Which query creates a stored procedure named 'get_student' to retrieve a student's details by their ID?
CREATE PROCEDURE get_student (IN student_id INT) SELECT * FROM students WHERE
id = student_id;
CREATE PROCEDURE get_student (IN student_id INT) BEGIN SELECT * FROM students
WHERE id = student_id; END;
CREATE PROCEDURE get_student (student_id INT) BEGIN SELECT * FROM students
WHERE id = student_id; END;
CREATE PROCEDURE get_student (IN student_id INT) SELECT * FROM students WHERE
id = student_id; END;
Which query uses a subquery to retrieve the highest age from the 'students' table and displays it?
SELECT MAX(age) FROM students;
SELECT age FROM students WHERE age = (SELECT MAX(age) FROM students);
SELECT age FROM students WHERE age IN (SELECT MAX(age) FROM students);
SELECT MAX(age) FROM (SELECT age FROM students);
Which query creates a stored procedure named 'delete_student' to delete a student record by their ID?
CREATE PROCEDURE delete_student (student_id INT) DELETE FROM students WHERE
id = student_id;
CREATE PROCEDURE delete_student (IN student_id INT) DELETE FROM students
WHERE id = student_id;
CREATE PROCEDURE delete_student (IN student_id INT) BEGIN DELETE FROM students
WHERE id = student_id; END;
CREATE PROCEDURE delete_student (IN student_id INT) BEGIN DELETE FROM students
WHERE id = student_id;
Which query uses a subquery to find all students enrolled in courses with 'Science' in their name?
SELECT name FROM students WHERE course LIKE '%Science%';
SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments
WHERE course_id IN (SELECT id FROM courses WHERE name LIKE '%Science%'));
SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments
WHERE course_id = (SELECT id FROM courses WHERE name LIKE '%Science'));
SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments
WHERE course_name LIKE '%Science%');
Which query creates a stored procedure named 'update_student_age' to update a student's age by their ID?
CREATE PROCEDURE update_student_age (student_id INT, new_age INT) UPDATE
students SET age = new_age WHERE id = student_id;
CREATE PROCEDURE update_student_age (IN student_id INT, IN new_age INT) UPDATE
students SET age = new_age WHERE id = student_id;
CREATE PROCEDURE update_student_age (IN student_id INT, IN new_age INT) BEGIN
UPDATE students SET age = new_age WHERE id = student_id; END;
CREATE PROCEDURE update_student_age (IN student_id INT, new_age INT) BEGIN
UPDATE students SET age = new_age WHERE id = student_id; END;
Which query uses a subquery to find students who are not enrolled in any course?
SELECT name FROM students WHERE id NOT IN (SELECT student_id FROM
enrollments);
SELECT name FROM students WHERE id NOT IN (SELECT id FROM enrollments);
SELECT name FROM students WHERE id NOT IN (SELECT student_id FROM courses);
SELECT name FROM students WHERE id NOT IN (SELECT course_id FROM enrollments);
Which query creates a stored procedure named 'get_course_students' to retrieve students enrolled in a specific course by course ID?
CREATE PROCEDURE get_course_students (course_id INT) SELECT name FROM
students WHERE id IN (SELECT student_id FROM enrollments WHERE course_id =
course_id);
CREATE PROCEDURE get_course_students (IN course_id INT) BEGIN SELECT name
FROM students WHERE id IN (SELECT student_id FROM enrollments WHERE course_id =
course_id); END;
CREATE PROCEDURE get_course_students (IN course_id INT) SELECT name FROM
students WHERE id IN (SELECT student_id FROM enrollments WHERE course_id =
course_id);
CREATE PROCEDURE get_course_students (IN course_id INT) BEGIN SELECT name
FROM students WHERE id IN (SELECT student_id FROM enrollments WHERE course_id =
course_id);
