Queries

Question 1

AND operator

Answer 1

SELECT model
FROM cars
WHERE color = ‘blue’
AND year > 2014;

• AND: allows multiple conditions to be combined
• Records must match both conditions that are joined by AND to be included in result set

Question 2

%Wildcard

Answer 2

SELECT name
FROM movies
WHERE name LIKE ‘The%’;

• can be used in a LIKE operator pattern to match zero or more unspecified character(s)
• example above will match any movie that begins with The followed by zero or more of any characters

Question 3

AS clause

Answer 3

SELECT name AS ‘movie_title’
FROM movies;

• AS = aliased / allows columns or tables to be specifically renamed in returned result set
• example above will rename column ‘name’ to ‘movie_title’ upon return of result set

Question 4

OR operator

Answer 4

SELECT name
FROM customers
WHERE state = ‘CA’
OR state = ‘NY’;

• allows multiple conditions to be combined / records matching either condition joined by OR are included in result set
• example above will return result set with customers from CA or NY

Question 5

_Wildcard

Answer 5

SELECT name
FROM movies
WHERE name LIKE ‘_ove’;

• can be used in a LIKE operator pattern to match any single unspecified character
• example above will match any movie which begins with a single character, followed by ‘ove’
• _ = single character / % = zero or more characters

Question 6

ORDER BY clause

Answer 6

SELECT *
FROM contacts
ORDER BY birth_date DESC;

• can be used to sort the result set by a particular column either alphabetically or numerically / can be ordered two ways:
• DESC: keyword used to sort results in descending order
• ASC: keyword used to sort results in ascending order (default sort method)
• example above will sort results by descending birth_date (youngest first, oldest last - 2000, 1999, 1998, etc)

Question 7

LIKE operator

Answer 7

SELECT name
FROM movies
WHERE name LIKE ‘Star%’;

• can be used inside of a WHERE clause to match a specified pattern
• example above will match any movie with ‘Star’ in its title / beginning with Star / % means anything can come after (Start, Stars, Star Wars, etc)

Question 8

DISTINCT clause

Answer 8

SELECT DISTINCT city
FROM contact_details;

• used to select unique values of a column
• example above will select each unique city from contact_details table (so if there are duplicate Chicago entries in the column, result will show Chicago)

Question 9

BETWEEN operator

Answer 9

SELECT *
FROM movies
WHERE year BETWEEN 1980 AND 1990;

• used to filter by a range of values / values can be text, number, or date data
• example above will match any movies made between 1980 and 1990, inclusive (1990 movies will appear in result)

Question 10

LIMIT clause

Answer 10

SELECT *
FROM movies
LIMIT 5;

• used to narrow, or limit, result set to the specified number of rows
• example above will limit result set to 5 rows

Question 11

NULL values

Answer 11

SELECT address
FROM records
WHERE address IS NOT NULL;

• NULL = no value / NOT NULL = some value
• can be matched, or unmatched, using the IS NULL or IS NOT NULL operators in combination with WHERE clause
• example above will return addresses that have a value, or NOT NULL

Question 12

WHERE clause

Answer 12

SELECT title
FROM library
WHERE pub_year = 2017;

• used to filter rows that match a certain condition
• example above will return titles with pub_year of 2017 only (= must be equal to 2017)