EXPERIMENT-8: SQL Clauses and JOIN Operations

🎯 Objective

To understand the use of GROUP BY and HAVING clauses and execute SQL commands using JOIN operations to retrieve and manipulate data across multiple related tables.

📋 Database Schema

Student (sid, sname, age)

Description: Stores student information including ID, name, and age.

Match (mid, mname, venue)

Description: Contains match details including ID, name, and venue.

Play (sid, mid, day)

Description: Junction table linking students to matches with the date played.

💡 Key Concepts

INNER JOIN

Returns records that have matching values in both tables.

GROUP BY

Groups rows sharing a property so aggregate functions can be applied.

HAVING Clause

Filters grouped records based on aggregate function conditions.

DISTINCT

Eliminates duplicate rows from the result set.

📝 Implementation

Query 1

Populate All Tables

Create Tables:

-- Create Student Table CREATE TABLE Student ( sid VARCHAR(10) PRIMARY KEY, sname VARCHAR(50) NOT NULL, age INT ); -- Create Match Table CREATE TABLE Match ( mid VARCHAR(10) PRIMARY KEY, mname VARCHAR(50) NOT NULL, venue VARCHAR(50) ); -- Create Play Table CREATE TABLE Play ( sid VARCHAR(10), mid VARCHAR(10), day DATE, PRIMARY KEY(sid, mid, day), FOREIGN KEY(sid) REFERENCES Student(sid), FOREIGN KEY(mid) REFERENCES Match(mid) );

Insert Sample Data:

-- Insert Students INSERT INTO Student VALUES ('S001', 'Amit', 21), ('S002', 'Priya', 22), ('S003', 'Rahul', 20), ('S004', 'Neha', 23), ('S005', 'Vikram', 21); -- Insert Matches INSERT INTO Match VALUES ('B10', 'Cricket Championship', 'Delhi'), ('B11', 'Football League', 'Mumbai'), ('B12', 'Basketball Tournament', 'Delhi'), ('B13', 'Tennis Open', 'Bangalore'), ('B14', 'Badminton Cup', 'Mumbai'); -- Insert Play Records INSERT INTO Play VALUES ('S001', 'B10', '2024-03-15'), ('S001', 'B11', '2024-03-20'), ('S002', 'B10', '2024-03-15'), ('S002', 'B12', '2024-03-15'), ('S003', 'B11', '2024-03-20'), ('S003', 'B14', '2024-03-20'), ('S004', 'B12', '2024-03-18'), ('S005', 'B13', '2024-03-22');

Sample Data Overview:

Student Table
sid	sname	age
S001	Amit	21
S002	Priya	22
S003	Rahul	20
S004	Neha	23
S005	Vikram	21

Match Table
mid	mname	venue
B10	Cricket Championship	Delhi
B11	Football League	Mumbai
B12	Basketball Tournament	Delhi
B13	Tennis Open	Bangalore
B14	Badminton Cup	Mumbai

Query 2

Find all information of students who have played match number B10

SELECT S.* FROM Student S INNER JOIN Play P ON S.sid = P.sid WHERE P.mid = 'B10';

Explanation: This query uses INNER JOIN to combine Student and Play tables based on matching sid values, then filters for match B10. Returns all columns from Student table for matching records.

Expected Output:

sid	sname	age
S001	Amit	21
S002	Priya	22

Query 3

Find the name of matches played by Amit

SELECT M.mname FROM Student S INNER JOIN Play P ON S.sid = P.sid INNER JOIN Match M ON P.mid = M.mid WHERE S.sname = 'Amit';

Explanation: Uses two INNER JOINs to connect all three tables. First joins Student with Play on sid, then joins result with Match on mid. Filters for student name 'Amit'.

Expected Output:

mname
Cricket Championship
Football League

Query 4

Find the names of students who have played a match in Delhi

SELECT DISTINCT S.sname FROM Student S INNER JOIN Play P ON S.sid = P.sid INNER JOIN Match M ON P.mid = M.mid WHERE M.venue = 'Delhi';

Explanation: Similar to Query 3 but filters on venue instead of student name. DISTINCT ensures each student name appears only once even if they played multiple matches in Delhi.

Expected Output:

sname
Amit
Priya
Neha

Query 5

Find the names of students who have played at least one match

SELECT DISTINCT S.sname FROM Student S INNER JOIN Play P ON S.sid = P.sid;

Explanation: Simple INNER JOIN between Student and Play tables. INNER JOIN naturally filters out students who haven't played any match. DISTINCT removes duplicate names.

Expected Output:

sname
Amit
Priya
Rahul
Neha
Vikram

Query 6

Find the IDs and names of students who have played two different matches on the same day

SELECT S.sid, S.sname FROM Student S INNER JOIN Play P ON S.sid = P.sid GROUP BY S.sid, S.sname, P.day HAVING COUNT(DISTINCT P.mid) >= 2;

Explanation: This demonstrates GROUP BY and HAVING clauses. Groups records by student and day, then uses HAVING to filter groups where count of distinct matches is 2 or more. This identifies students playing multiple matches on the same day.

Expected Output:

sid	sname
S002	Priya
S003	Rahul

Query 7

Find the IDs of students who have played a match in Delhi or Mumbai

SELECT DISTINCT S.sid FROM Student S INNER JOIN Play P ON S.sid = P.sid INNER JOIN Match M ON P.mid = M.mid WHERE M.venue IN ('Delhi', 'Mumbai');

Explanation: Uses IN operator with WHERE clause to filter matches in Delhi OR Mumbai. DISTINCT ensures each student ID appears only once even if they played multiple matches in these cities.

Expected Output:

sid
S001
S002
S003
S004

Query 8

Find the average age of students

SELECT AVG(age) AS average_age FROM Student;

Explanation: Uses the AVG() aggregate function to calculate the average age of all students. This is a simple aggregation without GROUP BY, so it returns a single value for the entire table.

Expected Output:

average_age
21.4

🎓 Learning Outcomes

✓ GROUP BY Clause: Learned to group records for aggregate operations
✓ HAVING Clause: Applied conditions on grouped data using aggregate functions
✓ Aggregate Functions: Used COUNT(), AVG(), and DISTINCT for data analysis
✓ Multi-table Queries: Successfully queried data across three related tables
✓ Data Filtering: Applied WHERE, IN, and comparison operators effectively

📚 Practice Exercises

Exercise 1: Advanced Filtering

Find students who played matches in exactly two different venues.

Exercise 2: Complex Aggregation

Find the venue where the maximum number of matches were played, along with the count.

Exercise 3: Date Analysis

List all matches that had more than one student participating on the same day.

Exercise 4: Subqueries

Find students who are older than the average age and have played at least one match.

⚡ SQL Best Practices

1. Use Meaningful Aliases

Always use clear table aliases (S for Student, M for Match, P for Play) to improve readability.

2. Apply DISTINCT Appropriately

Use DISTINCT to eliminate duplicates when joining tables that may produce multiple matching rows.

3. Optimize JOIN Conditions

Always specify JOIN conditions using ON clause rather than WHERE clause for better query optimization.

4. HAVING vs WHERE

Use WHERE to filter rows before grouping, HAVING to filter groups after aggregation.

⚠️ Common Mistakes to Avoid

❌ Forgetting JOIN Conditions

Always specify the relationship between tables using ON clause to avoid Cartesian products.

❌ Using Aggregate Functions Without GROUP BY

When using aggregate functions with other columns, ensure proper GROUP BY clause is included.

❌ Ignoring NULL Values

Be aware that aggregate functions typically ignore NULL values, which can affect results.

❌ Not Using DISTINCT When Needed

JOINs can produce duplicate rows. Use DISTINCT when you need unique results.

🔄 SQL Query Execution Order

1

FROM & JOIN: Tables are combined first

2

WHERE: Rows are filtered based on conditions

3

GROUP BY: Remaining rows are grouped

4

HAVING: Groups are filtered

5

SELECT: Columns are selected and calculated

6

DISTINCT: Duplicate rows are removed

7

ORDER BY: Results are sorted (if specified)

📌 Summary

This experiment demonstrated the practical application of SQL JOIN operations combined with GROUP BY and HAVING clauses. Students learned to:

Create and populate relational database tables with proper constraints
Perform INNER JOIN operations to combine data from multiple tables
Use GROUP BY to aggregate data based on specific columns
Apply HAVING clause to filter aggregated results
Utilize aggregate functions like COUNT(), AVG(), and DISTINCT
Write complex multi-table queries with proper filtering conditions
Understand the difference between WHERE and HAVING clauses

📖 Additional Resources

📘 Reference Materials

Database System Concepts by Silberschatz, Korth & Sudarshan

💻 Online Practice

SQLZoo, LeetCode, HackerRank SQL challenges

🎥 Video Tutorials

Stanford DB Course, MySQL Tutorial for Beginners

📚 Documentation

Official MySQL, PostgreSQL, Oracle documentation

🗄️ EXPERIMENT-8

Use of Different SQL Clauses and JOIN Operations

🎯 Objective

📋 Database Schema

💡 Key Concepts

INNER JOIN

GROUP BY

HAVING Clause

DISTINCT

📝 Implementation

Populate All Tables

Create Tables:

Insert Sample Data:

Sample Data Overview:

Find all information of students who have played match number B10

Expected Output:

Find the name of matches played by Amit

Expected Output:

Find the names of students who have played a match in Delhi

Expected Output:

Find the names of students who have played at least one match

Expected Output:

Find the IDs and names of students who have played two different matches on the same day

Expected Output:

Find the IDs of students who have played a match in Delhi or Mumbai

Expected Output:

Find the average age of students

Expected Output:

🎓 Learning Outcomes

📚 Practice Exercises

Exercise 1: Advanced Filtering

Exercise 2: Complex Aggregation

Exercise 3: Date Analysis

Exercise 4: Subqueries

⚡ SQL Best Practices

1. Use Meaningful Aliases

2. Apply DISTINCT Appropriately

3. Optimize JOIN Conditions

4. HAVING vs WHERE

⚠️ Common Mistakes to Avoid

❌ Forgetting JOIN Conditions

❌ Using Aggregate Functions Without GROUP BY

❌ Ignoring NULL Values

❌ Not Using DISTINCT When Needed

🔄 SQL Query Execution Order

📌 Summary

📖 Additional Resources

📘 Reference Materials

💻 Online Practice

🎥 Video Tutorials

📚 Documentation