TRAINING | DAY 3

SELECT Filters: DISTINCT, WHERE, IN, AND/OR & LIKE

Excel-style filtering done in SQL, plus new columns, case sensitivity and pattern matching with wildcards.

1. The Crux in 60 Seconds

DISTINCT removes repeats

SELECT DISTINCT ship_mode lists unique values; with several columns it lists unique combinations.

WHERE filters row by row

Operators = > < >= <= <>, BETWEEN and IN work on numbers, dates and strings. Quote strings and dates only.

AND narrows, OR widens

AND needs both conditions; OR needs either one. More ANDs mean fewer rows, more ORs mean more rows.

LIKE matches patterns

% = any number of characters, _ = exactly one, [ ] = a set or range. = only does exact matches.

2. Foundational Concepts & Comparisons

The instructor showed each idea in Excel first, then in SQL.

In Excel you...	In SQL you write
Open a column's filter drop-down to see its values	`SELECT DISTINCT ship_mode FROM orders`
Tick one value (first class)	`WHERE ship_mode = 'first class'`
Tick two values on one column	`WHERE ship_mode IN ('first class','same day')`
Filter two different columns together	`WHERE ... AND ...` or `... OR ...`
Type a formula such as profit / sales in a new column	`SELECT *, profit / sales AS ratio`

Exact match vs pattern

= and IN compare exact values only
LIKE compares patterns, like a search box that keeps suggesting matches as you type
Several patterns need several LIKE conditions joined by OR; IN cannot hold patterns

Recap of Day 2 (opening)

ALTER: change type, add, drop columns; no data loss allowed and types must be compatible
Constraints: primary key (can be several columns, written at the end), unique, check, not null
INSERT with a column list; basic UPDATE and DELETE with a filter
From here, most interview questions are SELECT questions

Opening doubt: default value vs a user-supplied value

One INSERT adds one row. A row that should use the default omits that column from the column list; a row with a given value (say credit card) is a separate INSERT. In a real app the back-end program decides: if the UI sends no payment method, use the default; otherwise pass what came.

3. Architectural Mechanics & Workflows: DISTINCT and WHERE

DISTINCT

SELECT DISTINCT ship_mode FROM orders;
SELECT DISTINCT ship_mode, segment
FROM orders;
SELECT DISTINCT * FROM orders;

Needed because scanning millions of rows by eye is impossible
Ship mode has 4 values: first class, same day, second class, standard class
Works on any data type (strings, dates)
Several columns: unique combinations (for example first class with consumer)
DISTINCT * drops only fully identical rows; the unique row ID keeps all 9,994 rows here
You cannot make one column distinct and not the other

WHERE on three data types

WHERE ship_mode = 'first class'
WHERE order_date = '2020-12-08'
WHERE quantity = 5

First-class query returned 1,538 rows (Excel showed 1,539 including its header)
Quotes: strings and dates need single quotes; numbers do not. Removing the quotes from a string gives "incorrect syntax"
Used before with DELETE and UPDATE; mostly used with SELECT, because you usually care about one department, region or period

Comparison operators (work on every data type)

Operator	Meaning	Seen in class
`=` / `<>` (not equal)	Equal / everything except	`quantity <> 5` showed 1, 2, 3, 4, 6 ... no 5
`>` `>=` `<` `<=`	Greater / less, optionally with equality	`quantity >= 5`: sorted, minimum 5 up to 14; `order_date <` a date: all earlier orders
`BETWEEN a AND b`	Range, both ends included	Dates 8 to 12 December returned the 8th through the 12th; also used on quantity (3 and 5 included)
`IN (v1, v2, ...)`	Any value from a list; no limit on list size	First class + same day returned 2,081 rows; `quantity IN (3, 5)` (no quotes for numbers)
`NOT IN (...)`	Everything except the list	Left standard class and second class

IN details

A list of one value works like =; one value: prefer =, several: use IN
IN is the same as OR in the background: no performance difference, but IN reads better
Works with numbers and dates; verify a filter with SELECT DISTINCT on that column (two ship modes came back)

Strings with > and <

Comparison follows alphabetical (character-code) order, like sorting ascending
ship_mode > 'second class' returned only standard class: "st" sorts after "se"; second class itself is excluded because the test is strictly greater
Useful in some real cases, good to know

Order of execution as taught today

FROMWhich table

→

WHEREKeep qualifying rows

→

ORDER BYSort what remains

→

TOP nPick the first n; columns shown

Example: SELECT TOP 5 order_date, quantity FROM orders WHERE quantity >= 5 ORDER BY quantity DESC filters first, sorts, then returns five rows with the highest quantity. The five keywords covered so far are SELECT, FROM, WHERE, ORDER BY and TOP.

Earlier in the class: how top-N works

ORDER BY order_date DESC with TOP 5 sorts the whole table first and then keeps five rows (the latest orders, all from December 2021 style dates). TOP is applied at the end.

3. Mechanics (continued): AND, OR and Row-by-Row Filtering

AND (intersection)

WHERE ship_mode = 'first class'
  AND segment = 'consumer'

Both conditions must hold: first-class shipments of the consumer segment (about 769 rows; Excel showed 768). Each AND shrinks the result: a quantity filter gave 1,863 rows and adding a date filter cut it to 1,125.

OR (either one)

WHERE ship_mode = 'first class'
   OR segment = 'consumer'

A row passes if either holds, so rows with second-class shipping appear (they are consumers) and so do other segments (they are first class). More ORs mean more rows. If the first test is true, the database does not bother with the second.

ship_mode IN ('first class','same day') equals ship_mode = 'first class' OR ship_mode = 'same day'.

Why OR on one column does not return "only one" value

A student asked why OR returns both first class and same day. The instructor walked the data: WHERE is checked per row, independently. Row 1: first class? No. Same day? No: dropped. A later row with first class passes the first test and is kept; a row with same day passes the second. Rows never look at each other. (Filters that compare several rows come later.)

3. Mechanics (continued): Dates With Time, New Columns

Date columns that hold a time

A date written without time means 00:00:00, so = matches only that exact moment; later times that day are missed
< still works as expected
A student suggested using 23:59 as an end time; the instructor said simpler ways exist, to be taught
Time ranges work: BETWEEN a start and an end datetime (12:00 to 12:40 included both ends)
Sorting on time works with ORDER BY, as for every data type

SELECT CAST(order_date AS date) AS order_date_new, *
FROM orders
WHERE CAST(order_date AS date) = '2022-11-08';

CAST converts the type for the query only: time is ignored, the table stays datetime.

Creating new columns

SELECT *,
  profit / sales AS ratio,
  profit + sales AS total
FROM orders;

Any calculation on columns; alias required because a new column has no name, and it becomes the header
The table itself is unchanged: they appear only in the output
Arithmetic (including profit times sales) and string functions can go here
GETDATE() returns the current date and time in the server's time zone (IST here)

What each test run proved

#	Run	Result
1	`ORDER BY order_date DESC` with `TOP 5`	Sort happens before the five rows are cut
2	String filter without quotes	Error: incorrect syntax
3	`quantity <> 5`	Every value except 5
4	`BETWEEN` on dates	Both boundary dates are included
5	`IN` then `SELECT DISTINCT ship_mode`	Exactly the two listed values, 2,081 rows
6	`ship_mode > 'second class'`	Only standard class
7	`OR` of two conditions on different columns	Rows matching either, so unrelated values show up

Aliases in WHERE

An alias from the SELECT list does not work in WHERE in SQL Server: "invalid column name". The instructor said SQL Server, Oracle and MySQL do not allow it, while modern databases (SAP HANA, Redshift) do, because internally the query is rewritten as a subquery (explained later with subqueries).

3. Mechanics (continued): LIKE and Wildcards

Pattern idea	Example	Matches
Starts with	`customer_name LIKE 'c%'`, `'chris%'`	Names beginning with those characters
Ends with	`LIKE '%shield'`, `'%t'`	Names ending with them (one name for shield)
Contains	`LIKE '%ven%'`	Anything with "ven" inside (e.g. Steven, Paul)
Starts and ends	`LIKE 'a%a'`	First and last character both A
One character `_`	`LIKE '_l%'`, `'__l%'`	L as the 2nd, 3rd character
Set `[ ]`	`LIKE 'c[al]%'`, `'c[albo]%'`	2nd character is any one listed
Not in set `[^ ]`	`LIKE 'c[^albo]%'`	2nd character is anything but those
Range `[a-f]`	`LIKE 'c[a-o]%'`	2nd character between a and o; works on digits too

% versus _

% = zero or more characters of any kind; '%l%' finds an L anywhere in the name, even at the start
_ = exactly one character; '_l%' pins L to the second position
Wildcards combine freely in one pattern, and none of them work with =; = would read the characters literally
Two or more patterns: separate LIKE conditions with OR, not IN

Searching for a literal % or _

If the data itself contains a % (a name like "Ankit%Bul"), the wildcard would swallow it
The ESCAPE option names an escape character so the following special character is treated as a normal one
Once escaped, % can no longer be a wildcard in that pattern

Digits and ranges

Ranges also work on numbers inside text such as an order ID: with the third character limited to 1 to 2, IDs from 2019 and 2020 stay; narrowing the fourth character to 1 to 2 removed 2020. Several bracket groups can follow each other, one per character position. Range filtering showed gaps when the data simply had no such letters; sorting the output made them visible.

Practical limits mentioned

LIKE only searches; it cannot split names (first and last name) or remove characters. String functions come in the next class
Wildcard rows used a test table of customer names

4. Case Sensitivity Setup

What changed in class

SQL Server compares case-insensitively by default: 'A%a' and 'a%a' give the same rows
The instructor switched his server to case-sensitive (most databases are by default) and will send a script
Existing data is not affected: re-import the data after running the script on your database
Column names became case-sensitive too: a column imported as "Order ID" failed as order id ("invalid column")
A new Excel file with lowercase column names will be shared; rename or delete the old table, keep table name lowercase orders

Case-insensitive search anyway

WHERE UPPER(customer_name) LIKE 'A%A'

UPPER() turns the value to capitals first, then the pattern (also in capitals) matches
Avoids repeating the condition with OR for each case combination
With case sensitivity on, 'c_l%'-style patterns need the correct letter case unless UPPER is used
The instructor recommends case-sensitive, so later functions are easier to understand; staying insensitive is acceptable

5. Best Practices & Golden Rules

Pitfall: quotes

Strings and dates in single quotes; numbers without.

Pitfall: dates with time

A date-only equality misses later times that day. Use a range, or CAST to date.

Rule: choose the filter type

Exact value or list: = / IN. Pattern: LIKE. Range: BETWEEN (ends included).

Rule: remember AND vs OR

AND removes rows, OR adds rows. Check by counting results.

Rule: verify with DISTINCT

After filtering, run DISTINCT on that column to confirm only expected values remain.

Rule: make data consistent

Keep names lowercase and the case setting known, so patterns behave predictably.

Trainer Q&A recap

DISTINCT on one of two columns? Not possible; it covers all listed columns.
Is IN slower than OR? No; same in the background.
Several patterns in one filter? Use OR, not IN.
Does % mean "anything"? It means any number of characters (zero or more); _ is exactly one.
Time-based sorting or range filters? ORDER BY and BETWEEN both work with times.
Last name from a full name, or removing characters? Via string functions, next class.
Doubts: practise this weekend; a doubt session is planned for the following weekend; the assignment, new data file and script follow by email.

6. Numbers & Metrics to Remember

4Distinct ship modes

1,538Rows with ship mode first class

2,081Rows for first class or same day

3Basic LIKE shapes: starts, ends, contains

7. Mini Glossary

DISTINCT:: keeps one copy of each unique value or combination.
BETWEEN:: range test including both boundary values.
IN:: tests a value against a list of exact values.
Wildcard:: symbol standing for unknown characters (%, _, [ ]).

ESCAPE:: marks a wildcard as a literal character.
CAST:: converts a value to another data type for the query.
UPPER:: function returning text in capitals.
Case sensitivity:: whether 'A' and 'a' count as different.

8. Quick Self-Test

What does SELECT DISTINCT ship_mode, segment return, and why can't you apply DISTINCT to just one of them?
Why do dates and strings need single quotes while numbers do not? What error follows a missing quote on a string?
Which rows does BETWEEN 8 Dec AND 12 Dec include?
Explain how OR on two columns can return second-class shipments when you asked for first class.
Scenario: order_date = '2022-11-08' returns nothing although orders exist that day with a time. Why, and what two fixes were shown?
What do the patterns '%ven%', '_l%' and 'c[^albo]%' each match?
Scenario: your name column holds a literal % and your LIKE pattern keeps matching too much. What do you use?
Scenario: the server is case-sensitive but you need a case-insensitive search for names starting and ending with A. Write the approach.