Perfect!
I can expand your SQL Server practical lessons to intermediate level, keeping everything
beginner-friendly but including relationships, all SQL commands (DDL, DML, DQL, DROP, TRUNCATE,
etc.), and making it lesson-based with full SQL code. Here’s the updated structured lesson plan:
---
SQL Server Practical Lessons – Intermediate (Units 1–4)
Software Needed: SQL Server Express + SQL Server Management Studio (SSMS)
---
Lesson 1: Database Basics & Relationships (Unit 1)
Objective: Understand databases, tables, relationships, and basic SQL concepts.
Topics Covered:
1. What is a database?
2. What is SQL Server?
3. Creating a database and tables
4. Understanding relationships (Primary Key, Foreign Key)
Practical:
1. Create Database
CREATE DATABASE CollegeDB;
USE CollegeDB;
2. Create Tables with Relationships
Students Table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Age INT,
City NVARCHAR(50)
);
Courses Table
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName NVARCHAR(100),
Credits INT
);
Enrollments Table (Relationship Table)
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
3. Insert Sample Data
INSERT INTO Students VALUES
(1, 'Ola', 'Hansen', 20, 'Sandnes'),
(2, 'Tove', 'Svendson', 22, 'Sandnes'),
(3, 'Kari', 'Pettersen', 21, 'Stavanger');
INSERT INTO Courses VALUES
(101, 'Mathematics', 3),
(102, 'Physics', 4),
(103, 'Chemistry', 3);
INSERT INTO Enrollments VALUES
(1, 1, 101, '2025-01-10'),
(2, 2, 102, '2025-01-11'),
(3, 3, 103, '2025-01-12');
Practice Exercises:
Add a Teachers table and link it to Courses with a foreign key.
Insert 3 teachers and assign courses.
---
Lesson 2: Data Definition Language – DDL (Unit 2)
Objective: Learn to create, modify, delete tables, and manage database structure.
Topics Covered:
CREATE, ALTER, DROP, TRUNCATE, RENAME statements
Adding/removing columns and constraints
Practical:
1. Add Column
ALTER TABLE Students ADD Email NVARCHAR(100);
2. Modify Column
ALTER TABLE Students ALTER COLUMN LastName NVARCHAR(100);
3. Delete Column
ALTER TABLE Students DROP COLUMN Email;
4. Rename Table (if needed)
EXEC sp_rename 'Students', 'StudentInfo';
5. Drop Table
DROP TABLE Enrollments;
6. Truncate Table (delete all rows but keep table structure)
TRUNCATE TABLE Courses;
Practice Exercises:
Add a new column to Courses table for CourseDescription.
Try truncating and then re-inserting the Courses data.
---
Lesson 3: Data Manipulation Language – DML (Unit 3)
Objective: Learn to manage table data efficiently.
Topics Covered:
INSERT, UPDATE, DELETE
Filtering with WHERE, AND, OR
Handling NULL values
Practical:
1. Insert Data
INSERT INTO Students (StudentID, FirstName, LastName, Age, City)
VALUES (4, 'Tom', 'Nilsen', 23, 'Stavanger');
2. Update Data
UPDATE Students
SET Age = 24
WHERE FirstName = 'Tom';
3. Delete Data
DELETE FROM Students
WHERE FirstName = 'Tom';
4. Select with Filters
SELECT * FROM Students WHERE City='Sandnes';
SELECT * FROM Students WHERE Age >= 21 AND City='Stavanger';
SELECT * FROM Students WHERE FirstName='Tove' OR FirstName='Ola';
5. Handling NULL
SELECT * FROM Students WHERE Email IS NULL;
SELECT COALESCE(Email, 'No email') AS EmailInfo FROM Students;
Practice Exercises:
Update multiple student records at once.
Delete students who live in a certain city.
Select students older than the average age.
---
Lesson 4: Data Query Language – DQL (Unit 4)
Objective: Retrieve, sort, group, and analyze data.
Topics Covered:
SELECT, DISTINCT, ORDER BY, GROUP BY, HAVING
Aggregate functions: COUNT, SUM, AVG, MAX, MIN
Subqueries and joins
Practical:
1. Sorting Data
SELECT * FROM Students ORDER BY LastName ASC;
SELECT * FROM Students ORDER BY LastName DESC;
2. Aggregate Functions
SELECT COUNT(StudentID) AS TotalStudents FROM Students;
SELECT AVG(Age) AS AverageAge FROM Students;
SELECT MAX(Age) AS OldestStudent FROM Students;
3. Group By
SELECT City, COUNT(StudentID) AS NumberOfStudents
FROM Students
GROUP BY City;
4. Filter Groups Using HAVING
SELECT City, COUNT(StudentID) AS NumberOfStudents
FROM Students
GROUP BY City
HAVING COUNT(StudentID) > 1;
5. Subquery Example
SELECT * FROM Students
WHERE Age > (SELECT AVG(Age) FROM Students);
6. Join Example (Relationships)
SELECT [Link], [Link], [Link]
FROM Students s
JOIN Enrollments e ON [Link] = [Link]
JOIN Courses c ON [Link] = [Link];
Practice Exercises:
Find the course with the most students enrolled.
List students and their teachers.
Find students enrolled in multiple courses.
---
✅ Intermediate Tips:
Always use transactions for updates/deletes to prevent accidental data loss:
BEGIN TRANSACTION;
-- your update/delete statements
COMMIT;
-- or ROLLBACK if something goes wrong
Understand relationships to avoid foreign key errors.
Practice combining joins, aggregates, and subqueries for real-world queries.
---
If you want, I can make a full “Unit 1–4 SQL Project” where students create a college database, add all
tables, insert data, write queries, updates, deletes, joins, and aggregates, and I can provide the answers
separately as well.
Do you want me to do that next?