SQL Substitution

[iamthesgt]

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.

[hfx642]

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.

[iamthesgt]

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?

[debasisdas]

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

[iamthesgt]

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).

[hfx642]

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.

[iamthesgt]

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