Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Exercise:
- 1. Write a query to display the current date. Label the column Date.
- -> SELECT SYSDATE As "Date" FROM dual;
- 2. Display the employee number, name, salary, and salary increase by 15% expressed as a whole number. Label the column New Salary.
- -> SELECT SYSDATE As "Date" FROM dual;
- 3. Modify your previous query to add a column that will subtract the old salary from the new
- salary. Label the column Increase. Rerun your query.
- -> SELECT empno, ename, sal, TRUNC(sal*1.15) As "New Salary", (TRUNC(sal*1.15)-sal) As "Increase" FROM emp;
- 4. Display the employee’s name, hire date, and salary review date, which is the first Monday after
- six months of service. Label the column REVIEW. Format the dates to appear in the format
- similar to “Sunday, the 7th of September, 1981.”
- -> SELECT ename, hiredate, TO_CHAR(NEXT_DAY(ADD_MONTHS(hiredate, 6), 'Monday'), 'Day, "the"
- fmDdth "of" Month, YYYY"."') As "REVIEW" FROM emp;
- 5. For each employee display the employee name and calculate the number of months between
- today and the date the employee was hired. Label the column MONTHS_WORKED. Order your
- results by the number of months employed.
- -> SELECT ename, ROUND(MONTHS_BETWEEN(SYSDATE, hiredate), 0) As "MONTHS_WORKED" FROM
- emp ORDER By MONTHS_BETWEEN(SYSDATE, hiredate) DESC;
- 6. Write a query that produces the following for each employee: <employee name> earns <salary>
- monthly but wants <3 times salary>. Label the column Dream Salaries.
- -> SELECT ename||' earns '||sal||' monthly but wants '||3*sal As "Dream Salaries" FROM emp;
- 7. Write a query that will display the employee’s name with the first letter capitalized and all other
- letters lowercase and the length of their name, for all employees whose name starts with J, A, or
- M. Give each column an appropriate label.
- -> SELECT INITCAP(ename), LENGTH(ename) FROM emp WHERE ename LIKE 'J%' OR ename LIKE 'A%' OR
- ename LIKE 'M%';
- 8. Create a query that will display the employee name and commission amount. If the employee
- does not earn commission, put “No Commission.” Label the column COMM.
- -> SELECT ename, NVL(TO_CHAR(comm), 'No Commission') As "COMM" FROM emp;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement