PostGreSQL-Project/JOIN OPERATION.sql at main · Govindku2003/PostGreSQL-Project · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
CREATE DATABASE employees6;
DROP DATABASE IF EXISTS employees6

CREATE TABLE Employees6 (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

SELECT * FROM EMPLOYEES6;


INSERT INTO Employees6 (first_name, last_name, department_id)
VALUES
('Rahul', 'Sharma', 101),
('Priya', 'Mehta', 102),
('Ankit', 'Verma', 103),
('Simran', 'Kaur', NULL),
('Aman', 'Singh', 101);


CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);


INSERT INTO Departments (department_id, department_name)
VALUES
(101, 'Sales'),
(102, 'Marketing'),
(103, 'IT'),
(104, 'HR');


SELECT *FROM Departments;


SELECT e.employee_id, e.first_name, e.last_name, e.department_id,
		d.department_id, d.department_name
FROM Employees6 e
INNER JOIN
      Departments d
ON
  e.department_id=d.department_id;


SELECT e.employee_id, e.first_name, e.last_name, e.department_id,
		d.department_id, d.department_name
FROM Employees6 e
LEFT JOIN
      Departments d
ON
  e.department_id=d.department_id;


SELECT e.employee_id, e.first_name, e.last_name, e.department_id,
		d.department_id, d.department_name
FROM Employees6 e
RIGHT JOIN
      Departments d
ON
  e.department_id=d.department_id;


SELECT e.employee_id, e.first_name, e.last_name, e.department_id,
		d.department_id, d.department_name
FROM Employees6 e
FULL OUTER JOIN
      Departments d
ON
  e.department_id=d.department_id;


SELECT e.employee_id, e.first_name, e.last_name, e.department_id,
		d.department_id, d.department_name
FROM Employees6 e
CROSS  JOIN
      Departments d;


SELECT e.employee_id, e.first_name,
		d.department_id, d.department_name
FROM Employees6 e
CROSS  JOIN
      Departments d;

-----------------------------------------------------------------------------
-- SELF JOIN


SELECT e1.first_name AS Employee_name1,
	   e2.first_name AS Employee_name2
FROM employees6 e1 JOIN employees6 e2
ON e1.department_id = e2.department_id AND e1.employee_id != e2.employee_id;


SELECT e1.first_name AS Employee_name1,
	   e2.first_name AS Employee_name2,
	   d.department_name
FROM employees6 e1 JOIN employees6 e2
ON e1.department_id = e2.department_id AND e1.employee_id != e2.employee_id
JOIN
departments d
ON
e1.department_id = d.department_id;