Make a Table By running the query and adding the information
CREATE TABLE employeeslist
(
id INT IDENTITY(1,1) PRIMARY KEY,
[Name] VARCHAR(100) NOT NULL,
Gender VARCHAR(100) NOT NULL,
city VARCHAR(100) NOT NULL,
salary INT NOT NULL
);
INSERT INTO employeeslist VALUES ('Peter', 'M', 'London', 30000);
INSERT INTO employeeslist VALUES ('Laura', 'F', 'London', 35000);
INSERT INTO employeeslist VALUES ('Anna', 'F', 'London', 33000);
INSERT INTO employeeslist VALUES ('Tom', 'M', 'London', 38000);
INSERT INTO employeeslist VALUES ('Maria', 'F', 'Liverpool', 36000);
INSERT INTO employeeslist VALUES ('Catherine', 'F', 'Liverpool', 32000);
INSERT INTO employeeslist VALUES ('Lily', 'F', 'Liverpool', 35000);
INSERT INTO employeeslist VALUES ('Mia', 'F', 'Liverpool', 31000);
INSERT INTO employeeslist VALUES ('Grace', 'F', 'Leeds', 37000);
INSERT INTO employeeslist VALUES ('Arthur', 'M', 'Leeds', 35000);
CREATE TABLE employeeslist1
(
id INT IDENTITY(1,1) PRIMARY KEY,
[Name] VARCHAR(100) NOT NULL,
Gender VARCHAR(100) NOT NULL,
city VARCHAR(100) NOT NULL,
salary INT NOT NULL
);
INSERT INTO employeeslist1 VALUES ('Peter', 'M', 'London', 30000);
INSERT INTO employeeslist1 VALUES ('Laura', 'F', 'London', 35000);
INSERT INTO employeeslist1 VALUES ('Grace', 'F', 'Liverpool', 12000);
INSERT INTO employeeslist1 VALUES ('Sophia', 'F', 'Leeds', 37000);
INSERT INTO employeeslist1 VALUES ('Alfie', 'M', 'York', 38000);
Questions
1. Fetch a resultant table that includes all records from both the table as one table.
Answers
select * from employeesGH
union all
select * from employeesGH1
2. Fetch a resultant table that includes all records from both tables as one table but no duplicate values.
Answer
select * from employeesGH
union
select * from employeesGH1
3. Fetch a resultant table that has the id, name, and city from the first table and the id and name from the second table in the same order.
Answer
select Id,name,city from employeesGH
union
select id,name from employeesGH1
* this will result in error as no. of colums should be equal in both select statements.
4. Fetch a resultant table that has id and city from the first table and name and id from the second table in the same order.
Answer
select name,city,Id from employeesGH
union
select id,name,city from employeesGH1
* this will result in error as the datatype of id and city and name and city is not same.
5. Fetch a resultant table that has the id, name, and city from the first table and the id, city, and name from the second table in the same order.
Answer
select id,[name],city from employeesGH
union
select id,city,[name] from employeesGH1
this will not result in error as the datatype of city and name is same but it will not be the proper result.
Union vs Union All
Union eliminates redundant rows but Union All does not
Union operates more quickly than Union All because it uses unique sorts to eliminate duplication.
Note: The select statement should have the same number of columns, data types, and column orders.
Summary
All of Union and Union together -> combines two or more select queries' result sets.
HostForLIFEASP.NET SQL Server 2022 Hosting