Akosua's Tech Blog
SQL logo

Learning SQL - Retrieving Data

SQL (Structured Query Language) is a specialized language to get information or data out of a database. There are multiple database systems such as MySQL, PostgreSQL, and SQLite.

In this series of posts I'm going to talk about how SQL works.

Let's start with retrieving data.

Commands that retrieve data start with SELECT and contain FROM. The examples below will show how these keywords are used to retrieve data.

Retrieving all colums and rows from a table

  • SELECT * FROM <table name>;
    • The asterisk ( * ) means all columns and the semicolon ( ; ) ends the statement.

Example:

  • SELECT * FROM user_data;

Retrieving Specific Columns

  • Retrieving a single column
    • SELECT <column name> FROM <table name>;
  • Retrieving multiple columns
    • SELECT <column name 1>, <column name 2>, ... FROM <table name>;

Examples:

  • SELECT first_name FROM user_date;
  • SELECT first_name, last_name, address FROM user_date;

Aliasing Column Names

  • SELECT <column name> AS <alias> FROM <table name>;
  • SELECT <column name> <alias> FROM <table name>;

Examples:

  • SELECT first_name AS "First Name" FROM user_data;
  • SELECT first_name "First Name" , last_name "Last Name", address Address FROM user_data;

*NOTE: If the column alias is multiple words it must be wrapped in quotes or a syntax error will be thrown.

Finding Specific Data

  • SELECT <columns> FROM <table> WHERE <condition>;
  • The WHERE clause filters for rows that meet certain criteria.
  • WHERE is followed by a condition that returns either true or false.

Equality/Inequality Examples:

  • SELECT first_name FROM user_date WHERE id = 12;
    • The equality operator( = ) finds all rows that match a specific value.
  • SELECT first_name FROM user_date WHERE id != 12;
    • The inequality operator( != ) finds all rows that do not match a specific value.

Relational Operators

  • < less than
  • <= less than or equal to
  • > greater than
  • >= greater than or equal to

*NOTE: These are primarily used to compare numeric and date/time types.

  • SELECT <columns> FROM <table> WHERE <column name> < <value>;
  • SELECT <columns> FROM <table> WHERE <column name> <= <value>;
  • SELECT <columns> FROM <table> WHERE <column name> > <value>;
  • SELECT <columns> FROM <table> WHERE <column name> >= <value>;

Examples:

  • SELECT first_name, last_name FROM user_date WHERE date_of_birth < '1988-01-01';
    • Date format is YYYY-MM-DD
  • SELECT name, prod_id, description FROM product_data WHERE price > 9.99;
  • SELECT title FROM movies WHERE release_year >= 2000;
  • SELECT title FROM movies WHERE release_year <= 2000;


Multiple Conditions

To compare multiple condition using the WHERE keyword you can use:

  • AND: to check if both conditions evaluate to true
    • SELECT <columns> FROM <table> WHERE <condition 1> AND <condition 2> ...;
  • OR: to check if one or the condition is true
    • SELECT <columns> FROM <table> WHERE <condition 1> OR <condition 2> ...;

Examples:

  • SELECT username FROM user_data WHERE age > 13 AND country = "CA"
  • SELECT username FROM user_data WHERE age > 13 OR country = "CA"

Values in a List/Subquery

  • WHERE IN returns values that matches values in a list or subquery.
  • WHERE IN is a shorthand for multiple OR conditions.
  • SELECT <columns> FROM <table> WHERE <column> IN (values);
  • SELECT <columns> FROM <table> WHERE <column> NOT IN (values);

Examples:

  • SELECT username FROM user_data WHERE id IN (22, 45, 7, 12);
  • SELECT username FROM user_data WHERE id NOT IN (22, 45, 7, 12);

Range of Values

  • WHERE BETWEEN returns values that fall within a given range.
  • BETWEEN operator is inclusive: begin and end values are included.
  • SELECT <columns> FROM <table> WHERE <column> BETWEEN <lesser value> AND <greater value>;

Example:

  • SELECT username FROM user_data WHERE birthday BETWEEN "1977-01-01" AND "1988-01-01";


Pattern Matching

  • WHERE LIKE determines if a character string matches a pattern.
  • Use WHERE LIKE when only a fragment of a text value is known.
  • WHERE LIKE supports two wildcard match options: % (percent) and _ (underscore).
    • A % matches any string with zero or more characters
    • An _ matches any single character.
  • SELECT <columns> FROM <table> WHERE <column> LIKE <pattern>;

Examples:

  • SELECT * FROM countries WHERE name LIKE "Ca%;
    • returns all countries that start with Ca
  • SELECT * FROM countries WHERE name LIKE "%istan;
    • returns all countries that end in istan
  • SELECT * FROM products WHERE name LIKE "Nik_";
    • returns all products that start with Nik and have more than one character

*NOTE: LIKE in PostgreSQL is case-sensitive. To do case-insensitive searches use ILIKE.

Finding Missing Data

  • NULL is a special value that signifies 'no value'.
  • Comparing a column to NULL using the = operator is undefined.
  • Instead, use WHERE IS NULL or WHERE IS NOT NULL.
    • SELECT * FROM <table> WHERE <column> IS NULL;
    • SELECT * FROM <table> WHERE <column> IS NOT NULL;

Examples:

  • SELECT * FROM user_data WHERE birthdate IS NULL;
  • SELECT * FROM user_data WHERE birthdate IS NOT NULL;