Queries Flashcards
What is a core purpose of SQL?
To retrieve information stored in a database.
What is retrieving information stored in a database usually referred to as?
Querying
What type of statement would you use to only query data from two columns?
SELECT column1, column2
FROM table_name;
(SELECT statement)
What keyword lets you rename a column or table using an alias?
AS
Where must the new name in the AS keyword be placed in between?
Single quotes
’ ‘
What would be used to find the distinct values of a particular column?
DISTINCT
What does DISTINCT do?
Returns unique values in the output.
What does DISTINCT filter out?
All duplicate values in the specified columns.
Where does DISTINCT go in the SQL structure?
After the SELECT clause before the column name
SELECT DISTINCT ____
FROM _____;
What clause can be used to restrict query results in order to obtain only the information wanted?
WHERE
What does this statement do?
SELECT *
FROM movies
WHERE imdb_rating > 8;
The WHERE clause filters the result set to only include rows where the following condition is true
imdb_rating is the column
the > is the greater than operator
imdb_rating > 8 is a condition and only the rows with a value greater than 8 in the imdb_rating column will be returned.
What comparison operators are used with WHERE clauses?
== (equal to)
!= (not equal to)
> (greater)
< (lesser)
>= (greater than or equal to)
<= (lesser than or equal to)
What are Comparison Operators?
Operators create a condition that can be evaluated as either true or false.
What special operator is used with the WHERE clause to search for a specific pattern in a column?
LIKE
(LIKE I)
What is LIKE used for?
A useful operator to compare similar values.
What does this statement do?
SELECT *
FROM movies
WHERE name LIKE ‘Se_en’;
LIKE is a special operator used with the WHERE clause to search for a specific patter in a column
name LIKE ‘Se_en’ is a condition evaluating the name column for a specific pattern
Se_en represents a patter with a wildcard character
What wildcard character allows for individual character substitution without breaking the pattern?
_
LIKE searches for a specific what in a column?
pattern
What is a another wildcard character that can be used with LIKE that filters patterns?
%
The wildcard character % matches ______ or _________ in a pattern?
zero
missing letters
What does this statement do?
SELECT *
FROM movies
WHERE name LIKE ‘A%’;
‘A%’ matches all movies with names that begin with the letter ‘A’.
What does this statement do?
SELECT *
FROM movies
WHERE name LIKE ‘%a’;
‘%a’ matches all movies that end with a ‘a’.
What does this statement do?
SELECT *
FROM movies
WHERE name LIKE ‘%man%’;
Any movie that contains the word ‘man’ in its name will be returned in the result.
True or False: LIKE is not case sensitive.
True
What are unknown values indicated by?
NULL
NULL values can not be tested with comparison operators = and !=. What operators are used as a replacement?
IS NULL
IS NOT NULL
What clause is the BETWEEN operator used in?
WHERE
What does the BETWEEN operator do?
Filters the result set within a certain range.
How many values does the BETWEEN operator accept?
Two
What kind of data types are acceptable values for a BETWEEN operator?
Numbers
Text
Dates
When the values are text, BETWEEN filers the result set for within what type of range?
Alphabetical
What would be filtered out from this statement?
SELECT *
FROM movies
WHERE name BETWEEN ‘A’ AND ‘J’;
Filters the result set to only include movies with the names that begin with the letter ‘A’ up to, but not including ones that begin with the letter ‘J’.
What would be filtered out from this statement?
SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;
Filters the result set to only include movies with the years from 1990 up to and including 1999.
What operator is used with the WHERE clause to combine multiple conditions?
AND
Why would the AND operator be added in a WHERE clause?
To make the result set more specific and useful.
What does this statement do?
SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999
AND genre = ‘romance’;
year BETWEEN 1990 AND 1999 is the 1st condition
genre = ‘romance’ is the 2nd condition
AND combines the two conditions
True or False: With the AND operator, both conditions must be true for the row to be included in the result.
True
Which operator in the WHERE clause displays a row if any condition is true?
OR
What clause lists data in the result set in a particular order?
ORDER BY
Which two ways can the results be sorted using ORDER BY?
Alphabetically
Numerically
What does this statement do?
SELECT *
FROM movies
ORDER BY name;
ORDER BY is a clause that indicates you want to sort the result set by a particular column
name is the specified column
Is this statement sorting data in a decreasing or increasing order?
SELECT *
FROM movies
WHERE imdb_rating > 8
ORDER BY year DESC;
Decreasing
Which ORDER BY keyword sorts results from high to low or Z to A?
DESC
Which ORDER BY keyword sorts results from low to high or A to Z?
ASC
What is ASC and DESC in ORDER BY?
Keywords that stand for Ascending and Descending
What clause does ORDER BY always go after? (If this clause is present).
WHERE
What clause specifies a maximum number of rows the result will have?
LIMIT
Where does the LIMIT clause go on a query?
At the very end
What does this statement do?
SELECT *
FROM movies
LIMIT 10;
LIMIT is a clause that lets you specify the maximum number of rows the result will have
What is an advantage of the LIMIT clause?
Saves space on screen
Queries run faster
What type of statement allows for the creation of different outputs? (… consider it as an if-then logic)
CASE
