Procedural language

Question 1

DECLARE

multi

Answer 1

DECLARE d DATE DEFAULT CURRENT_DATE();

DECLARE x, y, z INT64 DEFAULT 0;

Question 2

SET

multi

Answer 2

SET variable_name = expression;

SET (a, b, c) = (1 + 3, ‘foo’, false);

Question 3

EXECUTE IMMEDIATE

Answer 3

EXECUTE IMMEDIATE

“CREATE TEMP TABLE Books (title STRING, publish_date INT64)”;

Question 4

EXECUTE IMMEDIATE

USING

Answer 4

EXECUTE IMMEDIATE
“INSERT INTO Books (title, publish_date) VALUES(?, ?)”
USING 1815 as year, “Emma” as name;

Question 5

EXECUTE IMMEDIATE

FORMAT

Answer 5

EXECUTE IMMEDIATE FORMAT(“””

SELECT %s FROM books”””, “name”)

Question 6

BEGIN…EXCEPTION…END

Answer 6

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

Question 7

CASE

Answer 7

CASE
  WHEN boolean_expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

Question 8

CASE search_expression

Answer 8

CASE search_expression
  WHEN expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

Question 9

IF

Answer 9

IF condition THEN [sql_statement_list]
  [ELSEIF condition THEN sql_statement_list]
  [...]
  [ELSE sql_statement_list]
END IF;

Question 10

FOR…IN

Answer 10

FOR loop_variable_name IN (table_expression)
DO
sql_expression_list
END FOR;

Question 11

CALL

Answer 11

CALL procedure_name (procedure_argument[, …])

Question 12

EXPORT DATA

Answer 12

EXPORT DATA OPTIONS(
  uri='gs://bucket/folder/*.csv',
  format='CSV',
  overwrite=true,
  header=true,
  field_delimiter=';') AS
SELECT field1, field2
FROM mydataset.table1
ORDER BY field1
LIMIT 10