Skip to main content

SQL Set Operators

SQL Set Operators   Traditional Set Operators: Union Compatibility:     Requirement for the traditional set operators.    Strong requirement.                         - Same number of columns.                         - Each corresponding column is compatible.                         - Positional correspondence.     Apply to similar tables by removing columns first. SQL UNION Example:           Example 1: Retrieve basic data about all university people.                     SELECT FacNo AS PerNo, FacFirstName                                      ...

SQL Set Operators

SQL Set Operators

 Traditional Set Operators:

Union Compatibility:

  •    Requirement for the traditional set operators.
  •    Strong requirement.
                    -Same number of columns.
                    -Each corresponding column is compatible.
                    -Positional correspondence.
  •    Apply to similar tables by removing columns first.

SQL UNION Example:

        Example 1: Retrieve basic data about all university people.

                SELECT FacNo AS PerNo, FacFirstName 
                                            AS FirstName, FacLastName 
                                            AS LastName, FacCity 
                                            AS City, FacState 
                                            AS State 
                FROM Faculty 
                                    UNION 
                SELECT StdNo AS PerNo, StdFirstName 
                                            AS FirstName, StdLastName AS LastName, StdCity 
                                            AS City, StdState 
                                            AS State 
                FROM Student;

        Example 2: Show teaching assistants, faculty who are students.  Only show the common columns in the result.

                SELECT FacNo AS PerNo, FacFirstName 
                                           AS FirstName, FacLastName 
                                           AS LastName, FacCity 
                                           AS City, FacState 
                                           AS State 
                FROM Faculty 
                            INTERSECT 
                SELECT StdNo 
                                           AS PerNo, StdFirstName 
                                           AS FirstName, StdLastName 
                                           AS LastName, StdCity 
                                           AS City, StdState 
                                           AS State 
                FROM Student;

        Example 3: Show faculty who are not students (only faculty).  Only show the common columns in the result.

                SELECT FacNo AS PerNo, FacFirstName 
                                           AS FirstName, FacLastName 
                                           AS LastName, FacCity 
                                           AS City, FacState 
                                           AS State 
                FROM Faculty 
                                    MINUS 
                SELECT StdNo 
                                           AS PerNo, StdFirstName 
                                           AS FirstName, StdLastName 
                                           AS LastName, StdCity 
                                           AS City, StdState 
                                           AS State 
                FROM Student;

Our Official Website : Web Conquerors (https://www.webconquerors.com/) 

Want to get digital services? Contact US

Want to know about our services? Our Services



Comments

Popular posts from this blog

  d. Insert a new row in the Location table related to the Facility row in modification problem (c). The new row should have “Door” for the location name. Query Formulation + Result Snapshot Our Official Website :  Web Conquerors  (https://www.webconquerors.com/)  Want to get digital services?  Contact US Want to know about our services?  Our Services

Correction For ERD

 d. For each consistency error in Figure 1, you should identify the consistency rule violated and suggest possible resolutions of the error. The ERD has generic names to help will concentrate on finding diagram errors rather than focusing on the meaning of the diagram. Answer:                Entity6 is violation of the Primary key rule. As there are 3 attributes from within Entity6 that are marked as primary key. Entity6 also violates the Identifying relationship rule, as there is no foreign key in the Entity6 table to identify the item from Entity7. Since this a weak entity type, the cardinality must be 1:1, which it is not. Entity2 is a violation of the Identifying relationship rule due to it not being a weak entity type when there is an identifying relationship. Our Official Website :  Web Conquerors  (https://www.webconquerors.com/)  Want to get digital services?  Contact US Want to know about our services?  ...