Question

The common requirements of all questions are the same. Read each question carefully and submit an appropriate query to fulfill for each question. Also, students need to provide screenshot(s) of the query result under the corresponding query. The query result should be copied and pasted as it is printed on the screen without any modification. If necessary, the column names appearing in the result should be converted appropriately using "AS" in statement. Don’t delete or change questions. You just need to insert both your TEXT query and captured screenshot result under your query.

1. HR Manager is interested in counting branches in each country. Also, the manager wants to see the results in a report sorted from high to low sequence of the number of branches. (10 points)

2. HR manager wants to make a report which shows minimum, and maximum salary of each department. (5 points)

3. HR manager wants to make a report which shows sum and average salary of each department, and job position. (10 points) t

4. HR manager wants to make a report which contains 4 head columns “Job ID, Average Salary, Minimum Salary, Maximum Salary”. Also, he wants the report to be sorted by maximum Salary from low to high. (10 points)

5. Due to lower salary of salespersons may yield skewed total salary in the previous reports. The HR manager wants to calculate total salaries and count of only non-salespersons of each department and job id. Include total of salary and the number of employees of the groups, department id, job id in your report. Also, total sum of each department and total number of each department should be in included in the result (HINT: You need to use ROLLUP in GROUP BY clause). (15 points).

Answer 1

1. select count(*) as Branches,cOUNTRY from employee GROUP BY cOUNTRY;

2.select min(e.salary) as minimum,max(e.salary) as maximum,d.dept_id from employee e,department d where e.emp_id=d.emp_id group by d.dept_id

4.select JOB_ID, min(salary) as minimum,avg(salary)as AVERAGE,max(salary) as maximum from employee group by JOB_ID order by maximum DESC;

3. select min(e.salary) as minimum,max(e.salary) as maximum,d.dept_id from employee e,department d where e.emp_id=d.emp_id group by d.dept_id;

I had time to answer only 4 subparts. And the table data I have used is just a sample data and please use your own tables and run these queries.