1,105,594 Community Members

SQL Substitution

Member Avatar
iamthesgt
Junior Poster
119 posts since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

This is homework.
Here is the problem: Produce a list of employee last name, first name and department name. Use appropriate, user friendly column aliases.
This is from the tables DEMO.EMPLOYEE and DEMO.DEPARTMENT . The relevant column names are LAST_NAME, FIRST_NAME, DEPARTMENT_ID (from DEMO.EMPLOYEE ) and DEPARTMENT_ID, NAME from DEMO.DEPARTMENT .
As you can see, I need to have the last and first name columns from DEMO.EMPLOYEE and use the department id column to look up the department name in DEMO.DEPARTMENT . I will use the SELECT x AS y command to give the columns good aliases but I can't figure out how to reference the columns to each other. I tried the REPLACE command, but I couldn't get that to work and I couldn't find anything else. Any help would be appreciated.

Member Avatar
hfx642
Posting Pro
515 posts since Nov 2009
Reputation Points: 164 [?]
Q&As Helped to Solve: 105 [?]
Skill Endorsements: 1 [?]
 
0
 

1. If you are logging in as Demo, there is no need to specify "Demo.".
If you are logging into a different schema, then keep the "Demo.".

2. You can use Aliases.

From Department D, Employee E

3. Select your columns...

Select E.Last_Name "Last Name"... etc.

4. Join the appropriate columns with the Where clause...

Where D.ID = E.ID

That should get you going.
No Subtitution required.

Member Avatar
iamthesgt
Junior Poster
119 posts since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

It doesn't work quite right; I have the tables Employee, which contains the collumns LAST_NAME, FIRST_NAME, DEPARTMENT_ID and Department, which contains the collumns DEPARTMENT_ID, NAME . I want it to output a table with collumns LAST_NAME, FIRST_NAME, NAME , so that each person has the name of their department in collumn NAME. However, when I use the code:

SELECT EMPLOYEE.LAST_NAME "Last Name", EMPLOYEE.FIRST_NAME "First Name", EMPLOYEE.DEPARTMENT_ID, DEPARTMENT.DEPARTMENT_ID, DEPARTMENT.NAME "Department"
FROM DEMO.EMPLOYEE, DEMO.DEPARTMENT;
WHERE EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID
ORDER BY LAST_NAME

It outputs a list of all employees for each possible value of "NAME", i.e.

Last Name       First Name      DEPARTMENT_ID      DEPARTMENT_ID         Department
Smith           John            20                 10                    ACCOUNTING
Smith           John            20                 20                    RESEARCH
Smith           John            20                 30                    SALES

And so on for each employee and each department. Can you help?

Member Avatar
debasisdas
Posting Genius
6,542 posts since Feb 2007
Reputation Points: 580 [?]
Q&As Helped to Solve: 476 [?]
Skill Endorsements: 25 [?]
Featured
 
0
 

The 3rd and 4th column of your output clearly shows, this output is not of the query that you have posted here. Please recheck.

Member Avatar
iamthesgt
Junior Poster
119 posts since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Output:

Last Name 	First Name 	DEPARTMENT_ID 	DEPARTMENT_ID 	Department
SMITH 	JOHN 	20 	10 	ACCOUNTING
ALLEN 	KEVIN 	30 	10 	ACCOUNTING
DOYLE 	JEAN 	13 	10 	ACCOUNTING
DENNIS 	LYNN 	23 	10 	ACCOUNTING
BAKER 	LESLIE 	14 	10 	ACCOUNTING
WARD 	CYNTHIA 30 	10 	ACCOUNTING
PETERS 	DANIEL 	13 	10 	ACCOUNTING
SHAW 	KAREN 	13 	10 	ACCOUNTING
DUNCAN 	SARAH 	23 	10 	ACCOUNTING
LANGE 	GREGORY 23 	10 	ACCOUNTING
JONES 	TERRY 	20 	10 	ACCOUNTING
ALBERTS CHRIS 	12 	10 	ACCOUNTING
PORTER 	RAYMOND 13 	10 	ACCOUNTING

...and repeats for every department with the same people (goes on for pages).

Member Avatar
hfx642
Posting Pro
515 posts since Nov 2009
Reputation Points: 164 [?]
Q&As Helped to Solve: 105 [?]
Skill Endorsements: 1 [?]
 
0
 

There is a Semicolon at the end of your FROM clause.
END OF STATEMENT!! ie. No WHERE clause.
You probably get some errors following it as well, because of the WHERE.
Remove the Semicolon and rerun.

Member Avatar
iamthesgt
Junior Poster
119 posts since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Wow, I didn't realize that's what the semicolon did. Thanks for the help. The final statement:

SELECT EMPLOYEE.LAST_NAME "Last Name", EMPLOYEE.FIRST_NAME "First Name", DEPARTMENT.NAME "Department"
FROM DEMO.EMPLOYEE, DEMO.DEPARTMENT
WHERE EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID
ORDER BY LAST_NAME
Question Answered as of 2 Years Ago by hfx642 and debasisdas
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: