DAY 23-MySQL: Subqueries & Stored Procedures 10th July 24

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);