34.
Hands-On: ADF Project : Data Flow :
SQL Foundations : Mastering SQL Joins
&types with Scenarios
From <https://www.youtube.com/watch?v=0GUfhfRpAlE&t=1302s>
Who didn’t buy any product ? 2 , 3,4
Purchased - 1 rama
Matching records from both the tables - Inner
Inner - 3 records
Left - 3+3 = 6 records
Right - 3=1 = 4 records
Full join - 8 records
Easiest way to calculate
3*2=6
2*3=6
1*1=1
Total =13
How many records for inner , left join , right join and full outer ?
# duplicate records reduntancy
# important iq
Inner - 13
Thumb rule: LEFT | Right | Full outer always greater than inner Join
Left -15
Right - 15
Fullouter - 17
Sql server in local laptop
case1:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2),
DepartmentID INT
);
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Sales'),
(4, 'Marketing');
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate, Salary, DepartmentID) VALUES
(1, 'John', 'Doe', '2020-05-15', 68000.00, 1),
(2, 'Jane', 'Smith', '2021-03-10', 75000.00, 2),
(3, 'Emily', 'Jones', '2020-07-22', 75000.00, 3),
(4, 'Michael', 'Brown', '2022-01-30', 80000.00, 5),
(5, 'Sarah', 'Johnson', '2019-11-05', 75000.00, 1),
(6, 'David', 'Williams', '2021-06-14', 70000.00, 6)
employee who already working in dedicated departments ?
select * from employees e
inner JOIN departments d
on e.departmentid=d.departmentid
# to avoid departmentID twice
select e.*,d.departmentname from employees e
inner JOIN departments d
on e.departmentid=d.departmentid
4 records
select e.*,d.departmentname from employees e
left JOIN departments d
on e.departmentid=d.departmentid
6 records
select e.employeeid,e.firstname,e.lastname,e.hiredate,e.salary,d.* from employees e
right JOIN departments d
on e.departmentid=d.departmentid
select * from employees;
select * from departments;
select * from employees e
full JOIN departments d
on e.departmentid=d.departmentid
Cross join / cartesian product :
select * from employees e, departments d
case2:
CREATE TABLE emp (
dept_id INT
);
CREATE TABLE dept (
dept_id INT
);
INSERT INTO emp (dept_id) VALUES
(1),
(1),
(1),
(2),
(2),
(3),
(4),
(NULL);
INSERT INTO dept (dept_id) VALUES
(1),
(1),
(2),
(2),
(2),
(3),
(5),
(NULL);
select * from emp e
inner JOIN dept d
on e.departmentid=d.departmentid
select * from emp e
left JOIN dept d
on e.departmentid=d.departmentid
select * from emp e
right JOIN dept d
on e.departmentid=d.departmentid
select * from emp e
full JOIN dept d
on e.departmentid=d.departmentid
case3:
CREATE TABLE empsal (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
INSERT INTO empsal (EmployeeID, FirstName, LastName, HireDate, Salary, DepartmentID) VALUES
(1, 'John', 'Doe', '2020-05-15', 68000.00, 1),
(2, 'Jane', 'Smith', '2021-03-10', 75000.00, 2),
(3, 'Emily', 'Jones', '2020-07-22', 75000.00, 2),
(4, 'Michael', 'Brown', '2022-01-30', 80000.00, 3),
(5, 'Sarah', 'Johnson', '2019-11-05', 75000.00, 1),
(6, 'David', 'Williams', '2021-06-14', 70000.00, 1),
(7, 'Mary', 'Garcia', '2020-04-20', 60000.00, 1),
(8, 'James', 'Martinez', '2022-08-12', 68000.00, 1),
(9, 'Anna', 'Lee', '2021-05-25', 79000.00, 2),
(10, 'Chris', 'Kim', '2020-02-17', 75000.00, 2),
(11, 'Jessica', 'Taylor', '2021-03-22', 70000.00, 2),
(12, 'Robert', 'Anderson', '2022-04-10', 60000.00, 1),
(13, 'Linda', 'Thomas', '2019-09-15', 55000.00, 2),
(14, 'Kevin', 'Harris', '2022-06-05', 50000.00, 1),
(15, 'Laura', 'Clark', '2020-11-11', 80000.00, 2);
Select * from empsal;
select departmentid,count(*) as empcount from empsal
group by departmentid
select departmentid,count(salary) as totalsal from empsal
group by departmentid
# is it possible to display firstname and other columnn when we using group by ?????
Noooooooooooooooooooo . Possible using partition by
select departmentid,avg(salary) as avgsal from empsal
group by departmentid
Select * ,sum(salary) over (partition by departmentid order by employeeid) as runningtotal from
empsal
# to display all the column
select *,
row_number() over (partition by departmentid order by salary desc) as rn,
rank() over (partition by departmentid order by salary desc) as rank,
dense_rank() over (partition by departmentid order by salary desc) as drank
from empsal
===========================================