SQL Refresher : Part - II

In Part I, we built the foundation of our databases and tables. Now, let's dive deeper into querying that data — starting with the mighty SELECT statement.

1. The SELECT Keyword

In SQL, the SELECT statement is your telescope — it helps you look at data stored in tables.

Syntax:

SELECT column1, column2 FROM table_name;

Example:

Let’s look at the names and number of planets in each solar system:

SELECT name, num_planets FROM solarsystem;

2. Using * and Column Names

The asterisk (*) is like asking the table to show everything. It selects all columns from a table.

Example – Select all:

SELECT * FROM solarsystem;

This gives you every detail about every solar system.

Example – Select specific columns:

SELECT name, galaxy FROM solarsystem;

This narrows the scope to just the name and the galaxy.

3. Escaping Quotes in INSERT Statements

Sometimes, solar system names contain single quotes — like Luyten's Star. To handle that, you must escape the single quote:

Method 1: Use a backslash \

INSERT INTO solarsystem (id, name) VALUES (8, 'Luyten\'s Star');

Method 2: Double the quote ''

INSERT INTO solarsystem (id, name) VALUES (8, 'Luyten''s Star');

Both work — pick your favorite space maneuver!

4. Filtering Data with WHERE

Want to focus your telescope on specific solar systems? Use the WHERE clause!

Comparison Operators:

• = means Equal to

• <> means Not equal to

• > means Greater than

• < means Less than

• >= means Greater or equal to

• <= means Less or equal to

Examples:

SELECT * FROM solarsystem WHERE num_planets > 5;

SELECT * FROM solarsystem WHERE mass <= 3.5;

SELECT * FROM solarsystem WHERE galaxy <> 'Milky Way';

5. Combining Conditions: AND, OR, BETWEEN, IN, NOT IN, IS NULL

When searching the stars, sometimes one condition isn't enough. SQL lets you combine multiple filters using logical operators like AND and OR.

AND – Both Must Be True

AND is like saying "Only show me results that meet all conditions."

Both conditions must be true for the row to be selected.

Example:

SELECT * FROM solarsystem
WHERE galaxy = 'Milky Way' AND num_planets > 3;

This query will only return solar systems that are in the Milky Way and have more than 3 planets.

If either part fails (wrong galaxy or too few planets), the row is not returned.

OR – Either Can Be True

OR is like saying "Give me results that match at least one of the conditions."

Only one of the conditions has to be true.

Example:

SELECT * FROM solarsystem
WHERE mass < 5 OR num_planets >= 7;

This query will return any solar system that:

• Has a mass less than 5

• Or has 7 or more planets (Or both — that’s okay too!)

Even if just one condition is true, the row will appear in the result.

BETWEEN

Check for values in a range:

SELECT * FROM solarsystem
WHERE num_planets BETWEEN 2 AND 5;

IN and NOT IN

Choose from a set:

SELECT * FROM solarsystem
WHERE name IN ('Kepler-90', 'TRAPPIST-1');

SELECT * FROM solarsystem
WHERE galaxy NOT IN ('Milky Way', 'Andromeda');

IS NULL / IS NOT NULL

Check if a value is missing:

SELECT * FROM solarsystem WHERE mass IS NULL;

SELECT * FROM solarsystem WHERE mass IS NOT NULL;

6. Pattern Matching with LIKE

Search for names that match a pattern using LIKE.

Wildcards:

• % means Zero or more characters

• _ means Exactly one character

• * means (Not standard SQL, used in some DBs like MS Access)

Examples:

-- Names starting with 'K'
SELECT * FROM solarsystem WHERE name LIKE 'K%';

-- Names ending with 'i'
SELECT * FROM solarsystem WHERE name LIKE '%i';

-- Names with 'e' as second character
SELECT * FROM solarsystem WHERE name LIKE '_e%';

Conclusion

This refresher covered the basics from simple selections to advanced filters, these tools help you uncover hidden patterns across galaxies.

References: Head First SQL[Book]