5. More Data Retrieval – Test Yourself

Please find the answers at the end of the test.

Q1. Why would we use the IN operator?

  1. It allows a search to be made between 2 values
  2. To specify multiple OR statements in one statement
  3. To search for multiple AND statements in one statement
  4. To filter data over more than one line

Q2. Which of the following is correct?

  1. SELECT * FROM account WHERE name IN (‘VISA’ OR ‘Cash’ OR ‘Savings’) ;
  2. SELECT * FROM account WHERE name IN (‘VISA’ | ‘Cash’ | Savings’) ;
  3. SELECT * FROM account WHERE name IN (‘VISA’ / ‘Cash’ / Savings’) ;
  4. SELECT * FROM account WHERE name IN (‘VISA’, ‘Cash’, ‘Savings’) ;

Q3. Which operator would we use to search for something where a column’s value could start at 2 and end at 10?

  1. INBETWEEN
  2. IN
  3. <>
  4. BETWEEN

Q4. Which of the following statements are correct?

  1. SELECT * FROM account_transaction WHERE trans_date = ‘2022-01-13’;
  2. SELECT * FROM account_transaction WHERE trans_date = 2022-01-13;
  3. SELECT * FROM account_transaction WHERE trans_date = ‘2022’;
  4. SELECT * FROM account_transaction WHERE trans_date > ‘2022-01-13’;

Q5. What function would you use to find the number of rows in a table?

  1. SUM()
  2. COUNT()
  3. MAX()
  4. MIN()

Q6. What function would you use to find the total of a customer’s bank transactions?

  1. SUM()
  2. COUNT()
  3. MAX()
  4. MIN()

Q7. What function would you use to find the average number of customers that open a bank account each month?

  1. AVG()
  2. AVERAGE()
  3. COUNT() / 12 
  4. AGGREGATE()

Q8. What function would you use to find the top amount a customer has paid into their bank account?

  1. SUM()
  2. MAXIMUM()
  3. MAX()
  4. MIN()

Q9. What keyword is used to create an alias for a column?

  1. AS
  2. IS
  3. OF
  4. AT

Q10. What statement can be used to summarise data into categories?

  1. ORDER BY
  2. SUM()
  3. HAVING
  4. GROUP BY

Q11. What statement is correct?

  1. SELECT SUM(income – expense) FROM account_transaction GROUP BY category;
  2. SELECT COUNT(category) FROM account_transaction GROUP BY category;
  3. SELECT category, SUM(income – expense) FROM account_transaction GROUP BY category;
  4. SELECT category FROM account_transaction GROUP BY SUM(income – expense);

Q12. Which of the following statements are true for a HAVING clause?

  1. It is used to filter records in a database
  2. It is used because the WHERE clause cannot be used with aggregate functions
  3. It is used to group data 
  4. It is used to work with the aggregate functions (e.g. SUM(), COUNT() etc)

Q13. Which of the following statements are correct?

  1. SELECT category, SUM(income – expense) FROM account_transaction GROUP BY category HAVING SUM(income – expense) < 0; 
  2. SELECT category, SUM(income – expense) FROM account_transaction GROUP BY category HAVING expense; 
  3. SELECT category, count(*) as number_categories FROM account_transaction GROUP BY category HAVING number_categories > 2; 
  4. SELECT category, avg(income – expense) as average_amt FROM account_transaction GROUP BY category HAVING average_amt < 0;

ANSWERS:

Q1. Why would we use the IN operator?

  1. It allows a search to be made between 2 values
  2. To specify multiple OR statements in one statement
  3. To search for multiple AND statements in one statement
  4. To filter data over more than one line

Answer:

2

Q2. Which of the following is correct?

  1. SELECT * FROM account WHERE name IN (‘VISA’ OR ‘Cash’ OR ‘Savings’) ;
  2. SELECT * FROM account WHERE name IN (‘VISA’ | ‘Cash’ | Savings’) ;
  3. SELECT * FROM account WHERE name IN (‘VISA’ / ‘Cash’ / Savings’) ;
  4. SELECT * FROM account WHERE name IN (‘VISA’, ‘Cash’, ‘Savings’) ;

Answer:

4

Q3. Which operator would we use to search for something where a column’s value could start at 2 and end at 10?

  1. INBETWEEN
  2. IN
  3. <>
  4. BETWEEN

Answer:

4

Q4. Which of the following statements are correct?

  1. SELECT * FROM account_transaction WHERE trans_date = ‘2022-01-13’;
  2. SELECT * FROM account_transaction WHERE trans_date = 2022-01-13;
  3. SELECT * FROM account_transaction WHERE trans_date = ‘2022’;
  4. SELECT * FROM account_transaction WHERE trans_date > ‘2022-01-13’;

Answer:

1 + 4

Number 2: A DATE value must be set in single quotes

Number 3: A DATE value should be in the format ‘YYYY-MM-DD’

Q5. What function would you use to find the number of rows in a table?

  1. SUM()
  2. COUNT()
  3. MAX()
  4. MIN()

Answer:

2

Q6. What function would you use to find the total of a customer’s bank transactions?

  1. SUM()
  2. COUNT()
  3. MAX()
  4. MIN()

Answer:

1

Q7. What function would you use to find the average number of customers that open a bank account each month?

  1. AVG()
  2. AVERAGE()
  3. COUNT() / 12 
  4. AGGREGATE()

Answer:

1

Q8. What function would you use to find the top amount a customer has paid into their bank account?

  1. SUM()
  2. MAXIMUM()
  3. MAX()
  4. MIN()

Answer:

3

Q9. What keyword is used to create an alias for a column?

  1. AS
  2. IS
  3. OF
  4. AT

Answer:

1

Q10. What statement can be used to summarise data into categories?

  1. ORDER BY
  2. SUM()
  3. HAVING
  4. GROUP BY

Answer:

4

Q11. What statement is correct?

  1. SELECT SUM(income – expense) FROM account_transaction GROUP BY category;
  2. SELECT COUNT(category) FROM account_transaction GROUP BY category;
  3. SELECT category, SUM(income – expense) FROM account_transaction GROUP BY category;
  4. SELECT category FROM account_transaction GROUP BY SUM(income – expense);

Answer:

3

Number 1: To GROUP BY a column, the column name MUST be defined after the SELECT keyword

Number 2: COUNT is used as COUNT(*), not with a column name

Number 4: The GROUP BY SUM does not make sense here

Q12. Which of the following statements are true for a HAVING clause?

  1. It is used to filter records in a database
  2. It is used because the WHERE clause cannot be used with aggregate functions
  3. It is used to group data 
  4. It is used to work with the aggregate functions (e.g. SUM(), COUNT() etc)

Answer:

1 + 2 + 4

Q13. Which of the following statements are correct?

  1. SELECT category, SUM(income – expense) FROM account_transaction GROUP BY category HAVING SUM(income – expense) < 0; 
  2. SELECT category, SUM(income – expense) FROM account_transaction GROUP BY category HAVING expense; 
  3. SELECT category, count(*) as number_categories FROM account_transaction GROUP BY category HAVING number_categories > 2; 
  4. SELECT category, avg(income – expense) as average_amt FROM account_transaction GROUP BY category HAVING average_amt < 0;

Answer:

1 + 3 + 4

Return To Lesson ⇒ 5. More Data Retrieval