Oracle SQL and PL/SQL Developer Project

This project uses Oracle Express and SQL Developer. During your progress through the course, you have acquired access to the Human Resources schema bundled in Oracle Express as hr. The exercises in this project use this schema, which is shown below.

Your results may vary slightly as some of the data may have changed as you proceeded through the course. You may want to reinstall the software to get a fresh start so your results will reflect those illustrations shown in this project, but it is not a requirement.

HR Schema

HR Schema

Part 1 (50%)

Login as hr to the Human Resources database for the following exercises (5% for each except #5 at 10% and #6 at 15% totaling 50% for the section).

  1. (5%) Create a report listing employees first and last names and date hired. Sort descending on date hired and ascending on last name as shown below: Sort by date hired then last name
  2. (5%) Create report listing locations with a starting letter that comes before C alphabet: Cities Less Than C.
  3. (5%) Create report listing employees by department ordered on the department name. The employee name should be concatenated as shown in the example below: Order Dept Employee Name.
  4. (5%) Create report listing the employee's first, last, and department names and salary for employees that do not work on commission and who have salaries greater than 10000: Salaries greater than 10000
  5. (10%) 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.
  6. (15%) Using the dept_detail_view, create a report showing those departments in Europe. Use all the fields in the view without the id fields on as shown: Department View
  7. (5%) Using the DBMS_OUTPUT package's PUT_LINE subprogram, create the PL/SQL to find a country by its ID that will output the following:
    The country you selected is Canada.

Part 2 (10%)

Login to the Human Resources database as the DBA: (10% totaling 10% for the section).

  1. (10%) Create a new user for the Human Resources database with the user name newhr and the password newhr. Give the user session, resource, create synonym, and create view permissions. Grant select, insert, and delete permissions for the departments and employees tables.

Part 3 (40%)

Login to the Human Resource database as newhr (5% each except #5 at 10% for a total of 40% for the section).

  1. (5%) Execute SELECT * FROM hr.employees; to demonstrate newhr can access the employees table. Execute SELECT * FROM hr.departments; to demonstrate newhr can access the departments table. Execute SELECT * FROM; to demonstrate newhr cannot access the jobs table.
  2. (5%) 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: Using ROWNUM
  3. (5%) Using an inline view, report the number of departments starting with the letter R: Department Count
  4. (5%) The following SQL statement will produce a CROSS JOIN on all department and all employees resulting in unusable data:
    SELECT hr.departments.department_name, hr.employees.last_name
    FROM hr.departments, hr.employees
    WHERE hr.departments.department_name = 'Executive';
    Using an INNER JOIN, fix the statement to report only the Executive employees as follows: INNER JOIN
  5. (10%) Use the following data to create a new employee:
    • employee_id = 300
    • last_name = Tell
    • first_name = William
    • email = william
    • phone_number = 555.555.5555
    • hire_date = January 1, 2016
    • job_id = IT_PROG
    • salary = 50000
    • commission_pct = null
    • manager_id = null
    • department_id = 210
  6. (5%) Update William Tell's salary by 10% by calculating the value.
  7. (5%) Delete the William Tell record.


This project is meant for you to use your own skills and knowledge. This means that we expect the work to be your own work. We also expect that you will want to look some stuff up. Please feel free to use your course manual and the course content to help you along. You may also use the Internet as a source of help, especially for looking up documentation and errors.

Note that the instructor is not a resource during this project. The purpose of the project is to evaluate how well you can do without access to the instructor.

Submitting Project

  • You may submit your completed solutions as a single file containing your answers to each part of the final exam, or as a set of files (with one file for each question). In either case, please make sure your answers are clearly labeled so we know which answer corresponds to which question.
  • Please submit your work as text files with a .sql extension.
  • If you submit your work as a set of files, please create an archive (zip file) of your entire project.
  • Email the file to
  • If you are having trouble sending the project attachment via email we suggest using a file sharing service such as Dropbox, or Google Drive.
  • Please allow 10 business days for us to review the exam submission.
  • You must complete the project before the expiration date of your course. We estimate it will take about 20 hours. Be sure to leave yourself enough time.
Author: Roger Sakowski

Roger has over 35 years of experience in technical training, programming, data management, network administration, and technical writing for companies such as NASA, Sun Microsystems, Bell Labs, GTE, GE, and Lucent among other Fortune 100 companies.

About Webucator

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Check out our complete course catalog.