1. Create a SELECT statement that will use the CASE switch. Give the empno, enam
ID: 3753359 • Letter: 1
Question
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.
Explanation / Answer
ANSWER 1-
Step 1-
/* Create a table Dept with following columns*/
CREATE TABLE Dept(empno number(10),ename varchar2(50), job varchar2(50),location varchar2(50), deptno number(10));
/*Insert some dummy data */
INSERT INTO DEPT VALUES(1,'Tom', 'Accountant','England', 10);
INSERT INTO DEPT VALUES(2,'Lucy', 'Scientist','USA' , 20);
INSERT INTO DEPT VALUES(3,'Frank','Marketing','France', 30);
INSERT INTO DEPT VALUES(4,'Christopher','Engineer','Norway', 40);
/* Display all the records from the table */
/*SELECT * FROM DEPT;*/
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 'OPERATONS'
END AS DEPT
FROM Dept;
/*Its a switch case where when a condition is met it will show output */
Answer 2-
CREATE TABLE DEPT(empno number(10),ename varchar2(50), job varchar2(50),location varchar2(50), deptno number(10), joining_date date);
INSERT INTO DEPT VALUES(1,'Tom', 'Accountant','England', 10, '03-JAN-1994');
INSERT INTO DEPT VALUES(2,'Lucy', 'Scientist','USA' , 20, '04-FEB-1993');
INSERT INTO DEPT VALUES(3,'Frank','Marketing','France', 30, '05-MAR-1993');
INSERT INTO DEPT VALUES(4,'Christopher','Engineer','Norway', 40, '06-OCT-1993');
/* Display all the records from the table */
/*SELECT * FROM DEPT;*/
SELECT empno, ename, job,location,to_char(joining_date, 'Month-YYYY'),
CASE
WHEN deptno = 10 THEN 'Accounting'
WHEN deptno = 20 THEN 'RESEARCH'
WHEN deptno = 30 THEN 'SALES'
WHEN deptno = 40 THEN 'OPERATONS'
END AS DEPT
FROM DEPT;
//to_char method is used to convert date to (Month-YYYY)
Note -The output of the answers are tested in Oracle 11g
Happy Chegging :)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.