Datbaase Schema Setup

The following SQL script creates a student information database with four tables: major, stu (students), cou (courses), and sc (student-course enrollments).

-- Create and use database
DROP DATABASE IF EXISTS stuinfo;
CREATE DATABASE stuinfo;
USE stuinfo;

-- Majors table
CREATE TABLE major (
    mno INT PRIMARY KEY,
    mname VARCHAR(30)
);

-- Students table
CREATE TABLE stu (
    sno VARCHAR(13) PRIMARY KEY,
    sname VARCHAR(30) NOT NULL,
    age INT,
    sex BIT,
    mno INT,
    FOREIGN KEY (mno) REFERENCES major(mno)
);

-- Courses table
CREATE TABLE cou (
    cno VARCHAR(13) PRIMARY KEY,
    cname VARCHAR(30) NOT NULL,
    ctime INT,
    ccredit DECIMAL(5,2)
);

-- Enrollments table
CREATE TABLE sc (
    cno VARCHAR(13),
    sno VARCHAR(13),
    grade DECIMAL(5,2),
    PRIMARY KEY (cno, sno),
    FOREIGN KEY (sno) REFERENCES stu(sno)
);

-- Add foreign key for course
ALTER TABLE sc 
ADD CONSTRAINT fk_sc_cno 
FOREIGN KEY (cno) REFERENCES cou(cno);

Sample Data Insertion

Example data is inserted into each table to demonstrate queries.

-- Majors
INSERT INTO major VALUES 
(1, 'Computer Science'), 
(2, 'Software Engineering'), 
(3, 'Network Engineering'), 
(4, 'Intelligent Science');

-- Students
INSERT INTO stu VALUES 
('2020001', 'Xiao Yi', 18, 0, 1),
('2020002', 'Xiao Er', 18, 1, 1),
('2020005', 'Xiao Liu', 18, 0, 2),
('2020009', 'Xiao Shi', 19, 0, 3),
('20200013', 'Xiao Yu', 19, 0, NULL),
('20200014', 'Xiao Dian', 19, 0, 4);

-- Courses
INSERT INTO cou VALUES 
('20201', 'C Language', 32, 5.0),
('20202', 'C#', 32, 3.0),
('20203', 'Data Structures', 16, 5.0),
('20204', 'College English 1', 32, 3.5);

-- Enrollments
INSERT INTO sc (sno, cno, grade) VALUES 
('2020001', '20201', 90),
('2020002', '20201', 90),
('2020004', '20201', 58),
('2020004', '20202', 98),
('2020005', '20203', 90),
('2020006', '20201', NULL);

Basic DDL and DML Operations

-- Add/drop column
ALTER TABLE stu ADD qq VARCHAR(20);
ALTER TABLE stu DROP COLUMN qq;

-- Create/drop table
CREATE TABLE temp_table (id INT);
DROP TABLE temp_table;

Single-Table Queries

-- Basic selection
SELECT sno, sname FROM stu;
SELECT sname, 2024 - age AS birth_year FROM stu;

-- Filtering
SELECT * FROM stu WHERE age BETWEEN 18 AND 19;
SELECT * FROM stu WHERE mno IN (1, 2, 4);
SELECT * FROM stu WHERE sname LIKE 'Peng%';
SELECT * FROM sc WHERE grade IS NULL;

-- Distinct values
SELECT DISTINCT sno FROM sc;

Sorting, Aggregation, and Grouping

-- Sorting
SELECT * FROM sc ORDER BY grade DESC;

-- Aggregation
SELECT COUNT(*) FROM stu;
SELECT COUNT(grade) FROM sc;  -- Ignores NULLs

-- Grouped results
SELECT cno, COUNT(sno) AS enrollment_count 
FROM sc 
GROUP BY cno;

-- Filter groups
SELECT sno, AVG(grade) AS avg_score 
FROM sc 
GROUP BY sno 
HAVING AVG(grade) >= 90;

Multi-Table Queries

-- Implicit join (Cartesian product with condition)
SELECT stu.sname, cou.cname, sc.grade
FROM stu, sc, cou
WHERE stu.sno = sc.sno AND sc.cno = cou.cno;

-- Explicit LEFT JOIN
SELECT stu.sname, sc.cno, sc.grade
FROM stu
LEFT JOIN sc ON stu.sno = sc.sno;

-- Majors with zero students
SELECT m.mno, m.mname, COUNT(s.sno) AS student_count
FROM major m
LEFT JOIN stu s ON m.mno = s.mno
GROUP BY m.mno, m.mname;

Nested and Set Queries

-- Subquery with IN
SELECT sname FROM stu 
WHERE sno IN (SELECT sno FROM sc WHERE cno = '20201');

-- Correlated subquery
SELECT sname FROM stu 
WHERE EXISTS (
    SELECT 1 FROM sc 
    WHERE sc.sno = stu.sno AND cno = '20201'
);

-- Set operations
SELECT sno FROM sc WHERE cno = '20201'
UNION
SELECT sno FROM sc WHERE cno = '20203';

Views

Views provide virtual tables based on underlying base tables.

-- Student basic info
CREATE VIEW v_student_info AS
SELECT sno, sname, age FROM stu;

-- Student with major name
CREATE VIEW v_student_major AS
SELECT s.sno, s.sname, m.mname
FROM stu s
LEFT JOIN major m ON s.mno = m.mno;

-- Average grades per student
CREATE VIEW v_student_avg AS
SELECT sno, AVG(grade) AS avg_grade
FROM sc
GROUP BY sno;

Stored Procedures

-- Parameterized procedure
CREATE PROCEDURE GetStudentCourseInfo 
    @student_id VARCHAR(13), 
    @course_id VARCHAR(13)
AS
BEGIN
    SELECT sc.*, cou.ccredit
    FROM sc
    JOIN cou ON sc.cno = cou.cno
    WHERE sc.sno = @student_id AND sc.cno = @course_id;
END;

EXEC GetStudentCourseInfo '2020004', '20203';

Triggers

-- Prevent insertion if total students exceed 17
CREATE TRIGGER trg_limit_students
ON stu
AFTER INSERT
AS
BEGIN
    IF (SELECT COUNT(*) FROM stu) > 17
    BEGIN
        ROLLBACK TRANSACTION;
        PRINT 'Maximum student limit exceeded.';
    END
END;

-- Auto-adjust borderline grades (55–59 → 60)
CREATE TRIGGER trg_adjust_grades
ON sc
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @sno VARCHAR(13), @cno VARCHAR(13), @grade DECIMAL(5,2);
    SELECT @sno = sno, @cno = cno, @grade = grade FROM inserted;
    
    IF @grade BETWEEN 55 AND 59
        SET @grade = 60;
        
    INSERT INTO sc (sno, cno, grade) VALUES (@sno, @cno, @grade);
END;

User-Defined Functinos

-- Scalar function: average grade for a course
CREATE FUNCTION GetCourseAvg(@course_id VARCHAR(13))
RETURNS DECIMAL(5,2)
AS
BEGIN
    DECLARE @avg DECIMAL(5,2);
    SELECT @avg = AVG(grade) FROM sc WHERE cno = @course_id;
    RETURN @avg;
END;

-- Table-valued function: students in a major
CREATE FUNCTION GetStudentsByMajor(@major_id INT)
RETURNS TABLE
AS
RETURN (
    SELECT sno, sname FROM stu WHERE mno = @major_id
);

Indexes

Indexes improve query performance but incur maintenance overhead.

-- Unique composite index on sc
CREATE UNIQUE INDEX idx_sc_enrollment 
ON sc (sno ASC, cno DESC);

-- Avoid indexing on low-cardinality columns (e.g., sex)

Cursors

Cursors enable row-by-row processing of result sets.

-- Assign grade ranks using cursor
ALTER TABLE sc ADD sc_rank CHAR(1);

DECLARE grade_cursor CURSOR FOR 
    SELECT sno, cno, grade FROM sc;

DECLARE @s VARCHAR(13), @c VARCHAR(13), @g DECIMAL(5,2);
OPEN grade_cursor;
FETCH NEXT FROM grade_cursor INTO @s, @c, @g;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @rank CHAR(1) = 
        CASE 
            WHEN @g >= 80 THEN 'A'
            WHEN @g >= 70 THEN 'B'
            WHEN @g IS NOT NULL THEN 'C'
            ELSE NULL
        END;
        
    UPDATE sc SET sc_rank = @rank 
    WHERE sno = @s AND cno = @c;
    
    FETCH NEXT FROM grade_cursor INTO @s, @c, @g;
END;

CLOSE grade_cursor;
DEALLOCATE grade_cursor;