SDSU Operators in SQL Using the HR Schema Exam Practice in the file In this lab, we are exploring set operators in SQL using the HR schema. Set operators a

SDSU Operators in SQL Using the HR Schema Exam Practice in the file In this lab, we are exploring set operators in SQL using the HR schema. Set operators
are used to combine the result sets returned by two separate queries into a single result
set. The set operators for SQL are MINUS, INTERSECT, UNION, and UNION ALL.
A set operator appears between two independent queries. It is used to reconcile the
results of the two queries into a single result set. These operations are based upon
principles in elementary mathematical set theory.
•
•
•
•
MINUS is used to produce the difference between the two result sets.
INTERSECT is used to show the commonalities between the two result sets.
UNION combines two results sets but removes any duplicates.
UNION ALL combines two results sets but preserves any duplicates.
These relationships can be visualized using Venn Diagrams, which represent the logical
relations between sets in a visual form. A Venn diagram uses circles to show areas of
commonality and exclusivity between sets.
Relationship to Mathematical Set Theory
The diagrams that follow show the correlation between the Oracle set operator, the
mathematical notation, and a Venn diagram representing the relationship.
MINUS: operation is analogous to a difference:
INTERSECT: named the same as the mathematical operation:
UNION: is also named the same as the mathematical
-1-
UNION ALL: does not have an analogous operation in elementary set theory where
there is no notion of duplicates.
Other mathematical operations can be emulated by combining SQL operations. For
instance, creating a symmetric difference can be accomplished by the following
combination:
UNION – MINUS – INTERSECT
( UNION )
MINUS
( INTERSECT )
This next example using the INTERSECT operator is equivalent to a query that selects
distinct rows from the JOB and the JOB_HISTORY tables related by an inner join.
SELECT job_id
FROM jobs
INTERSECT
SELECT job_id
FROM job_history;
— This result is equivalent to using an INNER JOIN and DISTINCT
-SELECT DISTINCT j.job_id
FROM jobs j
INNER JOIN job_history jh
ON j.job_id = jh.job_id;
-2-
WHAT TO DO for Practical Assignment 4
1. Using a set operator, write a query to determine names that appear in both the
city and the state_province fields of the locations table.
2. Write a query (using an inline view) that returns a count of how many distinct
entries appear in either the city and the state_province fields.
3. Write a query (using an inline view) that returns a count of how many total entries
appear in the city and the state_province fields combined.
4. Find all employees work in department_id 10 just use minus operator.
5. Duplicate hr.employees as new table named empl_dup using create table
as .
6. Recreate a view called EMPLOYEES_VU based on the employee numbers,
employee names, and department numbers from the empl_dup table. Change
the heading for the employee name to EMPLOYEE. Precede the CREATE VIEW
command with the following comment lines
/* Assignment4_6
LastName, FirstName
*/
After the view created, please write queries to answer the following questions:
a. Display the contents of the EMPLOYEES_VU view
b. Select the view name and text from the USER_VIEWS data dictionary
view.
c. Using your EMPLOYEES_VU view, enter a query to display all employee
names and department numbers.
7. Recreate a view named DEPT50 that contains the employee numbers, employee
last names, and department numbers for all employees in department 50. Label
the view columns EMPNO, EMPLOYEE, and DEPTNO. Do not allow an
employee to be re-assigned to another department through the view.
/* Assignment4_7
LastName, FirstName
*/
After the view created, please write queries to answer the following questions:
a. Display the structure and contents of the DEPT50 view.
b. Select the view name and text from the USER_VIEWS data dictionary
view.
c. Attempt to re-assign Matos to department 80 by doing an update
command.
-3-

Purchase answer to see full
attachment