Menu

SQL Basics for Data Science

Data science projects frequently begin with information stored in relational databases. Instead of exporting entire databases, data scientists use SQL queries to retrieve the records and columns required for analysis.

For example, an e-commerce company may store millions of transactions in an orders table. SQL can identify high-value purchases, filter transactions by date, and retrieve customer spending information.

SQL is a declarative language used to query and manage relational databases. It allows users to specify which information they need without manually defining every step used by the database engine to retrieve it.

What Is a Relational Database?

A relational database organises information into tables containing rows and columns.

Each row represents a record, while each column represents an attribute.

Consider an orders table.

order_id customer_id category amount status
101 C001 Electronics 12000 Completed
102 C002 Clothing 3500 Completed
103 C003 Electronics 8500 Cancelled
104 C001 Home Decor 2500 Completed
105 C004 Clothing 6000 Completed

The table contains five records and five columns.

A database may contain multiple related tables, such as customers, orders, and products.

What Are Primary Keys and Foreign Keys?

A primary key uniquely identifies a record within a table.

For example, order_id may be the primary key of the orders table.

A foreign key references a key in another table and helps enforce relationships between records.

For example, customer_id in an orders table may reference the primary key of a customers table.

These relationships allow data scientists to combine information across tables.

What Is the SELECT Statement?

The SELECT statement retrieves specified columns from a database table.

SELECT
    customer_id,
    category,
    amount
FROM orders;

The query returns three columns from the orders table.

Selecting All Columns

SELECT *
FROM orders;

The asterisk selects every column.

Although convenient during initial exploration, selecting only the required columns is generally preferable for large analytical datasets.

It reduces unnecessary data transfer and makes the query's output structure clearer.

How Does the WHERE Clause Work?

The WHERE clause filters rows according to a condition.

For example, a retailer may want to identify completed orders.

SELECT
    order_id,
    customer_id,
    amount
FROM orders
WHERE status = 'Completed';

The query returns only records with the status Completed.

SQL Comparison Operators

Operator Meaning Example
= Equal to amount = 5000
<> Not equal to status <> 'Cancelled'
> Greater than amount > 5000
< Less than amount < 5000
>= Greater than or equal to amount >= 5000
<= Less than or equal to amount <= 5000

SQL also supports != in PostgreSQL, although <> is the standard inequality operator.

How Do You Filter Using Multiple Conditions?

SQL provides logical operators for combining conditions.

AND Operator

AND requires both conditions to be true.

SELECT
    order_id,
    amount
FROM orders
WHERE status = 'Completed'
  AND amount > 5000;

This retrieves completed orders with amounts greater than ₹5,000.

OR Operator

OR requires at least one condition to be true.

SELECT
    order_id,
    category
FROM orders
WHERE category = 'Electronics'
   OR category = 'Clothing';

NOT Operator

NOT reverses a condition.

SELECT *
FROM orders
WHERE NOT status = 'Cancelled';

For complex filters, parentheses help control logical precedence.

What Are BETWEEN, IN, and LIKE?

SQL provides operators for common filtering requirements.

BETWEEN

BETWEEN checks whether a value falls within an inclusive range.

SELECT *
FROM orders
WHERE amount BETWEEN 3000 AND 10000;

This includes orders with amounts from ₹3,000 through ₹10,000.

IN

IN compares a value against a collection of specified values.

SELECT *
FROM orders
WHERE category IN ('Electronics', 'Clothing');

This is more concise than repeating multiple OR conditions.

LIKE

LIKE performs pattern matching on text.

SELECT *
FROM orders
WHERE category LIKE 'Elec%';

The % wildcard matches zero or more characters.

The query matches categories beginning with Elec.

In PostgreSQL, LIKE is generally case-sensitive, while ILIKE supports case-insensitive pattern matching.

How Do You Sort Data Using ORDER BY?

The ORDER BY clause sorts query results.

SELECT
    order_id,
    amount
FROM orders
ORDER BY amount DESC;

DESC sorts values in descending order.

ASC sorts in ascending order and is the default when a direction is not specified.

Sorting Multiple Columns

SELECT
    customer_id,
    amount
FROM orders
ORDER BY customer_id ASC, amount DESC;

The query groups the output by customer ID order and sorts amounts from highest to lowest within each customer.

What Is the LIMIT Clause?

LIMIT restricts the number of rows returned.

SELECT
    order_id,
    amount
FROM orders
ORDER BY amount DESC
LIMIT 3;

Output:

order_id amount
101 12000
103 8500
105 6000

This retrieves the three highest-value orders, including the cancelled order because no status filter was applied.

LIMIT should generally be combined with ORDER BY when a predictable subset is required.

Without explicit ordering, the database does not guarantee which records will appear first.

What Is DISTINCT in SQL?

DISTINCT removes duplicate rows from the selected result columns.

SELECT DISTINCT category
FROM orders;

Output:

category
Electronics
Clothing
Home Decor

The query returns unique product categories.

Counting Unique Customers

SELECT
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Output:

unique_customers
4

The database counts four distinct customer IDs.

COUNT(DISTINCT column) excludes NULL values.

How Does SQL Handle NULL Values?

NULL represents missing, unknown, or unavailable information.

It is different from zero or an empty string.

A missing value cannot be reliably checked using the equality operator.

Incorrect:

WHERE amount = NULL

Correct:

SELECT *
FROM orders
WHERE amount IS NULL;

To identify non-missing values:

SELECT *
FROM orders
WHERE amount IS NOT NULL;

SQL uses three-valued logic involving TRUE, FALSE, and UNKNOWN.

This matters because comparisons involving NULL generally produce an unknown result rather than true or false.

What Is the Logical Execution Order of a SQL Query?

SQL queries are written in one order but are logically evaluated in another.

A simplified order is:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. DISTINCT
  7. ORDER BY
  8. LIMIT

This logical order helps explain why aggregate functions cannot generally be used directly in a WHERE clause.

Actual database engines may optimise the physical execution plan.

How Is Basic SQL Used in Data Science?

Data scientists use basic SQL queries to:

  • Extract relevant observations for analysis.
  • Filter historical records by dates or status.
  • Identify unique categories and customer identifiers.
  • Retrieve high-value transactions.
  • Examine missing fields.
  • Prepare smaller datasets for Python processing.

For example, a fraud detection project may retrieve recent transactions above a selected amount before performing further analysis.

Common SQL Mistakes

  1. Using SELECT * unnecessarily on large tables.
  2. Confusing = with IS NULL.
  3. Using LIMIT without ORDER BY when consistent ordering matters.
  4. Forgetting parentheses in complex logical conditions.
  5. Treating SQL results as automatically sorted.
  6. Ignoring cancelled or invalid records when calculating business metrics.

Data Science Tutorial for Beginners

VA

Vaishali Ardhana



Learn via Video Course

undefined Logo

0 hrs



Get in Touch For Details