Find out the names and number of years worked along with their department names in descending order by number of years worked.

Question

Use the NamanNavan (N2) Corporation database tables to design the following queries. (Use the spooling method to capture all queries and results in the CHAP7SP1.LST file.)

Answer 1

```
SQL> SELECT LNAME, FNAME, DEPTNAME, (SYSDATE-HIREDATE)/ 365 YEARS
2 FROM EMPLOYEE E, DEPT D
3 WHERE E.DEPTID = D.DEPTID
4 ORDER BY YEARS DESC;

LNAME FNAME DEPTNAME YEARS
--------------- --------------- ---------------- -------------
SMITH JOHN FINANCE 44.2244611
HOUSTON LARRY MARKETING 37.1285706
ROBERTS SANDI FINANCE 12.5724063
DEV DEREK INFOSYS 9.28747476
GARNER STANLEY SALES 8.32583092
MCCALL ALEX INFOSYS 7.13131038
CHEN SUNNY FINANCE 4.86555695
SHAW JINKU SALES 4.47925558

8 ROWS SELECTED.
```