Oracle SQL Project I - Create Reports
The goal of this project is to test your SQL skills. You would need to use both technical and analytical skills to solve this project.
Before you start, you must be able to access all HR schema tables.
Your goal is to write SQL queries to solve below requirements from client. In some of the requirements, client has included sample output. Use the sample output as a reference purpose only. Your output might be different in some cases.
Create a report which lists all the employees FULL NAME and HIRE DATE. Sort the report descending on Date Hired. Sample output:
Create a report which lists all the locations where the CITY starting letter comes before alphabet S. Sample output:
Create a report listing DEPARTMENT NAME, employee first name and employee last name. Sort the report on Depart Name column. Sample output:
Create report listing Employees First Name, Depart Name and Salary for the employees who do not work on a commission and whose salary is grater than 10000. Sample output:
Create the dept_detail_view view using all the fields in departments, locations, countries, and regions. The view is intended only for quick lookups relating to department site details.
Create a report with the first 4 records of the employees table showing the first and last names, and the rownum pseudo column ordered on the employee's last name.
Report the total number of Departments starting with letter R.
Using an INNER JOIN, create a report where employee last name is reported who work in Executive department. Sample output
Create a new employee with below details:
ID = 222
Last Name = Fernando
First Name = Francis
Email = email@example.com
Phone Number = 123.456.7890
Hire Date = Dec 07, 1988
Job ID = MK_MAN
Salary = 75000
Commission = Null
Manager = Null
Department ID = 50
Update William Tell's salary by 10% by calculating the value.
Delete the William Tell record.