SQL Essentials for Data Engineers: BigQuery & MySQL Quiz

Challenge your grasp of fundamental SQL concepts, syntax, and commands tailored for data engineering tasks in both BigQuery and MySQL environments. This quiz covers SQL basics, common operators, filtering, sorting, and essential best practices for efficient database management and querying.

1. Selecting Data

   Which SQL command retrieves all columns and rows from the table named 'employees'?

   1. SELECT * FROM employees;
   2. FETCH ALL employees;
   3. GET employees ALL;
   4. DISPLAY * employees;

   Explanation: The correct syntax to retrieve all columns and rows from a table in SQL is 'SELECT * FROM table_name;'. The asterisk (*) means all columns. The distractors use incorrect SQL keywords: 'FETCH ALL' and 'GET ... ALL' are not standard SQL, and 'DISPLAY * employees;' is not a recognized SQL command.

2. Inserting Data

   What is the correct SQL command to insert values 5 and 'John' into columns 'id' and 'name' of the 'users' table?

   1. INSERT INTO users (id, name) VALUES (5, 'John');
   2. ADD INTO users VALUES (5, 'John');
   3. INSERT users VALUES id=5, name='John';
   4. NEW ROW users (5, 'John');

   Explanation: To insert data in SQL, you use 'INSERT INTO' followed by the table name, column list, and 'VALUES' with the actual data. Other options use incorrect SQL syntax or keywords: 'ADD INTO', 'INSERT users VALUES id=5, name='John';', and 'NEW ROW' are not valid.

3. Updating Records

   How do you update the 'salary' field to 60000 in all records where 'position' is 'analyst' in an 'employees' table?

   1. UPDATE employees SET salary = 60000 WHERE position = 'analyst';
   2. CHANGE employees salary = 60000 FOR position = 'analyst';
   3. UPDATE salary IN employees TO 60000 IF position IS 'analyst';
   4. MODIFY employees SET salary 60000 WHEN position = 'analyst';

   Explanation: The proper SQL statement starts with 'UPDATE', then the table name, and uses 'SET' to specify the new value, followed by 'WHERE' to filter. The distractors contain nonstandard SQL keywords like 'CHANGE', 'MODIFY', or incorrect use of 'IF' and 'WHEN' instead of 'WHERE'.

4. Deleting Rows

   Which SQL statement removes all entries from the 'temp_data' table where the 'flag' column is set to 0?

   1. DELETE FROM temp_data WHERE flag = 0;
   2. REMOVE FROM temp_data IF flag = 0;
   3. DELETE temp_data WHERE flag = 0;
   4. DROP temp_data WHERE flag = 0;

   Explanation: 'DELETE FROM ... WHERE ...' is the correct syntax for removing rows by condition. 'REMOVE FROM' is invalid, 'DELETE temp_data WHERE...' misses the 'FROM' keyword, and 'DROP' is used to delete entire tables, not individual rows.

5. Filtering Data

   How can you select all records from the 'students' table where the 'age' is greater than 18?

   1. SELECT * FROM students WHERE age > 18;
   2. SELECT * FROM students WITH age > 18;
   3. SELECT * FROM students HAVING age > 18;
   4. SELECT >18 FROM students;

   Explanation: Filters in SQL are applied with the 'WHERE' clause in a SELECT statement. 'WITH', 'HAVING', and putting '>18' after SELECT are inappropriate for this context. 'HAVING' is used with group functions, not simple filtering.

6. Sorting Query Results

   What SQL clause will order the results of a query by the 'created_at' column in descending order?

   1. ORDER BY created_at DESC
   2. SORT BY created_at DOWN
   3. ORDER created_at descending
   4. ORDER BY created_at DOWNWARD

   Explanation: 'ORDER BY column DESC' is the standard way to sort in descending order. 'SORT BY', 'ORDER ... descending', and 'DOWNWARD' are not recognized SQL keywords. Only 'DESC' is valid.

7. Selecting Specific Columns

   Which SQL statement retrieves only the 'email' and 'signup_date' columns from the 'accounts' table?

   1. SELECT email, signup_date FROM accounts;
   2. SELECT * FROM accounts (email, signup_date);
   3. PICK email, signup_date FROM accounts;
   4. SELECT accounts.email, accounts.signup_date ONLY;

   Explanation: To retrieve specific columns, list them after SELECT and before FROM. The other options use incorrect commands such as 'PICK', 'ONLY', or wrong bracket usage.

8. Using Aggregate Functions

   Which SQL keyword calculates the total number of rows in the 'orders' table?

   1. SELECT COUNT(*) FROM orders;
   2. SELECT SUM(*) FROM orders;
   3. SELECT TOTAL FROM orders;
   4. SELECT QUANTITY(*) FROM orders;

   Explanation: 'COUNT(*)' returns the number of rows in a table. 'SUM(*)' is not valid and is used to total numbers, not row counts. 'TOTAL' and 'QUANTITY' are not standard SQL aggregate functions.

9. Using Wildcards

   In SQL, which symbol is used as a wildcard to match any sequence of characters in a 'LIKE' condition?

   1. %
   2. #
   3. &
   4. $

   Explanation: The percent sign (%) in 'LIKE' is used to represent zero or more characters. Other symbols such as '#', '&', and '$' have no wildcard role in standard SQL 'LIKE' patterns.

10. Limiting Result Rows

    What is the correct way to limit the output to 10 rows in a MySQL SELECT query?

    1. SELECT * FROM table_name LIMIT 10;
    2. SELECT * FROM table_name TOP 10;
    3. SELECT * FROM table_name TAKE 10;
    4. SELECT * FROM table_name FETCH 10 ROWS;

    Explanation: In MySQL, the 'LIMIT' clause restricts the number of rows returned. 'TOP' is used in some other SQL variants, 'TAKE' and 'FETCH 10 ROWS' are not valid in MySQL syntax.

11. Renaming Columns in Output

    How can you display the 'user_id' column as 'ID' in query results from the 'users' table?

    1. SELECT user_id AS ID FROM users;
    2. SELECT user_id RENAME ID FROM users;
    3. SELECT user_id TO ID FROM users;
    4. SELECT user_id = ID FROM users;

    Explanation: The 'AS' keyword renames a column in SQL query results. The other options use nonstandard SQL keywords like 'RENAME', 'TO', or assignment symbols, which are incorrect.

12. Filtering with Multiple Conditions

    Which SQL clause will select products where 'price' is above 50 and 'category' is 'books'?

    1. SELECT * FROM products WHERE price > 50 AND category = 'books';
    2. SELECT * FROM products WHERE price > 50, category = 'books';
    3. SELECT * FROM products WHERE price > 50 OR category = 'books';
    4. SELECT * FROM products WHERE (price > 50 BETWEEN category = 'books');

    Explanation: Combining conditions in SQL is done with 'AND' for both to be true. Using a comma or 'BETWEEN' is incorrect, and 'OR' would select records where either condition is true, not necessarily both.

13. Basic Date Filtering

    How can you select all events from the 'events' table that occurred on '2024-06-01'?

    1. SELECT * FROM events WHERE event_date = '2024-06-01';
    2. SELECT * FROM events WHEN event_date = '2024-06-01';
    3. SELECT * FROM events ON event_date = '2024-06-01';
    4. SELECT * FROM events DATE event_date = '2024-06-01';

    Explanation: A filter by exact date uses 'WHERE column = value'. The 'WHEN', 'ON', and 'DATE' clauses here do not perform filtering in SQL. Only 'WHERE' is correct.

14. Checking for Null Values

    Which SQL expression finds all rows in the 'orders' table where the 'shipped_date' is unspecified?

    1. SELECT * FROM orders WHERE shipped_date IS NULL;
    2. SELECT * FROM orders WHERE shipped_date == NULL;
    3. SELECT * FROM orders WHERE shipped_date = BLANK;
    4. SELECT * FROM orders WHERE shipped_date = 'NULL';

    Explanation: To check for null values, you must use 'IS NULL' in SQL. The '==' operator, 'BLANK', or the string 'NULL' are not recognized for this purpose.

15. Ordering in Ascending Order

    Which SQL command sorts the 'products' table by 'product_name' in alphabetical order?

    1. SELECT * FROM products ORDER BY product_name ASC;
    2. SELECT * FROM products SORT product_name ASC;
    3. SELECT * FROM products ORDER product_name ALPHABET;
    4. SELECT * FROM products GROUP BY product_name ASC;

    Explanation: 'ORDER BY column ASC' arranges results in ascending (A-Z) order. The distractors use 'SORT' and 'ORDER ... ALPHABET', which are not SQL syntax, or confuse 'GROUP BY' with sorting.