This problem set will use the emp and dept tables from the ApEx default schema.

Question

This problem set will use the emp and dept tables from the ApEx default schema.

1. Create a SELECT statement that will use the CASE switch. Give the empno, ename , job along with the location information from the dept table. For instance if deptno is ‘10’ then use the CASE statement to display the string ‘Accounting.’ Do the same for the three other locations in the dept table. Use ‘dept’ as the column alias so you don’t get:

‘CASEDEPTNOWHEN10THEN'ACCOUNTING'WHEN20THEN'RESEARCH'WHEN30THEN'SALES'WHEN40THEN'OPERATIONS'END’     as a column header.

2. Modify the previous query so we get the month and the year someone was hired. Don’t show the day that they were hired. Use the to_char() function along with a format mask to return the month and year that they were hired.

3. Modify the SQL query to show the number of years someone has worked for this company by subtracting hiredate from sysdate. Because hiredate is of the date data type you will have to use single row functions to extract the year value.

P.S. The tables are already created.

Explanation / Answer

If you have any doubts, please give me comment...

-- 1)

SELECT empno, ename, job, location,

CASE

WHEN deptno = 10 THEN 'ACCOUNTING'

WHEN deptno = 20 THEN 'RESEARCH'

WHEN deptno = 30 THEN 'SALES'

WHEN deptno = 40 THEN 'OPERATIONS'

END AS ‘CASEDEPTNOWHEN10THEN'ACCOUNTING'WHEN20THEN'RESEARCH'WHEN30THEN'SALES'WHEN40THEN'OPERATIONS'END’

FROM dept;

-- 2)

SELECT empno, ename, job, location, TO_CHAR(hiredate, "MM/YYYY") AS hireDate,

CASE

WHEN deptno = 10 THEN 'ACCOUNTING'

WHEN deptno = 20 THEN 'RESEARCH'

WHEN deptno = 30 THEN 'SALES'

WHEN deptno = 40 THEN 'OPERATIONS'

END AS ‘CASEDEPTNOWHEN10THEN'ACCOUNTING'WHEN20THEN'RESEARCH'WHEN30THEN'SALES'WHEN40THEN'OPERATIONS'END’

FROM dept;

-- 3)

SELECT empno, ename, job, location, TO_CHAR(hiredate, "MM/YYYY") AS hireDate, DATEDIFF(year, SYSDATE, hiredate) AS yearsWorked,

CASE

WHEN deptno = 10 THEN 'ACCOUNTING'

WHEN deptno = 20 THEN 'RESEARCH'

WHEN deptno = 30 THEN 'SALES'

WHEN deptno = 40 THEN 'OPERATIONS'

END AS ‘CASEDEPTNOWHEN10THEN'ACCOUNTING'WHEN20THEN'RESEARCH'WHEN30THEN'SALES'WHEN40THEN'OPERATIONS'END’

FROM dept;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.