DROP TABLE IF EXISTS Employees, Departments;
 
CREATE TABLE Departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50)
);
 
CREATE TABLE Employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT
);
 
INSERT INTO Departments (dept_name) VALUES
('Engineering'),
('HR'),
('Finance');
 
INSERT INTO Employees (emp_name, dept_id) VALUES
('Alice', 1),
('Bob', 1),
('Charlie', 2),
('Diana', NULL),
('Eve', 4); -- Note: Dept 4 does NOT exist
 
INSERT INTO Employees (emp_name, dept_id) VALUES
('Hii', 3);
 
SELECT * FROM Departments;
SELECT * FROM Employees;
 
-- Inner Join 
 
SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.dept_id;
 
-- LEFT JOIN (LEFT OUTER JOIN) 
 
SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id;
 
-- RIGHT JOIN (RIGHT OUTER JOIN)
 
SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.dept_id = d.dept_id;
 
 
 
-- FULL JOIN (FULL OUTER JOIN)
 
SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
FULL JOIN Departments d ON e.dept_id = d.dept_id;
 
 
-- CROSS JOIN
 
SELECT e.emp_name, d.dept_name
FROM Employees e
CROSS JOIN Departments d;
 
-- SELF JOIN
 
ALTER TABLE Employees ADD COLUMN mentor_id INT;
 
UPDATE Employees SET mentor_id = 1 WHERE emp_id IN (2, 3);
 
SELECT * FROM employees;
 
SELECT e1.emp_name AS employee, e2.emp_name AS mentor
FROM Employees e1
JOIN Employees e2 ON e1.mentor_id = e2.emp_id;
 
-- USING vs ON
 
SELECT e.emp_name, d.dept_name
FROM Employees e
JOIN Departments d USING (dept_id);
 
SELECT e.emp_name, d.dept_name
FROM Employees e
JOIN Departments d ON e.dept_id = d.dept_id;
 
-- Questions 
-- Drop both tables, then create again and insert again (to revert the changes we made in explanation)
 
-- Q1 
-- How can you list all the departments which are assigned to 0 employees.
 
SELECT * from employees;
 
SELECT d.dept_name
FROM departments d 
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

RFJPUCBUQUJMRSBJRiBFWElTVFMgRW1wbG95ZWVzLCBEZXBhcnRtZW50czsKCkNSRUFURSBUQUJMRSBEZXBhcnRtZW50cyAoCiAgICBkZXB0X2lkIFNFUklBTCBQUklNQVJZIEtFWSwKICAgIGRlcHRfbmFtZSBWQVJDSEFSKDUwKQopOwoKQ1JFQVRFIFRBQkxFIEVtcGxveWVlcyAoCiAgICBlbXBfaWQgU0VSSUFMIFBSSU1BUlkgS0VZLAogICAgZW1wX25hbWUgVkFSQ0hBUig1MCksCiAgICBkZXB0X2lkIElOVAopOwoKSU5TRVJUIElOVE8gRGVwYXJ0bWVudHMgKGRlcHRfbmFtZSkgVkFMVUVTCignRW5naW5lZXJpbmcnKSwKKCdIUicpLAooJ0ZpbmFuY2UnKTsKCklOU0VSVCBJTlRPIEVtcGxveWVlcyAoZW1wX25hbWUsIGRlcHRfaWQpIFZBTFVFUwooJ0FsaWNlJywgMSksCignQm9iJywgMSksCignQ2hhcmxpZScsIDIpLAooJ0RpYW5hJywgTlVMTCksCignRXZlJywgNCk7IC0tIE5vdGU6IERlcHQgNCBkb2VzIE5PVCBleGlzdAoKSU5TRVJUIElOVE8gRW1wbG95ZWVzIChlbXBfbmFtZSwgZGVwdF9pZCkgVkFMVUVTCignSGlpJywgMyk7CgpTRUxFQ1QgKiBGUk9NIERlcGFydG1lbnRzOwpTRUxFQ1QgKiBGUk9NIEVtcGxveWVlczsKCi0tIElubmVyIEpvaW4gCgpTRUxFQ1QgZS5lbXBfaWQsIGUuZW1wX25hbWUsIGQuZGVwdF9uYW1lCkZST00gRW1wbG95ZWVzIGUKSU5ORVIgSk9JTiBEZXBhcnRtZW50cyBkIE9OIGUuZGVwdF9pZCA9IGQuZGVwdF9pZDsKCi0tIExFRlQgSk9JTiAoTEVGVCBPVVRFUiBKT0lOKSAKClNFTEVDVCBlLmVtcF9pZCwgZS5lbXBfbmFtZSwgZC5kZXB0X25hbWUKRlJPTSBFbXBsb3llZXMgZQpMRUZUIEpPSU4gRGVwYXJ0bWVudHMgZCBPTiBlLmRlcHRfaWQgPSBkLmRlcHRfaWQ7CgotLSBSSUdIVCBKT0lOIChSSUdIVCBPVVRFUiBKT0lOKQoKU0VMRUNUIGUuZW1wX2lkLCBlLmVtcF9uYW1lLCBkLmRlcHRfbmFtZQpGUk9NIEVtcGxveWVlcyBlClJJR0hUIEpPSU4gRGVwYXJ0bWVudHMgZCBPTiBlLmRlcHRfaWQgPSBkLmRlcHRfaWQ7CgoKCi0tIEZVTEwgSk9JTiAoRlVMTCBPVVRFUiBKT0lOKQoKU0VMRUNUIGUuZW1wX2lkLCBlLmVtcF9uYW1lLCBkLmRlcHRfbmFtZQpGUk9NIEVtcGxveWVlcyBlCkZVTEwgSk9JTiBEZXBhcnRtZW50cyBkIE9OIGUuZGVwdF9pZCA9IGQuZGVwdF9pZDsKCgotLSBDUk9TUyBKT0lOCgpTRUxFQ1QgZS5lbXBfbmFtZSwgZC5kZXB0X25hbWUKRlJPTSBFbXBsb3llZXMgZQpDUk9TUyBKT0lOIERlcGFydG1lbnRzIGQ7CgotLSBTRUxGIEpPSU4KCkFMVEVSIFRBQkxFIEVtcGxveWVlcyBBREQgQ09MVU1OIG1lbnRvcl9pZCBJTlQ7CgpVUERBVEUgRW1wbG95ZWVzIFNFVCBtZW50b3JfaWQgPSAxIFdIRVJFIGVtcF9pZCBJTiAoMiwgMyk7CgpTRUxFQ1QgKiBGUk9NIGVtcGxveWVlczsKClNFTEVDVCBlMS5lbXBfbmFtZSBBUyBlbXBsb3llZSwgZTIuZW1wX25hbWUgQVMgbWVudG9yCkZST00gRW1wbG95ZWVzIGUxCkpPSU4gRW1wbG95ZWVzIGUyIE9OIGUxLm1lbnRvcl9pZCA9IGUyLmVtcF9pZDsKCi0tIFVTSU5HIHZzIE9OCgpTRUxFQ1QgZS5lbXBfbmFtZSwgZC5kZXB0X25hbWUKRlJPTSBFbXBsb3llZXMgZQpKT0lOIERlcGFydG1lbnRzIGQgVVNJTkcgKGRlcHRfaWQpOwoKU0VMRUNUIGUuZW1wX25hbWUsIGQuZGVwdF9uYW1lCkZST00gRW1wbG95ZWVzIGUKSk9JTiBEZXBhcnRtZW50cyBkIE9OIGUuZGVwdF9pZCA9IGQuZGVwdF9pZDsKCi0tIFF1ZXN0aW9ucyAKLS0gRHJvcCBib3RoIHRhYmxlcywgdGhlbiBjcmVhdGUgYWdhaW4gYW5kIGluc2VydCBhZ2FpbiAodG8gcmV2ZXJ0IHRoZSBjaGFuZ2VzIHdlIG1hZGUgaW4gZXhwbGFuYXRpb24pCgotLSBRMSAKLS0gSG93IGNhbiB5b3UgbGlzdCBhbGwgdGhlIGRlcGFydG1lbnRzIHdoaWNoIGFyZSBhc3NpZ25lZCB0byAwIGVtcGxveWVlcy4KClNFTEVDVCAqIGZyb20gZW1wbG95ZWVzOwoKU0VMRUNUIGQuZGVwdF9uYW1lCkZST00gZGVwYXJ0bWVudHMgZCAKTEVGVCBKT0lOIGVtcGxveWVlcyBlIE9OIGQuZGVwdF9pZCA9IGUuZGVwdF9pZApXSEVSRSBlLmVtcF9pZCBJUyBOVUxMOw==