Test Your PostgreSQL GROUP BY Command Knowledge with This Quiz

Progress

True / False

In PostgreSQL, the GROUP BY clause is used to group rows that have the same values in specified columns.

True False

The GROUP BY clause groups rows that have the same values in the specified columns into summary rows.

True / False

In PostgreSQL, you must use an aggregate function when using the GROUP BY clause.

True False

The GROUP BY clause is typically used with aggregate functions (such as COUNT, SUM, AVG, etc.) to perform calculations on each group.

True / False

In PostgreSQL, the GROUP BY clause can be used without any aggregate functions.

True False

While the GROUP BY clause can technically be used without aggregate functions, it's usually combined with them to perform calculations on grouped data.

True / False

In PostgreSQL, you can group by multiple columns using the GROUP BY clause.

True False

You can specify multiple columns in the GROUP BY clause to group the data by multiple criteria.

True / False

In PostgreSQL, the GROUP BY clause must include all non-aggregated columns in the SELECT statement.

True False

All non-aggregated columns in the SELECT statement must be included in the GROUP BY clause.

True / False

In PostgreSQL, you can use column aliases in the GROUP BY clause.

True False

Column aliases defined in the SELECT clause cannot be used in the GROUP BY clause. You must use the actual column names or expressions.

True / False

In PostgreSQL, the GROUP BY clause can be used with the HAVING clause to filter groups based on aggregate values.

True False

The HAVING clause is used to filter groups based on aggregate values, similar to how the WHERE clause filters rows.

True / False

In PostgreSQL, the GROUP BY clause can be used in combination with window functions.

True False

The GROUP BY clause can be used alongside window functions, although window functions are typically applied to the entire result set or partitions thereof.

True / False

In PostgreSQL, you can group by expressions (e.g., GROUP BY date_trunc('month', timestamp)).

True False

You can group by expressions in the GROUP BY clause to create groups based on calculated values.

Q10

True / False

In PostgreSQL, using the GROUP BY clause improves performance by reducing the number of rows processed.

True False

The GROUP BY clause does not necessarily improve performance; it groups rows based on specified columns and performs calculations. The performance depends on the dataset size and query complexity.

Q11

Single Choice

Which of the following SQL statements correctly uses the GROUP BY clause in PostgreSQL?

SELECT name, COUNT(*) FROM employees; SELECT name, department FROM employees GROUP BY name; SELECT department, COUNT(*) FROM employees GROUP BY department;SELECT name, COUNT(*) FROM employees GROUP BY salary;

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. The correct syntax is to include all non-aggregated columns in the GROUP BY clause.

Q12

Single Choice

In PostgreSQL, what does the GROUP BY clause do in a SQL query?

It filters rows based on a condition. It sorts rows in ascending order. It groups rows that have the same values in specified columns.It selects rows based on an index.

The GROUP BY clause is used to group rows that share the same values in specified columns and often accompanies aggregate functions like COUNT(), SUM(), AVG(), etc.

Q13

Single Choice

Which of the following SQL queries will group the results by the category column and count the number of products in each category in PostgreSQL?

SELECT category, COUNT(product_id) FROM products; SELECT product_id, COUNT(category) FROM products GROUP BY product_id; SELECT category, COUNT(product_id) FROM products GROUP BY category;SELECT COUNT(product_id) FROM products;

The query correctly groups the results by the category column and counts the number of product_id in each group.

Q14

Single Choice

Given the following table sales in PostgreSQL:

SQL Code

product_id	sale_date	amount
1	2024-01-01	 100
2	2024-01-01	 150
1	2024-01-02 200
3	2024-01-02 	250
Which query will return the total sales amount for each sale_date?

SELECT sale_date, SUM(amount) FROM sales GROUP BY sale_date; SELECT sale_date, amount FROM sales GROUP BY sale_date; SELECT sale_date, SUM(amount) FROM sales GROUP BY product_id;SELECT sale_date, SUM(amount) FROM sales;

This query correctly groups the sales by sale_date and calculates the total amount for each group.

Q15

Single Choice

What will be the output of the following PostgreSQL query if the table orders has multiple rows with the same customer_id?

SQL Code

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;

t will return a list of customer IDs and the total number of orders placed by each customer. It will return a list of customer IDs and their total order amounts. It will return the total number of unique customers.It will return an error because COUNT(*) cannot be used with GROUP BY

The query groups rows by customer_id and counts the number of orders (COUNT(*)) for each customer.

Q16

Single Choice

Consider a students table with columns student_id, name, class, and marks. Which of the following queries will correctly calculate the average marks for each class in PostgreSQL?

SELECT class, AVG(marks) FROM students GROUP BY student_id; SELECT class, AVG(marks) FROM students GROUP BY class; SELECT student_id, AVG(marks) FROM students GROUP BY class;SELECT class, marks FROM students GROUP BY marks;

The query correctly groups the data by class and calculates the average marks for each class.

Q17

Single Choice

In PostgreSQL, how can you filter the results of a GROUP BY query to only include groups with a total sum greater than 1000?

SQL Code

SELECT category, SUM(amount)
FROM transactions
GROUP BY category
HAVING SUM(amount) > 1000;

Add a WHERE clause after GROUP BY. Use the HAVING clause as shown in the query. Use the ORDER BY clause after GROUP BY.It’s not possible to filter grouped results.

The HAVING clause is used to filter groups based on aggregate functions, such as SUM(amount) > 1000.

Q18

Single Choice

Given the following PostgreSQL query, which groups the data by department and filters out departments with less than 5 employees, what will be the result?

SQL Code

SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department
HAVING COUNT(employee_id) >= 5;

Departments with exactly 5 employees will be excluded. Departments with less than 5 employees will be included. Only departments with 5 or more employees will be included.All departments will be included regardless of the employee count.

The HAVING clause filters out groups (departments) where the COUNT(employee_id) is less than 5.

Q19

Single Choice

In PostgreSQL, what will the following query return?

SQL Code

SELECT country, AVG(salary)
FROM employees
GROUP BY country
ORDER BY AVG(salary) DESC;

The average salary for each country, sorted in ascending order. The average salary for each country, sorted in descending order. The total salary for each country, sorted in descending order.The list of countries without any aggregation.

The query groups the data by country, calculates the average salary, and sorts the results in descending order based on the average salary.

Q20

Single Choice

How would you modify the following PostgreSQL query to include only those groups where the average age is greater than 30?

SQL Code

SELECT department, AVG(age)
FROM employees
GROUP BY department;

Add WHERE AVG(age) > 30. Add HAVING AVG(age) > 30. Add ORDER BY AVG(age) > 30.Add FILTER(WHERE AVG(age) > 30).

The HAVING clause is used to filter groups after the GROUP BY clause, allowing only those groups with an average age greater than 30 to be included in the results.

Q21

Multiple Choice

Which SQL scripts correctly group data by a specific column in PostgreSQL?

SELECT department_id, SUM(salary) FROM employees GROUP BY department_id; SELECT department_id, SUM(salary) FROM employees GROUP department_id; SELECT department_id, salary FROM employees GROUP BY department_id;SELECT department_id, SUM(salary) FROM employees GROUP BY salary;

The GROUP BY clause is used to group rows that have the same values in specified columns. The correct scripts will demonstrate how to group by 'department' and calculate the total salary for each group.

Q22

Multiple Choice

Identify the SQL scripts that correctly use GROUP BY with HAVING in PostgreSQL.

SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING SUM(salary) > 100000; SELECT department_id, SUM(salary) FROM employees HAVING SUM(salary) > 100000 GROUP BY department_id; SELECT department_id, SUM(salary) FROM employees GROUP BY department_id WHERE SUM(salary) > 100000;SELECT department_id, SUM(salary) FROM employees WHERE SUM(salary) > 100000 GROUP BY department_id;

The HAVING clause is used to filter groups based on a condition. The correct scripts will demonstrate how to use GROUP BY with HAVING to filter departments where the total salary exceeds 100,000.

Q23

Multiple Choice

Determine the SQL scripts that correctly group data by multiple columns in PostgreSQL.

SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id; SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id; SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY job_id;SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id HAVING AVG(salary) > 50000;

Grouping by multiple columns is a powerful way to aggregate data at a more granular level. The correct scripts will demonstrate how to use GROUP BY with multiple columns to calculate the average salary for each department and job.

Q24

Multiple Choice

Select the SQL scripts that correctly group data and include non-aggregated columns in PostgreSQL.

SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id; SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY department_id; SELECT department_id, SUM(salary) FROM employees GROUP BY department_id, job_id;SELECT job_id, department_id, SUM(salary) FROM employees GROUP BY department_id, job_id;

When using GROUP BY, every column in the SELECT statement that is not part of an aggregate function must be included in the GROUP BY clause. The correct scripts will demonstrate how to include non-aggregated columns correctly.

Q25

Multiple Choice

Identify the SQL scripts that correctly use GROUP BY with COUNT in PostgreSQL.

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id; SELECT department_id, COUNT(*) FROM employees GROUP department_id; SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;SELECT COUNT(*), department_id FROM employees GROUP BY department_id;

The COUNT function is often used with GROUP BY to count the number of rows in each group. The correct scripts will demonstrate how to use COUNT with GROUP BY to get the number of employees in each department.

Q26

Multiple Choice

Determine the SQL scripts that correctly group and filter data using WHERE and GROUP BY in PostgreSQL.

SELECT department_id, SUM(salary) FROM employees WHERE salary > 50000 GROUP BY department_id; SELECT department_id, SUM(salary) FROM employees GROUP BY department_id WHERE salary > 50000; SELECT department_id, SUM(salary) FROM employees WHERE salary > 50000 GROUP BY department_id HAVING SUM(salary) > 100000;SELECT department_id, SUM(salary) WHERE salary > 50000 FROM employees GROUP BY department_id;

The WHERE clause filters rows before they are grouped. The correct scripts will demonstrate how to filter data before using GROUP BY to calculate aggregated values.

Q27

Multiple Choice

Select the SQL scripts that correctly use GROUP BY with DISTINCT in PostgreSQL.

SELECT department_id, COUNT(DISTINCT job_id) FROM employees GROUP BY department_id; SELECT department_id, COUNT(DISTINCT job_id) FROM employees GROUP department_id; SELECT DISTINCT department_id, COUNT(job_id) FROM employees GROUP BY department_id;SELECT department_id, COUNT(DISTINCT job_id) FROM employees WHERE job_id IS NOT NULL GROUP BY department_id;

The DISTINCT keyword can be used to eliminate duplicate values before aggregation. The correct scripts will demonstrate how to use DISTINCT with GROUP BY to count unique job roles in each department.

Q28

Multiple Choice

Identify the SQL scripts that correctly use GROUP BY with multiple aggregate functions in PostgreSQL.

SELECT department_id, SUM(salary), AVG(salary) FROM employees GROUP BY department_id; SELECT department_id, SUM(salary) AS total_salary, AVG(salary) AS avg_salary FROM employees GROUP BY department_id; SELECT SUM(salary), AVG(salary) FROM employees GROUP BY department_id;SELECT department_id, SUM(salary), AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 50000;

Multiple aggregate functions can be used in the same query to provide different insights. The correct scripts will demonstrate how to use GROUP BY with SUM and AVG functions to calculate the total and average salary for each department.

Q29

Multiple Choice

Determine the SQL scripts that correctly group data with subqueries in PostgreSQL.

SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING SUM(salary) = (SELECT MAX(SUM(salary)) FROM employees GROUP BY department_id); SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING SUM(salary) = (SELECT MAX(salary) FROM employees GROUP BY department_id); SELECT department_id, SUM(salary) FROM employees WHERE salary = (SELECT MAX(SUM(salary)) FROM employees GROUP BY department_id) GROUP BY department_id;SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING MAX(salary) = (SELECT MAX(SUM(salary)) FROM employees GROUP BY department_id);

Subqueries can be used within GROUP BY queries to provide more complex filtering or calculation logic. The correct scripts will demonstrate how to use a subquery to find the department with the maximum total salary.

Q30

Multiple Choice

Select the SQL scripts that correctly use GROUP BY with complex expressions in PostgreSQL.

SELECT department_id, SUM(salary) * COUNT(*) FROM employees GROUP BY department_id; SELECT department_id, SUM(salary) * COUNT(*) AS total_compensation FROM employees GROUP BY department_id; SELECT department_id, (SUM(salary) * COUNT(*)) FROM employees GROUP BY department_id;SELECT department_id, SUM(salary * COUNT(*)) FROM employees GROUP BY department_id;

Complex expressions can be used in conjunction with GROUP BY to provide more detailed calculations. The correct scripts will demonstrate how to use expressions within the SELECT clause while grouping by 'department_id'.

PostgreSQL Database Quiz Questions