Database Query Practice

1. Which SQL statement retrieves first name, last name, and address of students sorted by address?

• SELECT * FROM students ORDER BY first_name;

• SELECT first_name, last_name FROM students;

• SELECT address FROM students WHERE last_name = 'Smith';

• SELECT first_name, last_name, address FROM students ORDER BY address;

2. How do you list instructors' first name, last name, and designation sorted by last name in descending order?

• SELECT last_name, first_name, designation FROM instructors ORDER BY first_name ASC.

• SELECT first_name, last_name, designation FROM instructors ORDER BY last_name DESC.

• SELECT first_name, last_name, designation FROM instructors ORDER BY designation DESC.

• SELECT first_name, last_name FROM instructors ORDER BY last_name;

3. What SQL command would you use to find the most expensive course?

• SELECT * FROM courses ORDER BY price DESC LIMIT 1;

• SELECT * FROM courses ORDER BY price ASC LIMIT 1;

• SELECT course_name FROM courses WHERE price = MAX(price);

• SELECT * FROM courses WHERE price > 1000;

4. Which query would return the total number of students enrolled in each course?

• SELECT course_id, COUNT(student_id) AS total_students FROM enrollments GROUP BY course_id;

• SELECT course_id FROM enrollments GROUP BY student_id;

• SELECT COUNT(*) FROM enrollments;

• SELECT student_id, course_id FROM enrollments;

5. How can you select all courses that have a duration greater than 30 days?

• SELECT * FROM courses WHERE duration < 30;

• SELECT * FROM courses WHERE duration = 30;

• SELECT * FROM courses WHERE duration >= 30;

• SELECT * FROM courses WHERE duration > 30;

6. What SQL statement lists all instructors who teach a specific course?

• SELECT * FROM instructors WHERE course_name = 'specific_course_name';

• SELECT instructors.name FROM instructors LEFT JOIN courses ON instructors.id = courses.instructor_id;

• SELECT instructors.name FROM instructors JOIN courses ON instructors.id = courses.instructor_id WHERE courses.course_name = 'specific_course_name';

• SELECT instructors.name FROM courses WHERE instructor_id = instructors.id;

7. Which query retrieves the average grade of students in a particular course?

• SELECT COUNT(grade) FROM grades WHERE course_id = 'specific_course_id';

• SELECT SUM(grade) FROM grades WHERE course_id = 'specific_course_id';

• SELECT AVG(grade) FROM grades WHERE course_id = 'specific_course_id';

• SELECT MAX(grade) FROM grades WHERE course_id = 'specific_course_id';

8. How do you find the students who have not enrolled in any courses?

• Count the total number of students enrolled in courses.

• List all courses and their enrolled students.

• Select students with no corresponding entries in the enrollment table.

• Select students who have enrolled in at least one course.

9. What SQL command would you use to update the address of a specific student?

• INSERT INTO students SET address = 'new address' WHERE student_id = 'specific_id';

• UPDATE students SET address = 'new address' WHERE student_id = 'specific_id';

• DELETE FROM students WHERE student_id = 'specific_id';

• SELECT address FROM students WHERE student_id = 'specific_id';

10. Which query lists all courses along with the number of students enrolled in each?

• SELECT course_name, SUM(enrollments.student_id) FROM courses GROUP BY course_name;

• SELECT courses.course_name FROM courses WHERE enrollments.student_id IS NOT NULL;

• SELECT courses.course_name, enrollments.student_id FROM courses JOIN enrollments ON courses.id = enrollments.course_id;

• SELECT courses.course_name, COUNT(enrollments.student_id) AS number_of_students FROM courses LEFT JOIN enrollments ON courses.id = enrollments.course_id GROUP BY courses.id;