SQL Assignment 2

SQL ASSIGNMENT PART-2

Display the first name and last name of all employees in uppercase.

code: Select upper(first_name), upper(last_name) from employees;

Display the first name of all employees in lowercase.

code: Select lower(first_name) from employees;

Display the first name and last name using INITCAP (capitalize first letter only).

code: Select initcap(first_name), initcap(last_name) from employees;

Display the first name and its length.

code: Select first_name, length(first_name) from employees;

5. Display the first name and show only the first three characters

code: Select first_name, substr(first_name, 1, 4) from employees;

Display the first name and the position of the letter ‘a’ in it

code:

7. Display the first name and replace all occurrences of ‘a’ with ‘@’

code: Select first_name, replace(first_name, 'a','@') from employees;

8. Display the first name right-padded with * up to 15 characters.

code: Select first_name, rpad(first_name, 15, '*') from employees;

11. Display the first name left-padded with # up to 20 characters.

code: Select first_name, lpad(first_name, 20, '#') from employees;

Display the first name and last name concatenated as Full_Name.

code: Select first_name, last_name, first_name || ' ' || last_name as Full_Name from employees;

13. Display the employee name, salary, and the rounded salary to the nearest thousand

code: Select first_name, salary, round(salary) from employees;

14. Display the employee name, salary, and the truncated salary to the nearest hundred.

code: Select first_name, salary, trunc(salary, -2P from employees;

15. Display the employee name and salary raised to power 2.

code: Select first_name, salary, power(salary,2) from employees;

16. Display the employee name and salary after adding 500 bonus.

code: Select first_name, salary, sum(salary,500) as New_Salary from employees;

17. Display the employee name, commission_pct, and the total pay

code: Select first_name, commission_pct, salary, (salary +(salary * commission_pct)) as "Total Pay" from employees;

16. Display the employee name and their hire date in the format DD-MON-YYYY

code: Select first_name, to_char(hire_date, 'DD-MON-YYYY') from employees;

17. Display the employee name, hire_date, and next Monday after hire_date

code: select first_name, hire_date, next_day(hire_date, 'Monday') from employees;

18. Display the employee name, hire_date, and last day of the hire month.

code: select first_name, hire_date, last_day(hire_date) from employees;

19. Display the employee name and the number of months worked till today

code: select first_ name, months_between(sysdate, hire_date) as "months worked" from employees;

20. Display the employee name and the hire year

code: select first_ name, to_char(hire_date, 'YYYY') as "hire year" from employees;

21. Display the employee name and hire_date + 30 days using date arithmetic.

code: select first_ name, hire_date, hire_date+30 from employees;

22. Display the employee name, hire_date, and add 6 months to hire_date using ADD_MONTHS.

code: select first_ name, hire_date, add_months(hire_date, 6) from employees;

23. Display the employee name and the day name (e.g., Monday, Tuesday) of their hire_date

code: select first_ name, to_char(hire_date, 'DAY') from employees;