Prática - Exercícios Join

praticajoin.sql

--Q1:

SELECT users.id, users.name, cities.name AS city FROM users JOIN cities ON users."cityId" = cities.id WHERE cities.name = 'Rio de Janeiro';

--Q2:

SELECT testimonials.id, writer.name AS writer, recipient.name AS recipient, testimonials.message FROM testimonials JOIN users AS "writer" ON testimonials."writerId" = writer.id JOIN users AS recipient ON testimonials."recipientId" = recipient.id;

--Q3:

SELECT educations."userId" AS id, users.name, courses.name AS course, schools.name AS school, educations."endDate" FROM educations JOIN courses ON educations."courseId" = courses.id JOIN users ON educations."userId" = users.id JOIN schools ON educations."schoolId" = schools.id WHERE educations."userId" = 30 AND educations.status = 'finished';

--Q4:

SELECT users.id, users.name, roles.name AS role, companies.name AS company, experiences."startDate" FROM experiences JOIN companies ON experiences."companyId" = companies.id JOIN users ON experiences."userId" = users.id JOIN roles ON experiences."roleId" = roles.id WHERE experiences."userId" = 50 AND experiences."endDate" IS NULL;