Core DQL Operations

SQL Server provides powerful data retrieval capabilities through DQL (Data Query Language). This guide covers essential query techniques using a teaching management system database scenario.

Database Context

The teaching information management system includes four main tables:

• tblStudents — student information (StudentID, StudentName, Gender, Age, Department)
• tblTeachers — faculty details (TeacherID, TeacherName, Title, Department)
• tblCourses — course catalog (CourseID, CourseName, Credits)
• tblEnrollments — student course enrollments with grades (StudentID, CourseID, Grade)

Basic Query Operations

Field Selection and Filtering

Retrieve specific columns using SELECT with WHERE conditions for data filtering:

USE TeachingDB;
GO

-- Fetch computer science majors with descending age order
SELECT StudentID AS 'ID', StudentName AS 'Name', Gender AS 'Sex'
FROM tblStudents
WHERE Department = 'Computer Science'
ORDER BY Age DESC;

Result Set Limiting

Use TOP to restrict the number of returned rows:

-- Retrieve first 3 non-lecturer faculty members
SELECT TOP 3 TeacherName, Title, Department
FROM tblTeachers
WHERE Title <> 'Lecturer';

Deduplication and Sorting

DISTINCT removes duplicate values across all selected columns:

-- List unique credit values in ascending order
SELECT DISTINCT Credits AS 'Course Credits'
FROM tblCourses
ORDER BY Credits;

Combined Conditions

Chain multiple conditions with AND, applying descending sort on grade:

-- Find enrollment records for specific student with grade above 80
SELECT CourseID, Grade
FROM tblEnrollments
WHERE StudentID = '2023010101' AND Grade > 80
ORDER BY Grade DESC;

Aggregate Functions and Grouping

Aggregate functions compute summary statistics across data sets.

Counting Records

COUNT tallies rows with in groups defined by GROUP BY:

-- Count students per department
SELECT Department AS 'College', COUNT(StudentID) AS 'Enrollment'
FROM tblStudents
GROUP BY Department;

Statistical Calculations

AVG, MAX, and MIN compute central tendency and spread:

-- Calculate statistics for specific course
SELECT 
    'CS101 - Database Principles' AS 'Course',
    AVG(Grade) AS 'Average',
    MAX(Grade) AS 'Highest',
    MIN(Grade) AS 'Lowest'
FROM tblEnrollments
WHERE CourseID = 'CS101';

Grouped Aggregation with Sorting

-- Faculty count by title, ordered descending
SELECT Title, COUNT(TeacherID) AS 'Faculty Count'
FROM tblTeachers
GROUP BY Title
ORDER BY COUNT(TeacherID) DESC;

Sum Operations

SUM adds numeric values across filtered records:

-- Calculate total credits across all courses
SELECT SUM(Credits) AS 'Total Credits'
FROM tblCourses;

Subquery Techniques

Subqueries nest SELECT statements within outer queries to solve complex data requirements.

Subquery as Filter Condition (IN/NOT IN)

-- Find students enrolled in specific course using course name lookup
SELECT StudentID AS 'ID', Grade AS 'Score'
FROM tblEnrollments
WHERE CourseID IN (
    SELECT CourseID 
    FROM tblCourses 
    WHERE CourseName = 'Database Principles'
);

Existence Testing (EXISTS/NOT EXISTS)

EXISTS checks whether subquery returns any rows, without retrieving actual data:

-- Identify students with at least one grade exceeding 90
SELECT s.StudentName, s.Department
FROM tblStudents s
WHERE EXISTS (
    SELECT 1 
    FROM tblEnrollments e
    WHERE e.StudentID = s.StudentID AND e.Grade > 90
);

Subquery as Derived Table

-- Fetch instructors from specific department teaching high-credit courses
SELECT TeacherID, TeacherName, Title
FROM tblTeachers
WHERE Department = 'Computer Science'
AND TeacherID IN (
    SELECT DISTINCT TeacherID 
    FROM tblCourses 
    WHERE Credits > 3
);

Multi-Level Nesting

Execute innermost subquery first, then progressively evaluate outer layers:

-- Find mathematics department students with overall average above 80
SELECT s.StudentID, s.StudentName
FROM tblStudents s
WHERE s.Department = 'Mathematics & Statistics'
AND s.StudentID IN (
    SELECT StudentID
    FROM tblEnrollments
    GROUP BY StudentID
    HAVING AVG(Grade) > 80
);

Query Verification Strategies

Step-by-Step Validation

Break complex queries into sequential components to confirm intermediate results:

-- Verify subquery output first
SELECT CourseID FROM tblCourses WHERE CourseName = 'Database Principles';

-- Confirm enrollment data matches
SELECT StudentID, Grade FROM tblEnrollments WHERE CourseID = 'CS101';

Manual Cross-Reference

Compare query results against source table data:

-- Count records for specific department
SELECT COUNT(*) FROM tblStudents WHERE Department = 'Computer Science';

-- Verify grouping accuracy
SELECT Department, COUNT(*) FROM tblStudents GROUP BY Department;

Subquery Decomposition

Isolate each nested level to trace data flow:

-- Level 1: Calculate averages per student
SELECT StudentID, AVG(Grade) AS 'Average'
FROM tblEnrollments
GROUP BY StudentID
HAVING AVG(Grade) > 80;

-- Level 2: Apply department filter
SELECT StudentID, StudentName
FROM tblStudents
WHERE Department = 'Mathematics & Statistics';

Common Pitfalls

Missing GROUP BY Clause: Aggregate functions require GROUP BY for all non-aggregated SELECT columns. Forgetting this causes syntax errors.

Correlating Subquery Columns: Ensure subquery references outer query columns correctly. Omitting correlation conditions (like e.StudentID = s.StudentID) returns incorrect results.

DISTINCT Scope: DISTINCT applies to the entire row, not individual columns. Multiple columns in SELECT means duplicates require matching values across all columns.

EXISTS Efficiency: EXISTS stops searching upon first match, making it more efficient than retrieving full result sets for existence checks.

HAVING vs WHERE: WHERE filters rows before grouping; HAVING filters groups after aggregation. Use HAVING for conditions involving aggregate results.