Question

Consider the following schema for a database where the primary keys are underlined:

Course (courseID, course_name, college_name)
Enroll (studentiD, courseID, status)
Write a single SQL query for each one of the following questions.
i. Find the course name and studentID of all the students who are enrolled in at least one course offered by "PTI college".
ii. Find the list of student IDs of all the courses for which the status is "enrolled".

Answer 1

SQL queries are crafted to extract the course names and student IDs for students enrolled in 'PTI college' courses, and another to list student IDs for courses where the enrollment status is 'enrolled'.

Step-by-step explanation:

To answer the given database-related questions, we can create SQL queries to fetch the required information from the tables provided in the schema. The queries address the specifics of finding course names and student IDs for a particular college and listing student IDs with a specific enrollment status.

i. Find the course name and studentID for all students enrolled in courses offered by "PTI college"

SQL Query:

SELECT Course.course_name, Enroll.studentiD FROM Course JOIN Enroll ON Course.courseID = Enroll.courseID WHERE Course.college_name = 'PTI college';

ii. Find the list of student IDs for all courses where the status is "enrolled"

SQL Query:

SELECT studentiD FROM Enroll WHERE status = 'enrolled';