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.
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).
- (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:
- (5%) Create report listing locations with a starting letter that comes before C alphabet: .
- (5%) Create report listing employees by department ordered on the department name. The employee name should be concatenated as shown in the example below: .
- (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:
- (10%) Create the
dept_detail_viewview using all the fields in departments, locations, countries, and regions. The view is intended only for quick lookups relating to department site details.
- (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:
- (5%) Using the
PUT_LINEsubprogram, 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).
- (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).
- (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 hr.jobs;to demonstrate newhr cannot access the jobs table.
- (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:
- (5%) Using an inline view, report the number of departments starting with the letter R:
- (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:
- (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
- (5%) Update William Tell's salary by 10% by calculating the value.
- (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.
- 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 firstname.lastname@example.org.
- If you are having trouble sending the project attachment via email we suggest using a file sharing service such as Dropbox, Box.net 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.