Homework 06: SQL Data Manipulation Submission Deadline: Wednesday 03/21/2018 11:
ID: 3737706 • Letter: H
Question
Homework 06: SQL Data Manipulation Submission Deadline: Wednesday 03/21/2018 11:00 AM. Consider the Agents table from Lob 03 Agents (agentNo,fName,IName,branchNo,salary,dob,address,city.county,state,zip.phonel phone2,email) Provide the SQL statements that answer the following questions: 1- How many agents are there? 2- What is the minimum, the average, and the maximum salaries of all agents? 3- What is the number of agents living in New York state and the sum of their salaries? 4- How many agents have NULL email addresses? 5- How many agents work in the branch B002 and have salaries greater than $40000?Explanation / Answer
1. How Many Agents are there?
Ans ) select count(*) from Agents
-----------------------------------------------------------------------------
2. what is the minimum, the average,and the maximum salaries of all agents?
Ans: select min(salary) from Agents;
select avg(salary) from Agents;
select max(salary) from Agents;
---------------------------------------------------------------------------------------
3. what is the number of agents living in NewYork State and the sum of their salaries?
Ans. select count(*) as NumberOfAgents,sum(salary) as SalarySUM from Agents group by state
having state='NewYork';
-----------------------------------------------------------------------------------------
4. How many agents have NULL email Address?
Ans. select count(*) from Agents where email is NULL;
-----------------------------------------------------------------------------------------
5. How many agents work in the branch B002 and have salaries greater than $40000?
Ans. select count(*) from Agents where branchNo='B002' and salary>40000;
-----------------------------------------------------------------------------------------------
6. How many different branches are there?
Ans. select count(distinct(branchNo)) from Agents;
--------------------------------------------------------------------------------------------------
7. what is the number of agents in each state?
Ans. select state, count(agentNo) as No_of_Agents from Agents group by state;
-------------------------------------------------------------------------------------------------------
8. what is the number of Agents in each branch and the average of their salaries?
Ans. select branchNo,count(agentNo) as No_of_Agents,avg(salaries) as Avg_Salary from Agents group by branchNo;
----------------------------------------------------------------------------------------------------------------
9. In each branch , what are the count,the min,the max,and the sum of salaries for all agents living in California or NewYork states?
Ans. select branchNo,count(agentNo) as Count,min(salary) as Min,max(salary) as Max,avg(salary) as Avg from Agents group by branchNo having state in ('California','NewYork');
---------------------------------------------------------------------------------------------------------------------------------------------------------
10. In each branch what are the count and the average salaries for all agents living in California or NewYork states? Display only the result for branches with an average salary greater than $36000?
Ans. select branchNo,count(agentNo) as Count,avg(salary) as Avg_Salary from Agents group by branchNo having state in ('California','NewYork') and avg(salary)>36000;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.