Oracle习题答案

Part 4 practice
1
select e.last_name,e.dept_id,d.name
from s_emp e,s_dept d
where e.dept_id=d.id;

2
select e.last_name,d.name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id and d.name='Sales';

3
select

e.last_name,d.name
from s_emp e,s_dept d
where e.dept_id=d.id and e.last_name='Smith';
4
select  p.name,p.id,i.quantity ordered
from s_product p,s_item i
where i.product_id=p.id and i.ord_id=101;

5
select c.id,e.last_name
from s_customer c,s_emp

e
where e.id=c.sales_rep_id
order by e.last_name,c.id;

6
select c.id "Customer ID",c.name "Customer Name",o.id "Order ID"
from s_customer c left outer join s_ord o
on c.id=o.customer_id;
7
select e.last_name emp_name,e.id emp_id,a.last_name

mgr_name,a.id mgr_id
from s_emp e join s_emp a
on e.manager_id=a.id
order by emp_id;

8

select e.last_name "Employee Name",e.id "Employee ID",a.last_name "Manger Name",a.id "Manger_ID"
from s_emp e left outer join s_emp a
on

e.manager_id=a.id
order by emp_id asc;

9
select c.name "Customer",i.product_id,i.quantity
from s_ord o inner join s_item i
on o.id=i.ord_id
inner join s_customer c
on o.customer_id=c.id
where o.total>100000;

Part 5 practice
2
select max(o.total)

"Highest" ,min(o.total) "Lowest"
from s_ord o;

3
select e.title job,max(e.salary) maxmum,min(e.salary) minmun
from s_emp e
group by e.title
order by maxmum desc;

4
select count(distinct e.manager_id) "Number of Managers"
from s_emp e;

5
select

o.id ord_id,count(i.ord_id) "NUMBER OF ITEMS"
from s_ord o inner join s_item i
on o.id=i.ord_id
group by o.id
order by o.id;

6
select e.manager_id,min(e.salary)  "LOWEST PAID EMPLOYEE"
from s_emp e
group by e.manager_id
having min

(e.salary)>1000
order by min(e.salary),e.manager_id ;

7
select (max(e.salary)-min(e.salary)) difference
from s_emp e;

8
select i.product_id,count(i.product_id) "TIMES ORDERED"
from s_item i
group by i.product_id
having count(i.product_id)>=3
order

by count(i.product_id),i.product_id;

9
select r.id,r.name,count(d.region_id)
from s_region r inner join s_dept d
on r.id=d.region_id
group by r.id,r.name
order by r.id;

10
select i.ord_id,sum(i.quantity)
from s_item i
group by i.ord_id
having sum

(i.quantity)>100;

11
select c.name,count(o.customer_id)
from s_customer c inner join s_ord o
on c.id=o.customer_id
group by c.name
order by c.name;

Part 6
2
select e.last_name,e.first_name,e.start_date
from s_emp e inner join s_dept d
on

e.dept_id=d.id
where e.dept_id=(select e1.dept_id from s_emp e1 inner join s_dept d1
                on e1.dept_id=d1.id
                where e1.last_name='Magee');

3
select e.id

,e.last_name,e.first_name,e.userid
from s_emp e
where e.salary>(select avg(e1.salary) from s_emp e1);

4
select  e.last_name,e.dept_id,e.title
from s_emp e  inner join s_dept d
on e.dept_id=d.id
where d.region_id='1' or d.region_id='2';

5
select

e.last_name,e.salary
from s_emp e
where e.manager_id=(select d.id from s_emp d
where d.first_name='LaDoris' and d.last_name='Ngao');

6
select e.id,e.first_name,e.last_name
from s_emp e
where e.salary>(select avg(e1.salary) from s_emp e1) and

e.title like '%t%';

7
select c.id,c.name, c.credit_rating,e.last_name
from s_customer c inner join s_emp e
on c.sales_rep_id=e.id
where c.region_id=(select id from s_region where name='North America') or
e.id=(select id from s_emp where

name='Nguyen');

8
select distinct(p.name),p.short_desc
from s_item i inner join s_product p
on i.product_id=p.id
inner join s_ord o
on i.ord_id=o.id
where o.date_ordered not between '01-SEP-92' and '30-SEP-92';

9
select c.name,c.credit_rating
from

s_customer c inner join s_emp e
on c.sales_rep_id=e.id
where e.id=(select id from s_emp where first_name='Andre' and last_name='Dumas');

10
select c.name,sum(o.total)
from s_customer c inner join s_emp e
on c.sales_rep_id=e.id
inner join s_ord

o
on c.id=o.customer_id
where e.id in (select a.id from s_emp a inner join s_dept b
            on a.dept_id=b.id
            where b.region_id=1 or b.region_id=2       
            )
group by c.name;