SQL

2. PRACTICAL IMPLEMENTATION: STUDENT INFORMATION SYSTEM

This guide provides a sequence of SQL commands to build and manage a functional student record system.

Database and Table Creation (Create)

The first step establishes the storage structure. The CREATE command defines the database and the specific tables within it.

-- Create the school database

CREATE DATABASE UniversityDB;

 

-- Select the database for use

USE UniversityDB;

 

-- Create the Students table with constraints

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    FirstName VARCHAR(50) NOT NULL,

    LastName VARCHAR(50) NOT NULL,

    EnrollmentDate DATE,

    Major VARCHAR(50),

    GPA DECIMAL(3, 2)

);

 

Data Population (Create)

The INSERT command adds individual records to the table. This represents the Capture phase of information management.

-- Insert multiple student records

INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate, Major, GPA)

VALUES

(202601, 'Elena', 'Rodriguez', '2026-01-15', 'Computer Science', 3.90),

(202602, 'Marcus', 'Chen', '2026-01-16', 'Mathematics', 3.45),

(202603, 'Sarah', 'Smith', '2026-01-16', 'Biology', 3.75);

 

Data Retrieval (Read)

The SELECT command allows for specific filtering of data. This demonstrates the Retrieval function.

-- Retrieve all data for a specific major

SELECT * FROM Students

WHERE Major = 'Computer Science';

 

-- Retrieve only names and GPAs for high-performing students

SELECT FirstName, LastName, GPA

FROM Students

WHERE GPA >= 3.5;

 

Data Modification (Update)

The UPDATE command changes existing records. This is necessary when student statuses change, such as a change in Major or GPA.

-- Change Marcus Chen's major to Physics

UPDATE Students

SET Major = 'Physics'

WHERE StudentID = 202602;

 

-- Increase GPA for Sarah Smith

UPDATE Students

SET GPA = 3.85

WHERE StudentID = 202603;

 

Data Removal (Delete)

The DELETE command removes records. Use the WHERE clause to avoid deleting all data in the table.

-- Remove a specific student record by ID

DELETE FROM Students

WHERE StudentID = 202601;

 

-- Remove all students with a GPA below a certain threshold

DELETE FROM Students

WHERE GPA < 2.0;

 

Summary of SQL Operations

SQL Command	CRUD Mapping	System Action
CREATE TABLE	Create (Schema)	Defines structure.
INSERT INTO	Create (Data)	Adds new records.
SELECT	Read	Fetches data for viewing.
UPDATE	Update	Modifies existing data.
DELETE	Delete	Removes records.