Student Database Example From Design Using Simple SQL Statements

database icons

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

basic student database ER diagram

Create a Student Database

To start we are going to keep it simple and create the student table and the course table.

We use an SQL statement to create the student table.

CREATE TABLE student (
student_id INT PRIMARY KEY,
student_name VARCHAR(100)
);
student table
We use an SQL statement to create the course table.

CREATE TABLE course (
course_code INT PRIMARY KEY,
course_name VARCHAR(100),
course_date VARCHAR(10)
);
course table in the student database

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.

INSERT INTO student (student_id, student_name) VALUES
(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’);
student table

SQL Insert Course Table Data

Lets also enter the course details.

INSERT INTO course (course_code, course_name, course_date) VALUES
(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’);
course table in the student database

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:

  1. SELECT – what columns you want to see
  2. FROM – from which tables
  3. WHERE – which rows
ERdiagram10sql

SQL Select Statements

SQL Select

Simple select statement

select student_id, name
from student;

SQL Select all

Select statement that select all the columns

select *
from student;

SQL order by

Select statement that orders the records in ascending or alphabetical order

select *
from student
order by student_name;

SQL order by descending

Select statement that orders the records in descending order 

select *
from student
order by student_name DESC;

SQL Where =

Select statement that select all the details of one student using the where clause.

select *
from student
where student_id = 652415501

SQL Where >

Select statement that selects all the details of students that have numbers above the value

select *
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.

select *
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.

select *
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

select name as new_name
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.

select first_name + ” ” + surname as fullname
from students

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.

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:

  1. Student Management System – Tracks student records, grades, and attendance.
  2. School Management Database – Handles student and staff information along with schedules.
  3. Academic Performance Database – Focuses on storing student performance data such as GPAs and test scores.
  4. 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.

return to stages to create simple database

Leave a Reply

English

Digital

Courses