SQL

Question 1

UNION

Answer 1

combines columns between data sets that have:
1. the same number of columns
2. similar data sets

you have to put the columns in the same order in the SELECT column

Question 2

Is SQL case sensitive for objects you are calling?

Answer 2

YES! you need to write it exactly as it is in the table

Question 3

When using group by, does it matter the order you list objects?

Answer 3

Yes! to group by that object first it needs to be listed first

Question 4

if you want to sort SQL columns what argument do you use?

Answer 4

ORDER BY

Question 5

How would you filter for even ID numbers only?

Answer 5

WHERE ID % 2 = 0

Question 6

How to order alphabetically in SQL

Answer 6

ORDER BY (variable name) – it defaults to alphabetical

Question 7

What can you use to connect to separate select causes?

Answer 7

UNION

Question 8

In MySQL, what do you use to find the length of a variable?

Answer 8

length()

Question 8

What is an easy way to find the max and min values of a table without using max or min?

Answer 8

select the first value (order by the variable in question and limit 1) , then use UNION to connect the same items but ORDER BY in desc (to get the last value), again limit 1

Question 9

how would you find values that started with vowels?

Answer 9

WHERE (value) LIKE ‘a%’ OR ‘e%’ OR ‘I%’ etc

Question 10

when you are using OR, AND, in a where clause can you just use commas or do you need to be specific

Answer 10

BE SPECIFIC ex:
WHERE (value) LIKE ‘a%’ OR ‘e%’ OR ‘I%’ etc

the OR has to be between each thing even if you want OR for all of them

Question 11

what is the easiest way to sort for variables that end and start with vowels?

Answer 11

use AND to connect LIKE a% AND %a for the whole list of vowels

Question 12

what is the easiest way to sort variables so they do not start with vowels?

Answer 12

use NOT LIKE (all the vowels)% connecting them with AND instead of OR

Question 13

when doing a join, does the name of the table go first or second in the select cause

Answer 13

first! ex: city.name ‘; city is the table, name is the variable

Question 14

describe a Left join

Answer 14

if we are thinking of a venn diagram, a left join takes all the information from the left table (first table called) then the information that matches the left table from the right table (table 2)

Question 15

describe an inner join

Answer 15

if we are thinking of a venn diagram, an inner join is only the information in the middle. it only pulls the information that matches from the left (first table) and right (second table)

Question 16

describe a right join

Answer 16

if we are thinking of a venn diagram, a right join takes all the information from the right table (2nd table) then the information that matches the left table (2nd table)

Question 17

Select all records where the first letter of the City is an “a” or a “c” or an “s”.

Answer 17

WHERE City LIKE ‘[acs]%’

Question 18

Select all records where the first letter of the City starts with anything from an “a” to an “f”.

Answer 18

WHERE City LIKE ‘[a-f]%’;

Question 19

Select all records where the first letter of the City is NOT an “a” or a “c” or an “f”.

Answer 19

WHERE City LIKE ‘[!acf]%’

Question 20

what does SQL stand for?

Answer 20

Structured Query Language

Question 21

TO DO:
- review CTE and subqueries
- review joins
- insert, delete, database?

Question 22

Self Join

Answer 22

self join allows you to compare data in the sample table as if it is from a different table. This can be helpful when you need to evaluate hierarchical relationships, compare rows in the same data or find duplicate rows

to do this, you give the table 2 different aliases to compare to

Question 23

Cross join

Answer 23

a cross join combines every row of the first table to every row of the second table resulting in all possible pairs of rows. this is helpful if you have a small data set and you need to see all combinations

Question 24

when do you use INTERSECT

Answer 24

Requires all fields to match, you don't need to state what the ON statement is because they all match INTERSECT operator is used to retrieve only the rows that are common between two SELECT queries. It returns unique records found in both queries' result sets, making it useful when you need to find overlapping data between two datasets.

Question 25

EXCEPT

Answer 25

It effectively performs a "set difference" operation, allowing you to find records that exist in one dataset but not in another.

Question 26

when you do need to use GROUP BY?

Answer 26

when you have an agg function and non-agg functions - all non agg need to be in group by

Question 26

How do you create an anti-join?

Answer 26

Use a NOT statement into a CTE EX: WHERE country NOT IN (SELECT country FROM states WHERE independent_year < 1800)

Question 27

When do you use a subquery?

Answer 27

When you need to do a smaller query within a larger query in order to filter, aggregate, or transform the data you need 1. Can place in WHERE to build filter or aggregate from other table 2. Can place in FROM to build virtual table to pull from these seem to be the most common based on the examples

Question 28

what does CTE stand for? What is it?

Answer 28

A Common Table Expression ; it is a temporary or virtual table that is present only during the calculation. Overall it helps to improve readability of results WITH cte_name AS ([insert CTE] SELECT * FROM cte_name

Question 29

in what order does SQL run code?

Answer 29

"Find Wise Great Hunters Secretly Ordering Lunch" FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT

Question 30

In MySQL, how do you create rank? what type of ranks are there?

Answer 30

Using RANK() OVER (ORDER BY [whatever_column]) AS ranking You can use DENSE_RANK to create ties, where RANK will pick one to be second despite it being tied. You can also rank amongst a column/group using partition: EX: DESNSE_RANK() OVER (PARTITION BY [column] ORDER BY [column2]) AS ranking you can't name it 'rank' because rank is a built in code

Question 31

What is a good reason to use a CTE?

Answer 31

a common table expression is best used when you need to create some sort of organization in the table and then you want to wittle it down from there. for ex: if you want to rank the top profits but then you only want to show the top 3, you should use a CTE first then pull from the CTE