Advance

Subquery

Skip repeating the age calculation

SELECT *
FROM (
  SELECT *, DATEDIFF(year, Birthdate, GETDATE()) AS AGE
  FROM Students
) AS S
WHERE S.AGE < 20

The courses that a specific student is not enrolled in

SELECT CourseID, Name
FROM Courses
WHERE CourseID NOT IN (
  SELECT CourseID FROM Enrollments WHERE StudentID = 3
);

The courses that have students enrolled in

SELECT * FROM Courses
WHERE EXISTS (SELECT * FROM Enrollments WHERE CourseID = Courses.CourseID)

Window Function

Top 3 courses with the most students enrolled, along with the number of students enrolled in each course

SELECT Name, EnrollmentCount
FROM (
  SELECT Name, COUNT(*) AS EnrollmentCount,
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS RowNumber
  FROM Courses
  JOIN Enrollments ON Courses.CourseID = Enrollments.CourseID
  GROUP BY Courses.CourseID, Courses.Name
) subquery
WHERE RowNumber <= 3;

Common Table Expression

List of all students who are older than the average age

WITH StudentData AS (
    SELECT AVG(Age) AS AvgAge
    FROM Students
)
SELECT Name, Age
FROM Students
WHERE Age > (SELECT AvgAge FROM StudentData)

Stored procedure

Retrieve students by age and name

CREATE PROC GetStudentsByAgeAndName
    @age INT,
    @name VARCHAR(50)
AS
BEGIN
    SELECT *
    FROM Students
    WHERE Age = @age AND Name LIKE '%' + @name + '%'
END

EXEC GetStudentsByAgeAndName @age = 20, @name = 'John';

ALTER PROC GetStudentsByAgeAndName
    @age INT,
    @name VARCHAR(50)
AS
BEGIN
    SELECT *, DATEDIFF(year, Birthdate, GETDATE()) AS AGE
    FROM Students
    WHERE DATEDIFF(year, Birthdate, GETDATE()) = @age AND Name LIKE '%' + @name + '%'
END

DROP PROC GetStudentsByAgeAndName;

Count students by birth year

CREATE PROC CountStudentsByYear
    @year INT,
    @num INT OUTPUT
AS
BEGIN
    SELECT @num = COUNT(*)
    FROM Students
    WHERE YEAR(Birthdate) = @year
END

DECLARE @t INT
EXEC CountStudentsByYear 2002, @t output
SELECT @t AS NumberOfStudents

Trigger

Updates the "EnrollmentDate" column in the "Enrollments" table to the current date whenever an existing row is updated

CREATE TRIGGER UpdateEnrollmentDate
ON Enrollments
AFTER UPDATE
AS
BEGIN
    UPDATE Enrollments
    SET EnrollmentDate = GETDATE()
    FROM Enrollments
    WHERE Enrollments.EnrollmentID = Enrollments.EnrollmentID
END