Here are some examples of SQL queries that I’ve found to be very useful when testing data-intense applications.
GROUP BY
This keyword is useful for getting summary stats, and is used in combination with the keyword COUNT:
SELECT rating, COUNT(rating) AS num_members
FROM table_name
WHERE year = '2025' AND month = '11'
GROUP BY rating;EXTRACT()
This function allows you to extract e.g. a month / year from a date, which is handy for when you want to group by dates.
SELECT EXTRACT(YEAR FROM createdate) AS year, EXTRACT(MONTH FROM createdate) AS month, COUNT(statuscode) AS num
FROM table_name WHERE campaign = '001'
GROUP BY EXTRACT(YEAR FROM createdate), EXTRACT(MONTH FROM createdate)
ORDER BY year DESC, month DESC;SELF JOIN
It can be useful to join a table on itself when you need to compare two rows of data in the same table. By using aliases, you are essentially treating the one table as two different tables (tr1 and tr2 in the example below) and can join by using these aliases.
SELECT * FROM table_name tr1
INNER JOIN table_name tr2
ON tr1.memberid = tr2.memberid
WHERE tr1.year = '2021' and tr1.month = '11'
AND tr2.year = '2021' AND tr2.month = '9'
AND tr1.weight = 0 AND tr2.weight > 0;Common Table Expressions (‘WITH’)
A CTE is a temporary SQL result that you can reference with e.g. a SELECT query.
Here is an example where we have a query that finds members who updated their cell number, and a query that finds members who updated their email, and we want to join these two result sets on each other and do so by giving each resultset a name (‘lefthandside’ and ‘righthandside’) and then treat them like tables in a join. (A CTE is necessary in this case because we are doing an outer or ‘full’ join and want to have records returned where there may be data missing in either the left hand side
or right hand side).
WITH
lefthandside AS (
SELECT pp.categorycode, pc.personcode, pc.contacttypecode, pc.contactnumber,
pc.changedate AS updatedCell FROM peoplecontact pc
INNER JOIN participants pp
ON pc.personcode = pp.personcode
WHERE pc.contacttypecode = '05' AND trunc(pc.changedate) BETWEEN '01/SEP/2021'
AND '30/NOV/2021'
AND pp.campaigncode = '0001' AND pp.categorycode LIKE 'EN_' AND pp.status = 'A'
),
righthandside AS (
SELECT pe.personcode, pe.emailtypecode, pe.emailaddress, pe.changedate AS
updatedEmail FROM peopleemail pe
INNER JOIN participants pp
ON pe.personcode = pp.personcode
WHERE pe.emailtypecode = '10' AND trunc(pe.changedate) BETWEEN '01/SEP/2021'
AND '30/NOV/2021'
AND pp.campaigncode = '0001' AND pp.categorycode LIKE 'EN_' AND pp.status = 'A'
)
SELECT *
FROM lefthandside lhs
FULL JOIN righthandside rhs ON
lhs.personcode = rhs.personcode;RANK() OVER PARTITION BY
You can ‘partition’ a table, and sort within subsets, assigning a ‘rank’ to each record within a partition. Here is an example finding all members whose latest credit card is Active (not expired or cancelled).
Step 1: rank each card that all members have ever had:
SELECT memberid, cardstatuscode, statuschangedate,
RANK() OVER (PARTITION BY memberid ORDER BY statuschangedate DESC) AS rank_val
FROM table_name
WHERE campaigncode = '0001';Step 2: use a CTE to filter the data according to rank:
WITH cte_creditcards AS (
SELECT memberid, cardstatuscode, statuschangedate,
RANK() OVER (PARTITION BY memberid ORDER BY statuschangedate DESC) AS rank_val
FROM table_name
WHERE campaigncode = '0001'
)
SELECT memberid, cardstatuscode FROM cte_creditcards WHERE rank_val
= 1 AND cardstatuscode = 'ACT';INSERT INTO SELECT
You can insert a record based on an existing one with the following query. What this does is copy a row, while allowing you to modify selected fields.
INSERT INTO table_name (transactionno, transactioncode, campaigncode, memberid, pointcredits, field_6, field_7, field_8, field_9, field_10)
SELECT
(SELECT MAX(transactionno) + 1 FROM table_name), 'ABC', '0001',
'1234567', ---insert new memberid
'50000', ----insert points
field_6, field_7, field_8, field_9, field_10
FROM
table_name
WHERE
transactionno = '1234567890';TO_DATE()
In Oracle, if you want to look up a particular date, you can’t just put quotes around the date, because that treats it as a string. Date checks need to use a date function.
SELECT *
FROM table_name
WHERE TO_DATE(createdate, 'DD-MON-YY') = TO_DATE('29 JAN-19', 'DD-MON-YY');
SELECT TO_DATE('202501', 'YYYYMM') FROM dual; MINUS
This is a handy way of finding the difference between two result sets.
SELECT memberid from table_name where commid = '002' and
trunc(datequeued) = '01-OCT-19'
MINUS
SELECT memberid from table_name where commid = '002' and
trunc(datequeued) = '25-OCT-19';Flattened queries
Instead of columnar results, you can display column values in separate fields (i.e horizontally instead of vertically) and “flatten” out the NULLs as follows:
Step 1 – the original query:
SELECT memberid, month, points
FROM table_name
WHERE year = '2025' and memberid = '123456' and month in (7,6,5);Step 2 – convert column values into rows:
SELECT memberid,
(case when month = 7 then points end) month_7,
(case when month = 6 then points end) month_6,
(case when month = 5 then points end) month_5
FROM table_name
WHERE year = '2025' and memberid = '123456' and month in (7,6,5);Step 3 – remove the NULLs (i.e. ‘flatten’ the results):
SELECT participantcode,
min(case when month = 7 then points end) month_7,
min(case when month = 6 then points end) month_6,
min(case when month = 5 then points end) month_5
FROM table_name
WHERE year = '2025' and memberid = '123456' and month in (7,6,5)
GROUP BY memberid;Prompts
The colon in front of ‘input’ will bring up a popup that allows you to enter a value to be substituted in the query:
SELECT * FROM table_name WHERE campaigncode = '0001' AND memberid = :input;Flashback queries (‘AS OF TIMESTAMP’)
Flashback queries allow you to query backups of tables, which is useful for retrieving data from the past.
SELECT field_name
FROM table_name AS OF TIMESTAMP to_timestamp('23
Oct-2023 13:00:00','dd-mm-yyyy hh24:mi:ss')
WHERE memberid = '1234567';