The asterisk or star symbol (*) means all columns.
The semi-colon (;) terminates the statement like a period in sentence or question mark in a question.
Examples:
SELECT * FROM books;
SELECT * FROM products;
SELECT * FROM users;
SELECT * FROM countries;
Retrieving Specific Columns of Information
Retrieving a single column:
SELECT <column name> FROM <table name>;
Examples:
SELECT email FROM users;
SELECT first_name FROM users;
SELECT name FROM products;
SELECT zip_code FROM addresses;
Retrieving multiple columns:
SELECT <column name 1>, <column name 2>, ... FROM <table name>;
Examples:
SELECT first_name, last_name FROM customers;
SELECT name, description, price FROM products;
SELECT title, author, isbn, year_released FROM books;
SELECT name, species, legs FROM pets;
Aliasing Column Names
SELECT <column name> AS <alias> FROM <table name>;
SELECT <column name> <alias> FROM <table name>;
Examples:
SELECT username AS Username, first_name AS "First Name" FROM users;
SELECT title AS Title, year AS "Year Released" FROM movies;
SELECT name AS Name, description AS Description, price AS "Current Price" FROM products;
SELECT name Name, description Description, price "Current Price" FROM products;
Finding the Data You Want
SELECT <columns> FROM <table> WHERE <condition>;
Equality Operator
Find all rows that a given value matches a column's value.
SELECT <columns> FROM <table> WHERE <column name> = <value>;
Examples:
SELECT * FROM contacts WHERE first_name = "Andrew";
SELECT first_name, email FROM users WHERE last_name = "Chalkley";
SELECT name AS "Product Name" FROM products WHERE stock_count = 0;
SELECT title "Book Title" FROM books WHERE year_published = 1999;
Inequality Operator
Find all rows that a given value doesn't match a column's value.
SELECT <columns> FROM <table> WHERE <column name> != <value>;
SELECT <columns> FROM <table> WHERE <column name> <> <value>;
The not equal to or inequality operator can be written in two ways != and <>. The latter is less common.
Examples:
SELECT * FROM contacts WHERE first_name != "Kenneth";
SELECT first_name, email FROM users WHERE last_name != "L:one";
SELECT name AS "Product Name" FROM products WHERE stock_count != 0;
SELECT title "Book Title" FROM books WHERE year_published != 2015;
Relational Operators
There are several relational operators you can use:
< less than
<= less than or equal to
> greater than
>= greater than or equal to
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 users WHERE date_of_birth < '1998-12-01';
SELECT title AS "Book Title", author AS Author FROM books WHERE year_released <= 2015;
SELECT name, description FROM products WHERE price > 9.99;
SELECT title FROM movies WHERE release_year >= 2000;
More Than One Condition
You can compare multiple values in a WHERE condition. If you want to test that both conditions are true use the AND keyword, or either conditions are true use the OR keyword.
SELECT <columns> FROM <table> WHERE <condition 1> AND <condition 2> ...;
SELECT <columns> FROM <table> WHERE <condition 1> OR <condition 2> ...;
Examples:
SELECT username FROM users WHERE last_name = "Chalkley" AND first_name = "Andrew";
SELECT * FROM products WHERE category = "Games Consoles" AND price < 400;
SELECT * FROM movies WHERE title = "The Matrix" OR title = "The Matrix Reloaded" OR title = "The Matrix Revolutions";
SELECT country FROM countries WHERE population < 1000000 OR population > 100000000;
Searching in a Set of Values
SELECT <columns> FROM <table> WHERE <column> IN (<value 1>, <value 2>, ...);
Examples:
SELECT name FROM islands WHERE id IN (4, 8, 15, 16, 23, 42);
SELECT * FROM products WHERE category IN ("eBooks", "Books", "Comics");
SELECT title FROM courses WHERE topic IN ("JavaScript", "Databases", "CSS");
SELECT * FROM campaigns WHERE medium IN ("email", "blog", "ppc");
To find all rows that are not in the set of values you can use NOT IN.
SELECT <columns> FROM <table> WHERE <column> NOT IN (<value 1>, <value 2>, ...);
Examples:
SELECT answer FROM answers WHERE id IN (7, 42);
SELECT * FROM products WHERE category NOT IN ("Electronics");
SELECT title FROM courses WHERE topic NOT IN ("SQL", "NoSQL");
Searching within a Range of Values
SELECT <columns> FROM <table> WHERE <column> BETWEEN <lesser value> AND <greater value>;
Examples:
SELECT * FROM movies WHERE release_year BETWEEN 2000 AND 2010;
SELECT name, description FROM products WHERE price BETWEEN 9.99 AND 19.99;
SELECT name, appointment_date FROM appointments WHERE appointment_date BETWEEN "2015-01-01" AND "2015-01-07";
Pattern Matching
Placing the percent symbol (%) any where in a string in conjunction with the LIKE keyword will operate as a wildcard. Meaning it can be substituted by any number of characters, including zero!
SELECT <columns> FROM <table> WHERE <column> LIKE <pattern>;
Examples:
SELECT title FROM books WHERE title LIKE "Harry Potter%Fire";
SELECT title FROM movies WHERE title LIKE "Alien%";
SELECT * FROM contacts WHERE first_name LIKE "%drew";
SELECT * FROM books WHERE title LIKE "%Brief History%";
PostgreSQL Specific Keywords
LIKE in PostgreSQL is case-sensitive. To do case-insensitive searches use ILIKE.
SELECT * FROM contacts WHERE first_name ILIKE "%drew";
Missing Values
SELECT * FROM <table> WHERE <column> IS NULL;
Examples:
SELECT * FROM people WHERE last_name IS NULL;
SELECT * FROM vhs_rentals WHERE returned_on IS NULL;
SELECT * FROM car_rentals WHERE returned_on IS NULL AND location = "PDX";
To filter out missing values use can use IS NOT NULL.
SELECT * FROM <table> WHERE <column> IS NOT NULL;
Examples
SELECT * FROM people WHERE email IS NOT NULL;
SELECT * FROM addresses WHERE zip_code IS NOT NULL;