QN.
A university has started a student-advisor plan which assigns a professor as an advisor to each student for academic guidance. Write a query to find the roll number and names of students who either have a male advisor with a salary of more than 15,000 or a female advisor with a salary of more than 20,000.
There are two tables in the database: student information and faculty_information. The primary key of student_ information is roll_number whereas that of faculty_information is employee_ID.
Solution:
select std.roll_number, std.name
from student_information std, faculty_information fi
where std.advisor = fi.employee_id and (fi.gender = 'M' and fi.salary > 15000 or fi.gender = 'F' and fi.salary > 20000).
Explanation----->
1. SELECT std.roll_number, std.name: This part selects the roll number and name columns from the student_information table.
2. FROM student_information std, faculty_information fi: This part specifies the tables involved in the query. It uses a comma-separated list of tables, which is an older, implicit join syntax.
3. WHERE std.advisor = fi.employee_id: This part specifies the join condition between the student_information and faculty_information tables. It states that the advisor column in the student_information table should match the employee_id column in the faculty_information table.
4. (fi.gender = 'M' AND fi.salary > 15000 OR fi.gender = 'F' AND fi.salary > 20000): This part is the filter condition in the WHERE clause. It checks if the advisor's gender is male and the salary is greater than 15000, or if the advisor's gender is female and the salary is greater than 20000.
Hacker Rank basic skill Student Advisor Solution
