SQL is case-insensitive, but by convention SQL commands are typed in upper case. The examples below return specific data from a hypothetical database named transactions with columns: first_name, last_name, customer_id, and price.

Basic commands:
SELECT
Select columns from database.

Return all columns from transactions database:

SELECT * FROM transactions;

Return columns first_name and last_name from transactions:

SELECT first_name,last_name FROM transactions;

DISTINCT
Modifies SELECT. Returns distinct (unique values in columns)

Return unique prices from transactions:

SELECT DISTINCT price FROM transactions;

WHERE
Keyword to specify rows. Used after SELECT.

SELECT price FROM transactions
WHERE price>10;

COUNT
Modifies SELECT. Returns number of rows which match condition.

Find the number of customers:

SELECT COUNT(customer_id) FROM transactions;

Can be used with DISTINCT. Find the number of unique customer ids if there are duplicates:

SELECT COUNT(DISTINCT customer_id) FROM transactions;

LIMIT
Limits the number of rows returned from a query.

Usually placed at end of query. Return first five cutsomer ids.

SELECT customer_id FROM transactions
LIMIT 5;

ORDER BY
Orders rows by a condition. Ascending is default but can set order to ascending explicitly (ASC) or descending (DESC). Note PostgreSQL can order by columns not selected in the query, but that is not available in all SQL tools. It is usually good practice to select the column you will order by so that your query would if used in other systems.

Return transaction price in descending order:

SELECT price FROM transactions
ORDER BY price DESC

BETWEEN
Modifies WHERE. Check if value is within a rang of value. NOT can be used to modify BETWEEN.

Return prices between 5 and 10 dollars:

SELECT price FROM transactions
WHERE price BETWEEN 5 AND 10

Return prices not between 5 and 10 dollars:

SELECT price FROM transactions
WHERE price NOT BETWEEN 5 AND 10

IN
A condition for WHERE statement. Returns true if a value is a member of a list. A subquery can be used to generate the value list. The use of IN is equivalent to a series of OR comparisons (but IN runs faster and is more readable). NOT may be used prior to IN to determine if an item is not in a list.

Return all columns for transactions where the price was 1.99, 2.99 or 9.99:

SELECT * FROM transactions
WHERE price IN (1.99, 2.9, 9.99)

LIKE
A condition for WHERE statement. Finds rows with partial matching. Uses wildcard commands: ‘_’ indicating a single character and ‘%’ indicating a range of characters. Case sensitive. NOT may be used to modify LIKE.

Return first_names which contain three letters, begin with ‘D’ and end with ‘e’:

SELECT first_name FROM transaction
WHERE first_name LIKE ‘D__e’;

Return first_names which do not contain a lower-case ‘a’:

SELECT first_name FROM transaction

Return first_names which contain begin with J:

SELECT first_name FROM transaction
WHERE first_name LIKE ‘J%’;

Return first_names which do not contain a lower-case ‘a’:

SELECT first_name FROM transaction

WHERE first_name NOT LIKE ‘%a%’;

ILIKE
Case-insensitive equivalent of LIKE.

Return first_names which contain ‘a’ or ‘A’:

SELECT first_name, last_name FROM customer
WHERE first_name ILIKE ‘%a%’;

Categories: SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

SQL

UNION

This command combines results from two or more select queries. In order to use the UNION command, you must have the same number of columns in each select statement and the corresponding columns must have Read more…

SQL

HAVING

This command is used with GROUP BY to filter group rows. It is similar to WHERE, but HAVING applies to the aggregated rows after the GROUP BY command (WHERE applies to individual rows before the Read more…

SQL

GROUP BY

GROUP BY statements use various aggregate functions to combine rows and look at aggregated information. Aggregate functions used below: MIN, MAX, AVG, COUNT, SUM. The calls below are made a hypothetical transaction database with columns Read more…