Introduction to SQL: Understanding Databases, Data Types, Constraints, and Core SQL Concepts

SQL (Structured Query Language) is one of the most fundamental skills for anyone interested in Data Analytics, Data Engineering, Software Development, or Database Administration. Whether you're building a web application or analyzing business data, SQL enables you to communicate with relational databases efficiently.

What is Data?

Data refers to raw, unorganized facts and figures. It can exist in many forms, including:

• Numbers
• Text
• Images
• Audio
• Videos
• Dates and times

On its own, data has little meaning until it is organized and processed.

What is a Database?

A database is an organized collection of data that is structured for easy access, management, and updating.

Instead of storing information in multiple spreadsheets, databases keep related information together, making it easier to search, retrieve, and maintain.

For example, a school database may contain:

• Students
• Teachers
• Subjects
• Exam Results
• Understanding Database Architecture

A database system is organized into different layers.

1. Server

The server is the top-most level.

It is the actual software process (such as PostgreSQL or MySQL) running on a computer.

Think of the server as the entire building.

2. Database

Inside a server are one or more databases.

A database acts like a separate floor within the building, storing data for a specific application or organization.

3. Schema

A schema organizes database objects inside a database.

Think of a schema as rooms on a floor, helping separate tables based on their purpose.

For example:

Server
│
├── Greenwood Academy Database
│ ├── Students Schema
│ ├── Finance Schema
│ └── Library Schema

What is a DBMS?

A Database Management System (DBMS) is software that enables users to create, manage, update, and interact with databases.

Popular DBMSs include:

• PostgreSQL
• MySQL
• Oracle Database
• Microsoft SQL Server

A DBMS provides the tools needed to store, organize, and retrieve information efficiently.

What is SQL?

SQL (Structured Query Language) is the standard language used to communicate with relational databases.

Think of SQL as the language you use to "talk" to your database.

Using SQL, you can:

1. Create databases
2. Create tables
3. Insert data
4. Update records
5. Delete records
6. Retrieve information
7. Manage users and permissions
8. Types of SQL Commands

SQL is divided into several categories depending on the task being performed.

*1. DDL (Data Definition Language)
*
DDL focuses on creating and modifying database structures.

Common commands include:

• CREATE
• ALTER
• DROP

Example:

CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50)
);
2. DML (Data Manipulation Language)

DML works with the data stored inside existing tables.

Common commands include:

*INSERT
UPDATE
DELETE
*
Example:

INSERT INTO students
VALUES (1, 'Amina');
*3. DQL (Data Query Language)
*
DQL is used to retrieve information from a database.

The primary command is:

SELECT

Example:

SELECT *
FROM students;

This is the command you'll use most frequently when analyzing data.

*4. DCL (Data Control Language)
*
**DCL **controls access to the database.

Examples include granting or revoking user permissions.

Common commands:

• GRANT
• REVOKE *5. TCL (Transaction Control Language) * TCL manages database transactions.

Common commands include:

• COMMIT
• ROLLBACK
• SAVEPOINT These commands ensure data consistency when multiple operations are performed.

Understanding Data Types

Every column in a database must specify the type of data it will store.

Choosing the correct data type improves performance, accuracy, and storage efficiency.

Numeric Data Types
INT

Stores whole numbers.

Example:

25
100
500
DECIMAL / NUMERIC

Stores numbers with fixed decimal places.

Ideal for:

• Prices
• Salaries
• Financial records Example:

2500.75
99.99
SERIAL

Automatically generates sequential numbers.

Commonly used for:

• Primary Keys
• IDs
• Text Data Types
• CHAR(n)

Stores text with an exact number of characters.

Example:

CHAR(10)

Useful for fixed-length values like codes.

VARCHAR(n)

Stores text with a maximum length.

Example:

VARCHAR(50)

Suitable for names and addresses.

TEXT

Stores large amounts of text without a predefined limit.

Useful for:

• Descriptions
• Articles
• Comments
• Date and Time Data Types
• DATE

Stores only the date.

Example:

2025-07-20
TIME

Stores only the time.

Example:

14:30:00
TIMESTAMP

Stores both the date and time.

Example:

2025-07-20 14:30:00
BOOLEAN

A Boolean column stores only two possible values:

• TRUE
• FALSE

Example:

is_active = TRUE
Understanding Constraints

Constraints are rules applied to tables or columns to ensure data integrity and prevent invalid information from entering the database.

NOT NULL

Ensures a column cannot be left empty.

Example:

Every student must have a first name.

DEFAULT

Provides a default value when none is supplied.

Example:

status DEFAULT 'Active'
UNIQUE

Ensures duplicate values are not allowed.

Example:

Two users cannot register with the same email address.

PRIMARY KEY

Uniquely identifies each row in a table.

A Primary Key:

Cannot be NULL
Must be unique

Example:

student_id
FOREIGN KEY

Links one table to another.

For example:

Students

student_id

Exam Results

student_id

The student_id in the Exam Results table must already exist in the Students table.

This relationship prevents invalid references.

CHECK

Applies custom validation rules.

Example:

CHECK (marks >= 0)

or

CHECK (price > 0)

The database will reject values that violate these conditions.

Why These Concepts Matter

Before writing complex SQL queries involving joins, aggregations, or window functions, it's essential to understand:

• How databases are organized.
• The purpose of schemas and tables.
• Choosing the correct data types.
• Using constraints to maintain clean and reliable data.
• The different categories of SQL commands.

JOINs – Combining Data from Multiple Tables

In a relational database, information is often split across multiple tables to reduce redundancy and improve organization. JOINs allow you to combine related data from these tables into a single result.

For example, imagine a school database with three tables:

1. Students
2. Subjects
3. Exam Results

Instead of storing all information in one table, the database links them using keys.
A JOIN lets you answer questions like:

• Which subjects is each student taking?
• What marks did each student score?
• Who teaches each subject?

Common types of JOINs include:

INNER JOIN – Returns only matching records from both tables.
LEFT JOIN – Returns all records from the left table and matching records from the right table.
RIGHT JOIN– Returns all records from the right table and matching records from the left table.
FULL OUTER JOIN – Returns all records from both tables, whether they match or not.

JOINs are among the most frequently used SQL operations because real-world databases almost always store information across multiple related tables.

2. Aggregate Functions – Summarizing Data

Aggregate functions calculate values across multiple rows and return a single result. Instead of viewing individual records, aggregates help summarize and analyze data.

Some common aggregate functions include:

• COUNT() – Counts the number of records.
• SUM() – Calculates the total.
• *AVG() *– Finds the average.
• *MIN() *– Returns the smallest value.
• MAX() – Returns the largest value. For example, a school administrator might want to know:

1. How many students are enrolled?
2. What is the average exam score?
3. Which student scored the highest mark?
4. How many students are in each class?

Aggregate functions are widely used in business intelligence, reporting, and dashboard development.

3. Subqueries – Queries Within Queries

A subquery is a SQL query nested inside another SQL query. It allows you to use the result of one query as input for another.

Subqueries are useful when solving more complex problems, such as:

1. Finding students who scored above the class average.
2. Identifying products with sales higher than the average.
3. Listing employees earning more than their department's average salary.

Instead of performing multiple separate queries, SQL can handle everything in one statement.

Subqueries make SQL more flexible and allow you to solve sophisticated analytical problems with minimal code.

4. Common Table Expressions (CTEs) – Writing Cleaner SQL

As SQL queries become longer, they can become difficult to read and maintain.

Common Table Expressions (CTEs) help organize complex queries by breaking them into logical sections.

A CTE acts like a temporary named result set that exists only during the execution of a query.

Benefits of using CTEs include:

• Improved readability.
• Easier debugging.
• Better organization of complex logic.
• Simplified maintenance. Instead of writing deeply nested subqueries, you can separate each logical step into its own CTE, making your SQL easier for both you and your teammates to understand.

CTEs are especially useful in reporting, analytics, and data engineering workflows.

Window Functions – Performing Advanced Analytics

Window functions are among the most powerful features in SQL. Unlike aggregate functions, which reduce multiple rows into one result, window functions perform calculations across related rows while keeping every individual row in the output.

This makes them ideal for analytical tasks such as:

1. Ranking students by exam score.
2. Comparing a student's mark to the class average.
3. Calculating running totals.
4. Finding previous or next values.
5. Identifying top-performing products or employees. .

Common window functions include:

**-ROW_NUMBER()

• RANK()
• DENSE_RANK()
• LAG()
• LEAD()
• FIRST_VALUE()
• LAST_VALUE()**

Window functions are heavily used in business intelligence, financial reporting, customer analytics, and machine learning data preparation.

How These Concepts Work Together

Consider a school management system.

You might:

• Use JOINs to combine student, subject, and exam data.
• Apply aggregate functions to calculate average marks.
• Use a subquery to identify students scoring above average.
• Organize the logic using a CTE for better readability.
• Apply window functions to rank students from highest to lowest. Each concept builds upon the previous one, enabling increasingly sophisticated analysis.

Why Every Data Professional Should Learn Advanced SQL

Modern organizations rely heavily on data-driven decision-making. Advanced SQL skills enable professionals to:

1. Build interactive dashboards.
2. Generate business reports.
3. Analyze customer behavior.
4. Monitor financial performance.
5. Prepare datasets for machine learning.
6. Design efficient data pipelines.
7. Support business intelligence initiatives.

Whether you're working in healthcare, finance, agriculture, education, or e-commerce, these SQL techniques are essential for extracting meaningful insights from data.

Conclusion

Mastering advanced SQL is a natural progression after learning the fundamentals. Concepts like databases, data types, constraints, SQL command categories ,JOINs, aggregate functions, subqueries, Common Table Expressions (CTEs), and window functions enable you to move beyond basic queries and solve complex, real-world data challenges.

These skills are not only valuable for writing efficient SQL—they are also core competencies for careers in Data Analytics, Data Engineering, Business Intelligence, Database Administration, and Software Development.

Every advanced SQL expert started with the basics. By practicing these concepts consistently and applying them to real projects, you'll build the confidence and expertise needed to work with large datasets, develop insightful reports, and create data-driven solutions that make a meaningful impact.