SQL – LIKE HAVING fun, but more IN depth!

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.

The 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 ORDER BY, GROUP BY, and HAVING all in the same query, as each one plays a different role in a query and are not mutually exclusive.

Advertisements

Triangular Reactions

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s