Create Reports SQL Project

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.

Task 1

Create a report which lists all the employees FULL NAME and HIRE DATE. Sort the report descending on Date Hired. Sample output

Task 2

Create a report which lists all the locations where the CITY starting letter comes before alphabet S. Sample output

Task 3

Create a report listing DEPARTMENT NAME, employee first name and employee last name. Sort the report on Depart Name column. Sample output

Task 4

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

Task 5

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.

Task 6

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.

Task 7

Report the total number of Departments starting with letter R.

Task 8

Using an INNER JOIN, create a report where employee last name is reported who work in Executive department. Sample output

Task 9

Create a new employee with below details

• ID = 222

• Last Name = Fernando

• First Name = Francis

• Email = francis@francisfernando.com

• Phone Number = 123.456.7890

• Hire Date = Dec 07, 1988

• Job ID = MK_MAN

• Salary = 75000

• Commission = Null

• Manager = Null

• Department ID = 50

Task 10

Update William Tell's salary by 10% by calculating the value.

Task 11

Delete the William Tell record.