Union Vs Union All  

With Examples

   Part - 16

create table tblEmployee_1 (id int)

Insert tblEmployee_1 values (1),(2),(3)

create table tblEmployee_2 (id int)

Insert tblEmployee_2 values (3),(4)

SELECT id FROM  tblEmployee_1

UNION 

SELECT id FROM tblEmployee_2

SELECT id FROM  tblEmployee_1

UNION ALL

SELECT id FROM tblEmployee_2

It Removes Duplicates and gives 

UNION ALL

------------------------------------------------------------------------------

--Columns of Select Statement should be schema compatible (Implicit Conversion)

--> Datatype compatible  (Implicit Conversion)

------------------------------------------------------------------------------

create table tblEmployee_3 (Dates date) 

Insert tblEmployee_3 values ('2019-01-02'),('2020-02-03')

SELECT Dates FROM tblEmployee_3 --> date 

UNION 

SELECT id FROM tblEmployee_2 --> Int

SELECT Dates FROM tblEmployee_3 --> date 

UNION ALL

SELECT id FROM tblEmployee_2 --> Int

--> Implicit conversion works

create table tblEmployee_4 (id varchar(5)) 

Insert tblEmployee_4 values (3),(4)

SELECT id FROM tblEmployee_4  -- varchar with numeric data

UNION 

SELECT id FROM tblEmployee_2 -- int

Both the tables are having 3,4 each and it has removed duplicate records and provided only once

SELECT id FROM tblEmployee_4  -- varchar with numeric data

UNION ALL

SELECT id FROM tblEmployee_2 -- int

Implicit conversion happened as the table is having only numeric values. 

Lets Add some text record, to fail the implicit conversion

Insert tblEmployee_4 values ('test')

create table tblEmployee_5 (id int)

Insert tblEmployee_5 values (6),(7),(8),(9),(1)

create table tblEmployee_6 (id int)

Insert tblEmployee_6 values (8),(2),(6),(5),(4),(3)

SQL for Beginners Series

Session 6

Constraints in SQL

Import data from File to a database

   Part - 15

YouTube Video link 

Lets see how to import data from File to a database table. 

Here the text qualifier help where the text column starts and where it ends. with that even if the delimiter comes in between a text column it will not shift column to right while importing

Change the length if the target table is having more data length

You can name the destination table if its not existing already

For SQL Server 2017 or later version, we have separate option provided to "Import Flat File"

Please Subscribe:)

Find Employees Whose Salary is Higher Than 

Their Manager Salary

   Part - 14

YouTube Video Link:

Below are the Steps to solve the Question:

Step 1: Get Employee Salary from tblEmployee 

Step 2: Derive Manager Salary Using Manager id column by joining with tblEmployee (self join)

Step 3: Compare the Salaries of Employee and Manager

Here is the Query:

SELECT E.Empid, E.Empname , E.Salary AS EmpSalary, 

M.Empname AS MgrName, M.Salary AS MgrSalary 

FROM tblEmployee E 

JOIN tblEmployee M

ON E.Managerid = M.Empid

WHERE E.Salary > M.Salary

Please Subscribe :)