PostgreSQL Questions: 1) 2) 3) 4) 5) 6)

[answerhappygod]

PostgreSQL Questions:
1)

Postgresql Questions 1 2 3 4 5 6 1 (40.51 KiB) Viewed 9 times

2)

Postgresql Questions 1 2 3 4 5 6 2 (17.47 KiB) Viewed 9 times

3)

Postgresql Questions 1 2 3 4 5 6 3 (51.78 KiB) Viewed 9 times

4)

Postgresql Questions 1 2 3 4 5 6 4 (59.77 KiB) Viewed 9 times

5)

Postgresql Questions 1 2 3 4 5 6 5 (28.19 KiB) Viewed 9 times

6)

Postgresql Questions 1 2 3 4 5 6 6 (61.14 KiB) Viewed 9 times

Write a query using the salaries table in the employees database to get the top 5 employee numbers who have the highest average salaries but only those who have a maximum salary of less than 100000. Use a combination of ORDER BY and LIMIT to get the top 5. Notice that you have to order such that the largest average salary should be in the first row. And don't forget to filter based on the maximum salary per employee number (we only want the top 5 of those whose max salary is less than 100000). The output should look like: emp_no | avg 226982 | 484862 99684.500000000000 99226.000000000000 37331 | 99139.000000000000 42596999091.000000000000 407277 | 98984.500000000000 (5 rows)
QUESTION 2 Write a query using the employees and salaries table in the employees database to get the average salaries for each of the genders. The result should look like this: gender F M (2 rows) average_salary | 63769.603163679128 | 63838.176925678111
Use the salaries table and the employees table in the employees database to calculate the average salary for each age rounded down to the nearest year (use a combination of AGE() and EXTRACT()). Order the result by the average salary, the highest average salary should be on the first row, and the lowest average salary should be on the last row. The result should look like this: employee_age | average_salary 69 64010.325792393752 63 | 64000.627899849976 66 63957.669472181299 60 63867.250741948485 58 63863.582784104071 62 | 63821.618702741074 65 63820.257508890895 59 63761.463006135253 70 | 63755.499160040318 57 63731.417198963390 (14 rows) 64 63725.067138576053 63716.615707934180 61 68 | 63656.352762122076 67 63603.364733442504
20 points Save Answer Write a query using the employees table and the salaries table in the employees database to calculate the average salaries of the employees based on how many years it has been since they were hired. Order from largest to smallest average salary. Output should look like: (We can observe here that the more years it passes by since you were hired, the higher your salary will be - which makes sense since people get promotions after working for a while) QUESTION 4 years since_hired | average_salary (16 rows) 37 66939.094783995656 36 66882.656322330498 35 66011.820833283491 34 | 64919.719011404780 33 64067.353459099755 32 | 63532.405004777522 31 | 62586.693597406767 30 | 61674.924643573568 29 60794.070042117423 28 | 60135.709676349648 27 59208.200240911082 26 58290.914056297095 25 | 57457.877305944212 24 56591.832914808291 23 56225.414889664908 22 55554.808360128617
QUESTION 5 Write a query using the employees, departments, and dept_emp table to get the departments that have more than 10000 female employees along with their female employee count. Output should look like: (You can see the three departments that have more than 10000 female employees and the count of female employees that they have) dept_name Development Production. Sales (3 rows) female_count 34258 29549 20854
Write a query using the salaries, employees, departments, and dept_emp tables to calculate the average salary for males and females per department, then order the result by the average salary from largest to smallest. The result should look like this: (we can see over here that males in the sales department have the highest average salary) gender M F M F F M F M M M F F F M F dept_name Sales | Sales | Marketing | Marketing | Finance | Finance | Research. | Production | Research | Development | Production | Development | Customer Service | Customer Service | Quality Management | Quality Management Human Resources Human Resources M F M (18 rows) average_salary 80736.678258884663 80563.857694595985 | 72087.820886376386 71643.419635713905 70537.766514414077 70456.555862349000 59678.302115743622 59676.233881299572 59656.415848334927 | 59507.640692195947 59500.289619182148 59435.643567357265 58901.569703597893 | 58682.652294905846 | 57305.189324567582 | 57214.731486310876 | 55744.959891615055 | 55462.130228380806