Answer Happy

question 3
Instructions Create a database in Oracle named ADDB7311Test Student number and execute the preloaded SQL code using either SQL Developer or SOLPlusto create the database schema Copy and paste your queries into a MS Word document. Save this file as "Advanced_Databases_Test_Student_Number". Write the path and Filename of this document on your test paper. PRELOADS: ADDB731Ta Preload.sal The following set of relations has been set up for an organization to record employees and their environmental clean-up activities. At present the database is small and only includes information about the employees, locations and environmental activity cleaning. The relationships between the tables must be derived from the data in each of the tables. The tables and the information required are as follows: EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, SURNAME, EMAIL) LOCATIONS (LOCATION_ID, LOCATION _TYPE, LOCATION_ADDRESS, LOCATION_DIFFICULTY) ENVIRONMENT_ACTIVITIES (ENV_ID, ENV_DATE, ENV_HOURS, EMP_ID, LOCATION_ID) EMAIL SURNAME Sample Data is shown below: EMPLOYEES EMPLOYEE_ID FIRST_NAME EMP 1 Jeff EMP_2 John EMP_3 Sam Hendricks Abelton [email protected] [email protected] [email protected] Goodwill LOCATIONS LOCATION ID LOCATION TYPE LOCATION_ADDRESS LOCATION_DIFFICULTY 10 Seaside Road 6 Beach 5 13 Treeview Road LOC_1 LOC_2 LOC_3 Forest 8 18 Waterside Lane Lake

21:22:23 2022 a Question 2 (Marks: 10) Create a SQL Query to display the full employee name and the environment cleaning activity difficulty level. In your query also display the number of hours performed for the environment activity NOTE: Provide screenshot of your output LOCATION DIFFICULTY ENVIRONMENTAL HOURS 6 3 Sample Output EMPLOYEE Jeff Hendricks John Abelton Jeff Hendricks Sam Goodwill 5 1 8 INN 8

(Marks: 10) 21, 22, 23 Question 4 Create a PL/SQL query to display the employee name and the address where the environmental clean-up took place. In your solution only display the results for an address that contains 'Waterside NOTE: Provide screenshot of your output Sample Output EMPLOYEE FIRST NAME: Sam EMPLOYEE SURNAME: Goodwill LOCATION ADDRESS: 18 Waterside Lane EMPLOYEE FIRST NAME: Jeff EMPLOYEE SURNAME: Hendricks LOCATION ADDRESS: 18 Waterside Lane

(Marks: 10) 21, 22, 23 Question 4 Create a PL/SQL query to display the employee name and the address where the environmental clean-up took place. In your solution only display the results for an address that contains 'Waterside NOTE: Provide screenshot of your output Sample Output EMPLOYEE FIRST NAME: Sam EMPLOYEE SURNAME: Goodwill LOCATION ADDRESS: 18 Waterside Lane EMPLOYEE FIRST NAME: Jeff EMPLOYEE SURNAME: Hendricks LOCATION ADDRESS: 18 Waterside Lane