Student Database Example From Design Using Simple SQL Statements
Home > database design > student database
Student Database Design
To start our student database we have very simple, basic but essential tables and relationships.
The design is easy to see in out Entity Relationship Diagram (ER diagram or ERD).
We can add attributes to our entities as seen below.
relational database
Create a Student Database
To start we are going to keep it simple and create the student table and the course table.
CREATE TABLE student (
student_id INT PRIMARY KEY,
student_name VARCHAR(100)
);
CREATE TABLE course (
course_code INT PRIMARY KEY,
course_name VARCHAR(100),
course_date VARCHAR(10)
);
SQL Insert Student Table Data
In real life, we would automate the process of entering large quantities of data, or have a form to enter records manually. But, in SQL, we can use the ‘INSERT’ statement to enter the students details
We use an SQL statement to insert the student table data.
(652415501, ‘Zhang Wei’),
(652415502, ‘Chen Mei’),
(652415503, ‘Li Hua’),
(652415504, ‘Somchai Prasert’),
(652415505, ‘Wanchai Suthep’),
(652415506, ‘Liu Xiaojun’),
(652415507, ‘Kritsada Anan’),
(652415508, ‘Wang Fang’),
(652415509, ‘Napat Siriwat’),
(652415510, ‘Zhang Min’),
(652415511, ‘Li Xiaomei’);
SQL Insert Course Table Data
Lets also enter the course details.
(888342, ‘Management Information Systems’, ‘2024-2’),
(887231, ‘Business Analytics’, ‘2024-2’),
(881234, ‘Marketing Principles’, ‘2024-2’),
(888341, ‘Data Science Essentials’, ‘2024-2’),
(883123, ‘Financial Accounting’, ‘2024-1’),
(884567, ‘Operations Management’, ‘2024-1’),
(885678, ‘Human Resource Management’, ‘2024-2’),
(886345, ‘Supply Chain Management’, ‘2024-1’),
(887654, ‘International Business’, ‘2024-2’),(888120, ‘Corporate Strategy’, ‘2024-1’);
Data Manipulation Language
We have our database with data but we need to be able to do things with the data and importantly query the data. We can use SQL select statements to access the data and decide what data to see.
We have three main parts:
- SELECT – what columns you want to see
- FROM – from which tables
- WHERE – which rows
SQL Select Statements
SQL Select
Simple select statement
from student;
SQL Select all
Select statement that select all the columns
from student;
SQL order by
Select statement that orders the records in ascending or alphabetical order
from student
order by student_name;
SQL order by descending
Select statement that orders the records in descending order
from student
order by student_name DESC;
SQL Where =
Select statement that select all the details of one student using the where clause.
from student
where student_id = 652415501
SQL Where >
Select statement that selects all the details of students that have numbers above the value
from student
where student_id > 652415501
SQL Like
If you are not sure of the exact value you can use the ‘LIKE’ SQL clause. For example, you can use it if you know the beginning of a value (e.g. 64).
Use a wilcard (‘%’) to mean any following values. Put the 64% in brackets like a string.
from student
where student_id like ‘64%‘
SQL Like (contains)
Use a wildcard (‘%’) to mean any values before and after to find text that contains your value.
from course
where course_name LIKE ‘%Management%’;
SQL alias
Alias means different name and an alias in SQL can be used to give a name to a column or a table
from student;
SQL alias
Another example from a different student table where we want to show the student’s name by adding, or concatenating, two strings of the first name and the surname.
from students
FAQ
What should a student database include?
A student database should include student ID, name, contact details, enrollment information, student grades, GPA, attendance records, and any other relevant student attributes. The database can also include information about each student’s courses, instructors, and performance metrics, making it easy to manage and analyze student data in one place.
What are some examples of databases?
Examples of databases include:
- Student Management System – Tracks student records, grades, and attendance.
- School Management Database – Handles student and staff information along with schedules.
- Academic Performance Database – Focuses on storing student performance data such as GPAs and test scores.
- Library Management Database – Tracks books issued, returned, and overdue by students.
Each of these databases uses a structured query language (SQL) to store, retrieve, and manage information efficiently.
How do you enroll students in a database?
You can enroll students by adding their information as new rows in the student table. Each row stores details about one student.
Does every student need to be in the database?
Yes, every student should be in the database if you want complete records. This makes it easier to track courses, grades, and enrollment.
Useful Information
When you design your first database, the first thing you need is to decide what information about each student should be stored. A simple student table might include name, ID, and course.
Many students also want to connect information in one place. For this, database tables can be linked. For example, one table could store the student body and another table could show what students are taking. A table that links the two tables is called a foreign key table. This makes it easy to add as many details as needed without repeating data.
Using SQLite as a database management system helps keep student records simple but powerful. It works well for small projects, class assignments, or even an information system used in learning data science.
Some students like to copy and paste results into Excel or a pdf for reports. Others may want to expand their project into another table that stores grades or attendance. With SQL, you can keep growing your design as your needs change.
If you need help, remember that starting with one table is fine. As your project grows, SQLite lets you create links, add more tables, and manage the information smoothly.
Database Examples
- Designing a Database: Learn how to create a schema that meets the requirements of managing student data.
- Managing Data Effectively: Use structured query language (SQL) to store, retrieve, and analyze student information efficiently.
- Common Use Cases: Examples include school management, student attendance tracking, and performance analysis.
- Why It Matters: A well-designed database helps manage information about your students in one place, making it easy to track student attributes and improve organizational workflows.
With these database concepts, you can build a student management system that links tables and stores everything from student info to academic performance, helping you manage data effectively.