The following is a discussion board assignment from SQL class last week. I received good feedback on it, and as I wrote it in a more tutorial-style format, I thought I would publish it here. 🙂
Question: Explain the difference in using a LIKE and an IN operator. Also Explain the difference between using ORDER BY, GROUP BY, and HAVING. Can you ever use these together? Why or Why not?
LIKE is used when you need to perform “fuzzy checking” or wildcard searches and not an exact check. The best way to demonstrate the
LIKE operator is through an example.
Suppose you want to find the names of all the people whose first name begins with “Ja”. You cannot execute the command
SELECT first_name, last_name FROM users WHERE first_name = 'Ja';
as that checks if the first name equals exactly “Ja”, thus the query will not work. Instead, you can use the
LIKE operator to perform wildcard searching using the
% sign. Thus you can use the command
SELECT first_name, last_name FROM users WHERE first_name LIKE 'Ja%';
and it will work as expected.
You use the
IN operator when checking if an item exists in an array-like structure that contains some predefined values. An example of such a command would be
SELECT email FROM users WHERE first_name IN ('Stacy', 'James', 'Mike', 'Julie', 'Bob');
ORDER BY lists the results of a query as determined by the ordering column’s values, or in other words, if you sort the query by ascending first name order, the results would being ordered starting at ‘A’ and finish at ‘Z’. On the other hand,
GROUP BY… er, groups, alike columns based on the determining column. So if I wanted to know how many guys and ladies are in this class, I could use
GROUP BY to section the results into two groups. Yes, it is possible to use
ORDER BY and
GROUP BY together. If I wanted to then sort everyone in the group by ascending first name, I would execute the command
SELECT first_name, last_name FROM students GROUP BY gender ORDER BY first_name ASC;
and it would do just that.
HAVING clause is the WHERE clause for groups. Just as
WHERE restricts the amount of data that is pulled from rows, so
HAVING restricts the data in groups. Continuing the previous example, if I wanted to show only people in a group who’s last name had more than or exactly 4 characters, then I would add a
HAVING clause to the statement, so the query now reads
SELECT first_name, last_name FROM students GROUP BY last_name HAVING LENGTH(last_name) >= 4 ORDER BY first_name ASC;
As shown in the new query, yes, you can use
GROUP BY, and
HAVING all in the same query, as each one plays a different role in a query and are not mutually exclusive.