DEV Community: Karen Wangui

SQL PRACTICAL GUIDE

Karen Wangui — Mon, 07 Sep 2026 14:59:48 +0000

Developing the Greenwood Academy Database: A Comprehensive Guide

In this article we will talk about the creation of a database that is made for Greenwood Academy. We will cover parts of Structured Query Language including database design, data manipulation, querying and techniques to make things work better. This project is meant to manage student records, courses that are available and exam results in a way. After reading this article you will have an idea of how to set up and work with a database that is based on relations through SQL.

Project Overview

The database for Greenwood Academy will have important parts:

Students: Information about the students who're in the academy.
Subjects: Details about the subjects that are taught at the academy.
Exam Results: Records of how students did in their exams.

Step 1: Database Design

Before writing any SQL commands we need to create the structure of the database. This means finding the tables what they hold and how they are connected to each other.

. Attributes

Students Table:

Student_id (Primary Key)

First_name

Last_name

Gender

Date_of_birth

Class

City

Subjects Table:

Subject_id (Primary Key)

Subject_name

Department

Teacher_name

Credits

Exam Results Table:

Result_id (Primary Key)

Student_id (Foreign Key that connects to Students)

Subject_id (Foreign Key that connects to Subjects)

Marks

Exam_date

Grade

Entity-Relationship Diagram (ERD)

The connections can be shown like this:

Each student can have exam results.
Each exam result is connected to one subject.

Step 2: Building the Database (DDL)

Using SQL Data Definition Language we can create our tables:

SQL Code for Creating Tables


-- Create schema

SCHEMA greenwood_academy;

SET search_path TO greenwood_academy;

-- Create students table

CREATE TABLE students (

student_id INT PRIMARY KEY,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

gender VARCHAR(1)

date_of_birth DATE,

class VARCHAR(10)

city VARCHAR(50)

);

-- Create subjects table

CREATE TABLE subjects (

subject_id INT PRIMARY KEY

subject_name VARCHAR(100) NOT UNIQUE

department VARCHAR(50)

teacher_name VARCHAR(100)

credits INT

);

-- Create exam_results table

CREATE TABLE exam_results (

result_id INT PRIMARY KEY,

student_id INT NOT NULL,

subject_id INT NOT NULL

marks INT NOT NULL

exam_date DATE,

grade VARCHAR(2)

FOREIGN KEY (student_id) REFERENCES students(student_id)

FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)

);

Step 3: Adding Information to the Database (DML)

After we create the tables we need to add some sample information. This part is very important to show how things work in the world.

SQL Code for Adding Data


-- Add students information

INSERT INTO students (student_id, first_name, last_name, gender, date_of_birth, class, city) VALUES

(1, 'Amina' 'Wanjiku' 'F' '2008-03-12' 'Form 3' 'Nairobi')

(2, 'Brian' 'Ochieng' 'M' '2007-07-25' 'Form 4' 'Mombasa')

(3, 'Cynthia' 'Mutua' 'F' '2008-11-05' 'Form 3' 'Kisumu');

-- Add subjects information

INSERT INTO subjects (subject_id, subject_name, department, teacher_name, credits) VALUES

(1, 'Mathematics 'Sciences 'Mr. Njoroge' 4)

(2, 'English' 'Languages 'Ms. Adhiambo' 3)

(3, 'Biology' 'Sciences 'Ms. Otieno' 4);

-- Add exam results information

INSERT INTO exam_results (result_id, student_id, subject_id, marks, exam_date, grade) VALUES

(1, 1 1 78 '2024-03-15' 'B')

(2, 2 1 92 '2024-03-15' 'A')

(3, 3 2 49 '2024-03-16' 'D');

Step 4: Getting Information from the Database

Now that the database has data we can ask for information. Using the features of SQL we can find, sort and change the data as needed.

Some Common SQL Queries

Find all students in Form 4:


SELECT * FROM students WHERE class = 'Form 4';

Get all subjects in the Sciences department:


SELECT * FROM subjects WHERE department = 'Sciences;

Get all exam results that have marks of 70 or more:


SELECT * FROM exam_results WHERE marks >= 70;

Step 5: Using Operators to Get Specific Data

SQL has operators that let us make more detailed queries.

Examples of Using Range and Membership Operators

Find exam results where marksre between 50 and 80:


SELECT * FROM exam_results WHERE marks BETWEEN 50 AND 80;

Find all subjects that have the word 'Studies in their name:


SELECT * FROM subjects WHERE subject_name LIKE '%Studies%';

Step 6: Using COUNT to Summarize Data

The COUNT() function is a tool that helps us summarize data.

Example of Counting Entries

Count how many studentsre in Form 3 right now:


SELECT COUNT(*) FROM students WHERE class = 'Form 3';

Step 7: Using Conditional Logic with CASE WHEN

The CASE statement helps us use logic in our SQL queries.

Examples of CASE WHEN

Give a description of the performance for each exam result:


SELECT result_id, student_id, subject_id, marks,

CASE

WHEN marks >= 80 THEN 'Distinction'

WHEN marks >= 60 THEN 'Merit'

WHEN marks >= 40 THEN 'Pass

ELSE 'Fail'

END AS performance

FROM exam_results;

Give a level to each student based on their class:


SELECT first_name, last_name, class,

CASE

WHEN class IN ('Form 3' 'Form 4') THEN 'Senior'

WHEN class IN ('Form 1' 'Form 2') THEN 'Junior'

END student_level

FROM students;

Creating the Greenwood Academy database shows the main steps of database design, building adding data and getting data. We looked at how to manage student, subject and exam result data using SQL. As shown SQL has abilities to store, change and get data making it a very useful tool in the tech world.

By using the ideas and ways shown in this article you will be ready, for database projects making sure that data is handled and found easily.

LINUX FUNDAMENTALS FOR DATA ENGINEERING

Karen Wangui — Mon, 08 Jun 2026 13:34:08 +0000

What is Linux

Linux is an open-source operating system (OS) that has been widely used in the tech industry for many years. At its center is the Linux kernel, which acts as the core of the system by managing hardware and system resources. Unlike closed-source systems such as Windows and macOS, Linux is built and supported by a worldwide community of developers. This collaborative development approach makes Linux highly flexible, secure, and efficient.
This article explores the key Linux fundamentals every data engineer should understand and how they apply in real-world data systems.

WHY DO DATA ENGINEERS PREFER LINUX

Data engineers tend to prefer Linux because it offers the control, flexibility, and reliability required for handling large-scale data systems. Here’s a clear breakdown:

Built for servers and large-scale systems
Most data platforms—such as Hadoop, Spark, Airflow, and Kafka—are designed to run on Linux servers. Production data pipelines almost always operate in Linux environments, not Windows.
Powerful command-line tools
Linux provides robust terminal utilities (bash, grep, awk, sed, cron) that make it easy to:
· process files quickly
· automate repetitive tasks
· inspect logs
· move and transform data efficiently
These are essential tasks in data engineering workflows.
Better performance and stability
Linux is lightweight compared to Windows, which means it:
· uses fewer system resources
· runs reliably for long periods without crashing
· handles heavy workloads more effectively
This is critical for pipelines that need to run 24/7.
Straightforward automation and scripting
With Linux, you can easily use:
· shell scripts (Bash)
· Python automation
· cron jobs for scheduling
This simplifies building and maintaining ETL pipelines.
Cloud and DevOps compatibility
Major cloud platforms—AWS, Google Cloud, and Azure—mostly run on Linux. As a result, deploying data pipelines almost always means working in Linux-based environments.
Open-source ecosystem
Linux is open source, like most data engineering tools. That brings:
· better compatibility
· broader community support
· easier integration with tools like Spark, Docker, and Kubernetes
Easy remote server access
Data engineers frequently work on remote machines. Linux makes this simple with SSH and remote terminal access.

The LINUX FILE SYSTEM

The linux file system isthe way linux organizes and stores files on a computer. Linux uses a single hierachical tree structure that starts from one root directory.

Path	Purpose
/	Root directory
/home	User data
/var/log	Log files
/tmp	Temporary files (cleared on reboot)
/mnt / /media	Mount points for external storage

ESSENTIAL COMMAND-LINE SKILLS

Navigating and Inspecting Files

Command	Purpose
pwd	Show the current working directory
ls -lah	List all files with permissions and sizes
cd /var/log/nginx	Change directory to /var/log/nginx
du -sh *	Display sizes of directories and files

Viewing and searching data

Command	What it does	Data Eng Use Case	Example
`head -n 20 access.log`	Show first 20 lines	Peek at CSV/log structure without loading full file	`head -n 1 data.csv`
`tail -f access.log`	Follow file live as it grows	Watch Airflow/Spark/Nginx logs in real time	`tail -f /var/log/spark/app.log`
`less -S huge_file.csv`	View file with horizontal scroll, no full load	Browse 200+ column CSVs without wrapping	`less -S +F huge.csv`
`tail -100f access.log`	Last 100 lines + keep following	Start from recent logs then watch	`tail -100f app.log`
`grep "ERROR" app.log`	Filter lines matching pattern	Isolate errors from huge logs	`grep "500" access.log \
{% raw %}`sed -n '1000000,1000020p' file`	Print lines 1M-1,000,020	Sample middle of huge file without loading all	`sed -n '1,5p' data.csv`
`awk -F',' '{print $1,$3}' file`	Print column 1 and 3	Quick column extraction before Spark	`awk -F',' '{print $2}' data.csv`
`sort file \	uniq -c`	Count unique values	Fast frequency table on a column

Text Processing Trio:

grep,awk,sed

grep – pattern matching
Extract HTTP 500 errors
grep ' 500 ' access.log > server_errors.

sed – stream editing
Replace , with | as delimiter:
sed 's/,/|/g' data.csv > data_pipe.txt

awk – column-based processing
Calculate average order value from a CSV:
awk -F ',' '{sum+=$4} END {print "Average: " sum/NR}' orders.csv

Redirection and Pipes: Building Pipelines

Redirect output

#stdout
python parse_logs.py > output.log

#stderr
python parse_logs.py 2> error.log

#both
python parse_logs.py &> all_output.log

Permissions and Ownership

In shared data environments, correct permissions prevent accidental writes or data leaks.
chmod 640 data/file.parquet # rw-r----- chown data_engineer:etl_group data/
rwxr-xr-- 754
rw------- 600
rw-rw-r-- 664

Check current user and groups

whoami groups id

Process Management for Long-Running Jobs

Your ETL script may run for hours. Managing processes is key.
Run job in background
python transform.py > transform.log 2>&1 &
View running processes
ps aux | grep python htop # interactive resource monitor

Kill a stuck process

kill -9 PID

survive terminal logout

nohup python heavy_etl.py &

Session management

screen -S etl_job
python run_pipeline.py

Ctrl+A, D to detach

screen -r etl_job # reattach
Scheduling with Cron
Orchestration doesn’t always require Airflow — cron is perfect for periodic data pulls.

Edit user’s crontab:

crontab -e
Schedule examples

# Every day at 2 AM
0 2 * * * /home/de/scripts/ingest_daily.sh

# Every 15 minutes
*/15 * * * * /home/de/scripts/check_new_data.sh

# First day of month at 4 AM
0 4 1 * * /home/de/scripts/aggregate_monthly.sh

Environment Variables & Configuration

Never hardcode credentials. Use env vars:
export DB_HOST="localhost" export DB_PASS="s3cr3t"

Make persistent in `~/.bashrc`or `~/.bashrc`:

echo 'export DATA_LAKE="/mnt/data_lake"' >> ~/.bashrc source ~/.bashrc

Assignment example

We stored API keys in .env file and loaded in Python:

from dotenv import load_dotenv
import os

load_dotenv()
API_KEY = os.getenv('WEATHER_API_KEY')

Conclusion

Understanding Linux fundamentals is essential for any aspiring data engineer because it forms the backbone of modern data infrastructure. In this article, we explored how important Linux concepts such as file system navigation, text manipulation using awk and sed, process monitoring, and task automation with cron are applied in real-world ETL workflows. The example assignment involving clickstream log ingestion, bot traffic filtering, and hourly aggregation reflects the type of practical challenges data engineers regularly solve in production systems. Developing command-line skills allows engineers to work more efficiently, automate repetitive tasks, and troubleshoot systems with greater confidence and speed.

As you advance in your data engineering career, view Linux skills as a valuable long-term asset rather than just another technical requirement. Begin by automating small repetitive tasks with shell scripts and challenge yourself to process large log files using command-line utilities before relying on graphical tools or Python libraries. Familiarize yourself with monitoring tools like htop and storage commands such as df -h to better understand system performance and resource usage. Mastering commands like grep, pipes, and cron will strengthen your ability to work across the entire data stack, including technologies such as Airflow, Spark, and Kubernetes. Since Linux powers much of today’s data infrastructure, becoming fluent in it will help you design pipelines that are efficient, scalable, and resilient.