MySQL basics and aggregation coderinme

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.

Input Format

The CITY table is described as follows:
MySQL basics and aggregation

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

The Blunder

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.

Input Format

The EMPLOYEES table is described as follows:

Note: Salary is measured in dollars per month and its value is < 10 ^5.

Sample Input

Sample Output

2061
Explanation

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

For more basic Mysql Query
For more basic Mysql Query

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

A web developer(Front end and Back end), and DBA at csdamu.com. Currently working as Salesforce Developer @ Tech Matrix IT Consulting Private Limited. Check me @about.me/s.saifi

Leave a reply:

Your email address will not be published.