The HR department needs a report that displays the last name, department number,
ID: 3700032 • Letter: T
Question
The HR department needs a report that displays the last name, department number, and job ID of all employees whose department location ID is 1800 (NOTE: the location ID is a column attribute from the DEPARTMENTS table, not the EMPLOYEES table). Write this SQL statement as a subquery.
A fellow Database professional is attempting to create a report for HR that displays the last name and salary of every employee who reports to Steven King (in other words each employee who is assigned the manager_id that corresponds to the manager whose last name is ‘King’. The database professional created the SQL statement below, but unfortunately received the error following the code.
SELECT last_name, salary
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE last_name = 'King');
ERROR RESULT:
A. Why did the database professional receive that error result?
B. How would you correct the code (write and run your corrected code)?
Create a report for HR that displays the department number, last name, and job ID for every employee in the Executive department (HINT: would you use an = or IN ?).
ERROR RESULT: 2 E SQL | Executing:SELECT last_name, salary FROM ORA-01427: single-row subquery returns more than one row 01427.00000 - "single-row subquery returns more than one row" *Cause: *Action:Explanation / Answer
Dear student,
PFB answer below,
The database professional is receiving that error because the subquery is returning more than one result.
If you consider the below query,
SELECT last_name, salary
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE last_name = 'King');
Where condition will need some condition to compare which we are providing in the subquery but here the subquery is returning more than one result so where clause gets confused as with which condition to compare, and so it is giving error. You can fix it as below,
Select only the top 1 result so in subquery you will get only one result and where will compare.
SELECT last_name, salary
FROM employees
WHERE manager_id = (SELECT Top 1 employee_id
FROM employees
WHERE last_name = 'King');
Else you can add some more condition to where clause in subquery.
--------------------------------------------------------------------------------------------------------------------------
SELECT department number, last name, job ID
FROM department
WHERE dept_name = (SELECT Top 1 dept_name
FROM department
WHERE dept_name = ‘Executive department’);
As you have not provided the schema, so it was tough to check. But I have written the queries by assuming the structure. Please comment for any doubts.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.