28 Questions
What is the purpose of the WHERE clause in SQL?
To filter data based on a condition
How can you get the total number of rows in a table?
Using the SELECT COUNT(*) function
What is the purpose of the SELECT SUM(col) function?
To get the total value of a column
How do you get rows where a number is greater than or equal to a value?
Using the WHERE col >= n clause
What is the purpose of the SELECT AVG(col) function?
To get the average value of a column
How do you get rows where a number is greater than a value?
Using the WHERE col > n clause
What is the purpose of the SELECT COUNT(*) function?
To get the total number of rows in a table
How can you get the mean value of a column?
Using the SELECT AVG(col) function
What is the purpose of the ORDER BY clause in a SELECT statement?
To sort rows in a specific order
How do you get rows where values are missing in a specific column?
WHERE col IS NULL
What is the purpose of the LIMIT clause in a SELECT statement?
To limit the number of rows returned
How do you get unique values in a column?
SELECT DISTINCT col FROM table
What is the purpose of the WHERE clause in a SELECT statement?
To filter rows based on a condition
How do you get the current date and datetime?
SELECT CURDATE(), NOW(), CURTIME()
What is the purpose of the FROM clause in a SELECT statement?
To specify the table to retrieve data from
How do you get rows where values are not missing in a specific column?
WHERE col IS NOT NULL
What is the purpose of the BINARY operator in a WHERE clause?
To get case sensitive matching
What is the purpose of the SHOW TABLES command?
To list available tables
What is the purpose of the GROUP BY clause in SQL?
To group rows of a table based on one or more columns
What is the syntax to get rows where a number is between two values in SQL?
WHERE col BETWEEN m AND n
What is the purpose of the ORDER BY clause in SQL?
To sort data in ascending or descending order
How do you get rows where text contains specific letters in SQL?
WHERE col LIKE '%abc%'
What is the purpose of the HAVING clause in SQL?
To filter data based on specific conditions after grouping
How do you select all columns from a table in SQL?
SELECT *
What is the purpose of the WHERE clause in SQL?
To filter data based on specific conditions
How do you get rows where one condition and another condition holds in SQL?
WHERE condn1 AND condn2
What is the purpose of the AS keyword in SQL?
To rename a column or alias
What is the syntax to get summaries grouped by values with a specific order in SQL?
GROUP BY col ORDER BY smmry DESC
Study Notes
Filtering Data
- Use
WHEREclause to filter data based on conditions - Filter on numeric columns:
-
WHERE col > nto get rows where a number is greater than a value -
WHERE col >= nto get rows where a number is greater than or equal to a value -
WHERE col BETWEEN m AND nto get rows where a number is between two values (inclusive)
-
- Filter on text columns:
-
WHERE col = 'x'to get rows where text is equal to a value -
WHERE col IN ('x', 'y')to get rows where text is one of several values -
WHERE col LIKE '%abc%'to get rows where text contains specific letters
-
- Filter on multiple columns:
-
WHERE condn1 AND condn2to get rows where one condition and another condition holds
-
Aggregating Data
- Use
SELECTclause to aggregate data - Simple aggregations:
-
SELECT COUNT(*)to get the total number of rows -
SELECT SUM(col)to get the total value of a column -
SELECT AVG(col)to get the mean value of a column
-
- Get summaries grouped by values:
-
SELECT col, COUNT(*)withGROUP BY colto get summaries grouped by values -
SELECT col, SUM(x)withGROUP BY colto get summaries grouped by values with sum -
SELECT col, COUNT(*)withGROUP BY col ORDER BY smmry DESCto get summaries grouped by values in order of summaries
-
Querying Tables
- Get all columns from a table using
SELECT * - Get a column from a table by name using
SELECT col - Get multiple columns from a table by name using
SELECT col1, col2 - Override column names with
SELECT col AS new_name - Arrange the rows in descending order of values in a column with
ORDER BY col DESC - Limit the number of rows returned with
LIMIT n
MySQL-Specific Syntax
- Not all code works in every dialect of SQL
- Use
WHERE BINARY condnto get case sensitive matching - Get the current date with
CURDATE()and the current datetime withNOW()orCURTIME() - List available tables with
SHOW TABLES
Test your skills on filtering data in MySQL, including 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
