MySQL basics and aggregation
We are very much familiar with “MySQL basic and aggregation”.here we will give and example and find out the problems.
After that, there is a competitive question The Blunder…
Query a count of the number of cities in CITY having a Population larger than 1,00,000.
The CITY table is described as follows:
select count(distinct name) from city where population>100000
Query the average population of all cities in CITY where District is California.
SELECT AVG(Population) from CITY where District='California';
Query the total population of all cities in CITY where District is California.
SELECT SUM(Population) from CITY where District='California';
Query the average population for all cities in CITY, rounded down to the nearest integer.
select floor(avg(population)) from city
Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.
select sum(population) from city where countrycode="JPN"
Query the difference between the maximum and minimum populations in CITY.
select max(population)-min(population) from city
Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table but did not realise her keyboard’s key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeroes removed), and the actual average salary.
Write a query calculating the amount of error (i.e. actual -miscalculated salary average monthly salaries), and round it up to the next integer.
The EMPLOYEES table is described as follows:
Note: Salary is measured in dollars per month and its value is < 10 ^5.
The table below shows the salaries without zeroes as they were entered by Samantha:
Samantha computes an average salary of 98.00. The actual average salary is 2159.00.
The resulting error between the two calculations is 2159.00-98.00=2061.00 which, when rounded to the next 2061
All rights reserved. No part of this Post may be copied, distributed, or transmitted in any form or by any means, without the prior written permission of the website admin, except in the case of brief quotations embodied in critical reviews and certain other noncommercial uses permitted by copyright law. For permission requests, write to the owner, addressed “Attention: Permissions Coordinator,” to the admin @coderinme