Last active
February 15, 2023 23:37
-
-
Save kendy-karakawa/32ebc0e9ba38f1bc8412652645c746a4 to your computer and use it in GitHub Desktop.
Prática - Exercícios Group By
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Questão 1: | |
Select COUNT("endDate") AS "currentExperiences" FROM experiences; | |
-- Questão 2: | |
Select "userId" AS id, COUNT("userId") as educations from educations group by "userId"; | |
-- Questão 3: | |
Select u.name AS writer, COUNT(t."writerId") as "testimonialCount" from users u JOIN testimonials t ON | |
u.id = t."writerId" WHERE "writerId" = 435 GROUP BY u.id; | |
- Questão 4: | |
Select MAX(j.salary) as maximumSalary, r.name as role FROM jobs j JOIN roles r ON j."roleId" = r.id | |
linkedrivenv2-# where active = true GROUP BY r.id order by maximumSalary asc; | |
- Questão bonus: | |
Select s.name as school, c.name as course, MAX(e."userId") as studentsCount | |
FROM educations e | |
JOIN schools s ON e."schoolId" = s.id | |
JOIN courses c ON e."courseId" = c.id | |
WHERE e.status = 'ongoing' or e.status = 'finished' | |
GROUP by s.id , c.id | |
ORDER BY studentsCount DESC | |
LIMIT 3; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment