We\'re working with substitution variables this chapter in my Advanced Databases

Question

We're working with substitution variables this chapter in my Advanced Databases class. We're using SQL Oracle. On the homework, this question came up and I'm stumped to why it's not working:

"Write a script file to display the employee name and salary for all employees whose salary is between a given low and high salary. Prompt the user for the two salaries. Concatenate the name and job together, separated by a space and a comma, and order by the employee’s last name. "

Here is my code:

SELECT last_name || ', ' || job_ID AS "Employees", salary AS "Salary"
FROM employees
WHERE salary BETWEEN '&Salary1' and '&Salary2'
ORDER BY last_name;

The code runs and it prompts me for each salary but when I test it out, it always returns with zero rows. What am I doing wrong?

Explanation / Answer

Use the below modified sql statement:

SELECT last_name || ', ' || job_ID AS "Employees", salary AS "Salary"
FROM employees
WHERE salary BETWEEN &Salary1 and &Salary2

ORDER BY last_name;

Explanation:

To read number input from user , use only & (ampersand )
so remove quotation for &Salary1 and &Salary.
Enclosed quotations are used to read text input.
So remove single quotations for Salary1 and Salary and
use only &Salary1 and &Salary2

SELECT last_name || ', ' || job_ID AS "Employees", salary AS "Salary"
FROM employees
WHERE salary BETWEEN &Salary1 and &Salary2

ORDER BY last_name;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.