MySQL SQL Filters
An interesting outcome of teaching SQL is discovering what skills new users require. One that I continuously rediscover is how to build a test case for various elements of SQL. This is a small article on querying with filters in the WHERE clause.
There are several of the exercises in Alan Beaulieu’s Learning SQL, 3rd Edition that would benefit from example setup. For example, Chapter 4 provides a snapshot of the payment table but doesn’t provide any instructions.
You can create an exercise_4_2 table with the following SQL statement if you plan to change the data:
CREATE TABLE exercise_4_2 AS SELECT payment_id , customer_id , amount , payment_date FROM payment WHERE payment_id BETWEEN 101 AND 120; |
Alternatively, you can create an exercise_4_2 view with the following SQL statement if you plan to only query the data:
CREATE VIEW exercise_4_2 AS SELECT payment_id , customer_id , amount , payment_date FROM payment WHERE payment_id BETWEEN 101 AND 120; |
After creating the new exercise_4_2 table or view from the payment table it will hold a subset of data. You can query all the rows from the new exercise_4_2 table or view with this statement:
SELECT * FROM exercise_4_2; |
It returns the following data set:
+------------+-------------+--------+---------------------+ | payment_id | customer_id | amount | payment_date | +------------+-------------+--------+---------------------+ | 101 | 4 | 8.99 | 2005-08-18 05:14:44 | | 102 | 4 | 1.99 | 2005-08-19 02:19:13 | | 103 | 4 | 2.99 | 2005-08-20 09:32:04 | | 104 | 4 | 6.99 | 2005-08-20 12:55:40 | | 105 | 4 | 4.99 | 2005-08-21 04:53:37 | | 106 | 4 | 2.99 | 2005-08-22 13:58:23 | | 107 | 4 | 1.99 | 2005-08-23 07:43:00 | | 108 | 5 | 0.99 | 2005-05-29 07:25:16 | | 109 | 5 | 6.99 | 2005-05-31 11:15:43 | | 110 | 5 | 1.99 | 2005-05-31 19:46:38 | | 111 | 5 | 3.99 | 2005-06-15 22:03:14 | | 112 | 5 | 2.99 | 2005-06-16 08:01:02 | | 113 | 5 | 4.99 | 2005-06-17 15:56:53 | | 114 | 5 | 2.99 | 2005-06-19 04:20:13 | | 115 | 5 | 4.99 | 2005-06-20 18:38:22 | | 116 | 5 | 4.99 | 2005-07-06 09:11:58 | | 117 | 5 | 2.99 | 2005-07-08 20:04:43 | | 118 | 5 | 4.99 | 2005-07-09 01:57:57 | | 119 | 5 | 5.99 | 2005-07-09 07:13:52 | | 120 | 5 | 1.99 | 2005-07-09 08:51:42 | +------------+-------------+--------+---------------------+ 20 rows in set (0.26 sec) |
With the exercise_4_2 table, you can test the exercises 4-1 and 4-2. Here are the two problems:
- The first exercise checks for rows where the customer_id is not equal to 5 and whether the amount is greater than 8 or payment_date is equal to ‘2005-08-23’. You can structure that question as the following query:
mysql> SELECT * -> FROM exercise_4_2 -> WHERE customer_id <> 5 -> AND (amount > 8 OR DATE(payment_date) = '2005-08-23');It would return the following two rows from the exercise_4_2 table:
+------------+-------------+--------+---------------------+ | payment_id | customer_id | amount | payment_date | +------------+-------------+--------+---------------------+ | 101 | 4 | 8.99 | 2005-08-18 05:14:44 | | 107 | 4 | 1.99 | 2005-08-23 07:43:00 | +------------+-------------+--------+---------------------+ 2 rows in set (0.14 sec)
- The second exercise checks for rows where the customer_id is equal to 5 and whether the amount is not greater than 6 or payment_date is not equal to ‘2005-06-19’. You can structure that question as the following query:
mysql> SELECT * -> FROM exercise_4_2 -> WHERE customer_id = 5 -> AND NOT (amount > 6 OR DATE(payment_date) = '2005-06-19');It would return the following eleven rows from the exercise_4_2 table:
+------------+-------------+--------+---------------------+ | payment_id | customer_id | amount | payment_date | +------------+-------------+--------+---------------------+ | 108 | 5 | 0.99 | 2005-05-29 07:25:16 | | 110 | 5 | 1.99 | 2005-05-31 19:46:38 | | 111 | 5 | 3.99 | 2005-06-15 22:03:14 | | 112 | 5 | 2.99 | 2005-06-16 08:01:02 | | 113 | 5 | 4.99 | 2005-06-17 15:56:53 | | 115 | 5 | 4.99 | 2005-06-20 18:38:22 | | 116 | 5 | 4.99 | 2005-07-06 09:11:58 | | 117 | 5 | 2.99 | 2005-07-08 20:04:43 | | 118 | 5 | 4.99 | 2005-07-09 01:57:57 | | 119 | 5 | 5.99 | 2005-07-09 07:13:52 | | 120 | 5 | 1.99 | 2005-07-09 08:51:42 | +------------+-------------+--------+---------------------+ 11 rows in set (0.00 sec)
- The third exercise checks for payment_id and amount values where the amount is either 1.98, 7.98, or 9.98. You can structure that question as the following query:
mysql> SELECT payment_id -> , amount -> FROM payment -> WHERE amount IN (1.98,7.98,9.98);It would return the following seven rows from the payment table:
+------------+--------+ | payment_id | amount | +------------+--------+ | 1482 | 7.98 | | 1670 | 9.98 | | 2901 | 1.98 | | 4234 | 7.98 | | 4449 | 7.98 | | 7243 | 7.98 | | 9585 | 7.98 | +------------+--------+ 7 rows in set (0.00 sec)
- The fourth exercise checks for the first_name and last_name of customers where the last_name contains an ‘A’ in the second position and a ‘W’ after the ‘A’ character. You can structure that question as the following query:
mysql> SELECT first_name -> , last_name -> FROM customer -> WHERE last_name LIKE '_A%W%';The trick to the
WHEREclause is that the ‘%’ looks for zero to many characters in between two strings.It would return the following nine rows from the customer table:
+------------+------------+ | first_name | last_name | +------------+------------+ | JILL | HAWKINS | | ERICA | MATTHEWS | | LAURIE | LAWRENCE | | JEANNE | LAWSON | | KAY | CALDWELL | | JOHN | FARNSWORTH | | SAMUEL | MARLOW | | LAWRENCE | LAWTON | | LEE | HAWKS | +------------+------------+ 9 rows in set (0.10 sec)
As always, I hope this helps those looking for a solution.
Subscribe via RSS