• Arun Kumar

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.

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.


Recent Posts

See All

Oracle Joins

When you normalize data and create tables inside database, the data is scattered into different tables. In order to create meaningful data, we must query data by joining multiple tables. This is where

SQL Case Statement in Oracle

Oracle SQL CASE statement gives you the flexibility to use sql IF ELSE logic in a SELECT, WHERE and JOIN clause. Note: same CASE statement is used in PL/SQL blocks. Select CASE SQL For example, Let us

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis