10 Questions
Use the ______________ statement to get all the columns from a table.
SELECT *
The __________________ clause is used to group rows that have the same values in one or more columns.
GROUP BY
The __________________ operator is used to select a range of values.
BETWEEN
The __________________ clause is used to filter records and fetch only those that meet certain conditions.
WHERE
The __________________ function is used to count the number of rows in a table.
COUNT
Use the __________________ clause to get rows where values in a group meet a criterion.
HAVING
The __________________ operator is used to select rows where a column is one of several values.
IN
Use the __________________ wildcard to select rows where a column contains specific letters.
LIKE
The __________________ clause is used to get summaries grouped by values in a specific order.
GROUP BY ... ORDER BY
Use the __________________ statement to get a column from a table by name.
SELECT col
Study Notes
Filtering Data
- Filter on numeric columns using
WHERE
clause with operators:>
,>=
,<
,<=
,!=
- Example:
SELECT franchise, inception_year FROM franchises WHERE inception_year > 1928
Aggregating Data
- Use aggregate functions:
COUNT(*)
,SUM(col)
,AVG(col)
,MIN(col)
,MAX(col)
- Example:
SELECT COUNT(*) FROM franchises
- Group data using
GROUP BY
clause - Example:
SELECT owner, COUNT(*) FROM franchises GROUP BY owner
Querying Tables
- Retrieve all columns using
SELECT *
- Retrieve specific columns using
SELECT col1, col2
- Use
FROM
clause to specify table name - Example:
SELECT franchise, inception_year FROM franchises
Filtering on Text Columns
- Filter on text columns using
WHERE
clause with operators:=
,IN
,LIKE
- Example:
SELECT franchise, original_medium FROM franchises WHERE original_medium = 'book'
- Use
LIKE
operator with wildcard%
to match patterns - Example:
SELECT franchise, original_medium FROM franchises WHERE original_medium LIKE '%oo%'
Grouping and Filtering
- Use
HAVING
clause to filter groups - Example:
SELECT original_medium, SUM(n_movies) AS total_movies FROM franchises GROUP BY original_medium HAVING total_movies > 10
- Use
WHERE
clause to filter before grouping - Example:
SELECT original_medium, SUM(n_movies) AS total_movies FROM franchises WHERE original_medium IN ('movie', 'video game') GROUP BY original_medium
MySQL-Specific Syntax
- Not all code works in every dialect of SQL.
- MySQL has its own syntax and features.
This quiz covers MySQL concepts including filtering data and aggregating data, specifically filtering on numeric columns and performing simple aggregations.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free