DEV Community: Sam Guantai

Introduction to SQL

Sam Guantai — Mon, 20 Jul 2026 00:33:36 +0000

Starting SQL can feel daunting. It might seem like an entirely new language being thrown at you, but after approaching the core ideas it becomes relatively simple to understand. Lets start with a few definitions.

Server - This is the actual software process (like PostgreSQL or MySQL) running on a machine.
Database - It's an organized collection of data structured to be easily accessed, managed, and updated.
Schema - an organizational layer within a database, used to group related tables together.
Data - This is raw, unorganized facts and figures and can come in various formats such as numbers, text or images
DBMS (Database Management System) - is the software that enables users to create, manage, and interact with databases. Some of the most common relational DBMS options you'll encounter are: MySQL, PostgreSQL, Microsoft SQL Server

So What Is SQL Anyway?

SQL (Structured Query Language) is a standard language used to communicate with relational databases. Put simply, it's how we talk to a database and how we ask it to store, change, or hand back information.
It is further divided into sub-languages based on the exact action needed to be performed on the database. In this article we will tackle the three first and most basic sub-languages

DDL - Data Definition Language

DDL commands deal with the structure of the database. This includes creating, changing or deleting the table.
Key commands: CREATE, ALTER, DROP

DML - Data Manipulation Language

DML commands deal with the records inside the database. They modify the data without changing the structure of the table itself.
Key commands: INSERT, UPDATE, DELETE

DQL - Data Query Language

DQL command deals with reading the data inside the database. It ask questions of the data without changing anything about the data or the structure of the database.
Key command: SELECT

Data Types

Every column in a table needs a defined data type, this decides what kind of value that column can hold. Picking the right one keeps your data both accurate and efficient.

Numbers

INT — a whole number
NUMERIC / DECIMAL — numbers with fixed decimal places (useful for prices, where rounding errors are unacceptable)
SERIAL — a number PostgreSQL generates automatically for you, usually used for IDs

Text

CHAR(n) — for text that is always exactly n characters (e.g., a 10-digit phone number stored as a fixed-length code)
VARCHAR(n) — for text up to n characters (variable length, up to a maximum of n)
TEXT — for text with no fixed length limit

Date & Time

DATE — stores a date only
TIME — stores a time only
TIMESTAMP — stores both date and time together

Boolean

BOOLEAN — only two possible values: true or false

Constraints

Constraints are rules applied to columns or tables to limit the type of data that can be inserted, updated, or deleted. They're your first line of defense against messy, inconsistent data.

NOT NULL — the column can never be left empty
DEFAULT(value) — if nothing is given, this value is used automatically
UNIQUE — no two rows may share the same value in this column (e.g., two customers can't share one email address)
PRIMARY KEY — uniquely identifies each row in a table; it's essentially a combination of NOT NULL and UNIQUE
FOREIGN KEY — a column whose values must already exist as a primary key somewhere else; this is how tables link to each other
CHECK — a custom rule the value must pass, e.g., price > 0

Building A School Database

Lets build a database including what we have learned and exploring new ideas in querying, filtering and updating.

Creating the table

The SET SEARCH_PATH command ensures the data we input is sent to the relevant schema. Alternatively you can refer to the schema every time you reference the table you're working on e.g FROM greenwood_academy.students

Altering the table

Tables are rarely perfect on the first try. ALTER TABLE is DDL's way of letting you change a table's structure without dropping and recreating it.

Adding a column - the school forgot to capture phone numbers:

Renaming a column - if credit should really be called credit_hours:

Dropping a column - if phone_number turns out not to be needed after all:

Fixing and Removing Data

Once rows exist, DML gives you three tools: INSERT to input the values, UPDATE to correct existing rows and DELETE to remove them.

INSERT adds the values to the table following the constraints put while building the tables.

UPDATE changes the value of one or more columns in rows that match a condition:

DELETE removes whole rows that match a condition:

Disclaimer:The WHERE clause is doing critical work in every one of these statements. Leave it off an UPDATE or DELETE, and you'll change or remove every row in the table. Always double-check your WHERE clause before running either of these.

Filtering Rows with WHERE

WHERE is how you narrow a SELECT down to only the rows you care about, using comparison operators (=, !=, >, <, >=, <=) and logical operators (AND, OR, NOT) to combine conditions.

AND narrows your results (every condition must hold), while OR widens them (any condition can hold), mixing the two up is one of the most common beginner mistakes in SQL.

Range, Membership, and Pattern Matching

Beyond basic comparisons, SQL gives you a few operators purpose-built for common filtering patterns.

BETWEEN checks whether a value falls within an inclusive range. This mostly used for both numbers and dates:

IN and NOT IN check membership against a list of values, saving you from writing a long chain of OR conditions:

LIKE matches text patterns using % as a wildcard for any number of characters:

Note the % on both sides of 'Studies' in that last query: %Studies% matches the word anywhere in the name, while %Studies (with only a leading %) would only match names that end in "Studies"

Summarizing Data with COUNT

COUNT is an aggregate function — instead of returning individual rows, it returns a single number summarizing how many rows matched. Giving the result a readable alias with AS (like form3_students) makes the output much easier to interpret than a column literally named count.

Adding Conditional Logic with CASE WHEN

Sometimes you want a query to label rows based on a condition, without writing separate queries for each case. CASE WHEN works like an if/else chain, evaluated top to bottom for each row, and lets you create a new derived column right inside your SELECT.

Something to note from the above examples, CASE WHEN checks conditions in order and stops at the first match, so put your most specific conditions first (marks >= 80 is checked before marks >= 60).

Conclusion

SQL can feel like a lot of new vocabulary at first, but it boils down to a few core ideas:

A server hosts databases, which are organized into schemas.
A DBMS is the software managing all of this.
SQL is how you talk to it, split into sub-languages including DDL (structure), DML (data changes) and DQL (reading data).
Data types define what a column can hold, and constraints define what values are actually allowed in.
ALTER TABLE lets a schema evolve after tables already exist by adding, renaming, or dropping columns.
UPDATE and DELETE modify or remove existing rows, always guided by a WHERE clause.
WHERE, combined with comparison and logical operators, is how you filter a SELECT down to exactly the rows you need.
BETWEEN, IN/NOT IN, and LIKE handle ranges, lists, and text patterns.
COUNT summarizes matching rows into a single number, and CASE WHEN lets a query label rows with its own conditional logic.

With just CREATE TABLE, INSERT, and SELECT, you already have enough to build and query a real relational database — and with ALTER TABLE, UPDATE, DELETE, WHERE, and CASE WHEN in your toolkit, you can maintain that database and ask real questions of it.

Connecting SQL Databases to Power BI

Sam Guantai — Mon, 06 Jul 2026 00:09:33 +0000

Introduction

In any organization, Data is one of the most important assets. With Power BI we are able to turn the raw information into useful and actionable insights. By connecting SQL databases to Power BI, businesses can seamlessly access, analyze, and visualize data stored in their relational databases without the need for manual exports or complex reporting processes. Thus this integration process is an important skill for a data analyst to have. In this article, we'll explore how to connect SQL databases to Power BI and the different connection options available.

a few prerequisites

PostgreSQL installed and running
An Aiven account with a running PostgreSQL stream.

DBeaver - An open source, data management tool
PowerBI Desktop

Local Database

This is a storage system that exists on your device or a local network as opposed to a remote cloud server

Step One

Open DBeaver, Click on Database and establish a new database connection (PostgreSQL).

Fill in the required information.

Ensure you test the connection before finishing. Any errors can be detected and fixed at this point.
Inside the new database you can create a new schema where the imported data will go.
Select Input file(s) then Browse. Find the relevant data on your device and then click Proceed.

Step Two

Launch Power BI
Go to Home, then Get Data > More... > Database and select PostgreSQL database

You will be prompted to fill in the Server and database details.
Enter your Username and password when prompted and connect.

Preview your data and load it into Power BI.

Cloud-Based SQL Database

Step One

In this case you will repeat all the steps as you did with the local database but the details required when setting up a new connection will be sourced from Aiven.

Follow the same steps from the Local database section to create and import the data.

Step Two

Download the CA certificate provided in the Aiven account.
Go to your search bar and find the manage user certificates.
Follow these steps; Trusted Root Certification > Certificates (right click) > Import

Browse your files and find the certificate.
Press next on the wizard prompts till the end and Finish

Step Three

Launch Power BI
Go to Home, then Get Data > More... > Database and select PostgreSQL database

Enter the connection details from Aiven

Enter your Username and Password.
Select the table you want and proceed with cleaning or analysis.

Conclusion

And its that simple! As data continues to grow in importance, leveraging SQL databases and Power BI together provides a powerful foundation for effective analytics and reduces instances of error that is common when manually handling data.

Exploring Excel.

Sam Guantai — Sat, 06 Jun 2026 22:38:55 +0000

Microsoft Excel is a spreadsheet application used to organize, calculate, analyze, and visualize data. It allows users to store large amounts of information in rows and columns, perform calculations using formulas, and create charts and reports. In most computerized workplaces worldwide, Excel is far and away the most used application. A relatively low barrier of entry means that even the most green of workers can learn to at the very least use it as a tool for organizing company data.

Excel in real world data analytics

A majority of small to mid sized businesses rely on the insight provided by the data analyzed through Excel. A small workforce is able to analyze large amounts of data that would otherwise require a large workforce or take immense amounts of time. With the correct formulas and visualization tools the data can be used to provide a better picture into trends and even performance of employees or products that the business handles.

The IF function is particularly useful in this area because it allows businesses to automatically categorize or evaluate data based on specific conditions. For example, a company could use:

=IF(B2>=1000,"Target Met","Target Not Met")

to quickly determine whether a salesperson has achieved their sales target. This saves time, reduces errors, and makes it easier to identify areas that need attention.

-Excel is one of the most widely used tools for financial reporting and budgeting in businesses of all sizes. Accountants and financial analysts use it to record income and expenses, create detailed budgets, calculate profits and losses, and prepare financial statements. By organizing financial data into spreadsheets, companies can easily track where money is being spent and compare actual results against planned budgets.

The SUM function is particularly useful for accountants who need to balance the books and ensure that all expenses are accurately recorded. For example, if employee salaries are listed in Column B and utility expenses are listed in Column D, an accountant can calculate the total spending on both categories using the formula:

=SUM(B2:B20,D2:D20)

This formula adds together all salary and utility expenses for the accounting period, providing a combined total. By comparing this total against the company's budget or revenue, the accountant can quickly assess whether spending is within acceptable limits and identify any discrepancies that may require further investigation.

Marketing teams use Excel to evaluate the success of advertising campaigns and promotional activities. Data such as website visits, social media engagement, customer responses, and sales conversions can be organized and analyzed in spreadsheets. By identifying which campaigns generate the best results, marketers can focus their resources on the most effective strategies and improve future campaigns.

Conditional Formatting is particularly useful in marketing analysis because it allows important trends and performance indicators to stand out visually. For example, a marketing manager tracking campaign conversion rates could highlight high-performing campaigns in green and low-performing ones in red.

Thoughts one week in...

As a person who had only used Excel a handful of times before it has been quite an adventure to discover exactly how powerful a little green app on most computers can be. A single worksheet can tell the entire story of an enterprise and what seemed like a daunting amount of data can be analyzed and meaningfully presented in only a few, properly executed commands. I'm quite excited to see how much improvement I can notice in my own analysis in the coming weeks.