Question

Create one sql script file to complete the following. You cannot run separate SQL statements for the homework. You will also need to place a semicolon after each SQL statement, a requirement for SQL files containing multiple SQL statements

• Lesson 3
  1. Write a query to display the current date. Label the column DATE.
  2. Display the last name of all employees who have an A and an E in their last name.
  3. For each employee, display the employee number, last_name, salary, and salary if it were increased by 18%
     and expressed as a whole number.
  4. Modify the previous query to add a column that will subtract the old salary from the new salary. Label the column INCREASE .
  5. 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 Seventh of September, 1981.
  6. 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. Round the number of months up to the closest whole number.
  7. For each employee, display an output in this format: Employee earns monthly but wants <3 times salary>. Title the column Dream Salaries.
     Dream Salaries
     KING earns $5,000.00 monthly but wants $15,000.00.
     BLAKE earns $2,850.00 monthly but wants $8,550.00.
     CLARK earns $2,450.00 monthly but wants $7,350.00.
     
     
  8. Write a query to display name and salary for all employees. Format the salary to be 15 characters long, left-padded with $s. Label the column SALARY.
  9. Write a query that will display the employee's name with the first letter capitalized and all other letters lowercase along with the length of their name, restrict your results to include only employees whose name starts with J, A, or M. Give each column an appropriate label.
  10. Display the name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday.

Answer 1

1). Write a query to display the current date. Label the column DATE.

Query: SELECT sysdate "DATE" FROM dual;

2). Display the last name of all employees who have an A and an E in their last name.

Query: SELECT last_name FROM employees WHERE last_name like '%A%' AND last_name like '%E%';

3). For each employee, display the employee number, last_name, salary, and salary if it were increased by 18% and expressed as a whole number. Label the column New Salary.  

Query: SELECT employee-id, last_name, salary, ROUND(salary * 0.18) "NEW Salary" FROM employees;

4). Modify the previous query to add a column that will subtract the old salary from the new salary. Label the column INCREASE.

Query: SELECT employee-id, last_name, salary, ROUND(salary * 0.18) "NEW Salary", ROUND(salary * 0.18) - salary "INCREASE" FROM employees;

5). Display the employees last_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 Seventh of September, 1981".

Query: SELECT last_name, hire_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date,6),'Monday'),'fmday,"the" Ddspath "of" Month, YYYY') REVIEW FROM employees;

6). For each employee display the employee last 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. Round the number of months up to the closest whole number.

Query: SELECT last_name,ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) MONTHS_WORKED FROM employees ORDER BY months_worked;

7). For each employee, display an output in this format: Employee earns monthly but wants <3 times salary>. Title the column Dream Salaries.
Dream Salaries
KING earns $5,000.00 monthly but wants $15,000.00.
BLAKE earns $2,850.00 monthly but wants $8,550.00.
CLARK earns $2,450.00 monthly but wants $7,350.00.

Query: SELECT lat_name || 'earns' || TO_CHAR(salary, 'fm$99,999.00') || 'monthly_nut_wants' || TO_CHAR(salary*3, 'fm$99,999.00') || '.' "Dream Salaries" FROM employees;

8).Write a query to display name and salary for all employees. Format the salary to be 15 characters long, left-padded with $s. Label the column SALARY.

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

9). Write a query that will display the employee's name with the first letter capitalized and all other letters lowercase along with the length of their name, restrict your results to include only employees whose name starts with J, A, or M. Give each column an appropriate label.

Query: SELECT INITCAP(last_name) "NAME", LENGTH(last_name) "Length" FROM employees WHERE    last_name like 'J%' OR last_name like 'A%' OR lat_name like 'M%' ORDER BY last_name;

10). Display the name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday.

Query: SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') DAY FROM employees ORDER BY TO_CHAR(hire_date - 1,'d');