0% found this document useful (0 votes)
9 views18 pages

34 ADF SQL

The document provides a detailed overview of SQL joins, including inner, left, right, and full outer joins, with examples and scenarios. It includes SQL code for creating tables and performing various join operations, demonstrating how to calculate records for each type of join. Additionally, it discusses grouping and partitioning data, as well as the use of aggregate functions in SQL queries.

Uploaded by

ipadair2books
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
9 views18 pages

34 ADF SQL

The document provides a detailed overview of SQL joins, including inner, left, right, and full outer joins, with examples and scenarios. It includes SQL code for creating tables and performing various join operations, demonstrating how to calculate records for each type of join. Additionally, it discusses grouping and partitioning data, as well as the use of aggregate functions in SQL queries.

Uploaded by

ipadair2books
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 18

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

===========================================

You might also like