请在思考之后展开select a.cust_id,cust_name,sum(tot_amt) totprice
from customer a,sales b
where a.cust_id=b.cust_id
groupby a.cust_id,cust_name
问题8、查找在1997年中有销售记录的客户编号、名称和订单总额
请在思考之后展开select a.cust_id,cust_name,sum(tot_amt) totprice
from customer a,sales b
where a.cust_id=b.cust_id andconvert(char(4),order_date,120)='1997'
groupby a.cust_id,cust_name
请在思考之后展开select emp_name 姓名, 性别= case a.sex when 'm' then '男'
when 'f' then '女'
else '未'
end,
销售日期= isnull(convert(char(10),c.order_date,120),'日期不详'),
qty 数量, qty*unit_price as 金额
from employee a, sales b, sale_item c,product d
where d.prod_name='16M DRAM' and d.pro_id=c.prod_id and
a.emp_no=b.sale_id and b.order_no=c.order_no
请在思考之后展开select emp_no 编号,emp_name 姓名, 性别= case a.sex when 'm' then '男'
when 'f' then '女'
else '未'
end,
prod_name 产品名称,销售日期= isnull(convert(char(10),c.order_date,120),'日期不详'),
qty 数量, qty*unit_price as 金额
from employee a leftouterjoin sales b on a.emp_no=b.sale_id , sale_item c,product d
where d.pro_id=c.prod_id and b.order_no=c.order_no
请在思考之后展开select emp_no,emp_name,d.sale_sum
from employee a,
(select sale_id,sale_sum
from (select sale_id, sum(tot_amt) as sale_sum
from sales
groupby sale_id ) b
where b.sale_sum <1000
) d
where a.emp_no=d.sale_id
问题17、查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额
请在思考之后展开select a.cust_id,cust_name,b.prod_id,prod_name,d.qty,d.qty*d.unit_price
from customer a, product b, sales c, sale_item d
where a.cust_id=c.cust_id and d.prod_id=b.prod_id and
c.order_no=d.order_no and a.cust_id in (
select cust_id
from (select cust_id,count(distinct prod_id) prodid
from (select cust_id,prod_id
from sales e,sale_item f
where e.order_no=f.order_no) g
groupby cust_id
havingcount(distinct prod_id)>=3) h )
问题18、查找至少与世界技术开发公司销售相同的客户编号、名称和商品编号、商品名称、数量和金额
请在思考之后展开select a.cust_id,cust_name,d.prod_id,prod_name,qty,qty*unit_price
from customer a, product b, sales c, sale_item d
where a.cust_id=c.cust_id and d.prod_id=b.prod_id and
c.order_no=d.order_no andnotexists
(select f.*
from customer x ,sales e, sale_item f
where cust_name='世界技术开发公司' and x.cust_id=e.cust_id and
e.order_no=f.order_no andnotexists
( select g.*
from sale_item g, sales h
where g.prod_id = f.prod_id and g.order_no=h.order_no and
h.cust_id=a.cust_id)
)
问题19、查找表中所有姓刘的职工的工号,部门,薪水
请在思考之后展开select emp_no,emp_name,dept,salary
from employee
where emp_name like '刘%'
请在思考之后展开select a.emp_no,a.emp_name,a.date_hired
from employee a
join employee b
on (a.emp_no!=b.emp_no and a.date_hired=b.date_hired)
orderby a.date_hired
请在思考之后展开Select cust_id,prod_id,sum(qty),sum(qty*unit_price)/sum(qty)
From sales a, sale_item b
Where a.order_no=b.order_no
Groupby cust_id,prod_id
Orderby cust_id,prod_id
请在思考之后展开Select a.prod_id,prod_name,sum(qty),sum(qty*unit_price)
From sale_item a,product b
Where a.prod_id=b.prod_id
Groupby a.prod_id,prod_name
Orderby a.prod_id
请在思考之后展开select a.cust_id, cust_name,c.prod_id,prod_name,qty, unit_price
from customer a,sales b, sale_item c ,product d
where a.cust_id=b.cust_id and b.order_no=c.order_no and c.prod_id=d.prod_id
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步