In this lab, you will dive into the practical applications of dynamic SQL$,$ a powerful feature that allows SQL queries to be constructed dynamically at runtime. This skill is essential for creating flexible database applications that can respond to user input or varying conditions without the need for hardcoded query logic.

Objective

You will learn to build dynamic SQL queries that can adapt based on input criteria such as course names and faculty names. This lab will guide you through creating queries that interact with a simplified educational institution database containing three related tables: cessection, course, and faculty.

Tables Structure

cessection: Contains records of course sections, including fields SectionID, CourseID $($links to course$),$ FacultyID $($links to faculty$),$ and other relevant information like Semester and Year.

course: Holds course details, including CourseID and Title of the course, and Credits for the course.

faculty: Stores faculty member information, including FacultyID and Name of the faculty member.

Lab Parts

This activity is divided into two main parts:

Part $1$: Start with constructing a basic dynamic SQL query to select all sections for a specific course based on the course name.

Part $2$: Extend your query to incorporate an additional filter criterion: faculty name. This will involve joining the cessection table with both the course and faculty tables and applying filters based on user input.

Instructions

Review PowerPoint notes and example code provided in Canvas.

Part $1$: Basic Dynamic SQL Query

Setup: Begin by declaring a variable to hold the course name you wish to filter on$.$ This simulates receiving user input in a real application.

Query Construction: Write a dynamic SQL statement that selects all sections of the specified course. Use proper SQL JOIN operations to link the cessection table with the course table.

Execution: Use sp$\_$executesql to execute your dynamic query safely, ensuring to parameterize the input to avoid SQL injection risks.