This project uses Oracle Express 11g and SQL Developer 4.1.3. During your progress through the course, you have created the Northwind database and acquired access to the Human Resources database bundled in Oracle Express as hr. The project exercises in this project use only the Human Resources database. The schema 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.
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).
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.
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:
PUT_LINEsubprogram, create the PL/SQL to find a country by its ID that will output the following:
The country you selected is Canada.
Login to the Human Resources database as the DBA: (10% totaling 10% for the section).
Login to the Human Resource database as newhr (5% each except #5 at 10% for a total of 40% for the section).
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.
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:
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 textbook 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.
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.