Learning of SQL Day 20

 

Day 20: JOIN Operations (INNER JOIN)

Introduction:

• The JOIN clause is used to combine rows from two or more tables based on a related column between them. The INNER JOIN returns only the rows that have matching values in both tables.

Key Concepts:

• INNER JOIN: Combines rows from two tables based on a condition, returning only the matching rows.

• JOIN Condition: The condition used to match rows from both tables.

SQL Commands and Examples:

1. Basic INNER JOIN:

   • Combine rows from two tables based on a common column.

sql

-- Assuming we have two tables: Students and Courses with a common column CourseID
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

2. INNER JOIN with Multiple Conditions:

   • Combine rows based on multiple conditions.

sql

-- Assuming Students table has columns CourseID and InstructorID and Courses table has columns CourseID and InstructorID
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID AND Students.InstructorID = Courses.InstructorID;

3. Combining INNER JOIN with WHERE Clause:

   • Filter the joined rows based on additional conditions.

sql

-- Join Students and Courses and select students enrolled in a specific course
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID
WHERE Courses.CourseName = 'Mathematics';

Practice Exercise:

1. Create two tables: Students with columns StudentID, FirstName, LastName, CourseID and Courses with columns CourseID, CourseName, Credits.

2. Write a query to join Students and Courses tables and select the StudentID, FirstName, and CourseName.

3. Write a query to join Students and Courses tables based on CourseID and Credits, and select the StudentID, FirstName, and CourseName.

4. Write a query to join Students and Courses tables and filter to show only students enrolled in 'Physics'.

sql

-- Create the Students table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    CourseID INT
);

-- Create the Courses table
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(255),
    Credits INT
);

-- Insert sample data into the Students table
INSERT INTO Students (StudentID, FirstName, LastName, CourseID)
VALUES
(1, 'Alice', 'Johnson', 101),
(2, 'Bob', 'Smith', 102),
(3, 'Charlie', 'Brown', 101);

-- Insert sample data into the Courses table
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES
(101, 'Mathematics', 4),
(102, 'Physics', 3),
(103, 'Chemistry', 3);

-- Join Students and Courses tables and select StudentID, FirstName, and CourseName
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

-- Join Students and Courses tables based on CourseID and Credits
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID AND Courses.Credits = 3;

-- Join Students and Courses tables and filter to show only students enrolled in 'Physics'
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID
WHERE Courses.CourseName = 'Physics';

Mastering INNER JOIN operations allows you to combine data from multiple tables effectively, providing richer insights and more comprehensive queries.