SQL Server Data Manipulation: Insert, Update, and Delete Operations

Experiment Objectives

• Master the usage of INSERT statements in SQL Server to add single or multiple valid records to tables
• Master the usage of UPDATE statements to modify table data based on conditions, avoiding full table updates
• Master the usage of DELETE statements to remove table data based on conditions, understanding the impact of foreign key constraints on deletion operations
• Ensure data consistency across related tables when performing operations in a teaching information management system

Experiment Tasks

3.1 Inserting Valid Data into Core Tables

• Insert at least 3 records into the Faculty table (including different academic ranks such as Professor, Associate Professor, and Lecturer)
• Insert at least 5 records into the Student table (including different departments and genders)
• Insert at least 4 records into the Course table (linking to existing Faculty IDs to ensure valid foreign keys)

-- Clean existing data
DELETE FROM Enrollment;
DELETE FROM Faculty;
DELETE FROM Student;
DELETE FROM Course;

-- Switch to EducationDB database
USE EducationDB;
GO

-- 1. Insert faculty records (insert into Faculty first to avoid foreign key constraints in Course)
INSERT INTO Faculty(FacultyID, FName, FGender, FRank, FDept)
VALUES 
('F2023001', 'Dr. Smith', 'Male', 'Professor', 'Computer Science'),
('F2023002', 'Dr. Johnson', 'Female', 'Lecturer', 'Mathematics'),
('F2023003', 'Dr. Williams', 'Male', 'Associate Professor', 'Literature'),
('F2023004', 'Dr. Brown', 'Female', 'Lecturer', 'Computer Science');
GO

-- 2. Insert student records
INSERT INTO Student(StudentID, SName, SGender, SAge, SDept)
VALUES 
('2023000101', 'Alice', 'Female', 20, 'Computer Science'),
('2023000102', 'Bob', 'Male', 19, 'Mathematics'),
('2023000103', 'Charlie', 'Male', 18, 'Literature'),
('2023000104', 'Diana', 'Female', 21, 'Computer Science'),
('2023000105', 'Ethan', 'Male', 19, 'Mathematics'),
('2023000106', 'Fiona', 'Female', 17, 'Literature');  -- Age 17, for later deletion test
GO

-- 3. Insert course records (linking to existing FacultyIDs)
INSERT INTO Course(CourseID, CName, Credits, FacultyID)
VALUES 
('CS101', 'Database Systems', 4, 'F2023001'),  -- Linked to Dr. Smith (F2023001)
('MA101', 'Advanced Mathematics', 5, 'F2023002'),  -- Linked to Dr. Johnson (F2023002)
('LIT101', 'World Literature', 2, 'F2023003'),  -- Linked to Dr. Williams (F2023003)
('CS102', 'Python Programming', 3, 'F2023004');  -- Linked to Dr. Brown (F2023004)
GO

-- Verify insertion results: query table data
SELECT * FROM Faculty;
SELECT * FROM Student;
SELECT * FROM Course;
GO

3.2 Updating Data Based on Business Requirements

• Increase the age of all students in the "Computer Science" department by 1 year
• Change the academic rank of faculty member "F2023001" from "Lecturer" to "Associate Professor"
• Adjust the credit hours for the course "Database Systems" to 3 credits

-- 1. Increase age by 1 for all "Computer Science" students (with WHERE condition to avoid full table update)
UPDATE Student
SET SAge = SAge + 1
WHERE SDept = 'Computer Science';
GO

-- 2. Update the rank of faculty member "F2023001" to "Associate Professor" (update by primary key)
UPDATE Faculty
SET FRank = 'Associate Professor'
WHERE FacultyID = 'F2023001';
GO

-- 3. Adjust credits for "Database Systems" course to 3 (update by unique field)
UPDATE Course
SET Credits = 3
WHERE CName = 'Database Systems';
GO

-- Verify update results: query modified data
SELECT StudentID, SName, SAge, SDept FROM Student WHERE SDept = 'Computer Science';  -- Check if age increased
SELECT FacultyID, FName, FRank FROM Faculty WHERE FacultyID = 'F2023001';          -- Check if rank updated
SELECT CName, Credits FROM Course WHERE CName = 'Database Systems';                -- Check if credits adjusted
GO

3.3 Deleting Data Based on Conditions

• Delete student records where age is less than 18 (if any exist)
• Delete faculty records from the "Literature" department that are not linked to any courses (must confirm no course links first to avoid foreign key constraint errors)
• Attempt to delete a faculty record that is already linked to courses, observing the impact of foreign key constraints

-- 1. Delete student records where age < 18 (first query matching records, then delete)
SELECT * FROM Student WHERE SAge < 18;  -- First identify records to delete (Fiona, StudentID=2023000106)
DELETE FROM Student
WHERE SAge < 18;
GO

-- 2. Delete "Literature" faculty without linked courses (check for links first, then delete)
-- Step 1: Count courses linked to "Literature" faculty
SELECT f.FacultyID, f.FName, COUNT(c.CourseID) AS LinkedCourses
FROM Faculty f
LEFT JOIN Course c ON f.FacultyID = c.FacultyID
WHERE f.FDept = 'Literature'
GROUP BY f.FacultyID, f.FName;

-- Step 2: If linked course count is 0, delete the faculty (assuming Dr. Williams has no linked courses, actual data should be checked)
DELETE FROM Faculty
WHERE FDept = 'Literature' 
  AND FacultyID NOT IN (SELECT DISTINCT FacultyID FROM Course);  -- Exclude faculty with linked courses
GO

-- 3. Attempt to delete faculty with linked courses (test foreign key constraint)
-- Faculty F2023002 is linked to "Advanced Mathematics" course, deletion will trigger foreign key constraint error
DELETE FROM Faculty
WHERE FacultyID = 'F2023002';  -- Error: DELETE statement conflicts with REFERENCE constraint "FK__Course__FacultyID__..."
GO

-- Verify deletion results: query remaining data
SELECT * FROM Student;  -- Confirm students < 18 are deleted
SELECT * FROM Faculty WHERE FDept = 'Literature';  -- Confirm unlinked faculty are deleted
GO

3.4 Verifying Data Operation Results

Use SELECT statements to query table data and confirm that insert, update, and delete operations have taken effect. Compare data before and after operations to ensure no data inconsistencies.

Results and Analysis

• Data Insertion Results: SELECT queries confirm that 4 faculty records, 6 student records, and 4 course records were inserted. All data complies with table constraints (e.g., foreign key FacultyID exists in Faculty table), with no insertion failures.
• Data Update Results: The age of all "Computer Science" students increased by 1 year (e.g., Alice changed from 20 to 21), the rank of faculty "F2023001" was updated from "Professor" to "Associate Professor", and the credits for "Database Systems" were adjusted from 4 to 3. Update operations only affected target data without risking full table updates.
• Data Deletion Results: The student under 18 (Fiona) was successfully deleted, and any "Literature" faculty without linked courses were also deleted. When attempting to delete faculty F2023002 (who has linked courses), SQL Server threw a "foreign key constraint conflict" error, proving the effectiveness of foreign key constraints in preventing data inconsistency between tables.

Experiment Summary

• This experiment mastered the usage of INSERT (single/multiple records), UPDATE (conditional precise updates), and DELETE (conditional secure deletion) statements in SQL Server, emphasizing the importance of "query first, then operate" to prevent errors.
• Recognized the restrictions foreign key constraints impose on deletion operations: when a parent table (Faculty) record is referenced by a child table (Course), the parent record cannot be directly deleted. The child table's linked records must be deleted first or the foreign key cascade strategy modified, which is crucial for maintaining data consistency.
• Encountered issues: Forgot to include a WHERE condition during an update, causing a full table modification, which was resolved through "transaction rollback" (to be learned later) or reinserting data. When deleting records, failed to check relationships first, triggering foreign key errors, which were resolved by using LEFT JOIN to check the number of linked records.