Unit 53 Final Paper

Overview This practice provides a variety of exercises using different functions that are available for character, number, and date data type.

Task Write the appropriate SQL statement for the following queries. (Using EMPLOYEES table)

1. Write a query to display the system date. Label the column as Date.

SELECT sysdate "Date"
FROM dual;

2. The HR department needs a report to display the employee number, last name, salary, and salary increased by 15.5%
(expressed as a whole number) for each employee. Label the column New Salary.

SELECT employee_id, last_name, salary,
ROUND (salary * 1.155, 0) "New Salary"
FROM employees;

3. Modify your query in Task 2 to add a column that subtracts the old salary from the new salary. Label
the column Increase.

SELECT employee_id, last_name, salary,
ROUND (salary * 1.155, 0) "New Salary",
ROUND (salary * 1.155, 0) - salary "Increase"
FROM employees;

4.
Write a query that displays the last name (with the first letter in uppercase and all other letters in lowercase) and the length of the last name for all employees whose name starts with the letters “J”, “A”, or “M.” Give each column an appropriate label. Sort the results by the employee’s last names.

SELECT INITCAP(last_name) "Name",
LENGTH(last_name) "Length"
FROM employees
WHERE last_name LIKE 'J%'
OR last_name LIKE 'M%'
OR last_name LIKE 'A%'
ORDER BY last_name ;

5. Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with the $ symbol. Label the column as SALARY.

SELECT last_name,
LPAD(salary, 15, '$') SALARY
FROM employees;

6. Create a query that displays the first eight characters of the employees’ last names and indicate the amount of their salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column as EMPLOYEES_AND_THEIR_SALARIES.

SELECT substr(last_name, 1,8)||’
‘|| ltrim(rpad(‘ ‘,trunc(salary/1000) + 1,’*’),’ ‘) “EMPLOYEES_AND_THEIR_SALARIES”
From employees order by salary desc;

7. Create a query to display the last name and the number of weeks employed for all employees in department 90. Label the number of week’s column as TENURE. Truncate the number of week’s value to 0 decimal places. Show the records in descending order of the employees’ tenure.

SELECT last_name, trunc((sysdate-hire_date)/7)“TENURE”
From employees
Where department_id = 90
Order by “TENURE” desc;