Question

Considering the schema above,
Which of the following finds the first name and last name10+ of all employees who are professors with the title as 'assistant professor' and first names starting with the letter W.
A. SELECT fname, lname FROM employee WHERE fname LIKE 'W%' AND ssn IN ( SELECT ssn FROM professor WHERE title = 'assistant professor' );
B. SELECT fname, lname FROM employee WHERE fname LIKE 'W%' AND ssn = (SELECT ssn FROM professor WHERE title = 'assistant professor');
C. SELECT fname, lname FROM employee INNER JOIN professor ON employee.ssn = professor.ssn WHERE fname LIKE "W%" AND title = 'assistant professor';
D. SELECT fname, lname FROM employee WHERE fname LIKE 'W%' AND ssn = (SELECT ssn FROM employee WHERE title = 'assistant professor');
E. All of these
F. None of these

Answer 1

Answer:

Option a is the correct answer for the above question.

Explanation:

• The above question wants a query that gives the output of the first name and the last name of the employee who has the title as an assistant professor and the name starts with w.
• Then option a is the correct answer which operates like:-

1. Firstly the subquery will execute and select the ssn from the professor table which has the title as an assistant professor. It is because the ssn is used as the foreign key in the employee table which needs to find that employee which is the assistant professor.
2. Then It fetches the record of the employee table whose name starts with the w and match with the SSN.

• But the other option is not valid because:-

1. Options b and d gives the error because it holds equal to symbols with the subquery.
2. Option c gives the result of other information.
3. Option E states about all option but only a is the correct option.
4. Option F states none of the above option but a is the correct option.