Practical:
6
To
apply the concept of Aggregating Data using Group functions.
(1)
List total deposit of
customer having account date after 1-jan-96.
Select
sum(amount) from deposit where adate> ‘1-jan-96’;
(2)
List total deposit of
customers living in city Nagpur.
select
sum(amount) from (deposit inner join customers on
deposit.cname
= customers.cname) where customers.city='nagpur';
(3)
List maximum deposit of
customers living in bombay.
select
max(amount) from (deposit inner join customers on
deposit.cname
= customers.cname) where customers.city='bombay';
(4)
Display the highest,
lowest, sum, and average salary of all employees. Label the columns Maximum,
Minimum, Sum, and Average, respectively. Round your results to the nearest
whole number.
select
round(max(emp_sal)) "Maximum" , round(min(emp_sal))
"Minimum" , round(sum(emp_sal)) "Sum" , round(avg(emp_sal))
"Average" from employee;
(5)
Write a query that
displays the difference between the highest and lowest salaries. Label the
column DIFFERENCE.
Select
max(emp_sal)-min(emp_sal) as DIFFERENCE from employee;
(6)
Create a query that will
display the total number of employees and, of that total, the number of
employees hired in 1995, 1996, 1997, and 1998.
select
count(actno) as total, count(decode(to_char(adate,'YYYY'),1995,'m1'))
"1995", count(decode(to_char(adate,'YYYY'),1996,'m2'))
"1996", count(decode(to_char(adate,'YYYY'),1997,'m3'))
"1997", count(decode(to_char(adate,'YYYY'),1998,'m4'))
"1998" from deposit;
(7)
Find the average
salaries for each department without displaying the respective department
numbers.
Select
avg(emp_sal) from employee group by dept_no;
(8)
Write a query to display
the total salary being paid to each job title, within each department.
Note: there is no relation between the
tables employee table containing department and job table containing job title.
So I have considered only job title part of the question in solution.
select
sum(min_sal) from job group by job_id;
(9)
Find the average
salaries > 2000 for each department without displaying the respective
department numbers.
Select
avg(emp_sal) from employee group by dept_no having avg(emp_sal)>2000;
(10)
Display the job and
total salary for each job with a total salary amount exceeding 3000, in which
excludes president and sorts the list by the total salary.
select
sum(min_sal),job_id from job group by job_id having
sum(min_sal)
> 3000 order by sum(min_sal);
selectbranch.bname
from deposit inner join branch on deposit.bname=branch.bname where
city='bombay';
No comments:
Post a Comment