Aggregate functions combine multiple rows together to form a single value of more meaningful

information.
COUNT takes the name of a column(s) as an argument and counts the number of rows where the
value(s) is not NULL.
GROUP BY is a clause used with aggregate functions to combine data from one or more columns.
SUM() takes the column name as an argument and returns the sum of all the values in that
column.
MAX() takes the column name as an argument and returns the largest value in that column.
MIN() takes the column name as an argument and returns the smallest value in that column.
AVG() takes a column name as an argument and returns the average value for that column.
ROUND() takes two arguments, a column name and the number of decimal places to round the
values in that column.
SELECT DISTINCT is used to return unique values in the result set. It filters out all duplicate values.
Here, the result set lists each genre in the movies table exactly once.
1. SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in
the specified column(s)
2. genre is the name of the column to display in the result set.
3. FROM movies indicates the table name to query from.
Filtering the results of a query is an important skill in SQL. It is easier to see the different possible
genres a movie can have after the data has been filtered, than to scan every row in the table.
The rest of this lesson will teach you different commands in SQL to filter the results of a query.
The way to filter queries in SQL is to use the WHERE clause. In the code editor type
SELECT * FROM movies WHERE imdb_rating > 8;

This statement filters the result set to only include movies with IMDb ratings greater than 8. How does it
work?
1. WHERE is a clause that indicates you want to filter the result set to include only rows where the
following condition is true.
2. imdb_rating > 8 is a condition that filters the result set. Here, only rows with a value greater than 8
in the imdb_rating column will be returned in the result set.
3. > is an operator. Operators create a condition that can be evaluated as either true or false. Common
operators used with the WHERE clause are:
= equals
!= not equals
> greater than
< less than
>= greater than or equal to
<= less than or equal to
There are also some special operators that we will learn more about in the upcoming exercises.
LIKE can be a useful operator when you want to compare similar values. Here, we are comparing two
movies with the same name but are spelled differently.
1. LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.
2. name LIKE Se_en is a condition evaluating the name column for a specific pattern.
3. Se_en represents a pattern with a wildcard character. The _ means you can substitute any individual
character here without breaking the pattern. The names Seven and Se7en both match this pattern.
% is another wildcard character that can be used with LIKE. We will learn more about % in the next
exercise.
This statement filters the result set to only include movies with names that begin with the letter "A"
% is a wildcard character that matches zero or more missing letters in the pattern.
 A% matches all movies with names that begin with "A"
%a matches all movies that end with "a"
SELECT * FROM movies WHERE name LIKE '%man%';

You can use % both before and after a pattern. Here, any movie that contains the word "man" in its name
will be returned in the result set. Notice, that LIKE is not case sensitive. "Batman" and "Man Of Steel"
both appear in the result set.
1.
BETWEEN is another special operator that can be used in a WHERE clause. In the code editor, type
SELECT * FROM movies
WHERE name BETWEEN 'A' AND 'J';

2.
Remove the previous query. Then type
SELECT * FROM movies
WHERE year BETWEEN 1990 AND 2000;

The BETWEEN operator is used to filter the result set within a certain range. The values can be numbers,
text or dates.
SELECT * FROM movies
WHERE name BETWEEN 'A' AND 'J';

This statement filters the result set to only include movies with names that begin with letters "A" up to
but not including "J".
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;

In this statement, the BETWEEN operator is being used to filter the result set to only include movies with
years between 1990 up to and including 2000.
Instructions
1.
Instead of returning every movie made BETWEEN 1990 and 2000, let's just return the movies that are
comedies. In the code editor, type
SELECT * FROM movies
WHERE year BETWEEN 1990 AND 2000
AND genre = 'comedy';

Sometimes you want to combine multiple conditions in a WHERE clause to make the result set more
specific and useful. One way of doing this is to use the AND operator.
1. year BETWEEN 1990 and 2000 is the first condition in the WHERE clause.
2. AND genre = 'comedy' is the second condition in the WHERE clause.
3. AND is an operator that combines two conditions. Both conditions must be true for the row to be
included in the result set. Here, we use the AND operator to only return movies made between 1990 and
2000 that are also comedies.
Instructions
1.
Similar to AND, the OR operator can be used with the WHERE clause, but there are some important
differences. In the code editor, type
SELECT * FROM movies
WHERE genre = 'comedy'
OR year < 1980;
Learn SQL
Connected to Codecademy
SELECT * FROM movies
ORDER BY imdb_rating DESC;
Database Schema
Schema undefined.
Query Results
Run a query to see results.
Learn
SELECT * FROM movies
WHERE genre = 'comedy'
OR year < 1980;

The OR operator can also be used to combine more than one condition in a WHERE clause. The OR
operator evaluates each condition separately and if any of the conditions are true then the row is added
to the result set.
1. WHERE genre = 'comedy' is the first condition in the WHERE clause.
2. OR year < 1980 is the second condition in the WHERE clause.
3. OR is an operator that filters the result set to only include rows where either condition is true. Here, we
return movies that either have a genre of comedy or were released before 1980.
You can sort the results of your query using ORDER BY. Sorting the results often makes the data more
useful and easier to analyze.
1. ORDER BY is a clause that indicates you want to sort the result set by a particular column either
alphabetically or numerically.
2. imdb_rating is the name of the column that will be sorted.
3. DESC is a keyword in SQL that is used with ORDER BY to sort the results in descending order (high to
low or Z-A). Here, it sorts all of the movies from highest to lowest by their IMDb rating.
It is also possible to sort the results in ascending order. ASC is a keyword in SQL that is used with ORDER
BY to sort the results in ascending order (low to high or A-Z).
SELECT * FROM movies
ORDER BY imdb_rating DESC
LIMIT 3;

Sometimes even filtered results can return thousands of rows in large databases. In these situations it
becomes important to cap the number of rows in a result set.
LIMIT is a clause that lets you specify the maximum number of rows the result set will have. Here, we
specify that the result set can not have more than three rows.
Congratulations! You just learned how to query data from a database using SQL. What can we
generalize so far?
SELECT is the clause you use every time you want to query information from a database.
WHERE is a popular command that lets you filter the results of the query based on conditions that
you specify.
LIKE and BETWEEN are special operators that can be used in a WHERE clause
AND and OR are special operators that you can use with WHERE to filter the query on two or more
conditions.
ORDER BY lets you sort the results of the query in either ascending or descending order.
LIMIT lets you specify the maximum number of rows that the query will return. This is
especially important in large tables that have thousands or even millions of rows.