fueanta

Query Practice 01

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