SQL for Beginners: Master SELECT, WHERE, ORDER BY, and GROUP BY

What does an SQL table look like inside a production app?#

Walk into any tech floor along OMR in Chennai or Outer Ring Road in Bengaluru, and you will see the same pattern. Whether the company builds a quick-commerce delivery app, an ERP tool, or a core banking engine, the business logic eventually speaks to a relational database.

Think of an SQL table as a locked-down spreadsheet. An Excel sheet lets you type text in a number column or leave random gaps without complaining. A production SQL table refuses to play along with sloppy inputs. Every column has a strict data type, and every row represents one single event, record, or entity.

Imagine a simplified e-commerce orders table named customer_orders tracking purchases across Indian cities. Each row stores an order_id, the buyer's city, the category, the transaction amount_inr, and the payment_status. You cannot build dashboards, debug API drop-offs, or calculate weekly revenue without knowing how to query those rows cleanly.

How do you pull exact records using SELECT and WHERE?#

The SELECT statement specifies the exact columns you want your application or report to receive. Grabbing every column with an asterisk is fine for quick local checks, but production code should explicitly ask for the fields it needs.

The WHERE clause acts as your primary filter. It tests every row in the table against one or more logical conditions. If a row satisfies the condition, the database includes it; otherwise, the engine skips it immediately.

Here is how you retrieve the order identifier, city, and bill amount for completed orders with a basket size above ₹1,000:

SELECT 
    order_id, 
    city, 
    amount_inr
FROM 
    customer_orders
WHERE 
    payment_status = 'COMPLETED'
    AND amount_inr > 1000;

In this snippet, SQL checks the table row by row. It keeps only the transactions that meet both criteria and returns just those three requested columns, avoiding unnecessary network payload.

How do ORDER BY and LIMIT organize your data pipeline?#

Databases do not store records in a guaranteed chronological visual order. When you run a plain query, the database engine returns records in whichever sequence is fastest to pull from storage disks or cache.

To enforce an order, you use ORDER BY. Adding DESC sorts from highest to lowest or newest to oldest, while ASC arranges from lowest to highest. When paired with LIMIT, you can pull ranked subsets, like the top five highest-value transactions.

Here is how you pull the top three highest-value electronics orders placed in Chennai:

SELECT 
    order_id, 
    category, 
    amount_inr
FROM 
    customer_orders
WHERE 
    city = 'Chennai'
    AND category = 'Electronics'
ORDER BY 
    amount_inr DESC
LIMIT 3;

The database first isolates all Chennai electronics purchases, sorts those remaining rows by bill value starting with the largest figure, and cuts off the result stream after three rows.

How do GROUP BY and aggregate functions summarize sales data?#

Extracting raw individual rows works when looking up an order for customer support. But leadership teams and product managers rarely care about one isolated ₹450 order. They want to know total revenue per city or average transaction values per department.

This is where aggregate functions come into play. Standard tools include COUNT() for volume, SUM() for totals, and AVG() for averages. When you combine these functions with GROUP BY, SQL bundles rows sharing the same value and runs the calculation on each group.

Here is how you find the total revenue and the number of successful orders across each operating city:

SELECT 
    city,
    COUNT(order_id) AS total_orders,
    SUM(amount_inr) AS gross_revenue_inr
FROM 
    customer_orders
WHERE 
    payment_status = 'COMPLETED'
GROUP BY 
    city
ORDER BY 
    gross_revenue_inr DESC;

Notice the execution sequence here. The engine applies the WHERE filter first to drop pending or failed payments. Then it groups the surviving rows by city name, computes the order count and revenue sum for each cluster, and finally presents the output sorted by total revenue.

How can you practice on a realistic Indian e-commerce sales dataset?#

To see these four clauses functioning together, picture this small snapshot of our customer_orders table:

Suppose your team lead asks: "Which product categories generated more than ₹5,000 in completed sales across Chennai and Bengaluru, and what was their total turnover?"

SELECT 
    category,
    SUM(amount_inr) AS category_revenue
FROM 
    customer_orders
WHERE 
    payment_status = 'COMPLETED'
    AND city IN ('Chennai', 'Bengaluru')
GROUP BY 
    category
HAVING 
    SUM(amount_inr) > 5000
ORDER BY 
    category_revenue DESC;

In this query, IN ('Chennai', 'Bengaluru') filters geographic zones before grouping. The HAVING clause works like a post-aggregation filter, kicking out any category whose combined total fails to cross ₹5,000.

Which common SQL mistakes trip up beginners in IT interviews?#

One frequent mistake is filtering aggregate metrics inside the WHERE clause. Writing WHERE SUM(amount_inr) > 1000 breaks your query because the database reads the WHERE condition long before it calculates group sums. You must use HAVING for metric filters.

Another common slip involves picking non-aggregated columns that are not included in the GROUP BY clause. If you group by city, asking for individual order_id values in the same SELECT line will throw an error in modern SQL engines like PostgreSQL because the engine cannot know which specific order ID to attach to the summary row.

A third regular error is using standard equality operators for missing values. In SQL, null represents unknown information. Writing WHERE payment_status = NULL will never return a row; you must explicitly check conditions using IS NULL or IS NOT NULL.

Where do Indian tech companies test these basic queries?#

Service majors like TCS, Infosys, and Cognizant, alongside product firms like Zoho and Swiggy, routinely begin initial technical screenings with plain SQL challenges. You might spend all week debugging a complex backend microservice or setting up automated tests in Selenium, but technical interviewers use SQL to gauge how logically you navigate relational data.

Junior developers are often asked to extract recurring users or identify stalled checkout attempts. Analysts are tested on cohort metrics and daily revenue cuts. If you can handle clean joins, group aggregations, and filtered sorting comfortably on a whiteboard or shared coding pad, clearing the initial screening round becomes straightforward.

What should your next practice exercise be?#

Do not stop at reading code snippets. Open an interactive sandbox like SQLite Online or install PostgreSQL locally, create the sample table above, and write a query for this requirement:

Find the average order value (AVG(amount_inr)) for all completed orders in Chennai, grouped by category, and display the highest average first. Once you can structure that without looking at cheat sheets, you are ready to tackle table joins.