Which department store has a marketing department?
select * from departments as t1 join department_stores as t2 on t1.dept_store_id = t2.dept_store_id where t1.department_name = "marketing"
Which department store has both a marketing and a managing department?
select * from departments as t1 join department_stores as t2 on t1.dept_store_id = t2.dept_store_id where t1.department_name = "marketing" intersect select * from departments as t1 join department_stores as t2 on t1.dept_store_id = t2.dept_store_id where t1.department_name = "managing"
Give me their id and name.
select t2.dept_store_id, t2.store_name from departments as t1 join department_stores as t2 on t1.dept_store_id = t2.dept_store_id where t1.department_name = "marketing" intersect select t2.dept_store_id, t2.store_name from departments as t1 join department_stores as t2 on t1.dept_store_id = t2.dept_store_id where t1.department_name = "managing"