SQL Queries

Question 1

what is the syntax for returning 2 columns from a table?

Answer 1

SELECT column 1, column 2
FROM table name;

Question 2

what does AS do

Answer 2

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.

Question 3

can you alias multiple columns in a single query?

Answer 3

yes

Question 4

what does DISTINCT do?

Answer 4

Returns unique values in the output. It filters out all duplicate values in the specified column(s).

Question 5

what is the syntax for extracting 2 distinct values from 2 columns?

Answer 5

SELECT DISTINCT column1, column2 from TABLE;

Question 6

what does WHERE do?

Answer 6

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.

Question 7

can we compare 2 values using a WHERE clause?

Answer 7

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;

Question 8

what does LIKE do?

Answer 8

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

Question 9

what is a wildcard character?

Answer 9

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

Question 10

can we apply the LIKE operator to data types other than text?

Answer 10

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__’;

Question 11

what is %?

Answer 11

% 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%

Question 12

When using SQL LIKE operators, how do we search for patterns containing the actual characters “%” or “_”?

Answer 12

using the escape character \ immediately before the character that has an inbuilt alternative meaning. ex:

SELECT *
FROM books
WHERE title LIKE ‘% 100\%’;

Question 13

how do we test for unknown absent values? will != or = work to do this?

Answer 13

we use IS NULL/ IS NOT NULL. comparison values =! and = will NOT work to test for unknown/absent values.

Question 14

When storing missing data, should I store them as NULL?

Answer 14

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 = ‘’

Question 15

AND

Answer 15

combine multiple conditions in a WHERE clause to make the result set more specific and useful. both conditions must be true

Question 16

OR

Answer 16

OR operator displays a row if any condition is true.

Question 17

ORDER BY

Answer 17

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

Question 18

ASC AND DESC

Answer 18

ASC SORTS results from low to high;

DESC sorts results from high to low

Question 19

can we use order by with multiple columns?

Answer 19

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;

Question 20

LIMIT

Answer 20

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.

Question 21

CASE syntax

Answer 21

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END [as alias optional];

Question 22

GROUP BY

Answer 22

Can reference the selected columns by number in which they appear in the SELECT statement. The example quer

Question 23

WITH clause

Answer 23

defines a temporary data set whose output is available to be referenced in subsequent queries.

Question 24

STRFTIME() function

Answer 24

returns a formatted date, as specified in a format string.

Question 25

STRFTIME() syntax

Answer 25

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.

Question 26

substitutions for date and time

Answer 26

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