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 https://livesql.oracle.com/apex/livesql/file/content_GV8MU6SITA2V3VYI179FAJUCY.html
Search Oracle Live SQL for the script, SQL Joins script. Execute and study the queries of the script, SQL Joins https://livesql.oracle.com/apex/livesql/file/content_O59U2TFO5QEAOEFC6OPJ9MHBP.html
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.
