SQL

1. Overview of SQL

Structured Query Language (SQL) is the standard programming language for managing relational databases. It allows users to create, read, update, and delete data. SQL provides a uniform interface for interacting with different database management systems such as PostgreSQL and MySQL.

Data Definition Language (DDL)

DDL commands define the structure of the database. These commands modify the schema rather than the data records.

       i.          CREATE: This command generates a new table.

Example: CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50));

     ii.          ALTER: This command modifies an existing table structure.

   iii.          DROP: This command deletes a table and all its data.

Data Manipulation Language (DML)

DML commands manage the data within the tables. These are the most frequent operations in daily database management.

       i.          INSERT: Adds new rows to a table.

Example: INSERT INTO Students (ID, Name) VALUES (1, 'John Smith');

     ii.          UPDATE: Modifies existing data in a table.

   iii.          DELETE: Removes specific rows from a table.

Data Query Language (DQL)

DQL consists of the SELECT statement. It retrieves specific data from one or more tables.

Syntax: SELECT column_name FROM table_name WHERE condition;

Usage: A bank uses DQL to display an account balance.

Example: SELECT Balance FROM Accounts WHERE AccountNumber = 101;

Practical Implementation Steps

To manage a database effectively, follow a standard sequence of operations.

1. Design: Identify the necessary entities and their attributes.
2. Creation: Use DDL to build the tables and define primary keys.
3. Population: Use DML to import or insert initial data sets.
4. Maintenance: Run updates and backups to ensure data integrity and protection.

 

Constraints in SQL

SQL enforces the integrity rules mentioned in Week 1 through specific keywords.

PRIMARY KEY: Ensures each row is unique.

NOT NULL: Prevents a column from being empty.

FOREIGN KEY: Links a record in one table to a record in another, maintaining referential integrity.

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.

3. ADVANCED DATA RELATIONSHIPS: JOINING TABLES

In a real-world student information system, data is normalized across multiple tables to reduce redundancy. This requires linking the Students table to a Courses table using a Foreign Key.

Schema Expansion (Create)

The system now requires a table for courses and a "junction" table to track which students are enrolled in which courses.

-- Create the Courses table

CREATE TABLE Courses (

    CourseID VARCHAR(10) PRIMARY KEY,

    CourseName VARCHAR(100) NOT NULL,

    Credits INT

);

-- Create the Enrollment table (Links Students and Courses)

CREATE TABLE Enrollment (

    EnrollmentID INT PRIMARY KEY,

    StudentID INT,

    CourseID VARCHAR(10),

    Semester VARCHAR(20),

    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),

    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

);

Population of Related Data (Create)

-- Insert courses into the catalog

INSERT INTO Courses (CourseID, CourseName, Credits)

VALUES

('CS101', 'Introduction to Databases', 3),

('MATH202', 'Linear Algebra', 4);

 

-- Enroll Marcus Chen (ID 202602) into CS101

INSERT INTO Enrollment (EnrollmentID, StudentID, CourseID, Semester)

VALUES (1, 202602, 'CS101', 'Spring 2026');

Data Integration (Read)

The JOIN command combines rows from two or more tables based on a related column. This is essential for generating transcripts or class lists.

-- Retrieve a list of students and the specific courses they are taking

SELECT Students.FirstName, Students.LastName, Courses.CourseName

FROM Students

JOIN Enrollment ON Students.StudentID = Enrollment.StudentID

JOIN Courses ON Enrollment.CourseID = Courses.CourseID;

CRUD Maintenance in Linked Tables

Update (U): If a course ID changes, the Enrollment table must reflect this to maintain Referential Integrity.

UPDATE Enrollment

SET CourseID = 'CS102'

WHERE EnrollmentID = 1;

Delete (D): When a student withdraws from a course, the record is removed from the Enrollment table, but the student remains in the Students table.

DELETE FROM Enrollment

WHERE StudentID = 202602 AND CourseID = 'CS101';

Summary of Relational Concepts

Primary Key: Uniquely identifies a record (e.g., StudentID).

Foreign Key: Points to a primary key in another table (e.g., Enrollment.StudentID).

Normalization: Dividing data into multiple tables to eliminate duplicate information.