MCQs on Basic Functions and Aggregates | PostgreSQL Database

PostgreSQL is a powerful open-source database known for its rich set of functions and aggregates. This guide features 30 essential MCQs to enhance understanding of PostgreSQL’s core functionalities and data manipulation techniques.

Basic Functions and Aggregates | PostgreSQL

Using Built-in Functions (COUNT, SUM, AVG, etc.)

1. Which function is used to count the total number of rows in a PostgreSQL table?
   • a) COUNT()
   • b) TOTAL()
   • c) SUM()
   • d) NUM()
2. What does the SUM() function do in PostgreSQL?
   • a) Counts the number of rows
   • b) Adds values in a numeric column
   • c) Finds the average of a column
   • d) Calculates the difference between rows
3. Which aggregate function calculates the average value of a numeric column?
   • a) AVG()
   • b) MEAN()
   • c) AVERAGE()
   • d) TOTAL()
4. How can NULL values be excluded when using aggregate functions?
   • a) By using DISTINCT
   • b) By applying WHERE NOT NULL
   • c) By default, aggregate functions exclude NULLs
   • d) Using COUNTNULLS()
5. Which clause is mandatory when using aggregate functions in conjunction with GROUP BY?
   • a) HAVING
   • b) SELECT
   • c) ORDER BY
   • d) WHERE
6. Which of the following correctly uses the COUNT function with DISTINCT?
   • a) COUNT(*)
   • b) COUNT(DISTINCT column_name)
   • c) DISTINCT COUNT(column_name)
   • d) COUNT(column_name DISTINCT)
7. In PostgreSQL, which function would you use to find the largest value in a column?
   • a) MAX()
   • b) BIGGEST()
   • c) GREATEST()
   • d) TOP()

String Functions and Manipulations

8. Which function is used to convert a string to uppercase in PostgreSQL?
   • a) TO_UPPER()
   • b) UPPER()
   • c) UCASE()
   • d) CAPITALIZE()
9. How would you find the length of a string in PostgreSQL?
   • a) LENGTH()
   • b) CHARCOUNT()
   • c) STRLEN()
   • d) SIZE()
10. What is the function of CONCAT() in PostgreSQL?
    • a) Joins two or more strings
    • b) Finds substrings
    • c) Counts characters in a string
    • d) Splits a string into parts
11. Which function extracts a substring in PostgreSQL?
    • a) SUBSTR()
    • b) EXTRACT()
    • c) SLICE()
    • d) MID()
12. What is the purpose of the POSITION() function?
    • a) Finds the position of a substring within a string
    • b) Rearranges the string characters
    • c) Extracts specific characters
    • d) Converts a string to uppercase
13. Which function replaces a substring within a string?
    • a) CHANGE()
    • b) SUBSTITUTION()
    • c) REPLACE()
    • d) UPDATE()
14. How can you remove whitespace from the beginning and end of a string?
    • a) TRIM()
    • b) STRIP()
    • c) CLEAN()
    • d) WHITESPACE()

Date and Time Functions

15. Which function is used to retrieve the current date in PostgreSQL?
    • a) GETDATE()
    • b) CURRENT_DATE
    • c) TODAY()
    • d) NOW()
16. What does the AGE() function do in PostgreSQL?
    • a) Calculates the difference between two timestamps
    • b) Returns the current age
    • c) Converts dates into integers
    • d) Formats a timestamp
17. How do you extract the year from a date in PostgreSQL?
    • a) YEAR(date_column)
    • b) EXTRACT(YEAR FROM date_column)
    • c) GET_YEAR(date_column)
    • d) TO_YEAR(date_column)
18. Which function combines a date and time into a single value?
    • a) CONCAT()
    • b) TO_TIMESTAMP()
    • c) MERGE_DATE_TIME()
    • d) TIMESTAMP_COMBINE()
19. What is the result of the INTERVAL keyword in PostgreSQL?
    • a) Defines a time period
    • b) Returns the difference between two dates
    • c) Converts timestamps
    • d) Updates a date format
20. Which function would you use to add a time interval to a date?
    • a) DATEADD()
    • b) ADDTIME()
    • c) NOW() + INTERVAL
    • d) AGE()
21. How do you extract the day of the week from a date?
    • a) DAYNAME()
    • b) DAYOFWEEK()
    • c) EXTRACT(DOW FROM date_column)
    • d) WEEKDAY()

Grouping and Aggregating Data

22. Which clause groups rows that have the same values into summary rows?
    • a) ORDER BY
    • b) GROUP BY
    • c) HAVING
    • d) PARTITION BY
23. How does the HAVING clause differ from the WHERE clause in PostgreSQL?
    • a) HAVING filters grouped data
    • b) HAVING is applied before grouping
    • c) HAVING ignores NULL values
    • d) Both are identical in purpose
24. How do you calculate the total of a numeric column in each group?
    • a) SUM() in combination with GROUP BY
    • b) COUNT() with WHERE
    • c) AVG() in combination with ORDER BY
    • d) MAX() with DISTINCT
25. Which aggregate function counts only unique values in a column?
    • a) UNIQUE_COUNT()
    • b) COUNT(*)
    • c) COUNT(DISTINCT column_name)
    • d) DISTINCT_COUNT(column_name)
26. What is the purpose of the ROLLUP modifier in a GROUP BY clause?
    • a) Generates subtotals and grand totals
    • b) Groups columns by default order
    • c) Aggregates values across rows without grouping
    • d) Removes NULL values in grouped data
27. How can you filter aggregated data after using GROUP BY?
    • a) Use HAVING
    • b) Use WHERE
    • c) Use FILTER
    • d) Use PARTITION
28. Which of the following clauses ensures rows are grouped but sorted by a specified column?
    • a) GROUP BY and ORDER BY
    • b) SORT BY
    • c) GROUP BY with DISTINCT
    • d) FILTER BY
29. What is the default sorting order of GROUP BY in PostgreSQL?
    • a) Ascending
    • b) Descending
    • c) Random
    • d) No default order
30. When is GROUPING SETS used in a GROUP BY clause?
    • a) To define multiple grouping levels
    • b) To ignore duplicates
    • c) To eliminate NULL values
    • d) To order grouped data

Answer Key