SQL Joins and Window Functions Explained

The Tables We'll Use We'll keep the same two tables throughout so examples stay consistent and easy to follow. CREATE TABLE employees ( emp_id INT PRIMARY KEY , name VARCHAR ( 100 ), dept_id INT , salary DECIMAL ( 10 , 2 ) ); INSERT INTO employees VALUES ( 1 , 'Alice' , 1 , 72000 ), ( 2 , 'Brian' , 2 , 58000 ), ( 3 , 'Carol' , 1 , 65000 ), ( 4 , 'Eve' , 3 , 91000 ); CREATE TABLE departments ( dept_id INT PRIMARY KEY , dept_name VARCHAR ( 100 ), location VARCHAR ( 100 ) ); INSERT INTO departments VALUES ( 1 , 'Engineering' , 'Nairobi' ), ( 2 , 'Marketing' , 'Lagos' ), ( 3 , 'Finance' , 'Accra' ); Part 1 — SQL Joins What is a Join? In relational databases, data is stored in separate tables to keep it organized, reduce duplication, and improve efficiency. For example: employee details might be stored in one table, while department information is stored in another. When you need information from both tables at the same time, you use a JOIN. A JOIN combines rows from two tables using a rela