From Rows to Results: How to Start Writing SQL Queries

From Rows to Results: How to Start Writing SQL Queries

UNDERSTANDING SQL BASICS: DDL, DML, FILTERING, AND DATA TRANSFORMATION

I was new to all the terms above like SQL, DDL, DML, Filtering, and Data Transformation. Today I will take you through how I understood the above terms with examples.

1. What is SQL?

SQL (Structured Query Language) is a language used to:

• Talk to a database
• Store data
• Get data
• Change data Think of it like giving instructions to a computer about data.

2. DDL (Data Definition Language)

DDL is used to create or change the structure of a database. It helps you build or design the database
Examples:

• CREATE → create a table
• ALTER → change a table
• DROP → delete a table

-- Create a table
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- Add a new column
ALTER TABLE students
ADD gender VARCHAR(10);

-- Delete table
DROP TABLE students;

3. DML (Data Manipulation Language)

DML is used to work with the data inside the table.
It helps you add, change, or remove data
Examples:

• INSERT → add data
• UPDATE → change data
• DELETE → remove data

-- Insert data
INSERT INTO students (id, name, age)
VALUES (1, 'Ali', 20);

-- Update data
UPDATE students
SET age = 21
WHERE id = 1;

-- Delete data
DELETE FROM students
WHERE id = 1;

4. Filtering Data

Filtering means selecting only the data you want.
You pick specific records, not everything
Keyword used:

• WHERE
• AND, OR
• IN, BETWEEN, LIKE

-- Get all students aged 20
SELECT * FROM students
WHERE age = 20;

-- Using AND
SELECT * FROM students
WHERE age > 18 AND gender = 'Male';

-- Using OR
SELECT * FROM students
WHERE age = 18 OR age = 20;

-- Using IN
SELECT * FROM students
WHERE age IN (18, 20, 22);

5. Data Transformation

Data transformation means changing how data looks when you display it.
You modify or format data while viewing it
Examples:

• Change text to uppercase
• Combine columns
• Do calculations

-- Convert names to uppercase
SELECT UPPER(name) AS student_name
FROM students;

Let's create queries with a school settings,

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)
);

The above does the following:

• Sets student_id as the PRIMARY KEY
• Makes first_name and last_name NOT NULL
• Keeps other fields optional (can accept NULL values)

Lets create another table with differnt columns as shown below

 CREATE TABLE subjects (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    teacher_name VARCHAR(100),
    credits INT
);

sql
Another table example
Create using this;

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)
);

Lets say we want to add students in our tables above, we do the following;

INSERT INTO students (student_id, first_name, last_name, gender, date_of_birth, class, city) VALUES
(1, 'Ali', 'Hassan', 'M', '2005-03-15', 'Form 4', 'Nairobi'),
(2, 'Amina', 'Mohamed', 'F', '2006-07-22', 'Form 3', 'Mombasa'),
(3, 'Brian', 'Otieno', 'M', '2005-11-10', 'Form 4', 'Kisumu'),
(4, 'Diana', 'Njeri', 'F', '2007-01-05', 'Form 2', 'Nakuru'),
(5, 'Kevin', 'Mwangi', 'M', '2006-09-18', 'Form 3', 'Eldoret'),
(6, 'Fatma', 'Ali', 'F', '2005-06-30', 'Form 4', 'Garissa'),
(7, 'John', 'Kamau', 'M', '2007-04-12', 'Form 2', 'Nyeri'),
(8, 'Grace', 'Wanjiku', 'F', '2006-12-25', 'Form 3', 'Thika'),
(9, 'Peter', 'Ochieng', 'M', '2005-08-09', 'Form 4', 'Kisii'),
(10, 'Esther', 'Chebet', 'F', '2007-02-14', 'Form 2', 'Kericho');

Insert all 10 subjects into the subjects table.

INSERT INTO subjects (subject_id, subject_name, department) VALUES
(1, 'Mathematics', 'Science'),
(2, 'English', 'Languages'),
(3, 'Kiswahili', 'Languages'),
(4, 'Biology', 'Science'),
(5, 'Chemistry', 'Science'),
(6, 'Physics', 'Science'),
(7, 'History', 'Humanities'),
(8, 'Geography', 'Humanities'),
(9, 'Computer Studies', 'Technology'),
(10, 'Business Studies', 'Commerce');

Insert all 10 exam results into the exam_results table

INSERT INTO exam_results (result_id, student_id, subject_id, marks, exam_date, grade) VALUES
(1, 1, 1, 85, '2024-11-10', 'A'),
(2, 2, 2, 78, '2024-11-10', 'B'),
(3, 3, 3, 65, '2024-11-11', 'C'),
(4, 4, 4, 88, '2024-11-11', 'A'),
(5, 5, 5, 72, '2024-11-12', 'B'),
(6, 6, 6, 60, '2024-11-12', 'C'),
(7, 7, 7, 90, '2024-11-13', 'A'),
(8, 8, 8, 55, '2024-11-13', 'D'),
(9, 9, 9, 80, '2024-11-14', 'A'),
(10, 10, 10, 68, '2024-11-14', 'C');

Write a query to find all students who are in Form 4.

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

Write a query to find all exam results where the marks are greater than or equal to 70.

SELECT * 
FROM exam_results
WHERE marks >= 70;

Write a query to find all exam results where marks are between 50 and 80

SELECT * 
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

Write a query to find all exams that took place between 15th March 2024 and 18th March 2024.

SELECT *
FROM result
WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18';

Write a query to find all students who live in Nairobi, Mombasa, or Kisumu - use IN.

SELECT student_id, first_name, last_name, city
FROM student
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

Write a query using CASE WHEN to label each exam result with a grade description - 'Distinction' if marks >= 80

SELECT 
    result_id,
    student_id,
    subject_id,
    marks,
    CASE 
        WHEN marks >= 80 THEN 'Distinction'
        ELSE 'Not Distinction'
    END AS grade_description
FROM result;

Conclusion

Mastering a query language like SQL is much like learning to speak directly to your data. As we've explored throughout this article, whether you are narrowing down specific date ranges, filtering records across multiple categories with the IN operator, or transforming raw numbers into meaningful labels using CASE WHEN, SQL gives you the exact vocabulary needed to ask the right questions.