Experiment No 10: Use Set operators to perform different operations

Experiment No 10: Use Set operators to perform different operations

 

---

Result

The experiment was successfully executed. SQL SET operators (UNION, UNION ALL, INTERSECT, MINUS) were applied to combine and compare tables, and the expected outputs were obtained.

---

Q1. Explain the need of set operators in SQL

Set operators in SQL are needed to combine and compare the results of two or more queries. They help in:

• Finding common data between tables.

• Identifying unique records.

• Merging results from multiple queries.

• Simplifying analysis when working with large datasets.

Thus, set operators make data manipulation and analysis easier and more efficient.

---

Q2. Describe various set operators in SQL

1. UNION – Combines results of two queries and removes duplicate rows.

2. UNION ALL – Combines results of two queries and keeps duplicate rows.

3. INTERSECT – Returns only the common rows present in both queries.

4. MINUS (or EXCEPT in some databases) – Returns rows from the first query that are not present in the second query.

---

Exercise 

Schema:

emp1(empno, ename, deptno)  
emp2(empno, ename, deptno)

---

Step 1: Create Tables

CREATE TABLE emp1 (
    empno INT,
    ename VARCHAR(50),
    deptno INT
);

CREATE TABLE emp2 (
    empno INT,
    ename VARCHAR(50),
    deptno INT
);

---

Step 2: Insert Data (names + deptno)

-- emp1 data
INSERT INTO emp1 VALUES (101, 'Amit', 10);
INSERT INTO emp1 VALUES (102, 'Sneha', 20);
INSERT INTO emp1 VALUES (103, 'Rohit', NULL);
INSERT INTO emp1 VALUES (104, 'Priya', 30);

-- emp2 data
INSERT INTO emp2 VALUES (201, 'Sneha', 20);
INSERT INTO emp2 VALUES (202, 'Vikas', 30);
INSERT INTO emp2 VALUES (203, 'Amit', 10);
INSERT INTO emp2 VALUES (204, 'Meera', NULL);

---

Step 3: Queries

1. Display the names of employees including duplicate employee names.

SELECT ename FROM emp1
UNION ALL
SELECT ename FROM emp2;

👉 Output: Amit, Sneha appear twice

---

2. Display the names of employees excluding duplicate employee names.

SELECT ename FROM emp1
UNION
SELECT ename FROM emp2;

👉 Output: Amit, Sneha, Rohit, Priya, Vikas, Meera

---

3. Display the common employee names from both the tables.

SELECT e1.ename
FROM emp1 e1
INNER JOIN emp2 e2 ON e1.ename = e2.ename;

👉 Output: Amit, Sneha

---

4. List employees who are not assigned to any department.

SELECT ename FROM emp1
WHERE deptno IS NULL
UNION
SELECT ename FROM emp2
WHERE deptno IS NULL;

👉 Output: Rohit, Meera

---