SQL_joins.sql

-- creating table_1 with name "students" along with constaints
create table students (
  student_id int primary key,
  name varchar(50),
  age int,
  gender char(1),
  department_id int
);


-- inserting values into the table
INSERT INTO students VALUES
(1, 'Aakash Sharma', 20, 'M', 101),
(2, 'Bhavna Patel', 21, 'F', 102),
(3, 'Chirag Mehta', 22, 'M', 101),
(4, 'Divya Rao', 20, 'F', 103),
(5, 'Eshan Singh', 21, 'M', 102),
(6, 'Farah Khan', 22, 'F', 104),
(7, 'Gaurav Jain', 20, 'M', 101),
(8, 'Harshita Shah', 21, 'F', 103),
(9, 'Ishan Roy', 23, 'M', 104),
(10, 'Juhi Joshi', 22, 'F', 102),
(11, 'Karan Verma', 20, 'M', 101),
(12, 'Lavanya Pillai', 22, 'F', 103),
(13, 'Mihir Desai', 21, 'M', 102),
(14, 'Nidhi Reddy', 23, 'F', 104),
(15, 'Omkar Pawar', 22, 'M', 101),
(16, 'Pooja Nair', 20, 'F', 103),
(17, 'Qasim Ansari', 21, 'M', 104),
(18, 'Riya Kapoor', 22, 'F', 101),
(19, 'Sahil Bansal', 23, 'M', 102),
(20, 'Tanvi Shetty', 21, 'F', 103),
(21, 'Ujjwal Singh', 22, 'M', 104),
(22, 'Vaishnavi Rao', 23, 'F', 101),
(23, 'Wasim Akhtar', 21, 'M', 102),
(24, 'Yashika Jain', 20, 'F', 103),
(25, 'Zaid Sheikh', 22, 'M', 104);


-- creating another table_2 with name "department"
CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);


-- inserting value to it
INSERT INTO departments VALUES
(101, 'Computer Science'),
(102, 'Mechanical Engg'),
(103, 'Electronics'),
(104, 'Civil Engg');


-- creating table_3 name "course_details"
CREATE TABLE course_details (
  course_id VARCHAR(10) PRIMARY KEY,
  course_name VARCHAR(50),
  instructor VARCHAR(50)
);


INSERT INTO course_details VALUES
('C101', 'Data Structures', 'Prof. Verma'),
('C102', 'Thermodynamics', 'Dr. Nair'),
('C103', 'Microprocessors', 'Prof. Iyer'),
('C104', 'Structural Engg', 'Dr. Chatterjee'),
('C105', 'Machine Learning', 'Prof. Kapoor');


-- creating table_3 name "courses"
CREATE TABLE courses (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id VARCHAR(10),
  marks INT
);

INSERT INTO courses VALUES
(1, 1, 'C101', 78),
(2, 1, 'C105', 82),
(3, 2, 'C102', 88),
(4, 3, 'C101', 69),
(5, 3, 'C105', 91),
(6, 4, 'C103', 85),
(7, 5, 'C102', 73),
(8, 6, 'C104', 77),
(9, 7, 'C101', 90),
(10, 8, 'C103', 65),
(11, 9, 'C104', 68),
(12, 10, 'C102', 89),
(13, 11, 'C105', 92),
(14, 12, 'C103', 76),
(15, 13, 'C102', 81),
(16, 14, 'C104', 70),
(17, 15, 'C101', 84),
(18, 16, 'C103', 88),
(19, 17, 'C104', 79),
(20, 18, 'C105', 93),
(21, 19, 'C102', 75),
(22, 20, 'C103', 66),
(23, 21, 'C104', 80),
(24, 22, 'C101', 87),
(25, 23, 'C102', 69),
(26, 24, 'C103', 72),
(27, 25, 'C104', 74);

-- ----------------------------------------------------------Examples--------------------------------------------------------

/*Using the tables in this dataset:

table_1:  students
table_2:  departments
table_3:  courses (student enrollments)
table_4:  course_details

Now examples of all types of JOINs using these tables.*/

-- 1. INNER JOIN
-- Returns records that have matching values in both tables.
-- Example: Get student names along with their department names

SELECT 
  s.name AS student_name, 
  d.department_name
FROM 
  students s
INNER JOIN 
  departments d ON s.department_id = d.department_id;

-- 2. LEFT JOIN (a.k.a. LEFT OUTER JOIN)
-- Returns all records from the left table, and matched records from the right table (NULL if no match).
-- Example: Get all students and their enrolled courses (if any)

SELECT 
  s.name AS student_name,
  c.course_id,
  c.marks
FROM 
  students s
LEFT JOIN 
  courses c ON s.student_id = c.student_id;


-- 3. RIGHT JOIN (a.k.a. RIGHT OUTER JOIN) (Not supported in SQLite)
-- Returns all records from the right table, and matched records from the left table.
-- Example: Get all courses and which students are enrolled (if any)

SELECT 
  s.name AS student_name,
  c.course_id,
  c.marks
FROM 
  courses c
RIGHT JOIN 
  students s ON s.student_id = c.student_id;


-- FULL OUTER JOIN
-- Returns all records when there is a match in one of the tables.
-- Example: Get full list of students and enrollments, even if they don't match

SELECT 
  s.name AS student_name,
  c.course_id,
  c.marks
FROM 
  students s
FULL OUTER JOIN 
  courses c ON s.student_id = c.student_id;
  -- Note: Not supported directly in MySQL or SQLite. In those systems, emulate it using UNION of LEFT and RIGHT JOINs.

 -- 5. CROSS JOIN
-- Returns the Cartesian product (every combination of rows).
-- Example: Every student paired with every course (for demonstration only)

SELECT 
  s.name AS student_name, 
  cd.course_name
FROM 
  students s
CROSS JOIN 
  course_details cd;


 -- 6. SELF JOIN
-- Table joins with itself.
-- Example: Find students in the same department

SELECT 
  A.name AS student1,
  B.name AS student2,
  A.department_id
FROM 
  students A
JOIN 
  students B ON A.department_id = B.department_id
WHERE 
  A.student_id < B.student_id;