PART-15 | Appian Interview Question | SQL Joins Tutorial For Beginners

PART-15 | Appian Interview Question | SQL Joins Tutorial For Beginners | Inner, Left, Right, Full Join | SQL Joins With Examples

--------------------------------------------------------------jOINS--------------------
1)Create table :Employee_BasicInfo

CREATE TABLE Employee_BasicInfo
(
Emp_id INT Identity,
Emp_First_Name Varchar(100),
Emp_Last_Name Varchar(100),
);
CREATE TABLE Employee_DepartmentInfo
(
Dept_id INT Identity,
Emp_id INT,
Emp_Dept_Name Varchar(100),
);

CREATE TABLE Employee_SalaryInfo
(
id INT Identity,
Dept_id INT,
Emp_id INT,
Emp_salary Varchar(100),
);

ALTER TABLE Employee_SalaryInfo
DROP COLUMN Emp_id;

select * from Employee_BasicInfo;
select * from Employee_DepartmentInfo;
select * from Employee_SalaryInfo;
INSERT INTO Employee_BasicInfo VALUES ('Manoj','Sharma');
INSERT INTO Employee_BasicInfo VALUES ('Rohit','Verma');
INSERT INTO Employee_BasicInfo VALUES ('Ravi','Ranjan');
INSERT INTO Employee_BasicInfo VALUES ('R','Kumar');
INSERT INTO Employee_BasicInfo VALUES ('Abhi','Raj');
INSERT INTO Employee_BasicInfo VALUES ('B','Reddy');

INSERT INTO Employee_DepartmentInfo VALUES (1,'ECE')
INSERT INTO Employee_DepartmentInfo VALUES (2,'IT');
INSERT INTO Employee_DepartmentInfo VALUES (3,'Support');
INSERT INTO Employee_DepartmentInfo VALUES (4,'Help Desk');
INSERT INTO Employee_DepartmentInfo VALUES (5,'CSE');
INSERT INTO Employee_DepartmentInfo VALUES (7,'Lab');

INSERT INTO Employee_SalaryInfo VALUES (1,'10000')
INSERT INTO Employee_SalaryInfo VALUES (2,'12000');
INSERT INTO Employee_SalaryInfo VALUES (3,'14000');
INSERT INTO Employee_SalaryInfo VALUES (4,'16000');
INSERT INTO Employee_SalaryInfo VALUES (5,'18000');

select * from Employee_BasicInfo;
select * from Employee_DepartmentInfo;
select * from Employee_SalaryInfo;

UPDATE Employee_SalaryInfo SET Emp_salary=18000 where id=5

SELECT *
FROM Employee_BasicInfo eb
INNER JOIN Employee_DepartmentInfo ed
ON eb.Emp_id = ed.Emp_id;
SELECT *
FROM ((Employee_BasicInfo eb
INNER JOIN Employee_DepartmentInfo ed ON eb.Emp_id = ed.Emp_id)
INNER JOIN Employee_SalaryInfo es ON es.Dept_id = ed.Dept_id) where eb.Emp_id=4

SELECT *
FROM ((Employee_BasicInfo eb
INNER JOIN Employee_DepartmentInfo ed ON eb.Emp_id = ed.Emp_id)
INNER JOIN Employee_SalaryInfo es ON es.Dept_id = ed.Dept_id) where eb.Emp_id=4

-----------------JOIN-----------
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

select * from Employee_BasicInfo;
select * from Employee_DepartmentInfo;
-----INNER JOIN/JOIN----------
The INNER JOIN keyword selects records that have matching values in both tables.

SELECT *
FROM Employee_BasicInfo eb
INNER JOIN Employee_DepartmentInfo ed
ON eb.Emp_id = ed.Emp_id;

SELECT eb.Emp_id, eb.Emp_First_Name,eb.Emp_Last_Name,ed.Emp_Dept_Name
FROM Employee_BasicInfo eb
INNER JOIN Employee_DepartmentInfo ed
ON eb.Emp_id = ed.Emp_id;

-----LEFT JOIN----------
The LEFT JOIN keyword returns all records from the left table (table1),
and the matching records from the right table (table2).
The result is 0 records from the right side, if there is no match.

SELECT *
FROM Employee_BasicInfo eb
left JOIN Employee_DepartmentInfo ed
ON eb.Emp_id = ed.Emp_id;

SELECT eb.Emp_id, eb.Emp_First_Name,eb.Emp_Last_Name,ed.Emp_Dept_Name
FROM Employee_BasicInfo eb
left JOIN Employee_DepartmentInfo ed
ON eb.Emp_id = ed.Emp_id;

-----RIGHT JOIN----------
The RIGHT JOIN keyword returns all records from the right table (table2),
and the matching records from the left table (table1).
The result is 0 records from the left side, if there is no match.

SELECT *
FROM Employee_BasicInfo eb
Right JOIN Employee_DepartmentInfo ed
ON eb.Emp_id = ed.Emp_id;

SELECT eb.Emp_id, eb.Emp_First_Name,eb.Emp_Last_Name,ed.Emp_Dept_Name
FROM Employee_BasicInfo eb
Right JOIN Employee_DepartmentInfo ed
ON eb.Emp_id = ed.Emp_id;

-----OUTER JOIN/FULL OUTER JOIN----------
The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

SELECT *
FROM Employee_BasicInfo eb
FULL JOIN Employee_DepartmentInfo ed
ON eb.Emp_id = ed.Emp_id;
SELECT eb.Emp_id, eb.Emp_First_Name,eb.Emp_Last_Name,ed.Emp_Dept_Name
FROM Employee_BasicInfo eb
FULL join Employee_DepartmentInfo ed
ON eb.Emp_id = ed.Emp_id;

#join #sql #sqlserver #sqlinterviewquestionsandanswers
#sqlinterview
#sqlinterviewquestions
#sqljoins

Видео PART-15 | Appian Interview Question | SQL Joins Tutorial For Beginners канала Appian Interview Questions