Question

You can just give me the commends, I have access to the database and can plug in... Thanks

Using the SELECT statement, query the track table to find the average length of a track that has the genre_id not equal to 1.
A.) 283910.0432
B.) 291755291755
C.) 393599.2121
D.) 458090.0789
Using the SELECT statement, query the invoice table to find the average total cost for all orders purchased in the country USA rounded to the nearest cent.
A.) 5.8
B.) 6
C.) 5.7
D.) 5.80
Using the GROUP BY clause and the count aggregate function, filter the customer table grouped based on country.
How many customers are in France?
A.) 4
B.) 3
C.) 5
D.) 6
Using the GROUP BY and HAVING clause, filter the track table by album_id.
How many groups have more than 25 tracks?
A.) 8
B.) 4
C.) 6
D.) 2
Using the WHERE and HAVING clauses, filter the invoice table to include the billing_country as either the USA, France, or Canada. Group the invoices by country, having the number of orders greater than 40. Provide the list of countries and their counts that fit these criteria.
Which of the following queries would provide the correct results?
A.) select billing_country, count(*)
FROM invoice
WHERE billing_country in ('France','USA','Canada')
GROUP BY billing_country
HAVING count(*) > 40
B.) select billing_country, count(*)
FROM invoice
WHERE count(*) > 40
GROUP BY billing_country
HAVING billing_country in ('France','USA','Canada')
C.) select billing_country, count(*)
FROM invoice
GROUP BY billing_country
WHERE billing_country in ('France','USA','Canada')
HAVING count(*) > 40
D.) select billing_country, count(*)
FROM invoice
WHERE billing_country in ('France','USA','Canada')
GROUP BY billing_country
HAVING sum(*) > 40

Answer 1

The correct SQL commands are provided to solve each of the queries related to the average track length, average total cost, customer count by country, track groups by album_id, and filtering the invoice table by certain countries with a minimum number of orders.

Step-by-step explanation:

To find the average length of a track with a genre_id not equal to 1 from the track table, you would use the following SQL command:

SELECT AVG(length) FROM track WHERE genre_id <> 1;

To calculate the average total cost for all orders purchased in the country USA from the invoice table and round it to the nearest cent, your SQL command would be:

SELECT ROUND(AVG(total), 2) FROM invoice WHERE billing_country = 'USA';

To find out how many customers are in France using the customer table with a GROUP BY clause, this SQL command will suffice:

SELECT COUNT(*) FROM customer WHERE country = 'France' GROUP BY country;

To determine how many groups have more than 25 tracks in the track table filtered by album_id using the GROUP BY and HAVING clause, the SQL command is:

SELECT album_id FROM track GROUP BY album_id HAVING COUNT(*) > 25;

And lastly, to filter the invoice table to include the billing_country as either the USA, France, or Canada, group the invoices by country, having the number of orders greater than 40, the correct SQL query is:

A) SELECT billing_country, COUNT(*) FROM invoice WHERE billing_country IN ('France','USA','Canada') GROUP BY billing_country HAVING COUNT(*) > 40;