2. Queries: Learn the most commonly used SQL commands to query a table in a database-retrieve information

Operators

Create a condition that can be evaluated as either true or false. Common operators used with the WHERE clause are: = equals ! and <> = not equals > greater than < less than >= greater than or equal to <= less than or equal to 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.

DESC

DESC is a keyword in SQL that is used with ORDER BY clause to sort the results in descending order (high to low or Z-A).

WHERE

Is a clause that indicates you want to filter the result set to include only rows where the following condition is true. 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.

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 cannot have more than three rows: SELECT * FROM movies ORDER BY imdb_rating ASC LIMIT 3; Literally, the query results show three rows. 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.

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).

AND

Is a special operator used with the WHERE clause to combine multiple conditions, the two conditions must be true for the row to be included in the result. For example: 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. SELECT * FROM movies WHERE year BETWEEN 1990 and 2000 AND genre = 'comedy'; Here, we use the AND operator to only return movies made between 1990 and 2000 that are also comedies.

LIKE

Is a special operator used with the WHERE clause to search for a specific pattern in a column. In other words, you can compare similar values with this operator.

%

Is a wildcard character that matches zero or more missing letters in the pattern using with the LIKE clause. • A% matches all movies with names that begin with "A" • %a matches all movies that end with "a" • %word% matches all movies regardless of order of phases. 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 clause is not case sensitive.

SELECT

Is the clause you use every time you want to query information from a database.

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. For example: 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.

Queries

Allow us to communicate with the database by asking questions and having the result set return data relevant to the question.

ORDER BY

Sorting your results by alphabetically or numerically often makes the data easier to analyze. SELECT * FROM movies ORDER BY imdb_rating DESC; imdb_rating is the name of the column that will be sorted. ORDER BY lets you sort the results of the query in either ascending/ASC or descending/DESC order.

OR

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. For example: 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.

BETWEEN

This operator is used to filter the result set within a certain range. The values can be numbers, text or dates. Notice, the ranges among text and dates in the following example: For example: This statement filters the result set to only include movies with names that begin with letters "A" up to but not including "J". 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.