Excel/SQL functions

Question 1

CAST

Answer 1

SQL: changes datatype (string to float)

CAST (revenue AS Float)
or
CAST (date AS date) AS date only

Question 2

CONCAT (in excel/ databases)

CONCATENATE (in sheets)

Answer 2

SQL: joins 2 or more text strings

_____ (1,2) AS ___name it____ OR
Concatenate (A1, “:”, B1) if you want to add something

Question 3

COALESCE

Answer 3

SQL: accounts for null values
SELECT
COALESCE (column1, column2, column3)

____ (1. 2, 3,0(backup if 1 isn’t available, try this field, then this field, then this field, then zero))

Question 4

TRIM

Answer 4

SQL: to eliminate leading or trailing spaces

Question 5

CASE

Answer 5

Change value/spelling of something (like Find/Replace)

SELECT
WHEN customer_name = “Johno” THEN “John”
ELSE customer_name
END AS updated_customer_name

OR

SELECT
 WHEN quantity >30 THEN 'above 30'
WHEN quantity = 30 THEN 'Is 30'
ELSE 'under 30'
END AS QuantityText

Question 6

SORT (sheets/excel syntax)

Answer 6

=SORT(range, sort column, is ascending (true or false))

Question 7

ORDER BY

Answer 7

SELECT, FROM, ______column like Release_date DESC

Question 8

SORT (databases/SQL)

Answer 8

ORDER BY

Question 9

CONVERT (sheets)

Answer 9

Converts fareinheight to celcius

=CONVERT(B2, “F”, “C”)

Question 10

SPLIT

Answer 10

=SPLIT (A2, “ “) (text, delimiter)(For ex: Ann Jones)

Question 11

=TO_PURE_NUMBER(A2)

Answer 11

Converts to number without text ($ sign gone)

Question 12

DATA VALIDATION

Answer 12

create checkboxes, lists to choose from

Question 13

SAFE_CAST

how to make a date a string

Answer 13

SAFE_CAST (MyDate AS STRING) FROM MyTable

Question 14

COUNT (3 types)

Answer 14

COUNT (column_name) - returns number of values in colum - doesn’t count nulls

COUNT(*) - counts the number of records in a table

COUNT (DISTINCT column_name) - returns number of distinct values of specified column

Question 15

GROUP BY

Answer 15

I think all of them together = 1 entry

Question 16

VALUE

Answer 16

converts string to number

Question 17

COUNT DISTINCT

Answer 17

sql COUNT (DISTINCT column)

Question 18

IF

Answer 18

IF (quantity>10, more, less)

IF (condition, value if true, value if false)

Question 19

HAVING

Answer 19

Group by:  Last Name
HAVING COUNT (orders.orderID) > 10

Question 20

COUNTIF

Answer 20

sheets
=COUNTIF(range, “value”)
=COUNTIF (B2-B50, “=1”)

Question 21

SUMIF

Answer 21

=SUMIF (range, criteria, (sum_range))

=SUMIF (B2-B50, “=1”, C2-C50)

Question 22

SUMIFS

Answer 22

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2,…])
=SUMIFS (B1:B9, A1:A9, “Fuel”, C1:C9, “12/15/2020”)

Question 23

SUMPRODUCT

Answer 23

=SUMPRODUCT(array1, array2…)

= (multiplication of corresponding values, then adding them all together)

Question 24

The value we’re calculating should NOT be equal to the value we specify

Answer 24

< >  4
< >  0
or
! = 0
! = 4

Question 25

EXTRACT

Answer 25

SQL - let's us pull one part of a given date to use EXTRACT (YEAR from starttime_run) AS Year

Question 26

Returns the remainder of a division calculation

Answer 26

% Modulo operator

Question 27

ROUND

Answer 27

sql ROUND (number, # decimals) ROUND(MAX(UnitPrice), 2)

Question 28

WITH

Answer 28

sql a type of temporary table that you can query from multiple times WITH (new name) AS (select,from,where table)

Question 29

Tells query t's part of description and not part of the code

Answer 29

##

Question 30

INNER,OUTER,LEFT, RIGHT JOIN

Answer 30

FROM _____table1_____ INNER JOIN ____table2____ ON table1.column1 = table2.column1

Question 31

SELECT INTO

Answer 31

``` (temporary table) SELECT * INTO AfricaSales FROM GlobalSales WHERE Region=Africa ```

Question 32

CREATE TABLE

Answer 32

``` CREATE TABLE AfricaSales AS ( SELECT* FROM GlobalSales WHERE Region= "Africa" ) ```

Question 33

IF

Answer 33

sql SELECT IF (genre=`horror`, 'will not watch', 'will watch') AS watch_category,

Question 34

how to delete a table

Answer 34

DROP TABLE IF EXISTS table1

Question 35

Create tables (2 ways)

Answer 35

``` CREATE TABLE IF NOT EXISTS CREATE OR REPLACE TABLE (tablename1) AS SELECT FROM WHERE ```

Question 36

create temporary table

Answer 36

``` CREATE TEMP TABLE ExampleTable AS SELECT colors FROM favorite_colors ```