TRAINING | SQL REFRESHER

SQL for Excel Users: Filter, Sort, Group and Aggregate

Creating and loading tables, SELECT, TOP, ORDER BY, WHERE operators, NULL, GROUP BY, HAVING and DISTINCT on an orders dataset.

1. The Crux in 60 Seconds

1Excel actions have SQL equivalents. Filter is WHERE, sort is ORDER BY, drop-down list of values is DISTINCT, pivot table is GROUP BY with an aggregate.

2Do not run SELECT * on big tables. Use TOP n and pick only the columns you need: fewer rows and columns is better for performance.

3WHERE filters rows, HAVING filters groups. WHERE checks each row on its own; HAVING filters the aggregated result after GROUP BY.

4NULL is unknown. = NULL never works; use IS NULL / IS NOT NULL.

2. Foundational Concepts & Comparisons

The dataset: Superstore-style orders (1,000 rows in class)

Like Amazon-style e-commerce orders: order ID, order date, ship date, ship mode (first, second, standard class), customer ID and name, segment (consumer, corporate, home office), country, city, state, product ID, category (furniture, office supplies, technology), subcategory, product name, sales, quantity, discount, profit. One order ID can have several rows (one per product). The SQL is the same with millions of rows; only 1,000 are used so queries run fast.

In Excel	In SQL
Open the sheet and see all data	`SELECT * FROM orders`
Hide columns, copy to another sheet	Name the columns: `SELECT order_id, order_date FROM orders`
Sort A to Z or Z to A	`ORDER BY col` (ascending by default) or `DESC`
Filter on a value, a list or a range	`WHERE` with `=`, `IN`, `BETWEEN`, `LIKE`
Filter drop-down of unique values	`SELECT DISTINCT segment` (or GROUP BY)
Filter blanks	`IS NULL`
Pivot table: category and sum of sales	`GROUP BY category` with `SUM(sales)`
Filter pivot results (sum above 20,000)	`HAVING SUM(sales) > 20000`

SQL flavours

SQL Server (Microsoft, used in class), PostgreSQL, MySQL, Teradata, Redshift and others: about 90% is the same; only 5 to 10% (some functions, e.g. date part) differs. MySQL works for practice.

How class was run

The instructor ran statements and shared code in chat to copy and run. A Word cheat sheet of all commands and the scripts were promised on the course portal.

3. Architectural Mechanics & Workflows: Tables, Loading and SELECT

Server structure and table creation

Think of the server as your laptop and the database (e.g. master) as a drive; tables live inside the database. In Excel you just paste data; in SQL you first define the structure.

CREATE TABLE orders_test (order_id INT, category VARCHAR(100), order_date DATE);
INSERT INTO orders_test VALUES (1, 'Technology', '2023-01-01');
SELECT * FROM orders_test;

Data type tells the database what each column stores: integer for numbers, VARCHAR(n) for strings with a maximum length (a longer value errors), DATE for dates.
Numbers go in without quotes; strings and dates need single quotes. Default date format is yyyy-mm-dd. Values follow the column order.
Put a semicolon between statements to run several together. Refresh the Tables folder to see a new table.

Two ways to load the orders data

Option 1: import wizard (SQL Server)right-click the database > Tasks > Import Data

→

SourceExcel file, first row has column names

→

DestinationOLE DB provider for SQL Server (or native); table name orders

→

Edit mappingsdate time can become date; Finish: 1,000 rows

Option 2: run the shared create-table and insert scripts (works in MySQL too; if square brackets fail, remove them). In a browser tool, run the inserts 500 at a time: 1,000 at once errors. Name the table orders (not orders_demo). Keeping date time is harmless: it shows 00:00:00.

Viewing data: TOP, columns and ORDER BY

Do not SELECT * on large tables

Millions of rows take long to show and can hurt the database (CPU spikes). Use SELECT TOP 10 * to see columns and data types first. Also list only needed columns: SELECT TOP 10 order_id, order_date FROM orders. TOP limits rows, column names limit columns.

ORDER BY

Ascending by default (ASC optional); add DESC for descending. ORDER BY then TOP 10 gives the oldest (or newest with DESC) 10 orders. Works on strings, numbers and dates.

Sorting detail	What was shown
Ties	Equal order dates keep database order. Add a second column: `ORDER BY order_date DESC, row_id DESC`; no limit on the number of columns (also sales as second key).
Row ID	It is an ordinary column in this Excel data, not present by default. Oracle has a pseudo-column called ROWID (not SQL Server).
Comments	Double hyphen `--` starts a comment.

3 (cont.). Filtering with WHERE

Order of execution so far

FROMwhich table

→

WHEREfilter rows immediately

→

SELECTonly the needed columns

→

ORDER BYsort the result

→

TOPlast

Selecting two columns is faster than all columns because less data is fetched. WHERE is applied at row level: each row is checked on its own; the next row does not matter.

Filter	Example	Result in class
Equal, string	`WHERE category = 'Technology'`	192 rows (same as the Excel filter)
Equal, date	`WHERE order_date = '2021-11-11'`	2 orders
Equal, number (no quotes)	`WHERE quantity = 2`	238 rows
Greater or equal	`WHERE quantity >= 2`	905 rows (quantity 1 excluded)
Date comparison	`WHERE order_date >= '2021-12-11'` (after a given date)	25 rows; `>` excludes the boundary date, `>=` includes it
Range, boundaries included	`WHERE order_date BETWEEN '2021-01-01' AND '2021-01-31'`	24 orders for January 2021; also `quantity BETWEEN 2 AND 4` (2, 3, 4)
List of values	`WHERE category IN ('Technology', 'Furniture')`	Both categories, not office supplies; works on numbers (`quantity IN (1, 3)`) and dates (two dates gave five orders)
Not equal / not in	`WHERE category <> 'Furniture'`, `quantity NOT IN (1, 3)`	Everything else

Comparison on strings

Greater than, less than and BETWEEN work on strings (by character value) but rarely make sense. category > 'Technology' returns nothing because T is highest; < 'Technology' returns 808 rows (furniture and office supplies); >= includes technology.

Quotes rule

Strings and dates always in single quotes; integers and floats without. Any filter on any column can be combined: there is no limit on columns in a WHERE.

Apostrophe inside a value

Inserting "Gupta's Dairy"

When data is imported from Excel nothing special is needed. In an INSERT, a backslash did not work in SQL Server in the demo; two single quotes worked ('Gupta''s Dairy'). The same trick is needed when writing a query condition.

3 (cont.). LIKE, AND / OR / NOT and NULL

LIKE: pattern matching on customer name

Pattern	Meaning	Result
`LIKE 'AN%'`	Starts with AN, anything after	Names starting with AN
`LIKE '%E'`	Ends with E	109 rows
`LIKE 'N%G'`	Starts with N, ends with G	4 rows
`LIKE '%G%'`	G anywhere (start, middle or end)	Percent also matches nothing
`LIKE '_E%'`	Second character is E	Names such as KE..., TE..., BE...

Wildcards

% = any characters or none (like * in Excel). _ = exactly one character.

Literal percent in data

Use the ESCAPE option so the percent loses its special meaning and is treated as a normal character.

AND, OR, NOT

AND

category = 'Furniture' AND segment = 'Consumer': both must be true per row: 109 rows (same as Excel). Each extra AND removes more rows. Order is left to right; no brackets needed.

OR

category = 'Furniture' OR segment = 'Consumer': either is enough. Put brackets around conditions that belong together, e.g. (category = 'Furniture' AND segment = 'Consumer') OR quantity = 2. Without brackets evaluation is left to right.

NOT reverses a whole filter

Each condition gives true or false per row; NOT ( ... ) flips it. Furniture + consumer + quantity above 2 gave 82 rows, NOT (...) gave 918. It is not the same as turning every condition into not equal (<> ... AND <= ... AND <> ... gave only 126 rows): a furniture row with quantity 1 fails the original AND, so NOT keeps it, but the not-equal version drops it because category is furniture. Furniture with segment not consumer: category = 'Furniture' AND segment <> 'Consumer'.

NULL values

Unknown, not blank

Excelblank cell

→

SQLNULL = unknown value (e.g. middle name left empty on a form)

→

FilterIS NULL / IS NOT NULL

Demo: two rows had ship mode set to NULL via UPDATE: IS NULL returned 2 rows, IS NOT NULL returned 998. = NULL returns nothing since NULL is not equal to anything, not even NULL (like infinity not equal to infinity). = 'NULL' in quotes searches the text "NULL". NULL is not an empty string either: an empty string (two single quotes) is a real value; to store NULL in an INSERT you write NULL. Python's None / NaN has the same meaning. Combine with other filters: ship_mode IS NOT NULL AND quantity = 2.

3 (cont.). Aggregation, GROUP BY, HAVING and DISTINCT

Aggregate functions on the whole table

SELECT COUNT(*) AS number_of_records FROM orders
SELECT SUM(sales) AS total_sales FROM orders
SELECT MAX(sales), MIN(sales), AVG(sales) ...

COUNT(*) = 1,000. SUM(sales) = 207,379 (matches Excel). MAX(sales) = 9,892. AVG equals SUM divided by COUNT. AS renames the output header only (an alias); the table is unchanged.

No plain column with an aggregate

SELECT SUM(sales), category FROM orders errors: the column must be in GROUP BY. You can use several aggregates together in one query.

GROUP BY = the pivot table

SELECT category, SUM(sales) AS category_sales
FROM orders GROUP BY category

Three rows, one per category (office supplies sales 63,286). Add subcategory to both SELECT and GROUP BY for category and subcategory sales (technology accessories 20,525). Every non-aggregated column in SELECT must be in GROUP BY (selecting segment alone fails). The reverse is allowed, but you would not know which subcategory each row is, so put all group columns in SELECT. GROUP BY only groups; it does not filter.

Combination	Behaviour
WHERE + GROUP BY	WHERE runs first (e.g. quantity = 2), the remaining rows are grouped, then summed. Example: only furniture rows with quantity 2 are grouped and summed.
ORDER BY on an aggregate or alias	Works because SELECT runs before ORDER BY (the alias exists by then). `ORDER BY category_sales DESC` plus `TOP 2` gives the top two category + subcategory combinations.
GROUP BY an aggregate	Not possible (`GROUP BY SUM(sales)` makes no sense). GROUP BY a column such as sales or quantity groups equal values.
NULLs	All NULL values form one group. `COALESCE(ship_mode, 'Unknown')` (in SELECT and GROUP BY) shows "Unknown" instead of NULL; functions come next class.

HAVING: filter groups

SELECT subcategory, SUM(sales) FROM orders
GROUP BY subcategory HAVING SUM(sales) > 20000

Five subcategories qualify. WHERE cannot do this because it works on one row at a time and does not know the group total. HAVING works after GROUP BY and can use any aggregate (MIN, MAX...), even one not shown in SELECT.

Final order of execution

FROM

→

WHERE

→

GROUP BY

→

HAVING

→

SELECT

→

ORDER BY

→

TOP

DISTINCT

SELECT DISTINCT segment FROM orders gives the three segments (like the Excel drop-down); GROUP BY segment does the same.

Getting results into Excel

Select the result, choose copy with headers, paste into Excel. Excel can also connect to SQL Server (ODBC) and run the query itself: to be shown next class. Insert statements can be built in Excel with CONCAT.

4. Best Practices & Golden Rules

Peek with TOP, name the columns

Avoid SELECT * on large tables; fetch only the rows and columns you need.

Bracket your OR conditions

Group the conditions that belong together; AND needs no brackets.

Use IS NULL, never = NULL

NULL is unknown, not blank and not equal to itself.

WHERE for rows, HAVING for groups

Filter on a column with WHERE; filter on an aggregate with HAVING after GROUP BY.

5. Numbers & Metrics to Remember

1,000Rows in the orders table

207,379Total sales (SUM)

109Furniture AND consumer rows

918Rows after NOT on the 82-row filter

6. Mini Glossary

Data type: What a column stores: integer, VARCHAR(n) string, DATE.
Alias (AS): Renames an output column header; the table is unchanged.
Wildcard: % any characters, _ exactly one, used with LIKE.
NULL: Unknown value; tested with IS NULL.
Aggregate function: COUNT, SUM, MIN, MAX, AVG over many rows.
GROUP BY: Groups rows by column values, like a pivot table's row labels.
HAVING: Filters groups on an aggregated value.
DISTINCT: Returns unique values only.

7. Quick Self-Test

1Which Excel action matches WHERE, ORDER BY and a pivot table in SQL?

2Why use TOP and named columns instead of SELECT * on a big table?

3When are single quotes needed in a condition? Which operator keeps both boundary values?

4What do % and _ match in LIKE? Write the pattern for "second character is E".

5Scenario: WHERE ship_mode = NULL returns nothing. Why, and how do you fix it?

6Scenario: you want subcategories with total sales above 20,000, but WHERE does not work. Why, and what do you use?

7Scenario: SELECT category, segment, SUM(sales) ... GROUP BY category errors. What is wrong?

8State the order of execution of FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY and TOP.