MCQs on BigQuery SQL Essentials | Google BigQuery

BigQuery SQL Essentials empower users to analyze large datasets with ease. From understanding data types and schemas to mastering SQL syntax and advanced query techniques, this chapter covers core BigQuery SQL skills. Learn how to use joins, subqueries, and functions to extract insights efficiently in a cloud-based environment.

---

Data Types and Schema Design

1. Which data type in BigQuery is used to store true or false values?
a) STRING
b) BOOLEAN
c) INTEGER
d) BYTES

2. What is the maximum length of a STRING data type in BigQuery?
a) 1 MB
b) 10 MB
c) 2 GB
d) Unlimited

3. Which BigQuery data type is suitable for handling JSON-like structures?
a) RECORD
b) ARRAY
c) STRUCT
d) FLOAT

4. How does BigQuery enforce schema consistency?
a) Through automatic detection of schema changes
b) By requiring all rows to conform to a predefined schema
c) By converting all data to STRING type
d) By applying schema on read

5. What is a key advantage of designing schemas with nested and repeated fields?
a) Simplifies storage costs
b) Reduces query complexity for hierarchical data
c) Prevents data loss during queries
d) Ensures strict data normalization

---

Basic SQL Syntax

6. What keyword is used to retrieve specific columns in BigQuery SQL?
a) FETCH
b) SELECT
c) SHOW
d) GET

7. In SQL, how do you represent a missing or undefined value?
a) 0
b) NULL
c) EMPTY
d) DEFAULT

8. What is the purpose of the LIMIT clause in SQL?
a) To filter rows based on a condition
b) To specify the maximum number of rows to return
c) To create a new table
d) To set memory limits for queries

9. Which operator is used to concatenate two strings in BigQuery SQL?
a) ||
b) +
c) CONCAT
d) &

10. What is the default sort order for the ORDER BY clause?
a) Descending
b) Ascending
c) Random
d) Alphabetical

---

SELECT, WHERE, GROUP BY, HAVING, ORDER BY

11. Which clause is used to filter rows before aggregation in SQL?
a) HAVING
b) GROUP BY
c) WHERE
d) SELECT

12. What does the GROUP BY clause do in a query?
a) Filters rows based on a condition
b) Organizes rows into groups for aggregation
c) Combines tables based on a common key
d) Limits the rows returned

13. The HAVING clause filters:
a) Columns from a table
b) Aggregated groups
c) Rows before grouping
d) Results with NULL values

14. What is the purpose of the SELECT clause?
a) To insert new rows into a table
b) To specify columns for retrieval
c) To group rows into categories
d) To delete data from a table

15. In which order are the SQL clauses executed logically?
a) SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
b) FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
c) WHERE → SELECT → GROUP BY → HAVING → ORDER BY → FROM
d) SELECT → HAVING → WHERE → GROUP BY → ORDER BY

---

Working with Joins and Subqueries

16. What is the default type of JOIN in SQL?
a) INNER JOIN
b) LEFT JOIN
c) FULL JOIN
d) CROSS JOIN

17. What does a LEFT JOIN return?
a) Only rows with matching keys in both tables
b) All rows from the left table and matching rows from the right
c) All rows from both tables
d) Rows from the left table excluding matches

18. How can a subquery be identified?
a) It is always written inside parentheses
b) It must contain a JOIN clause
c) It must include an ORDER BY clause
d) It is executed after the main query

19. When should you use a CROSS JOIN?
a) To find Cartesian products of two tables
b) To combine tables based on a key
c) To filter rows after aggregation
d) To select columns from multiple tables

20. A correlated subquery:
a) Executes independently of the outer query
b) Refers to columns in the outer query
c) Requires the use of JOIN
d) Must always use aggregate functions

---

Functions: Scalar, Aggregate, and String

21. Which is an example of a scalar function in SQL?
a) COUNT()
b) CONCAT()
c) SUM()
d) AVG()

22. What does the COUNT() function return?
a) The total value of a column
b) The average value of a column
c) The number of non-NULL values in a column
d) The highest value in a column

23. How does the STRING function LOWER() operate?
a) Converts a string to uppercase
b) Converts a string to lowercase
c) Extracts a substring
d) Removes spaces from a string

24. What is the purpose of the TRIM() function in SQL?
a) To remove leading and trailing spaces from a string
b) To delete rows from a table
c) To shorten a column’s size
d) To extract characters from a string

25. Which aggregate function calculates the average of numeric data?
a) AVG()
b) SUM()
c) MAX()
d) COUNT()

26. The CONCAT() function in BigQuery is used to:
a) Merge strings into a single value
b) Sort strings alphabetically
c) Split a string into parts
d) Remove duplicate values from a string

27. Which of these is a window function in BigQuery SQL?
a) ROW_NUMBER()
b) CONCAT()
c) TO_DATE()
d) LOWER()

28. What does the COALESCE() function do?
a) Returns the first non-NULL value from its arguments
b) Calculates the difference between two dates
c) Formats numbers as strings
d) Removes duplicate rows

29. The aggregate function SUM() operates on:
a) String values
b) Boolean values
c) Numeric values
d) NULL values only

30. How does the LENGTH() function help in SQL?
a) Counts the number of words in a string
b) Counts the characters in a string
c) Counts NULL values in a table
d) Counts the number of rows in a table

---

Answer Key

QNo	Answer
1	b) BOOLEAN
2	c) 2 GB
3	a) RECORD
4	b) By requiring all rows to conform to a predefined schema
5	b) Reduces query complexity for hierarchical data
6	b) SELECT
7	b) NULL
8	b) To specify the maximum number of rows to return
9	a)
10	b) Ascending
11	c) WHERE
12	b) Organizes rows into groups for aggregation
13	b) Aggregated groups
14	b) To specify columns for retrieval
15	b) FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
16	a) INNER JOIN
17	b) All rows from the left table and matching rows from the right
18	a) It is always written inside parentheses
19	a) To find Cartesian products of two tables
20	b) Refers to columns in the outer query
21	b) CONCAT()
22	c) The number of non-NULL values in a column
23	b) Converts a string to lowercase
24	a) To remove leading and trailing spaces from a string
25	a) AVG()
26	a) Merge strings into a single value
27	a) ROW_NUMBER()
28	a) Returns the first non-NULL value from its arguments
29	c) Numeric values
30	b) Counts the characters in a string