SQL Queries Flashcards
what is the syntax for returning 2 columns from a table?
SELECT column 1, column 2
FROM table name;
what does AS do
Allows you to rename a column or table using an alias. The new name can be anything you want as long as you put it inside of single quotes. the columns are not being renamed in the table. The aliases only appear in the result.
can you alias multiple columns in a single query?
yes
what does DISTINCT do?
Returns unique values in the output. It filters out all duplicate values in the specified column(s).
what is the syntax for extracting 2 distinct values from 2 columns?
SELECT DISTINCT column1, column2 from TABLE;
what does WHERE do?
filters the result set to only include rows where the following condition is true. can be used with (not) equal to, and greater/less than(or equal to) operators to create a conditional.
can we compare 2 values using a WHERE clause?
yes: When comparing two columns in a WHERE clause, for each row in the database, it will check the value of each column and compare them.
This will return all rows where the value in the
x column is greater than the y column value.
*/
SELECT x, y
FROM coordinates
WHERE x > y;
what does LIKE do?
LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.
what is a wildcard character?
_ underscore
means you can substitute any individual character here without breaking the pattern.
ex: name LIKE ‘Se_en’;
means the names Seven and Se7en both match this pattern.
can we apply the LIKE operator to data types other than text?
YES, but you must always wrap the pattern with quotations ‘ ‘, even if you are looking for numbers.
ex:
This will select movies where the id number
starts with 2 and is followed by any two numbers:
SELECT *
FROM movies
WHERE id LIKE ‘2__’;
what is %?
% is a wildcard character that matches zero or more missing letters in the pattern. For example:
A% matches all movies with names that begin with letter ‘A’ %a matches all movies that end with ‘a’
We can also use % both before and after a pattern: %man%
When using SQL LIKE operators, how do we search for patterns containing the actual characters “%” or “_”?
using the escape character \ immediately before the character that has an inbuilt alternative meaning. ex:
SELECT *
FROM books
WHERE title LIKE ‘% 100\%’;
how do we test for unknown absent values? will != or = work to do this?
we use IS NULL/ IS NOT NULL. comparison values =! and = will NOT work to test for unknown/absent values.
When storing missing data, should I store them as NULL?
It can depend entirely on how you need the data to be stored and utilized.
Say we have a table of employee data with a column for addresses.
If we stored the missing address values as an empty string ‘’ then these values are not NULL. Empty strings are seen as a string of length 0. So, if we ran a query using
WHERE address IS NULL
it would not give us the rows with missing address values. We would have to check using
WHERE address = ‘’
AND
combine multiple conditions in a WHERE clause to make the result set more specific and useful. both conditions must be true
OR
OR operator displays a row if any condition is true.
ORDER BY
SORTS RESULT SET either alphabetically or numerically. always goes after the WHERE clause
*NOTE that we can order by columns. the column we order by doesn’t have to be one of the columns we’re displaying
ASC AND DESC
ASC SORTS results from low to high;
DESC sorts results from high to low
can we use order by with multiple columns?
it will first order the data on the first column, then, keeping the previous column order, it will order on the next column, and so on.
You can also specify ascending or descending order for each listed column.
ex:
ORDER BY year ASC, name DESC;
LIMIT
lets you specify the maximum number of rows the result set will have. This saves space on our screen and makes our queries run faster.
always goes at the very end of the query. Also, it is not supported in all SQL databases.
CASE syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END [as alias optional];
GROUP BY
Can reference the selected columns by number in which they appear in the SELECT statement. The example quer
WITH clause
defines a temporary data set whose output is available to be referenced in subsequent queries.
STRFTIME() function
returns a formatted date, as specified in a format string.
STRFTIME() syntax
STRFTIME(format, timestring, modifier1, modifier2, …)
format is the format string. timestring is the column or the original value. The remaining arguments are 0 or more optional modifiers to transform the time string.
substitutions for date and time
%Y returns the year (YYYY)
%m returns the month (01-12)
%d returns the day of month (01-31)
%H returns the hour (00-23)
%M returns the minute (00-59)
%S returns the second (00-59)
