Crowdly

Group functions operate on sets of rows to produce multiple results per group

DISTINCT keyword makes a group function consider duplicate values

Which SELECT statement brings both the customers with and those without an order?

Assuming that we have 2 tables - customers and orders:

select * from customers join orders on orders.customer_id = customers.customer_id;

select * from customers left join orders on orders.customer_id = customers.customer_id;

select * from customers left join orders on orders.customer_id = customers.customer_id where orders.order_id is not null;

select * from customers left join orders on orders.customer_id = customers.customer_id where orders.order_id is null;

Assuming that we have 2 tables - customers and orders:

Which of the following queries is correct to count the customers who have put at least one order?

select count(*) from customers join orders on orders.customer_id = customers.customer_id;

select count(distinct customers.customers_id) from customers join orders on orders.customer_id = customers.customers_id;

Assuming that we have 2 tables - customers and orders:

Assume also that we have 2 customers and the first customer has 3 orders and the second customer has 5 orders. How many rows will the following select query bring?

select * from customers join orders on orders.customer_id = customers.customer_id;

PostgreSQL supports inner join, left join, right join, full outer join, cross join, natural join, and a special kind of join called self-join.

True

False

Match the type of join and the appropriate purpose of usage (PostgreSQL).

Is a join that creates an implicit join based on the same column names in the joined tables

allows you to produce a Cartesian Product of rows in two or more tables

combines the results of both the left join and the right join

to query hierarchical data or to compare rows within the same table

If the rows in the joined table do not match, the full outer join sets values for every column of the table that does not have the matching row.

When you want to use a join between columns that are in the same table, you use what type of join?

SELF

INNER

UNION

OUTER

Which JOIN returns the Cartesian product of the two tables?

CROSS JOIN

INNER JOIN

FULL OUTER JOIN

None of the JOINs

We use to combine the result set of two or more SELECT statements