Assignment 3
Submit a Word document with the answers to the following questions.
Review Oracle Database 19c: Basic SQL and Oracle Database 19c: PL/SQL
Search Oracle Live SQL for the HR Objects and Data For Live SQL script. Save the script and execute it. Or use this link to find the script
Search Oracle Live SQL for the script, SQL Joins script. Execute and study the queries of the script, SQL Joins
1. Explain the construction of Statement 1:
SELECT e1.last_name||’ works for ‘||e2.last_name
“Employees and Their Managers”
FROM hr.employees e1, hr.employees e2
WHERE e1.manager_id = e2.employee_id
AND e1.last_name LIKE ‘R%’
ORDER BY e1.last_name;
2. Explain the construction of this statement:
SELECT d.department_id, e.last_name
FROM hr.departments d, hr.employees e
WHERE d.department_id = e.department_id(+)
ORDER BY d.department_id, e.last_name;
3. Execute and explain the construction of the uncorrelated subquery shown in the screenshot:
-find screenshot and links in the attached document
4. Execute and explain the construction of the correlated subquery shown in the screenshot:
-find screenshot and links in the attached document
5. Explain the difference between a correlated and uncorrelated subquery.
6. Write a query demonstrating the use of the WHERE clause with a comparison condition (Use a relational operator.). Use the HR database.
7. Write a query demonstrating the use of the WHERE clause with a logical operator (AND, OR, or NOT) in the condition. Use the HR database.
8. Write a query demonstrating the use of the WHERE clause with a NULL condition. Use the HR database and the emp_details_view. Hint: The manager_id is NULL for one record in the table.
9. Write a query demonstrating the use of the WHERE clause with an IN condition. Use the HR database.
10. Write a query demonstrating the use of the WHERE clause with a compound condition. Use the HR database.
