Question

Subject DataBase/MySQL

What is wrong with this statement?

SELECT vendor_id, (SELECT vendor_name FROM vendors), avg(invoice_total)
FROM invoices
GROUP BY vendor_id;

Select one:

a. The subquery returns more than 1 row.

b. You need a JOIN ON clause to pull from multiple tables.

c. You cannot have a subquery in the SELECT statement.

d. The result is a Cartesian Product.

e. There is nothing wrong with this statement.

What is wrong with this statement?

SELECT vendor_name, avg(invoice_total) AS 'Invoice Total'
FROM vendors v
JOIN invoices i ON v.vendor_id = i.vendor_id

Select one:

a. You are missing the GROUP BY clause.

b. Nothing, this statement runs correctly.

c. The JOIN ON clause is incorrect.

d. Invoices should also be in the FROM clause.

e. You are using single quotes instead of double quotes.

- TIA

Answer 1

Query1 -

SELECT vendor_id, (SELECT vendor_name FROM vendors), avg(invoice_total)

FROM invoices

GROUP BY vendor_id;

Answer - correct option c

Explanation :

Vendor_id and vendor_name belongs to same table so there is no need of subquery in SELECT clause.

It is required to JOIN two tables on common attribute or use vendor table in FROM clause. Group BY will arrange values according to vendor_id in ascending order by default.

SELECT vendor_id, vendor_name , avg(invoice_total)

FROM invoices, vendors

GROUP BY vendor_id;

Query 2 -

SELECT vendor_name, avg(invoice_total) AS 'Invoice Total'

FROM vendors v

JOIN invoices i ON v.vendor_id = i.vendor_id

Answer - correct option c

Explanation :

This query will fetch vendor name form table vendors as v

average of total invoice renaming the column as Invoice Total

JOIN will join the table invoice and vendors on id. But we have to join invoice with vendors.

SELECT vendor_name, avg(invoice_total) AS 'Invoice Total'

FROM vendors v

JOIN invoices i ON i.vendor_id = v.vendor_id

So this query will give vendor name and invoice total in the output.