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