Created
February 13, 2023 19:36
-
-
Save kendy-karakawa/7e9c5fdd7fabd20e2a3831de653870cc to your computer and use it in GitHub Desktop.
Exercícios Join SQL
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
-- 1 - Utilizando uma query, obtenha todos os usuários (users) que vivem na cidade (cities) cujo nome seja “Rio de Janeiro”. | |
SELECT users.id, users.name, cities.name as city FROM users JOIN cities ON users."cityId" = cities.id WHERE cities.name = 'Rio de Janeiro'; | |
-- 2 - Utilizando uma query, obtenha todos os depoimentos (testimonials) cadastrados, incluindo o nome do remetente e do destinatário. | |
SELECT testimonials.id, t1.name AS writer, t2.name AS recipient, testimonials.message FROM ((testimonials | |
JOIN users t1 ON testimonials."writerId" = t1.id) | |
JOIN users t2 ON testimonials."recipientId" = t2.id); | |
-- 3 - Utilizando uma query, obtenha todos os cursos (courses) que o usuário com id 30 já finalizou, incluindo o nome da escola. | |
-- O que indica que um usuário terminou um curso é o campo status da tabela educations, que deve estar como "finished". | |
SELECT users.id, users.name AS nome, courses.name AS course, schools.name AS school, educations."endDate" FROM (((educations | |
JOIN users ON educations."userId" = users.id) | |
JOIN courses ON educations."courseId" = courses.id) | |
JOIN schools ON educations."schoolId" = schools.id) WHERE status = 'finished' AND "userId" = 30 ; | |
-- 4 - Utilizando uma query, obtenha as empresas (companies) para as quais o usuário com id 50 trabalha atualmente. | |
--Para filtrar quem trabalha atualmente, utilize o campo endDate da tabela experiences. Se ele estiver null (IS NULL), | |
--significa que a pessoa ainda não encerrou a experiência dela na empresa, ou seja, está trabalhando lá. | |
SELECT experiences."userId" AS id, users.name, roles.name AS role, companies.name AS company, experiences."startDate" | |
FROM experiences | |
JOIN users ON experiences."userId" = users.id | |
JOIN roles ON experiences."roleId" = roles.id | |
JOIN companies ON experiences."companyId" = companies.id | |
WHERE "userId" = 50 AND "endDate" IS NULL; | |
-- 5 - Utilizando uma query, obtenha a lista das diferentes escolas (schools) e cursos (courses) onde estudaram as pessoas | |
--que estão aplicando pra posição de “Software Engineer” na empresa com id 10. Só devem ser consideradas as vagas que estiverem | |
--ativas, ou seja, quando o campo active da tabela jobs estiver true. | |
SELECT schools.id, schools.name AS school, courses.name AS course, companies.name AS company, roles.name AS role | |
FROM jobs | |
JOIN companies ON jobs."companyId" = companies.id | |
JOIN roles ON jobs."roleId" = roles.id | |
JOIN applicants ON jobs.id = applicants."jobId" | |
JOIN educations ON applicants."userId" = educations."userId" | |
JOIN schools ON educations."schoolId" = schools.id | |
JOIN courses ON educations."courseId" = courses.id | |
WHERE active = true AND "companyId" = 10 AND roles.name = 'Software Engineer'; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment