Reference no: EM131706130
Relational Set Operators" Please respond to the following:
A relational Database Model allows database users to analyze data thoroughly. To accomplish this, advanced commands such as UNION and INTERSECT may be used.
Describe a business scenario where a UNION relational set operator may be used to merge two similar data sets. Within the context of your business scenario, analyze the analysis and data consistency advantages of using a UNION operator rather than simply merging two data sets into one result table.
Suppose you are tasked with finding common data elements amongst various data sets. Specify how an INTERSECT operator may assist you in accomplishing this task. Construct a query that can perform the same function as the INTERSECT operator without using the "INTERSECT" syntax within the query. Note: The Strayer Oracle Server may be used to test and compile the SQL Queries developed.
Classmate:
A relational Database Model allows database users to analyze data thoroughly. To accomplish this, advanced commands such as UNION and INTERSECT may be used. Describe a business scenario where a UNION relational set operator may be used to merge two similar data sets. Within the context of your business scenario, analyze the analysis and data consistency advantages of using a UNION operator rather than simply merging two data sets into one result table.
The book provides a very good example of when a UNION relational set operator would be used when a business acquires another business. They merge their customer lists through the use of the UNION operator. I think another good use of this would be when you have two departments within a company that need to be combined for any reason. Since most department managers want to keep their data separate, the ability to combine data if needed would be a good case for the UNION operator.
If we look at the scenario of combining two customer list tables, we already know that a UNION operator will combine the information and discard the duplicate records.
The UNION operator can also fail since it is a SQL query if the data types do not match. By just trying to merge the data from two tables to one you may have proprietary data types or certain data formats that need to be preserved within each table. The DBMS of the database may be programmed differently for each table and until the DBMS can be fixed keeping the tables separated may be the best solution.
Suppose you are tasked with finding common data elements amongst various data sets. Specify how an INTERSECT operator may assist you in accomplishing this task. Construct a query that can perform the same function as the INTERSECT operator without using the "INTERSECT" syntax within the query. Note: The Strayer Oracle Server may be used to test and compile the SQL Queries developed.
The INTERSECT query is used to find duplicates within two queries by finding common elements within both queries. You can think of the INTERSECT query as a Venn Diagram where each query is one part of the diagram and only the records overlapping in both queries will be returned.
To create an INTERSECT query without using the INTERSECT operator, you can use a JOIN syntax.
Here is the INTERSECT query from the book.
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE
FROM CUSTOMER
INTERSECT
SELECT CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE
FROM CUSTOMER_2;
This will return all of the information from both CUSTOMER and CUSTOMER_2 using the INTERSECT operator. To do the same with the join, I'm going to keep it simple and just look for Last Name
SELECT CUSTOMER.CUS_LNAME
FROM CUSTOMER
JOIN CUSTOMER_2 ON CUSTOMER.CUS_LNAME = CUSTOMER_2.CUS_LNAME;
Of course, what you would want to do is look at all of the fields that you needed for your SQL query and you could even add in a WHERE clause if needed.