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