Question

Write a query to display the company name and the number of medications manufactured by that company. Give an alias to the number of medications as medication_count. Include only pharmaceutical companies that have manufactured atleast 1 medication. Display the records sorted in ascending order based on company name. 

Answer 1

select user_name, dept_name from(
select name as user_name, profile_id,id from user) as u join profile on u.profile_id=profile.id
join degree on profile.degree_id=degree.id
join (select name as dept_name,id from department)as d on d.id=degree.department_id
join query on query.user_id=u.id
group by user_name
having count(query.content)=6 order by user_name;