- Creating Base Tables
The CREATE TABLE statement is used to define a new table structure in the database.
Syntax Structure
CREATE TABLE table_name (
column_name1 data_type [constraints],
column_name2 data_type [constraints],
...
[table-level constraints]
);
Example: Creating Enrollment Table
CREATE TABLE Enrollment (
StudentID CHAR(9),
CourseID CHAR(4),
Score INT,
PRIMARY KEY(StudentID, CourseID),
FOREIGN KEY(StudentID) REFERENCES Student(StudentID),
FOREIGN KEY(CourseID) REFERENCES Course(CourseID)
);
Note: When the primary key consists of multiple columns, it must be defined as a table-level constraint. Foreign key constraints reference other tables to ensure referential integrity.
- Modifying Base Tables
The ALTER TABLE statement allows modifications to existing table structures. It supports adding new columns, adding constraints, and modifying column data types. However, it cannot directly rename tables, columns, or constraints.
ALTER TABLE Syntax
ALTER TABLE table_name
[ADD new_column_name data_type [integrity_constraints]]
[ADD integrity_constraint_definition]
[DROP constraint_name]
[ALTER COLUMN column_name new_data_type];
Example 1: Adding a New Column
ALTER TABLE Student ADD EnrollmentDate DATE;
Newly added columns will contain NULL values for all existing rows.
Example 2: Modifying Column Data Type
ALTER TABLE Student ALTER COLUMN Age INT;
Example 3: Adding Unique Constraint
ALTER TABLE Course ADD UNIQUE(CourseName);
Creating Various Constraints
NOT NULL Constraint:
ALTER TABLE table_name ALTER COLUMN column_name column_type NOT NULL;
Primary Key Constraint:
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(column);
Unique Constraint:
ALTER TABLE table_name ADD CONSTRAINT uq_name UNIQUE(column);
Check Constraint:
ALTER TABLE table_name ADD CONSTRAINT chk_name CHECK(Gender IN ('Male', 'Female'));
Foreign Key Constraint:
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY(column_name) REFERENCES ref_table(ref_column);
Extended Operations: Renaming Objects
Since ALTER TABLE cannot rename tables, columns, or constraints, use the following methods:
Method 1: Use the object explorer's right-click menu to rename directly.
Method 2: Use system stored procedures (use with caution):
1. Rename Table:
EXEC sp_rename 'OldTableName', 'NewTableName';
2. Rename Column:
EXEC sp_rename 'TableName.OldColumnName', 'NewColumnName', 'COLUMN';
3. Rename Index:
EXEC sp_rename 'TableName.OldIndexName', 'NewIndexName', 'INDEX';
- Deleting Base Tables
DROP TABLE table_name [RESTRICT | CASCADE];
RESTRICT: Deletion is restricted. The table cannot be deleted if its referenced by other tables or if dependent objects exist.
CASCADE: No restrictions. The table and all dependent objects will be deleted to gether.
- Index Operations
4.1 Creating Indexes
CREATE [UNIQUE] [CLUSTERED] INDEX index_name
ON table_name (column_name [ASC|DESC], ...);
UNIQUE: Creates a unique index where all values must be distinct.
CLUSTERED: Creates a clustered index that determines the physical order of data.
Example 1: Creating Clustered Index
CREATE CLUSTERED INDEX idx_student_name ON Student(Name);
Note: A table can have at most one clustered index. The primary key automatically becomes a clustered index.
Example 2: Creating Unique Indexes
CREATE UNIQUE INDEX idx_student_id ON Student(StudentID);
CREATE UNIQUE INDEX idx_course_id ON Course(CourseID);
CREATE UNIQUE INDEX idx_enrollment ON Enrollment(StudentID ASC, CourseID DESC);
4.2 Deleting Indexes
DROP INDEX index_name;
When an index is deleted, its description is removed from the data dictionary.
Example:
DROP INDEX idx_student_name;
4.3 Modifying Indexes
ALTER INDEX old_index_name RENAME TO new_index_name;
- Data Query Operations
SELECT Statement Syntax
SELECT [ALL|DISTINCT] target_expression [, target_expression ...]
FROM table_or_view [, table_or_view ...]
[WHERE condition_expression]
[GROUP BY column_name1 [HAVING condition_expression]]
[ORDER BY column_name2 [ASC|DESC]];
ALL: Default, returns all rows including duplicates.
DISTINCT: Eliminates duplicate rows from results.
ASC: Ascending order (default).
DESC: Descending order.
5.1 Single Table Queries
Selecting Specific Columns
/*Retrieve all student IDs and names, sorted by ID*/
SELECT StudentID, Name
FROM Student
ORDER BY StudentID;
/*Retrieve all student records*/
SELECT *
FROM Student;
/*Select specific columns*/
SELECT StudentID, Name, Gender, Age, Department
FROM Student;
/*Calculate birth year*/
SELECT Name, 2024 - Age AS BirthYear
FROM Student;
/*Using functions and aliases*/
SELECT Name, 'Birth Year:' AS Label, 2024 - Age AS BirthYear, LOWER(Department) AS Dept
FROM Student;
Query Conditions
| Condition Type | Operators/Predicates |
|---|---|
| Comparison | =, >, <, >=, <=, !=, <>, !>, !<; NOT + comparison operators |
| Range | BETWEEN AND, NOT BETWEEN AND |
| Set Membership | IN, NOT IN |
| Pattern Matching | LIKE, NOT LIKE |
| Null Values | IS NULL, IS NOT NULL |
| Logical Operators | AND, OR, NOT |
Comparison Operations
/*Find all students in Computer Science department*/
SELECT Name
FROM Student
WHERE Department = 'CS';
/*Find students younger than 20*/
SELECT Name, Age
FROM Student
WHERE Age < 20;
/*Find students with failing grades*/
SELECT DISTINCT StudentID
FROM Enrollment
WHERE Score < 60;
Range Queries
/*Find students aged between 20 and 23 (inclusive)*/
SELECT Name, Department, Age
FROM Student
WHERE Age BETWEEN 20 AND 23;
Set Membership Queries
/*Find students in IS, MA, or CS departments*/
SELECT Name, Gender
FROM Student
WHERE Department IN ('IS', 'MA', 'CS');
/*Find students NOT in IS, MA, or CS departments*/
SELECT Name, Gender
FROM Student
WHERE Department NOT IN ('IS', 'MA', 'CS');
Pattern Matching
/*Match exact string - find student with ID 202007*/
SELECT *
FROM Student
WHERE StudentID = '202007';
/*Find students whose names start with 'Liu'*/
SELECT Name, StudentID, Gender
FROM Student
WHERE Name LIKE 'Liu%';
/*Find students whose name is 3 characters starting with 'Ge'*/
SELECT Name
FROM Student
WHERE Name LIKE 'Ge__';
/*Escape special characters - find course named 'DB_Design'*/
SELECT CourseID, Credits
FROM Course
WHERE CourseName LIKE 'DB\_Design' ESCAPE '\';
/*Find courses starting with 'DB_' and ending with 'i' as the third from last*/
SELECT *
FROM Course
WHERE CourseName LIKE 'DB\_%i__' ESCAPE '\';
NULL Value Queries
/*Find enrollments with missing scores*/
SELECT StudentID, CourseID
FROM Enrollment
WHERE Score IS NULL;
/*Find enrollments with recorded scores*/
SELECT StudentID, CourseID
FROM Enrollment
WHERE Score IS NOT NULL;
Important: Use IS NULL or IS NOT NULL for NULL comparisons. The equals sign (=) cannot be used for NULL values.
Multiple Conditions
/*Find CS students under age 20*/
SELECT Name
FROM Student
WHERE Department = 'CS' AND Age < 20;
/*IN operator can be rewritten with OR*/
SELECT Name
FROM Student
WHERE Department IN ('IS', 'MA', 'CS');
ORDER BY Clause
/*Find students enrolled in course '2', sorted by score descending*/
SELECT StudentID, Score
FROM Enrollment
WHERE CourseID = '2'
ORDER BY Score DESC;
/*List all students by department (ascending), then by age (descending)*/
SELECT *
FROM Student
ORDER BY Department, Age DESC;
When sorting contains NULL values: ASC places NULLs last; DESC places NULLs first.
Aggregate Functions
/*Count total students*/
SELECT COUNT(*) AS TotalStudents
FROM Student;
/*Count students who enrolled in courses*/
SELECT COUNT(DISTINCT StudentID)
FROM Enrollment;
/*Calculate average score for course '2'*/
SELECT AVG(Score)
FROM Enrollment
WHERE CourseID = '2';
/*Find highest score in course '1'*/
SELECT MAX(Score)
FROM Enrollment
WHERE CourseID = '1';
/*Find total credits for student '202021'*/
SELECT SUM(Credits)
FROM Enrollment, Course
WHERE StudentID = '202021' AND Enrollment.CourseID = Course.CourseID;
GROUP BY Clause
/*Count students enrolled in each course*/
SELECT CourseID, COUNT(StudentID) AS EnrollmentCount
FROM Enrollment
GROUP BY CourseID;
/*Find students enrolled in 3 or more courses*/
SELECT StudentID
FROM Enrollment
GROUP BY StudentID
HAVING COUNT(*) >= 3;
Difference between HAVING and WHERE: WHERE filters rows before grouping; HAVING filters groups after grouping.
5.2 Join Queries
Equi-joins and Non-equi-joins
/*Retrieve each student with their enrollment records*/
SELECT Student.*, Enrollment.*
FROM Student, Enrollment
WHERE Student.StudentID = Enrollment.StudentID;
Self-joins
/*Find indirect prerequisites for each course*/
SELECT c1.CourseID, c1.CourseName, c2.CourseID AS PrereqID
FROM Course c1, Course c2
WHERE c1.PrerequisiteID = c2.CourseID;
/*Find students in the same department as 'Liu Chen'*/
SELECT s2.StudentID, s2.Name
FROM Student s1, Student s2
WHERE s1.Name = 'Liu Chen' AND s1.Department = s2.Department;
5.3 Nested Queries
IN Subqueries
/*Find students in the same department as 'Liu Chen'*/
/*This is an uncorrelated subquery - executes once*/
SELECT StudentID, Name, Department
FROM Student
WHERE Department IN (
SELECT Department
FROM Student
WHERE Name = 'Liu Chen'
);
Comparison Operator Subqueries
/*Find students whose average score exceeds the overall average*/
/*Correlated subquery - executes multiple times based on parent query*/
SELECT StudentID
FROM Enrollment
GROUP BY StudentID
HAVING AVG(Score) > (
SELECT AVG(Score)
FROM Enrollment
);
/*Find courses where the student scored above their own average*/
SELECT StudentID, CourseID
FROM Enrollment e1
WHERE Score >= (
SELECT AVG(Score)
FROM Enrollment e2
WHERE e2.StudentID = e1.StudentID
);
ANY/ALL Subqueries
/*Find students who did not enroll in course '2'*/
SELECT StudentID, Name
FROM Student
WHERE StudentID != ALL (
SELECT StudentID
FROM Enrollment
WHERE CourseID = '2'
);
/*Find the student with the highest average score*/
SELECT StudentID
FROM Enrollment
GROUP BY StudentID
HAVING AVG(Score) >= ALL (
SELECT AVG(Score)
FROM Enrollment
GROUP BY StudentID
);
- Data Update Operations
SQL provides three main data manipulation statements: INSERT, UPDATE, and DELETE.
- View Operations
Views are virtual tables based on the result of a query. They provide data abstraction and security.
Complete Example Code
/*Create database*/
CREATE DATABASE SchoolDB;
/*Create Student table - StudentID is primary key, Name must be unique*/
CREATE TABLE Student (
StudentID CHAR(9) PRIMARY KEY,
Name CHAR(20) UNIQUE,
Gender CHAR(2),
Age INT,
Department CHAR(20)
);
/*Create Course table*/
CREATE TABLE Course (
CourseID CHAR(4) PRIMARY KEY,
CourseName CHAR(40),
PrerequisiteID CHAR(4),
Credits INT,
FOREIGN KEY(PrerequisiteID) REFERENCES Course(CourseID)
);
/*Create Enrollment table with composite primary key*/
CREATE TABLE Enrollment (
StudentID CHAR(9),
CourseID CHAR(4),
Score INT,
PRIMARY KEY(StudentID, CourseID),
FOREIGN KEY(StudentID) REFERENCES Student(StudentID),
FOREIGN KEY(CourseID) REFERENCES Course(CourseID)
);
/*Insert sample data*/
INSERT INTO Student VALUES ('202007', 'Li Yong', 'Male', 20, 'CS');
INSERT INTO Student VALUES ('202021', 'Li Yong', 'Male', 20, 'CS');
INSERT INTO Student VALUES ('202022', 'Liu Chen', 'Female', 19, 'CS');
INSERT INTO Student VALUES ('202023', 'Wang Min', 'Female', 18, 'MA');
INSERT INTO Student VALUES ('202024', 'Ge Niu', 'Male', 19, 'CS');
INSERT INTO Student VALUES ('202025', 'Zhang Li', 'Male', 19, 'IS');
INSERT INTO Course VALUES ('1', 'Database', '5', 4);
INSERT INTO Course VALUES ('2', 'Mathematics', '', 4);
INSERT INTO Course VALUES ('3', 'Information Systems', '1', 4);
INSERT INTO Course VALUES ('4', 'Operating System', '6', 3);
INSERT INTO Course VALUES ('5', 'Data Structures', '7', 4);
INSERT INTO Enrollment VALUES ('202021', '1', 92);
INSERT INTO Enrollment VALUES ('202021', '2', 85);
INSERT INTO Enrollment VALUES ('202022', '2', 90);
INSERT INTO Enrollment VALUES ('202022', '3', 80);
INSERT INTO Enrollment VALUES ('202022', '4', NULL);
INSERT INTO Enrollment VALUES ('202023', '5', 89);
INSERT INTO Enrollment VALUES ('202023', '2', 86);
INSERT INTO Enrollment VALUES ('202023', '1', 98);
/*Query examples*/
SELECT StudentID, Name FROM Student ORDER BY StudentID;
SELECT * FROM Student;
SELECT Name, 2024 - Age AS BirthYear FROM Student;
SELECT Name, 'Birth Year:' AS Label, 2024 - Age AS BirthYear, LOWER(Department) AS Dept FROM Student;
SELECT DISTINCT StudentID FROM Enrollment WHERE Score < 60;
SELECT * FROM Student WHERE Age BETWEEN 20 AND 23;
SELECT * FROM Student WHERE StudentID = '202007';
SELECT Name FROM Student WHERE Name LIKE 'Liu%';
SELECT Name FROM Student WHERE Name LIKE 'Ge__';
SELECT StudentID, CourseID FROM Enrollment WHERE Score IS NULL;
SELECT StudentID, CourseID FROM Enrollment WHERE Score IS NOT NULL;
SELECT Name FROM Student WHERE Department = 'CS' AND Age < 20;
SELECT StudentID, Score FROM Enrollment WHERE CourseID = '2' ORDER BY Score DESC;
SELECT * FROM Student ORDER BY Department, Age DESC;
SELECT COUNT(*) FROM Student;
SELECT COUNT(DISTINCT StudentID) FROM Enrollment;
SELECT AVG(Score) FROM Enrollment WHERE CourseID = '2';
SELECT MAX(Score) FROM Enrollment WHERE CourseID = '1';
SELECT SUM(Credits) FROM Enrollment, Course WHERE StudentID = '202021' AND Enrollment.CourseID = Course.CourseID;
SELECT CourseID, COUNT(StudentID) FROM Enrollment GROUP BY CourseID;
SELECT StudentID FROM Enrollment GROUP BY StudentID HAVING COUNT(*) >= 3;