练习.txt

orale培训教材包括了所有的sql说明和实例

--练习

--通过两个view 实现 一个视图：包括雇员姓名，雇员编号,salary,及雇员总销售额。

--view1:销售交易单分组求出雇员总销售额，EmployID组成total视图
--sum_price 是sum(totalprice)的别名，作为view 的'字段'

create view total as
select sum(totalprice) sum_price,EmployID from sm_saleorderlist
group by EmployID;

create view total as
select sum(totalprice) ,EmployID from sm_saleorderlist
group by EmployID;

--view2:total视图通过EmployID与sm_emp建立联接join,建立emp_tatal视图
create view emp_tatal as
select name,empid,salary,sum_price
from sm_emp,total
where empid=EmployID;


--只用一个view :ename,etotal,salary,telno为查询语句的别名
--使用多表查询同时用分组
create or replace view emp_totalprice as 
select max(name) ename,sum(totalprice) etotal,
max(salary) salary,max(telno) telno
from sm_emp,sm_saleorderlist
WHERE sm_saleorderlist.EmployID=sm_emp.empid
group by sm_saleorderlist.EmployID;


select name,totalprice,telno
from sm_emp,sm_saleorderlist
WHERE sm_saleorderlist.EmployID=sm_emp.empid

select name,sum(totalprice) 
from sm_emp,sm_saleorderlist
WHERE sm_saleorderlist.EmployID=sm_emp.empid
group by sm_saleorderlist.EmployID;



update sm_saleorderlist set totalprice = 555 
where transactionid=2;