DEV Community: Miss Analyst

SQL Functions — A Friendly Guide With Real Examples

Miss Analyst — Mon, 07 Sep 2026 16:32:39 +0000

When I was learning SQL, "functions" felt like a big scary word borrowed from programming. Turns out, they're just small helpers that do one job: take some data in, give you a changed version back out. Once that clicked, SQL got a lot less intimidating.

This article walks through the SQL functions you'll actually use, with real examples — an online store, a user table, a sales report — and notes on when each one is the right tool.

What Is a SQL Function?

Think of a function as a little machine. You feed it something (a column, a number, a piece of text), and it hands back a result. You don't need to know how it works inside, you just need to know what it takes in and what it gives back.

SELECT UPPER(name) FROM customers;

Here, UPPER is the machine. Feed it a name, it hands back the name in capital letters. That's the whole idea behind every function in this article.

SQL functions generally fall into a few groups:

Aggregate functions — take many rows and turn them into one answer (like a total or an average).
String functions — work on text.
Date/time functions — work on dates and times.
Numeric functions — do math.
Window functions — a bit more advanced, but incredibly useful once you get them.

Let's go through each with examples.

1. Aggregate Functions

They look at a group of rows and give you back a single number.

`COUNT()` — how many?

SELECT COUNT(*) FROM orders;

Used any time you're asking "how many of these are there?" — how many orders came in today, how many users signed up this month, how many products are out of stock.

`SUM()` — add it all up

SELECT SUM(order_total) FROM orders WHERE order_date >= '2026-09-01';

Used when calculating totals example; total revenue this month, total items sold, total hours logged.

`AVG()` — the average

SELECT AVG(order_total) FROM orders;

Used anytime "on average" comes up in a question. It could be average order size, average time to respond to a support ticket or average rating on a product.

`MIN()` and `MAX()` — the smallest and biggest

SELECT MIN(order_total) AS cheapest, MAX(order_total) AS priciest
FROM orders;

Used when finding extremes like the cheapest order ever placed, the newest signup, the oldest unpaid invoice.

Grouping it all together with `GROUP BY`

Aggregate functions get really useful once you pair them with GROUP BY. Say you want total sales per month, not just one giant total:

SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(order_total) AS total_sales,
  COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

Used with basically any report. "Sales by month," "orders by customer," "signups by country" all of these are a GROUP BY with one or more aggregate functions attached.

2.String Functions

Text is messy. People type names in weird cases, leave extra spaces, or you need to combine two columns into one readable label. String functions clean this up.

`CONCAT()` — joining text together

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

It is used when building a display name, combining an address from separate columns, making a readable label out of pieces of data.

`UPPER()` and `LOWER()` — changing case

SELECT email FROM customers WHERE LOWER(email) = 'jane@example.com';

Used when comparing text without worrying about capital letters. This is a common one, someone might type Jane@Example.com at signup, and you still want to match it against jane@example.com later.

`TRIM()` — removing extra spaces

SELECT TRIM(coupon_code) FROM orders;

Used when cleaning up data that came from a form or an import file, where people (or systems) often leave a stray space at the start or end.

`LENGTH()` — how long is this text?

SELECT name FROM products WHERE LENGTH(name) > 50;

Used when catching data problems, like a product name that's way too long to display properly, or a password that's too short.

`SUBSTRING()` — grabbing part of a text value

SELECT SUBSTRING(phone_number, 1, 3) AS area_code
FROM customers;

We can use it when pulling out a piece of a larger value, like an area code from a phone number, or the first few letters of a product code.

`REPLACE()` — swapping text

SELECT REPLACE(address, 'St.', 'Street') FROM customers;

Can be used when fixing inconsistent data, like standardizing abbreviations before generating a report.

3.Date and Time Functions

Dates come up constantly: "when did this happen," "how long ago was that," "what month was this in." These functions make dates usable.

`NOW()` / `CURRENT_DATE` — right now

SELECT * FROM subscriptions WHERE renewal_date < CURRENT_DATE;

anything that depends on "today" — overdue payments, expired trials, upcoming renewals.

`DATE_TRUNC()` — rounding a date down to a unit

SELECT DATE_TRUNC('month', signup_date) AS signup_month, COUNT(*)
FROM users
GROUP BY 1;

We can use it when grouping data by day, week, or month. This is one of the most useful functions for building reports — it turns a messy timestamp into a clean bucket you can group by.

`DATEDIFF()` / date subtraction — how much time passed

SELECT
  name,
  CURRENT_DATE - signup_date AS days_since_signup
FROM users;

(Note: the exact syntax for this varies a bit by database — Postgres lets you subtract dates directly like above, while MySQL and SQL Server use DATEDIFF().)

Used to calculating someone's account age, how many days a task has been open, or how long it's been since a customer's last order.

`EXTRACT()` — pulling out one piece of a date

SELECT EXTRACT(DOW FROM order_date) AS day_of_week, COUNT(*)
FROM orders
GROUP BY 1;

Used when finding patterns, like "do we get more orders on weekends?" DOW here means day of week.

4. Numeric Functions — Basic Math

These are the functions you reach for when you need to do a bit of arithmetic inside your query, instead of after the fact.

`ROUND()` — rounding numbers

SELECT ROUND(AVG(order_total), 2) AS avg_order
FROM orders;

Used when cleaning up decimals for a report. Nobody wants to see 47.836218 when 47.84 is what actually matters.

`ABS()` — absolute value

SELECT ABS(balance) AS balance_owed FROM accounts WHERE balance < 0;

Used when working with numbers that can be negative, like a balance or a difference, when you just care about the size of the number, not the sign.

5. Window Functions

Window functions look a bit different, they solve problems that are painful otherwise unlike aggregate functions, they don't collapse rows into one — they add a calculated value next to each row.

`ROW_NUMBER()` — numbering rows

SELECT
  name,
  order_total,
  ROW_NUMBER() OVER (ORDER BY order_total DESC) AS rank
FROM orders;

Used when ranking things, like "who are our top 10 spenders" or "what's the 3rd most recent order for each customer."

`RANK()` and `DENSE_RANK()` — ranking with ties

SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM quiz_results;

example — "top order per customer"

Say you want each customer's biggest order, but you still want to see their name and every order next to it:

SELECT *
FROM (
  SELECT
    customer_id,
    order_total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;

Used anytime you want "the top result per group", the newest order per customer, the highest score per student, the latest login per user. This is genuinely hard to do without a window function, and once you learn this pattern, you'll reuse it constantly.

Final thoughts

SQL functions aren't complicated once you see them as small, single-purpose helpers. Aggregate functions turn many rows into one number. String functions clean up and shape text. Date functions make "when" usable. Window functions let you rank and compare rows without losing the details.

You don't need to memorize all of them right away. Start with COUNT, SUM, AVG, and DATE_TRUNC, you'll use those constantly. The rest will stick as you run into the problems they solve

SUBQUERIES AND CTEs

Miss Analyst — Mon, 07 Sep 2026 15:54:55 +0000

When I first started learning SQL, writing a basic SELECT query wasn't too bad.
Then came questions like:

"What if I need to use the result of one query inside another query?"

That's where subqueries come in.
And when those queries start getting longer and harder to read, you might come across something called a CTE.
At first, both can seem confusing. But once you understand what they're doing, they actually make a lot of sense.

In this article, I'll break down subqueries and CTEs using simple examples and explain when you might want to use each one.

What Is a Subquery?

A subquery is simply a query inside another query. Instead of running two completely separate queries, we can put one query inside another and use its result.

Here's where they tend to show up in real code.

1. Inside a `WHERE` clause — "find rows above or below some number"

Say you run a small online store. You want to find your best customers: people who spent more than the average.

SELECT customer_id, name
FROM customers
WHERE lifetime_spend > (
  SELECT AVG(lifetime_spend) FROM customers
);

The inner query gives back one number: the average. The outer query uses that number as a cutoff. You'll see this pattern everywhere — flagging a payment that's way above someone's usual spend, or a product selling faster than others in its category.

2. Inside a `FROM` clause — using a query result like a table

Say your marketing team wants the average order value per city, but only for cities where that average is above $50.

SELECT city, avg_order_value
FROM (
  SELECT city, AVG(order_total) AS avg_order_value
  FROM orders
  GROUP BY city
) AS city_averages
WHERE avg_order_value > 50;

This inner query builds a small, temporary table, and the outer query filters it. You can't filter on avg_order_value in the same step where you calculate it, so this two-step approach is what lets you do it.

3. Inside a `SELECT` clause — pulling in one extra fact per row

Say someone asks: "next to each customer's name, show how many orders they've placed."

SELECT
  name,
  (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) AS order_count
FROM customers;

This only works if the subquery returns exactly one number per row. It's fine for small cases like this, but if you're doing it across a lot of rows, a JOIN is usually the faster choice.

4. Correlated subqueries — when the inner query needs the outer row

A correlated subquery looks at the current row from the outer query while it runs. It can't run on its own — it runs once for every row.

Say you want to find employees who earn more than the average salary in their own department (not the whole company).

SELECT e1.name, e1.salary, e1.department
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e1.department
);

See that line, e2.department = e1.department? That's the connection back to the outer row. For each employee, the subquery works out the average pay for just their department. This kind of check is hard to write any other way, which is exactly when a subquery is the right call.

What Is a CTE?

A CTE (short for Common Table Expression) is a named subquery placed at the top of your query, using WITH. Same result, easier to read.

Here's the "cities with above-average order value" example again, written as a CTE:

WITH city_averages AS (
  SELECT city, AVG(order_total) AS avg_order_value
  FROM orders
  GROUP BY city
)
SELECT city, avg_order_value
FROM city_averages
WHERE avg_order_value > 50;

It does the exact same thing as the earlier version. But read it out loud: "with city averages as this, select from city averages where..." It sounds close to plain English. That's the main reason people like CTEs.

Chaining CTEs together

This is where CTEs really help. Say you're building a dashboard and want to know which cities have the most big-spending customers.

WITH big_spenders AS (
  SELECT * FROM customers WHERE lifetime_spend > 1000
),
big_spenders_by_city AS (
  SELECT city, COUNT(*) AS spender_count
  FROM big_spenders
  GROUP BY city
)
SELECT * FROM big_spenders_by_city ORDER BY spender_count DESC;

Each step has a name, and each step builds on the one before it. Try writing this with nested subqueries instead, and you'll end up with closing brackets stacked on top of each other. Nobody enjoys reading that.

Recursive CTEs — the one thing subqueries can't do

This is the real strength of CTEs. Recursive CTEs handle anything shaped like a tree: org charts, product categories (like "Electronics > Laptops > Gaming Laptops"), comment threads with replies to replies.

Here's an org chart example — showing who reports to who, all the way down.

WITH RECURSIVE org_chart AS (
  -- Start at the top: anyone with no manager
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Then find everyone who reports to someone we already found
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

It starts at the top of the company, then keeps adding the next level down, one step at a time, until there's nobody left to add. Without a recursive CTE, you'd need to write a loop in your app code to do this.

A product category tree works almost the same way. Just swap manager_id for parent_category_id.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_category_id, 1 AS depth
  FROM categories
  WHERE parent_category_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_category_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_category_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

Same pattern, different data. Once you see it once, you'll notice it everywhere.

Subquery vs CTE: Quick Comparison

	Subquery	CTE
Can you reuse it in the same query?	No, you'd have to write it again	Yes, just reference its name
Easy to read when there are many steps?	Gets messy fast	Stays readable, step by step
Can it repeat itself (recursion)?	No	Yes, with `WITH RECURSIVE`
Can it use the outer row for context?	Yes	Not directly

"But Which One Is Faster?"

People ask this a lot, so let's settle it: there's no single right answer. Be careful trusting anyone who claims one is always faster.

Older versions of Postgres (before version 12) always computed a CTE fully before using it, even if you only needed a few rows out of it. That gave CTEs a bad reputation. Postgres 12 and later fixed this.
In SQL Server, MySQL 8+, and modern Postgres, a CTE and an equivalent subquery are usually treated the same way by the database, with the same speed.
Recursive CTEs aren't really part of this comparison, since there's no subquery way to repeat a query like that.

Don't choose based on an old rumor about speed. Choose based on what's easier to read. If speed really matters for a specific query, check the real execution plan instead of guessing.

So, Which One Should You Use?

Most of the time it comes down to how many steps your query needs. Here's a simple way to decide:

Use a subquery when:

It's a small, one-off check, like "only show rows above the average."
Giving it a name would be more effort than it's worth.
It needs to use the current row from the outer query (a correlated subquery).

Use a CTE when:

Your query has more than one step, and naming each step makes it easier to follow.
You need to use the same result more than once in your query.
You're working with tree-shaped data, like org charts or categories.
You're debugging and want to check one step at a time.

Using Both Together (This Is Normal)

Most real queries mix the two. A CTE handles the overall structure, and a small subquery handles a quick check inside it. Here's a monthly sales report that does both:

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY 1
)
SELECT
  month,
  total_sales,
  total_sales - (SELECT AVG(total_sales) FROM monthly_sales) AS diff_from_avg
FROM monthly_sales
ORDER BY month;

The CTE groups sales by month. The small subquery at the end compares each month to the average. Simple, and easy to follow.

Final thoughts

Subqueries and CTEs both do similar work, and most SQL developers use both, depending on the situation. Any CTE without recursion could technically be written as a subquery instead — but that doesn't mean it should be. If your query has several steps, let a CTE lay them out clearly. If you just need one quick check, a subquery works fine. And if your data is shaped like a tree, a recursive CTE is really your only option.

The best test isn't "which one is technically correct." It's "can I read this again in six months without getting confused." Aim for that, and you'll be fine.

Understanding Relationship Schemas and Joins in Power BI

Miss Analyst — Mon, 07 Sep 2026 13:08:04 +0000

When I first started learning databases, I honestly found the terminology a little overwhelming.

There were so many words being thrown around — schemas, primary keys, foreign keys, relationships, joins, normalization — and at first, they all seemed like separate things I had to memorize.

But after working with SQL and creating actual tables, I started realizing that these concepts are all connected.

So in this article, I want to explain them in the simplest way possible, especially for anyone who is just getting started with databases. Let us start by knowing what data modelling is.

DATA MODELLING
Data modeling is the process of deciding:

What information do we need?
What tables should we create?
What should each table contain?
How should the tables be connected?

For example, if I were designing a database for a university, I might need information about:

Students
Departments
Courses
Lecturers
Enrollments

I wouldn't want to throw all of that information into one giant table. That would quickly become messy and difficult to manage.
Instead, I'd create separate tables and connect them.

Students
Departments
Courses
Lecturers
Enrollments

That's the beginning of a data model.

What Is a SCHEMA
Once we've decided how we want our database to look, we need to actually define its structure.
That's where a schema comes in.

A database schema describes what exists in our database.
It can include:

Tables
Columns
Data types
Primary keys
Foreign keys
Constraints
Relationships

For example:

CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
We're telling the database to create a table called departments and give it these two columns where department_id is the primary key.

The difference between data modelling and the schema is that; data modeling is the plan, the schema is the structure we actually create from that plan.

PRIMARY KEYS
A primary key is basically a unique identifier for each record in a table.

For example:

 
student_id  name
101        Alice
102       Brian
103       Carol

Here, student_id is the primary key.

FOREIGN KEYS
Where Tables Start Connecting
Let's say we have a departments table:

department_id department_name
1 Data Science
2 Computer Science

And we have our students table:

student_id name department_id
101 Alice 1
102 Brian 2
103 Carol 1

Both tables have department_id.
In the departments table, it's the primary key.
In the students table, it's a foreign key.

departments
department_id (PK)
↑
|
|
students
department_id (FK)

Hence we are allowed to say:
Alice belongs to Data Science.
Brian belongs to Computer Science.
Carol belongs to Data Science.

The foreign key is basically helping us connect the information.

Understanding RELATIONSHIPS
You need to understand how tables relate to each other, there are three main relationships which are;

1. One-to-One (1:1)
This means one record is connected to one record.
For example:

Person → Passport

One person has one passport.

2. One-to-Many (1)
It means one record in one table can be connected to many records in another table.

For example:

Department → Students

One department can have many students.

For example:

Data Science
|
├── Alice
├── Carol
└── David

So we have:

One department → Many students

3. Many-to-Many (M)
Here one record can be connected to many records and the same record can also be connected to many records take an instance
students and courses.

One student can take many courses and at the same time, one course can have many students.
So:

Students ↔ Courses

That's a many-to-many relationship.

We usually handle this by creating another table in between.
This is often called a junction table or bridge table.

** What Is a JOIN?**
A JOIN allows us to combine information from different tables.
For example, our students table only tells us the department ID:

student_id name department_id
101 Alice 1
102 Brian 2
103 Carol 1

But what if I want to see the actual department name?
That's where a JOIN comes in.

SELECT
students.name,
departments.department_name
FROM students
JOIN departments
ON students.department_id = departments.department_id;

The result would look something like:

name department_name
Alice Data Science
Brian Computer Science
Carol Data Science

The part I really want to pay attention to is:

ON students.department_id = departments.department_id

This tells SQL:
"These are the columns I want you to use to connect these two tables."
Once I understood that, JOINs became much easier.

Types of JOINs
There are several types of JOINs, but let's focus on the main ones.

INNER JOIN
An INNER JOIN gives you only the records that have a match in both tables.

SELECT *
FROM students
INNER JOIN departments
ON students.department_id = departments.department_id;

Think of it as:

"Show me the records that match."

LEFT JOIN
A LEFT JOIN keeps everything from the table on the left, even when there isn't a matching record on the right.

SELECT *
FROM students
LEFT JOIN departments
ON students.department_id = departments.department_id;

The easiest way to remember it:

LEFT JOIN = Keep everything on the left.

So if there's a student without a matching department, that student can still appear in the results.

RIGHT JOIN
A RIGHT JOIN is basically the opposite.
It keeps everything from the table on the right.

SELECT *
FROM students
RIGHT JOIN departments
ON students.department_id = departments.department_id;

So:

RIGHT JOIN = Keep everything on the right.

FULL OUTER JOIN
A FULL OUTER JOIN keeps everything from both tables.

SELECT *
FROM students
FULL OUTER JOIN departments
ON students.department_id = departments.department_id;

It includes matching records as well as records that don't have a match.

CROSS JOIN
This one is a little different.
A CROSS JOIN creates every possible combination between two tables.
For example, if we have:
3 students
4 courses

We would get:

3 × 4 = 12 combinations

The query would be:

SELECT *
FROM students
CROSS JOIN courses;

It's not something you'll use every day, but it's good to know what it does.

Relationship vs JOIN
A relationship describes how tables are connected in the database.

For example:
Department 1 ───────< Students

This tells us that one department can have many students.

A JOIN, on the other hand, is something we use in an SQL query to actually bring information from those tables together.
For example:

SELECT *
FROM departments
JOIN students
ON departments.department_id = students.department_id;

So the easiest way to remember it is:
Relationship = how the tables are connected.
JOIN = how we retrieve data from those connected tables.

Where Does Normalization Come In?
Another term you'll probably hear a lot when learning databases is normalization.
The basic idea is to organize your data so that you're not unnecessarily repeating the same information everywhere.

For example, imagine having:

student department lecturer
Alice Data Science Mr. John
Brian Data Science Mr. John
Carol Data Science Mr. John

There's a lot of repeated information.Instead, we can separate the information into different tables and connect them.

Departments
↓
Students
↓
Enrollments
↓
Courses

This makes the database cleaner and easier to maintain.
If something about the Data Science department changes, we don't want to update hundreds of student records individually.

Final Thoughts
Learning databases can feel confusing in the beginning because there are so many new terms to learn at once.
But you don't necessarily have to memorize everything separately.
Once you understand how the pieces fit together, it starts making much more sense.

For me, the biggest thing was realizing that tables aren't just random collections of data. They're designed to work together.

So if you're currently learning SQL, don't just memorize JOIN syntax.

Try creating a small database yourself.
Create a few tables, give them primary and foreign keys, establish relationships, and then write queries to bring the information together.

That's when these concepts really start to click.

And trust me, once JOINs finally make sense, SQL becomes a lot less scary.

SQL PROJECT:Build a greenwood academy database with postgresql

Miss Analyst — Mon, 17 Aug 2026 13:40:39 +0000

INTRODUCTION
For this week's SQL assignment, I was given a practical scenario involving Greenwood Academy, a secondary school in Nairobi. The task was to take on the role of a new database administrator and build a school database from scratch, populate it with information, and then use SQL queries to retrieve and work with the data.

At first, the assignment looked like a lot because there were several different SQL concepts involved. However, as I worked through it step by step, I realized that each section was connected to the next. I started by creating the database structure, added the required information, and finally used queries to make sense of the data.

WHAT THE PROJECT IS ABOUT:
We are required to build the data the database from scratch and start querying it, let's go step by step on how we are going to do that.We will start by
1.creating a schema called greenwoods_academy;

the schema will act as a blue print to ensure our data is stored, exchanged and validated consistently
2.Creating tables
Tables store information that is going to be used in the query. Tables usually have keys that are important in building databases. Keys help identify records and where relationships are defined, connect information between different tables examples of the keys are primary and foreign key. In our database we have 3 tables that we are going to create;
(a)create the students table
This table store the students information. The primary key here is the students_id

(b)create the subjects table
Here the subjects information is going to be stored. The primary key is subject_id

(c)create the exam_results table
It contains the exam information. The primary key is result_id

3.Altering the tables
Databases can be changed to suit the users requirements after they have been created, this is very suitable because real world databases might need some adjustments. The school forgot to add a phone_number column, it also needed to rename a column to credit_hours and delete the phone number column. we will use the ALTER function to make these changes.

4.filling the database
We will now insert data to the tables we created. Here we use the Data manipulation language. I mainly worked with insert, update and delete. You have to be keen when inserting the data to get the right thing

Esther Akinyi moved from Nakuru to Nairobi, we will use update to adjust the query

In the result_id column we entered 59 instead of 49

Result for result_id 9 was cancelled, we use delete to adjust the query

It is very important we have these operations since data in the real work keeps changing
5.Querying the data
Here we retrieve information from the query and we use the where clause. We will first find all students who are in form 4 and all subjects in the science department.

Now we find all exam results whose marks are greater than or equal to 70 and all females only
! Image description
Using and, or
they are used to combine different conditions. For and all conditions must have been satisfied while or either of the two must have been satisfied.

Queries allow you to easily search for data rather than doing it manually.
6.Working with operators
The operators we are going to use here are between, in, not in and like Between returns values within a range with the boundaries included example 50 and 80 will be included

In matches any value in a list, not in excludes values in a list while like matches a text pattern

7.Using count
We use count to get the number of records, that matches your query. In our database we will find the number of students who are in form 3 and those that have marks 70 or above

8.Using case when
We use case when to assign labels or description to numbers or values

what I learnt
The biggest thing I learned from this assignment is that SQL is not just about writing individual commands. There is a process involved.

First, I have to create the structure of the database. Then I insert the data, make corrections when necessary, and finally query the information I need.

I also got more comfortable with commands such as CREATE, ALTER, INSERT, UPDATE, DELETE, SELECT, WHERE, BETWEEN, IN, LIKE, COUNT(), and CASE WHEN.

Another important lesson was the importance of being precise. A small mistake in a table name, column name, condition, or value can cause a query to fail or return the wrong information. Working through the assignment helped me become more careful with my SQL syntax.

final thoughts
Overall, this assignment gave me a more practical understanding of how databases work. Instead of only learning SQL commands individually, I got to use them in a situation that resembles something a real school might need.

Starting with an empty database and gradually building it into something that could store student, subject, and examination information made the different SQL concepts easier to connect.

The assignment also helped me see how SQL can be used to organize information, correct mistakes, search through large amounts of data, and turn raw data into useful information. Going forward, I feel more confident about using SQL for larger and more realistic database projects.

HOW EXCEL IS USED IN REAL-WORLD DATA ANALYSIS

Miss Analyst — Wed, 10 Jun 2026 08:03:27 +0000

INTRODUCTION
Data is raw facts and figures that have less meaning to the user therefore we use tools such as excel to organize,analyse and visualize it. Lets dig deep to what excel is
EXCEL?
It is a spreadsheet application that allows users to store data in rows and columns, perform calculations using formulas,create charts and graphs and generates reports

USES OF EXCELL IN THE REAL WORLD

Data Cleaning and Preparation: Data collected from all over including surveys, businesses and research can sometimes be dirty therefore containing duplicate entries, blank cells, spelling mistakes or inconsistent formatting. Excel provides tools such as Remove Duplicates, Find and Replace, Text to Columns and sort and Filter to correct these issues. Clean data ensures that the final analysis is accurate and reliable.

2.Financial Reporting and Budgeting: Organizations and businesses can track income, expenses and profits using spreadsheets. This makes it easier to plan budgets and monitor financial performance without complicated software.

3.Business decision-making: Companies collect alot of information every day, such as sales figures and customer data. Excel helps organize this information so managers can identify trends and make better decisions about their products or services.

EXCEL FEATURES AND FORMULAS I HAVE LEARNED

Excel has several features and formulas that make data analysis easier and more efficient, they include

1.=SUM FUNCTION- It is used to add values within a selected range of cells example =SUM(A1:A10)

2. =AVERAGE FUNCTION-It is used to calculate the mean of a group of numbers example =AVERAGE(B1:B10)

It is useful for finding the average score of students monthly income or average customer ratings.

*3.IF Function *-The IF function performs logical tests and returns different results depending on whether a condition is true or false

4.Sort and Filter-This feature is used to organize data and display only relevant information therefore making data cleaning easier to improve the quality of analysis.

CONCLUSION
Learning Excel has changed the way I see data. I no longer view data as just rows of numbers but as valuable information that can answer questions and solve problems when it is properly cleaned and analyzed. Excel has enhanced my understanding of data and it's role in solving real-world problems making it an essential skill in todays data-driven world.

DEV Community: Miss Analyst

SQL Functions — A Friendly Guide With Real Examples

What Is a SQL Function?

1. Aggregate Functions

COUNT() — how many?

SUM() — add it all up

AVG() — the average

MIN() and MAX() — the smallest and biggest

Grouping it all together with GROUP BY

2.String Functions

CONCAT() — joining text together

UPPER() and LOWER() — changing case

TRIM() — removing extra spaces

LENGTH() — how long is this text?

SUBSTRING() — grabbing part of a text value

REPLACE() — swapping text

3.Date and Time Functions

NOW() / CURRENT_DATE — right now

DATE_TRUNC() — rounding a date down to a unit

DATEDIFF() / date subtraction — how much time passed

EXTRACT() — pulling out one piece of a date

4. Numeric Functions — Basic Math

ROUND() — rounding numbers

ABS() — absolute value

5. Window Functions

ROW_NUMBER() — numbering rows

RANK() and DENSE_RANK() — ranking with ties

Final thoughts

SUBQUERIES AND CTEs

What Is a Subquery?

1. Inside a WHERE clause — "find rows above or below some number"

2. Inside a FROM clause — using a query result like a table

3. Inside a SELECT clause — pulling in one extra fact per row

4. Correlated subqueries — when the inner query needs the outer row

What Is a CTE?

Chaining CTEs together

Recursive CTEs — the one thing subqueries can't do

Subquery vs CTE: Quick Comparison

"But Which One Is Faster?"

So, Which One Should You Use?

Using Both Together (This Is Normal)

Final thoughts

Understanding Relationship Schemas and Joins in Power BI

SQL PROJECT:Build a greenwood academy database with postgresql

HOW EXCEL IS USED IN REAL-WORLD DATA ANALYSIS

EXCEL FEATURES AND FORMULAS I HAVE LEARNED

`COUNT()` — how many?

`SUM()` — add it all up

`AVG()` — the average

`MIN()` and `MAX()` — the smallest and biggest

Grouping it all together with `GROUP BY`

`CONCAT()` — joining text together

`UPPER()` and `LOWER()` — changing case

`TRIM()` — removing extra spaces

`LENGTH()` — how long is this text?

`SUBSTRING()` — grabbing part of a text value

`REPLACE()` — swapping text

`NOW()` / `CURRENT_DATE` — right now

`DATE_TRUNC()` — rounding a date down to a unit

`DATEDIFF()` / date subtraction — how much time passed

`EXTRACT()` — pulling out one piece of a date

`ROUND()` — rounding numbers

`ABS()` — absolute value

`ROW_NUMBER()` — numbering rows

`RANK()` and `DENSE_RANK()` — ranking with ties

1. Inside a `WHERE` clause — "find rows above or below some number"

2. Inside a `FROM` clause — using a query result like a table

3. Inside a `SELECT` clause — pulling in one extra fact per row