Joins and Subqueries

CSC-265 · Semester IV · Database Management System

intermediate35 minAvailable

SQLin-browser engine · runs locallyjoins

Ctrl Enter to run14 lines

Write SQL, then press Run. Result tables, affected-row counts and error messages appear here.

Lab brief

Objective

Write INNER, LEFT and self joins plus subqueries to answer multi-table questions, and verify equivalent queries return the same answer.

Theory & concepts

Joins combine rows from multiple tables on a condition. INNER JOIN keeps only matches; LEFT JOIN keeps all left rows padded with NULLs. Subqueries nest one query inside another - IN / NOT IN and EXISTS / NOT EXISTS answer membership questions, while a correlated subquery runs per outer row.

Rendering diagram…

Combining tables with joins or nested subqueries

SELECT STUDENT.Name, ENROLLS.Grade
FROM STUDENT
JOIN ENROLLS ON STUDENT.Sid = ENROLLS.Sid
WHERE ENROLLS.Cno = 'C1';

-- equivalent with a subquery
SELECT Name FROM STUDENT
WHERE Sid IN (SELECT Sid FROM ENROLLS WHERE Cno = 'C1');

Prerequisites

Lesson: JOIN queries in SQL
Lesson: Basic SQL queries: SELECT, FROM, WHERE

Instructions

1Load the starter STUDENT, COURSE and ENROLLS tables.
2Write an INNER JOIN that lists students with their course titles and grades.
3Rewrite the same answer using subqueries and compare the result sets.
4Run a LEFT JOIN and explain the NULL rows that appear.
5Perform a self join on EMPLOYEE to pair each employee with their manager.
6Answer the practice questions about when to use each construct.

Tasks

Inner join
List each student's name, course title and grade using an explicit JOIN.
Subquery equivalent
Answer the same question with IN and a nested SELECT; confirm identical results.
Left join
Show all students including those with no enrollments, using LEFT JOIN and NULL.
Self join
Pair employees with managers using aliases and a self join.

Self-check

All four queries return correct, complete results.
The student explains when NULLs appear in outer joins.
The student justifies choosing a join over a subquery or vice versa.
Self-join output correctly distinguishes employee from manager.

Environment

SQL consoleresult comparisonstarter schemapractice questions