Assignment: SQL Server Development & Copilot Mastery (Local)
Duration: 2.5 - 3 Hours
Target Environment: Local SQL Server (Express/Developer Edition)
Topics: T-SQL Foundations, Programmability (SPs/Functions), Indices, Temporal Tables,
and JSON Parsing.
Module 1: Environment & Database Foundations (45 mins)
Objective: Set up your local environment, define the schema, and manage data using DML.
Task 1.1: Database & Schema Creation
1. Open SQL Server Management Studio (SSMS) and connect to your local instance
((local) or .\SQLEXPRESS).
2. Create a new database named AssignmentDB.
3. Create the following tables:
– Departments: DepartmentID (PK), DepartmentName, Location.
– Employees: EmployeeID (PK), FirstName, LastName, DepartmentID (FK),
Salary, HireDate.
[!TIP] Copilot Prompt: “Create a T-SQL script to define two tables in a database
named AssignmentDB: Departments (with an identity primary key) and
Employees (with a foreign key to Departments). Include basic constraints like
NOT NULL.”
Task 1.2: Data Ingestion
Insert at least 5 records into Departments and 10 records into Employees.
• Ensure some employees belong to the same department.
• Include some employees with high salaries for later testing.
[!TIP] Copilot Prompt: “Generate INSERT statements for 5 departments and 10
diverse employees for the tables created above. Ensure realistic names and
balanced salary distributions.”
Module 2: Advanced Querying & Set Operations (45 mins)
Objective: Master complex data retrieval using Joins, Subqueries, and Set Operations.
Task 2.1: Multi-Table Joins
Write a query to list all employees along with their department names and locations. Use
an INNER JOIN.
Task 2.2: Correlated Subqueries
Find all employees whose salary is higher than the average salary of their respective
department.
[!TIP] Copilot Prompt: “Write a T-SQL query using a correlated subquery to find
employees who earn more than the average salary in their own department.”
Task 2.3: Set Operations
Assuming you have a Suppliers table (or similar), use INTERSECT and EXCEPT to find
common cities between employees and suppliers.
[!TIP] Copilot Prompt: “Explain the difference between UNION and UNION ALL
with an example query comparing two lists of cities.”
Module 3: Programmability (SPs & Functions) (45 mins)
Objective: Encapsulate logic using Stored Procedures and User-Defined Functions.
Task 3.1: Stored Procedure with Parameters
Create a Stored Procedure GetEmployeesByDept that takes a DepartmentID as an input
parameter and returns the list of employees in that department.
Task 3.2: SP with Output Parameters
Create an SP GetTotalDeptSalary that takes a DepartmentID and returns the total sum
of salaries for that department via an OUTPUT parameter.
[!TIP] Copilot Prompt: “Create a T-SQL Stored Procedure that accepts an input
DepartmentID and returns the total salary sum as an output parameter.”
Task 3.3: Scalar & Table-Valued Functions
• Create a Scalar Function to format employee names as “LastName, FirstName”.
• Create an Inline Table-Valued Function that returns all employees hired after a
specific date.
Module 4: Advanced Features & Optimization (45 mins)
Objective: Implement history tracking, JSON parsing, and performance tuning.
Task 4.1: System-Versioned Temporal Tables
Enable system versioning on the Employees table.
• Perform an update on a salary and query the EmployeeHistory table.
[!TIP] Copilot Prompt: “Generate the T-SQL syntax to convert an existing
Employees table into a system-versioned temporal table.”
Task 4.2: JSON Handling
Parse a JSON string containing employee skills (e.g., '{"skills": ["SQL", "Azure",
"Python"]}') and return it as a relational table using OPENJSON.
[!TIP] Copilot Prompt: “How do I use OPENJSON in T-SQL to parse a JSON array
into separate rows?”
Submission Requirements
1. A single .sql script containing all your creation and logic code.
2. A document containing screenshots of successful execution for each module in
SSMS.