Beginner Guide to Writing SQL Queries for Full-Stack Projects

Introduction

When developing full-stack applications, understanding SQL and how to write queries is essential.

SQL (Structured Query Language) allows you to store, retrieve, and manipulate data in a relational database like SQL Server, MySQL, or PostgreSQL.

Even if you are mainly working on Angular, React, or ASP.NET Core, knowing SQL basics is critical for building functional applications.

This guide will explain SQL concepts, queries, and examples you can use in full-stack projects.

What is SQL?

SQL is a language used to communicate with relational databases.
It allows you to perform operations like:

Creating tables and databases
Inserting, updating, deleting data
Querying data
Managing relationships between tables

Common relational databases used in full-stack projects include:

SQL Server
MySQL
PostgreSQL
SQLite

Basic SQL Operations

SQL commands are usually categorized as:

DDL (Data Definition Language): Defines database structure.
DML (Data Manipulation Language): Manages data inside tables.
DCL (Data Control Language): Manages user permissions.
TCL (Transaction Control Language): Manages database transactions.

For full-stack projects, you will mostly use DDL and DML.

Step 1: Creating a Database and Tables

Create Database

CREATE DATABASE FullStackDB;

This command creates a new database named FullStackDB.

Use the Database

USE FullStackDB;

Create Table

Example: Create a Users table

CREATE TABLE Users (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
);

Id – Auto-increment primary key.
Name and Email – Text fields.
CreatedAt – Timestamp for record creation.

Step 2: Inserting Data

To add data to the Users table:

INSERT INTO Users (Name, Email)
VALUES ('Rahul Sharma', '[email protected]');

INSERT INTO Users (Name, Email)
VALUES ('Priya Singh', '[email protected]');

Each row represents a user.
INSERT INTO is a DML command.

Step 3: Querying Data

Select All Data

SELECT * FROM Users;

Retrieves all columns and rows from the Users table.

Select Specific Columns

SELECT Name, Email FROM Users;

Retrieves only Name and Email columns.

Filter Data Using WHERE

SELECT * FROM Users
WHERE Name = 'Rahul Sharma';

Returns only the user with the specified name.

Step 4: Updating Data

UPDATE Users
SET Email = '[email protected]'
WHERE Name = 'Rahul Sharma';

UPDATE changes existing data.
WHERE ensures only the intended row is updated.

Step 5: Deleting Data

DELETE FROM Users
WHERE Name = 'Priya Singh';

Deletes the row matching the condition.
Be careful: omitting WHERE deletes all rows.

Step 6: Sorting Data

SELECT * FROM Users
ORDER BY Name ASC;

ASC – Ascending order.
DESC – Descending order.

Step 7: Using Joins

In full-stack projects, tables are often related.

Example: Users and Orders

CREATE TABLE Orders (
    Id INT PRIMARY KEY IDENTITY(1,1),
    UserId INT,
    Product NVARCHAR(100),
    Quantity INT,
    FOREIGN KEY (UserId) REFERENCES Users(Id)
);

Inner Join

SELECT Users.Name, Orders.Product, Orders.Quantity
FROM Users
INNER JOIN Orders ON Users.Id = Orders.UserId;

Combines Users and Orders data based on UserId.
Returns only matching rows.

Left Join

SELECT Users.Name, Orders.Product, Orders.Quantity
FROM Users
LEFT JOIN Orders ON Users.Id = Orders.UserId;

Returns all users, even if they have no orders.

Step 8: Aggregation Functions

SQL has built-in functions for counting, summing, and averaging:

-- Count total users
SELECT COUNT(*) AS TotalUsers FROM Users;

-- Sum quantity of all orders
SELECT SUM(Quantity) AS TotalQuantity FROM Orders;

-- Average quantity per order
SELECT AVG(Quantity) AS AverageQuantity FROM Orders;

Useful for dashboards and reports in full-stack applications.

Step 9: Filtering with LIKE

SELECT * FROM Users
WHERE Email LIKE '%example.com';

% – Wildcard character.
Returns all users with emails ending in example.com.

Step 10: Using Transactions

Transactions help ensure data integrity:

BEGIN TRANSACTION;

UPDATE Users SET Email = '[email protected]' WHERE Id = 1;
INSERT INTO Orders (UserId, Product, Quantity) VALUES (1, 'Laptop', 2);

COMMIT;  -- Save changes
-- ROLLBACK;  -- Undo changes if there is an error

Ensures multiple operations succeed or fail together.

Step 11: Best Practices for Full-Stack Projects

Use Primary Keys: Every table should have a unique identifier.
Use Foreign Keys: Maintain relationships between tables.
Avoid Hardcoding Data: Use parameters in queries for safety.
Normalize Data: Reduce redundancy by splitting related data into tables.
Use Indexes: Improve query performance on large tables.
Always Test Queries: Check with sample data before integrating with backend.

Step 12: Connecting SQL with Full-Stack Applications

In ASP.NET Core, use Entity Framework Core or ADO.NET to run queries.
In Node.js, use packages like mysql, pg, or sequelize.
In Angular/React, you never connect directly to SQL. Instead, the frontend communicates with backend APIs, which then interact with SQL.

Example workflow

Angular Frontend → HTTP Request → ASP.NET Core API → SQL Server → API Response → Angular

Conclusion

Learning SQL basics is essential for full-stack developers.

SQL helps you create, read, update, and delete data in relational databases.
Understanding joins, aggregation, and transactions is crucial for real-world projects.
Writing clean and efficient SQL queries ensures your backend APIs are fast and reliable.

With this knowledge, you can confidently connect your frontend frameworks like Angular or React to a SQL database via backend APIs and build robust full-stack applications.