Are you with me or with them? It's time to choose a side!

Don't mix up AND and OR!

If ALL the conditions must be true, use AND.

If AT LEAST ONE of the conditions must be true, use OR.

Still can't tell which side you're on? Then scroll down the level, and we'll help you look inside and understand your true nature.

The following examples show the possible combinations of two conditions joined with AND and OR.

California has way too many cities. If Greg tried to list them all in a query, joining them with OR, it would take him forever. Luckily, there's a handy LIKE keyword that, combined with special characters, searches for part of a text string and returns the matches.

Greg can use LIKE like this:

SELECT * FROM my_contacts

WHERE location LIKE`%CA`;

The bar owner wants to pick out the drinks whose calorie count falls within a given range. How do you write a query for the range from 30 to 60, inclusive?

SELECT drink_name FROM drink_info

WHERE

calories >= 30

AND

calories <= 60;

SELECT drink_name FROM drink_info

WHERE calories BETWEEN 30 AND 60

Let's try to answer some questions and check your knowledge. Take a pen and a sheet of paper and write your versions of the queries for the questions below. As always, we'll check the answers when you're ready.

Amanda, Greg's friend, uses Greg's contact list to look for guys. She has already been on a few dates and has started her own table with her impressions.

Amanda named her table black_book. She wants a list of the successful dates, so she picks the entries with good ratings.

Instead of building long chains of OR, we can simplify the query with the IN keyword. IN is followed by a set of values in parentheses. If a column's value matches one of the values in the set, the record (or the specified subset of its columns) is included in the query result.

And of course, Amanda wants to know which of her acquaintances got bad ratings. If they call, she'll suddenly have some urgent business to take care of.

To get the names of the acquaintances with low ratings, put the NOT keyword before IN. With NOT IN, the result includes the records whose column value is not in the given set

Want to sort the results of your query? It's easy: just add the ORDER BY keywords and a table column name to your SELECT command.

Let's create a simple table with a single CHAR(1) column named “test_chars”.
Insert into it the digits, letters (uppercase and lowercase), and non-alphabetic characters listed below (each character goes into a separate row). Insert a space, and leave one row with a NULL value.

Run a SELECT query with the new ORDER BY clause against the column. Fill in the blanks in the book.

!"#$%&' ()*+,-./0123: ;<=> ?@ABCD[\]^ 'abcd{|}~

Looks like everything is going great: we can arrange the movies alphabetically and build an alphabetical list for each category.

Unfortunately, the director has come up with something else for you...

Fortunately, a single command can sort data by several columns at once.

SELECT * FROM movie_table

ORDER BY category, purchased, title;

Let's see what data the SELECT command returns for the original movie table.

The DESC keyword after a column name in the ORDER BY clause sorts the results in descending order.

Imagine your data standing on the steps of a staircase. When you climb up the stairs (data sorted in ascending order), you'll run into the letter A before the letter B. When you walk down (data sorted in descending order), the first letter you meet will be Z, and the last will be A.

The following query returns a list of movies sorted by purchase date, newest first. For each date, the movies purchased on that day are listed in alphabetical order.

SELECT title, purchased

FROM movie_table

ORDER BY title ASC, purchased DESC;

TO: Galaxy QA Academy Video Library Staff

FROM: Director

Subject: Dig in!

Hello, everyone!

Everything is just great! The movies are right where they belong, and thanks to those clever ORDER BY clauses, every customer can easily find exactly what they need.

To reward you all for your exemplary work, there's a pizza party at my house tomorrow. We're getting together at 6:00 PM.

And don't forget to bring your report!

Your Director.

P.S. And don't dress up too much, I need to move some furniture around...

Princess Leia, Master of the Girl Padawans

The girl who sells the most cookies wins free Tauntaun riding lessons. Every girl wants to win, so it's very important for Leia to name the winner quickly, before things turn into a fight.

Use your ORDER BY skills and write a query that will help Edwina find out the winner's name.

Write your own version of the query on a piece of paper, then click the Master's answer and compare the two.

What if you need to apply a droid-reflashing query not to all droids, but only to a group of a certain type? To group data, SQL has a wonder command: GROUP BY. This construct is designed to pick out separate groups of rows from a table, with the functions specified in SELECT (for example, COUNT(), MIN() and so on) applied to each group.

Another very common use of GROUP BY in SQL is selecting unique records from tables. In the following example, you'll notice that the result set has no repeated girls' names, whereas in the original table they appeared more than once.

SELECT first_name, MAX (sales)

FROM cookie_sales

GROUP BY first_name;

SELECT first_name, MIX (sales)

FROM cookie_sales

GROUP BY first_name;

To find out which girl sold cookies on more days than the others, Edwina tries using the COUNT function for counting. The COUNT function returns the number of records in a column

First, let's see how the DISTINCT keyword works without the COUNT function

Now let's try running the command with the COUNT: function

I know you're itching to put your knowledge into practice and try everything we've just learned in real queries. So don't delay: into battle! Combat assignments await you.