Introduction
Most schools in Kenya decided to migrate to the online platforms because of COVID-19. The change was necessary so that learning could proceed as usual. Schools contracted information technologists to start the migration from physical to digital learning such as Google classroom and Zoho Cliq. During the process of migration, databases were created to store and retrieve data on web apps in real-time remotely.
Where is the information stored?
Information is stored in databases.
Databases are highly organized, electronically stored collection of data designed for rapid search, retrieval and manipulation. The developer/ data analyst decided to use a common database called relational databse since the data was already structured in excel files. The database of choice for migration was PostgreSQL.
What are some benefits of the migration from Spreadsheets?
- Scale: Databases can hold massive volumes of data that would crash a standard spreadsheet.
- Concurrency: Hundreds of users can read and write to a database simulataneously.
- Complexity: Databases form complex networks of connected information to reduce redundancy, utilizing query languages like SQL to extract precise insights.
What is SQL and how is it used?
SQL(Structured Query Language) is the standard programming language used to communicate with, manage, and manipulate relational databases. It allows you to store, update, search, and retireve data as well as manage the structure of the database itself. SQL commands are fundamental building blocks used to perform given operations on database. The operations include queries of data: Table creation, Data addition, Dropping Tables, Table modification and permission setting for users.
Table: Stores data in rows and columns.
Row(Record): A single entry in a table.
Column(Field): An attribute of the data.
Primary Key: Uniquely identifies each record.
Query: A request to access or modify data.
Constraint: Rules applied to ensure valid data.
- DDL - Data Definition Language These consists of SQL commands that can be used for defining, altering and deleting database structures such as tables, indexes and schemas.Eg: Create, Drop, ALter, Truncate, Comment and Rename
- DQL - Data Query Language is used to fetch data from the database. The main command is SELECT, which retrieves records based on the query.Eg: Select, From, Where, Group By, Having, Distinct, Order By and Limit
- DML - Data Manipulation Language are used to manipulate the data stored in database tables. Eg: Insert, Update, Delete
- DCL - Data Control Language includes commands such as GRANT and Revoke which mainly deal with the rights, permissions and other controls of the database system.
- TCL - Transaction Control Language. Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all tasks in the group are successfully completed. If any of the tasks fail, transaction fails.
Before we start designing the database for Tumaini, we need to select a Database management service like DBeaver. We create a database, then we can create a _schema _(a schema is a blueprint or structure that defines how data is organized and stored in the database), followed by a table. The search path was set to the schema.
create database school;
create schema tumaini;
show search_path; output --> {$user | Public}
set search_path tumaini;
To make work easier, we compare the excel sheets that was offered by the school. We will use 3 tables: Student table, Teacher Table and Grade table.
We start by creating the student table, we insert data into it and we try to see the output.
The expected output is show below.
Next we design the teacher's table and insert data into it.
Expected outcome once the data is inserted:
Lastly we design the grade table that has the exam scores for the students. We will reference the teacher_id, student_id and results.
Based on the results show, we can decide to alter the grade table.
The table name should be altered to performance.
ALTER TABLE tumaini.grade
RENAME TO Performance;
SELECT * FROM tumaini.Performance.
The performance table initially grade, requires an additional column, for each student scores.
ALTER TABLE tumaini.Performance
ADD COLUMN score int;
We added data into the score column.
update tumaini.Performance
set score = 80
where student_id = 1;
The expected outcome after adding the values is shown below:
Let's do some math with SQL.
A) We need to find the student with the highest math score. This will require us to do some joins, followed by filtering. We will join the students table and performance table. Then we will filter out the subjects by math and lastly limit the output by 1.
select st.full_name, p.score
from tumaini.students st
left join tumaini.Performance p
on p.student_id = st.student_id
where subject = 'Math'
group by st.full_name, p.score
limit 1;
Expected outcome is shown below:
B)We can order the students based on their score from descending.
select st.full_name, p.score
from tumaini.students st
left join tumaini.Performance p
on p.student_id = st.student_id
order by p.score desc;
Expected outcome is shown below:
Conclusion
The main aim for Tumaini was to migrate their learning to cloud based. This allowed us to witness how databases are created, through SQL commands. SQL commands are basically close to the human language and thus can be easily understood. We reviewed the essential SQL commands like Select, Insert, Update and ALter. Using SQL, a lot of operations can be done like Average, Summation and finding max values in a table.
Top comments (2)
Nice article.
thank you. I noticed we need to focus on story telling rather than copy pasting documentation.