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
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/)
Comments
Post a Comment