The Ultimate Beginner-Friendly SQL Guide (With Clear Examples)

If you’re starting your SQL journey, you’ve probably realized that most tutorials either oversimplify SQL or overwhelm you with theory. This guide breaks down essential SQL concepts through practical examples—based entirely on real teaching notes—to help you understand not just what SQL can do, but why it’s used this way.

Whether you want to query databases, analyze data, or prepare for a tech job interview, this guide will walk you through SQL fundamentals, joins, subqueries, ranking functions, and data cleaning techniques with step-by-step examples.

1. Getting Started: Creating Databases and Tables

Create a Database

The first step in SQL is defining where your data will live.

CREATE DATABASE sample2;
USE sample2;

Create a Table

This example demonstrates a customer table with constraints such as PRIMARY KEY, UNIQUE, and CHECK.

CREATE TABLE customer (
  customerid INT IDENTITY(1,1) PRIMARY KEY,
  customernumber INT NOT NULL UNIQUE CHECK (customernumber > 0),
  lastname VARCHAR(30) NOT NULL,
  firstname VARCHAR(30) NOT NULL,
  areacode INT DEFAULT 71000,
  address VARCHAR(50),
  country VARCHAR(50) DEFAULT 'Malaysia'
);

2. Insert, Update, and Delete Records (DML Commands)

Once the table exists, you can manipulate the data using Data Manipulation Language (DML).

Insert Data

INSERT INTO customer VALUES
(100,'Fang Ying','Sham','418999','sdadasfdfd',DEFAULT),
(200,'Mei Mei','Tan',DEFAULT,'adssdsadsd','Thailand'),
(300,'Albert','John',DEFAULT,'dfdsfsdf',DEFAULT);

Update Data

UPDATE customer
SET phonenumber='1234545346'
WHERE customerid = 1;

Delete a Record

DELETE FROM customer
WHERE country='Thailand';

Tip: Omitting the WHERE clause will delete every record in the table—one of the most common SQL mistakes.

3. Querying Data (DQL)

SQL’s SELECT command retrieves data. You can return all results or only specific columns.

Basic Select

SELECT * FROM customer;

Select Specific Columns

SELECT customerid, lastname, firstname FROM customer;

Sorting Results

SELECT lastname, firstname
FROM customer
ORDER BY lastname DESC;

Using DISTINCT

SELECT DISTINCT lastname
FROM customer;

4. Filtering Data With Powerful Conditions

SQL allows precise filtering using operators and pattern matching.

LIKE (Pattern Search)

SELECT *
FROM customer
WHERE lastname LIKE '_r%';  -- last names with 'r' as the second character

IN (Multiple Matches)

SELECT *
FROM customer
WHERE lastname IN ('Brown', 'Michael', 'Jim');

BETWEEN

SELECT *
FROM sale
WHERE saleunitprice BETWEEN 5 AND 10;

5. Aggregations: COUNT, SUM, AVG, MIN, MAX

Aggregate functions summarize data—an essential skill for analytics, reporting, and dashboards.

Count the Number of Customers With B-Lastnames

SELECT COUNT(*) AS NumberOfRecords
FROM customer
WHERE firstname LIKE 'B%';

Sum Sales by Employee

SELECT 
  sale.employeeid,
  EmployeeFirstName,
  EmployeeLastName,
  COUNT(*) AS NumberOfOrders,
  SUM(salequantity) AS TotalQuantity
FROM sale, employee
WHERE sale.employeeid = employee.employeeid
GROUP BY sale.employeeid, EmployeeFirstName, EmployeeLastName;

6. SQL Joins Explained With Real Examples

Joins connect data from multiple tables. This is where SQL truly becomes powerful.

Inner Join

Returns matching rows from both tables.

SELECT inventoryname, saledate, saleunitprice
FROM inventory
INNER JOIN sale
ON sale.inventoryid = inventory.inventoryid;

Left Join

Returns all records from the left table—even if there’s no match.

SELECT inventory.inventoryid, inventoryname
FROM inventory
LEFT JOIN sale 
ON sale.inventoryid = inventory.inventoryid;

Full Outer Join

Returns all records from both sides—even unmatched ones.

SELECT sale.inventoryid, inventoryname
FROM inventory
FULL OUTER JOIN sale
ON sale.inventoryid = inventory.inventoryid
WHERE sale.inventoryid IS NULL;

Self Join (Hierarchy Example)

Useful for employee–manager relationships.

Staff Table Example

employeeID	firstname	managerID
1001	Tan Mei Ling	NULL
1002	Kelvin Koh	1001
1003	Amin Wong	1002

Self Join Query

SELECT E.employeeID, 
       E.employeefirstname + ' ' + E.employeelastname AS FullName,
       M.employeefirstname + ' ' + M.employeelastname AS ManagerName
FROM staff E
INNER JOIN staff M
ON E.managerID = M.employeeID;

7. UNION, INTERSECT, EXCEPT

UNION (Remove Duplicates)

SELECT cust_lname, cust_fname FROM customer
UNION
SELECT cust_lname, cust_fname FROM customer_2;

EXCEPT (Records Unique to First Table)

SELECT cust_lname, cust_fname FROM customer
EXCEPT
SELECT cust_lname, cust_fname FROM customer_2;

8. Views, Temp Tables, and CTEs

View Example

Reusable virtual table:

CREATE VIEW CustomerView AS
SELECT customerfirstname + ' ' + customerlastname AS CustomerName,
       customerphonenumber, inventoryname, saledate,
       salequantity, saleunitprice,
       salequantity * saleunitprice AS TotalAmount
FROM customer
INNER JOIN sale ON customer.customerid = sale.customerid
INNER JOIN inventory ON sale.inventoryid = inventory.inventoryid;

Temp Table

Stored only for the session:

CREATE TABLE #temp_Employee (
  JobTitle VARCHAR(100),
  EmployeesPerJob INT,
  AvgAge INT,
  AvgSalary INT
);

CTE Example

Useful for complex queries:

WITH CTE_Employee AS (
  SELECT FirstName, LastName, Gender, Salary,
         COUNT(Gender) OVER (PARTITION BY Gender) AS TotalGender
  FROM EmployeeDemographics ED
  JOIN EmployeeSalary ES ON ED.EmployeeID = ES.EmployeeID
  WHERE Salary > 45000
)
SELECT * FROM CTE_Employee
WHERE TotalGender = (SELECT MIN(TotalGender) FROM CTE_Employee);

9. Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)

These functions are essential for analytics and reporting.

ROW_NUMBER

SELECT *,
  ROW_NUMBER() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM EmployeeSalary;

RANK (skips ranks on ties)

DENSE_RANK (does not skip ranks)

NTILE (splits into quartiles or any number of groups)

These are especially useful for salary banding or sales performance dashboards.

10. Data Cleaning Techniques (Real-World Examples)

Splitting Addresses

SELECT PropertyAddress,
  SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress)-1) AS Address,
  SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress)+1, LEN(PropertyAddress)) AS City
FROM NashvilleHousing;

Remove Duplicate Records

WITH RowNumCTE AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY ParcelID, PropertyAddress, SalePrice, SaleDate, LegalReference
      ORDER BY UniqueID
    ) AS row_num
  FROM NashvilleHousing
)
SELECT * FROM RowNumCTE
WHERE row_num > 1;

Final Thoughts

The SQL foundations in this guide—table creation, querying, joins, analytics functions, and data cleaning—represent the skills used daily in data analysis, backend development, and business intelligence. Once you understand these building blocks, you can confidently handle complex databases, optimize queries, and build insights from real-world datasets.