Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commit86c7a7b

Browse files
committed
More coomlicated sql
1 parent5ad68d3 commit86c7a7b

File tree

3 files changed

+71
-6
lines changed

3 files changed

+71
-6
lines changed

‎hackerrank/Sql/README.md

Lines changed: 71 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -40,6 +40,31 @@ Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE fo
4040
SELECT nameFROM cityWHERE countrycode='JPN';
4141
```
4242

43+
###Average Population
44+
Query the average population for all cities in CITY, rounded down to the nearest integer.
45+
```sql
46+
-- mysql
47+
SELECT FLOOR(AVG(population))AS average_population
48+
FROM CITY;
49+
```
50+
51+
###Japan Population
52+
Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.
53+
```sql
54+
-- mysql
55+
SELECTSUM(population)
56+
FROM CITY
57+
WHERE countrycode='JPN';
58+
```
59+
60+
###Population Density Difference
61+
Query the difference between the maximum and minimum populations in CITY.
62+
```sql
63+
-- mysql
64+
SELECT (MAX(population)-MIN(population))
65+
FROM CITY;
66+
```
67+
4368

4469
##STATION table used for all below
4570
![alt text](station.png)
@@ -57,7 +82,7 @@ The sum of all values in LAT_N rounded to a scale of decimal places.
5782
The sum of all values in LONG_W rounded to a scale of decimal places.
5883
```sql
5984
/* mysql*/
60-
SELECTround(sum(LAT_N),2),round(sum(LONG_W),2)FROM station;
85+
SELECTROUND(SUM(LAT_N),2),ROUND(SUM(LONG_W),2)FROM station;
6186
```
6287

6388
###Weather Observation Station 3
@@ -283,26 +308,66 @@ FROM Counted;
283308

284309

285310
###Higher Than 75 Marks
286-
Query the Name of any student in STUDENTS who scored higher than Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.
311+
Query the Name of any student in STUDENTS who scored higher than75 Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.
287312
```sql
288313
-- mysql
289-
314+
SELECT Name
315+
FROM STUDENTS
316+
WHERE Marks>75
317+
ORDER BY RIGHT(Name,3), IDASC;
290318
```
291319

292-
###
293320

321+
##EMPLOYEE table used for all below
322+
![alt text](employee.png)
323+
324+
###Employee Names
325+
Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.
294326
```sql
295327
-- mysql
296-
328+
SELECT name
329+
FROM EMPLOYEE
330+
ORDER BY name;
297331
```
298332

299-
###
333+
###Employee Salaries
334+
Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than $2000 per month who have been employees for less than 10 months. Sort your result by ascending employee_id.
335+
```sql
336+
-- mysql
337+
SELECT name
338+
FROM EMPLOYEE
339+
WHERE salary>2000AND months<10
340+
ORDER BY employee_idASC;
341+
```
300342

343+
###Top Earners
344+
We define an employee's total earnings to be their monthly`salary x months` worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as`2` space-separated integers.
301345
```sql
302346
-- mysql
347+
SELECT
348+
MAX(salary* months)AS max_total_earnings,
349+
COUNT(*)AS num_employees
350+
FROM EMPLOYEE
351+
WHERE salary* months= (
352+
SELECTMAX(salary* months)FROM EMPLOYEE
353+
);
354+
```
355+
356+
357+
358+
##EMPLOYEES table used for all below
359+
![alt text](employees.png)
303360

361+
###The Blunder
362+
Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's 0 key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.
363+
Write a query calculating the amount of error (i.e.:`actual - miscalculated` average monthly salaries), and round it up to the next integer.
364+
```sql
365+
-- mysql
366+
SELECT CEIL(AVG(Salary)-AVG(CAST(REPLACE(Salary,'0','')AS UNSIGNED)))AS error
367+
FROM EMPLOYEES;
304368
```
305369

370+
306371
###
307372

308373
```sql

‎hackerrank/Sql/employee.png

7.85 KB
Loading

‎hackerrank/Sql/employees.png

9.69 KB
Loading

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp