• Hey, guest user. Hope you're enjoying NeoGAF! Have you considered registering for an account? Come join us and add your take to the daily discourse.

Oracle SQL age

Status
Not open for further replies.

unomas

Banned
I'm stuck on question 2 for a homework assignment, I've asked my professor who gave an example on how to do it and some classmates but still no luck. For some reason subqueries just don't register with me at all and we have to use a subquery to show this.. Any thoughts SQL GAF?



Use a subquery to display the employee identification number and employee name of all employees of the production department.


SELECT EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_ID, DEPARTMENT_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_NAME IN (SELECT DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = ‘PRODUCTION’);

And I get the error "quoted string not properly terminated". My question is am I even somewhat close here?

The tables are EMPLOYEES and DEPARTMENTS. EMPLOYEES contains the employee_id, employee_name, while the DEPARTMENTS table contains department_id and department_name.
 

Tabris

Member
EDIT - Nevermind. I read it more closely.

You need something to join the tables on. That's a horrible schema if there is no link / foreign key to the other table. Usually you would put department_id column in the employees table as a foreign key.
 

unomas

Banned
EDIT - Nevermind. I read it more closely.

You need something to join the tables on. That's a horrible schema if there is no link / foreign key to the other table. Usually you would put department_id column in the employees table as a foreign key.

Sorry, the employees table does have a department_id column. I'm just not sure how to join these tables/columns via subquery
 

Tabris

Member
Oh, then just do this.

SELECT E.Employee_ID, E.Employee_Name
FROM Employees E
WHERE E.Department_ID IN
(SELECT Department_ID FROM Departments
WHERE Department_Name = 'Production')

Usually I would just write it as a join but your teacher wants you to do it as a subquery.
 

ultron87

Member
Presumably you don't want to use a join since the assignment specifically says to use a subquery.

You'll want to have the subquery grab the DepartmentID from the other table. And you can't select Department_ID or Department_Name from the main query and you shouldn't need to since that isn't what the assignment asks for.
 

unomas

Banned
Oh, then just do this.

SELECT E.Employee_ID, E.Employee_Name
FROM Employee E
WHERE E.Department_ID IN
(SELECT Department_ID FROM Departments
WHERE Department_Name = 'Production')

Usually I would just write it as a join but your teacher wants you to do it as a subquery.

Thanks, the first question of the assignment is do it as a join, the second question is to do the same exact thing as a subquery, I already did the join successfully and easily, but for some reason have been stuck on the subquery for the last 5 hours.......sad but true.

He also said it would be easier without using aliases, but you're definitely using aliases above which is fine by me. Thanks for your help, I've been banging my head against a wall for hours here.
 

unomas

Banned
Presumably you don't want to use a join since the assignment specifically says to use a subquery.

You'll want to have the subquery grab the DepartmentID from the other table. And you can't select Department_ID or Department_Name from the main query and you shouldn't need to since that isn't what the assignment asks for.

Correct, this is week 4 of the class and unfortunately I've had a rough time of things this week with some serious health issues, but our assignment is due tomorrow and I've been stuck on this second problem forever. I'll try to work through it on my own, thanks for the thoughts.
 

Tabris

Member
5 hours? Woah man. Think of it like this. You can only grab columns from the tables within the FROM (and all joins from that from). You were trying to grab a column that didn't exist in Employees and then do the subquery based on that column. The only thing in Employees that matched with Departments was the id, so you want to do your subquery based on that.
 

unomas

Banned
5 hours? Woah man. Think of it like this. You can only grab columns from the tables within the FROM (and all joins from that from). You were trying to grab a column that didn't exist in Employees and then do the subquery based on that column. The only thing in Employees that matched with Departments was the id, so you want to do your subquery based on that.

Thanks, for your help, I really appreciate you taking the time to explain it as well. Now I realize why I was making the mistakes in the first place. Thanks
 
Status
Not open for further replies.
Top Bottom