All names are to be in the “Last Name, First name” format. These columns should be labeled by the appropriate name (Customer, Supplier).

Display a list of customers and the total amount of their purchases, sorted by amount.

Display the name of the Item, the total number of each item sold and the total sales (purchase price times quantity) for each item. Column headings are Name, Total sold, and Total sales.

Display the names of the Widgets that have sold more than 1 item . Your answer should display a name only once. Use a HAVING clause.

Display the Customer name, the order number, the product name, the quantity of the product, and the price of the product in order number order where the total cost of the order is greater than $500.00.

Dislpay a list of customers and salesperson who sold them something and the date of the sale.

Solution 1:

QUERY 1
Display a list of customers and the total amount of their purchases, sorted by amount.

Sol)
SELECT CONCAT(CUSTOMER.Lname,' ', CUSTOMER.Fname) AS
CUSTOMER, Sum(`Order`.Price+`Order`.Tax) AS `Total Amount Of
Their Purchases`
FROM CUSTOMER INNER JOIN `ORDER` ON CUSTOMER.Customerid
= `ORDER`.Customerid
GROUP BY CUSTOMER.Customerid
ORDER BY Sum(`Order`.Price+`Order`.Tax) DESC;

QUERY 2
Display the name of the Item, the total number of each item sold and the total sales (purchase price times quantity) for each item. Column headings are Name, Total sold, and Total sales.

QUERY 4
Display the Customer name, the order number, the product name, the quantity of the product, and the price of the product in order number order where the total cost of the order is greater than $500.00.

Sol)
SELECT CONCAT(CUSTOMER.Lname,' ', CUSTOMER.Fname) AS
Customer, `ORDER`.Ordernr, PRODUCT.name, ITEM.Quantity,
PRODUCT.cost
FROM PRODUCT INNER JOIN ((CUSTOMER INNER JOIN `ORDER`
ON CUSTOMER.Customerid = `ORDER`.Customerid) INNER JOIN
ITEM ON `ORDER`.Ordernr = ITEM.Ordernr) ON
PRODUCT.productid = ITEM.Type
WHERE (((`ORDER`.Price+`ORDER`.Tax)>500))
ORDER BY `ORDER`.Ordernr;QUERY 5
Dislpay a list of customers and salesperson who sold them something and the date of the sale.

1. I want a list of countries and languages spoken there that are used by more than 50% of the population.

2. I want a list of countries and the number of languages spoken there for all countries with 10 or more languages.

3. I want a list of countries and official languages and precentages where the official language is spoken by less then 50% of the population.

4. I want a list of countries and cities where the city population is greater than 10% of the country population.

5. I want a list of all African countries that gained their independence since 1964

6. I want the name of the country, the name of the district, and the number of cities in each district for each district that has 10 or more cities in it.

7. I want the average life expectancy for each continent, from highest to lowest.

8. I want the name of the country and the life expectancy for each country that has a life expectancy less than the average life expectancy.

9. I want a list of languages spoken in the United States and the number of people in the United States who speak that language.

10. I want the name of the country and its population for every country that has more than the average population of the world sorted by population.

Solution 2:Q1) I want a list of countries and languages spoken there that are used by more than 50% of the population.
Sol:SELECT COUNTRY.Name, `COUNTRY LANGUAGE`.LanguageFROM COUNTRY INNER JOIN `COUNTRY LANGUAGE` ON COUNTRY.Code = `COUNTRY LANGUAGE`.CountrycodeWHERE (((`COUNTRY LANGUAGE`.Percentage)>50));

Q2) I want a list of countries and the number of languages spoken there for all countries with 10 or more languages.
Sol:SELECT COUNTRY.Name, Count(`COUNTRY LANGUAGE`.Language) AS CountOfLanguageFROM COUNTRY INNER JOIN `COUNTRY LANGUAGE` ON COUNTRY.Code = `COUNTRY LANGUAGE`.CountrycodeGROUP BY COUNTRY.NameHAVING (((Count(`COUNTRY LANGUAGE`.Language))>=10));

Q3) I want a list of countries and official languages and precentages where the official language is spoken by less then 50% of the population.
Sol:SELECT COUNTRY.Name, `COUNTRY LANGUAGE`.Language, `COUNTRY LANGUAGE`.PercentageFROM COUNTRY INNER JOIN `COUNTRY LANGUAGE` ON COUNTRY.Code = `COUNTRY LANGUAGE`.CountrycodeWHERE (((`COUNTRY LANGUAGE`.Percentage)<50) AND ((`COUNTRY LANGUAGE`.Isofficial)=True));

Q4) I want a list of countries and cities where the city population is greater than 10% of the country population.
Sol:SELECT COUNTRY.Name, CITY.NameFROM COUNTRY INNER JOIN CITY ON COUNTRY.Code = CITY.`Country code`WHERE (((CITY.Population)>0.1*COUNTRY.Population));

Q5) I want a list of all African countries that gained their independence since 1964.
Sol:SELECT COUNTRY.NameFROM COUNTRYWHERE (((COUNTRY.Continent)="Africa") AND ((COUNTRY.Indepyear)>"1964"));

Q6) I want the name of the country, the name of the district, and the number of cities in each district for each district that has 10 or more cities in it.
Sol:SELECT COUNTRY.Name, CITY.District, Count(CITY.ID) AS CityCountFROM COUNTRY INNER JOIN CITY ON COUNTRY.Code = CITY.`Country code`GROUP BY COUNTRY.Name, CITY.DistrictHAVING (((Count(CITY.ID))>=10));

Q7) I want the average life expectancy for each continent, from highest to lowest.
Sol:SELECT COUNTRY.Continent, Avg(COUNTRY.Lifeexpectancy) AS AvgOfLifeexpectancyFROM COUNTRYGROUP BY COUNTRY.ContinentORDER BY Avg(COUNTRY.Lifeexpectancy) DESC;

Q8) I want the name of the country and the life expectancy for each country that has a life expectancy less than the average life expectancy.
Sol:SELECT COUNTRY.Name, COUNTRY.LifeexpectancyFROM COUNTRY WHERE COUNTRY.Lifeexpectancy<(SELECT AVG(COUNTRY.Lifeexpectancy) from COUNTRY);

Q9) I want a list of languages spoken in the United States and the number of people in the United States who speak that language.
Sol:SELECT `COUNTRY LANGUAGE`.Language, 0.01*Percentage*Population AS NumberOfPeopleFROM COUNTRY INNER JOIN `COUNTRY LANGUAGE` ON COUNTRY.Code = `COUNTRY LANGUAGE`.CountrycodeWHERE (((COUNTRY.Name)="United States"));

Q10) I want the name of the country and its population for every country that has more than the average population of the world sorted by population.
Sol:SELECT COUNTRY.Name, COUNTRY.PopulationFROM COUNTRYWHERE (((COUNTRY.Population)>(SELECT AVG(COUNTRY.Population) FROM COUNTRY)))ORDER BY COUNTRY.Population DESC;