Question

This is a database with two tables relating to students at a school. Each student has a unique ID. There is a backlog table that maintains a record of active backlogs for each student.

Write a query my oracle sql to print the names of the students who have at least one active backlog. The names should be printed in ascending order.The results should be in the following format: NAME

Note: There could be students with the same name but they would have different IDs.

Schema

Student Table

Name	Type	Description
ID	INteger	This is the student's ID. Also Primary key
Name	String	This is the name of the student

Backlog Table

Name	Type	Description
Student_id	Integer	This is the student's ID
subject_ID	String	This is the subject ID.

no complicated answer explanation, just working query.

Answer 1

Let’s consider below sample data for tables:

Student:

ID	Name
1	Adam
2	Denial
3	Recklis
4	Adam

Backlog:

Student_ID	Subject_ID
1	DBMS
1	CSO
2	DBMS
4	DBMS

Required Query: Please find below the query to find the required data:

select s.Name as 'NAME' from Backlog b left join Student s on b.Student_ID = s.ID group by s.ID

Result: It will show the below data:

**As per instructions, a student can have same Name, thus the query will return same name based on ID

NAME

Adam

Adam

Denial

Query Description: The query will join two tables based on Student -> ID and Backlog -> Student_ID.

Query will traverse all records in Backlog table and find the corresponding Name in Student -> Name column.

Group by ID is used as there can be many columns which will have same student ID i.e. ID 1 has 2 backlogs and result will show Adam twice for ID 1. Group By ID will remove the duplicates for same student and return only single record for all students.

Order By s.Name will sort the names in ascending order.