原创力文档数据库系统原理与设计第二版实验实验一到实验三课案
实验一
1：1.select employeeName,headship,salary
from Employee
2select CustomerName,address
from Customer
where customerName like %有限%
3select *
from employee
where employeeName like 张%梅
4 SELECT employeeName,department,address,
isnull (convert(char(10),birthday,120),不详)出生日期,
case sex when Mthen 男
when Fthen女
end as 性别
from employee
where (address like %上海%or address like %南昌%)and sex=F
5select *
from employee
where (headship=职员 or headship=科长) and sex=F
6Select *
from Customer where CustomerNo not in ( ,
7update ordermaster set ordersum=sum2
from ordermaster a,(select orderno,sum(quantity*price)sum2
from orderdetail
group by orderno)b where a.orderno=b.orderno
Select * From ordermaster Where ordersum=5000
8 SELECT?TOP?10?PERCENT?*??
from?orderdetail?order?by?price?DESC
实验二
1select a.employeeNo, a.employeeName,a.hireDate
from employee a,Employee b
where a.employeeNo!=b.employeeNo and a.employeeName!=b.employeeName
and a.hiredate=b.hireDate order by b.employeeName
2 select a.employeeName,a.sex,a.department,a.headShip
from Employee a,Employee b
where a.department=b.department and b.employeeName=陈诗杰
3．select *
from Employee a
where a.salary(select avg(b.salary) from Employee b)
4 select a.customerNo,a.customerName,b.orderNo,sum(quantity*price) orderSum
from Customer a,OrderMaster b,OrderDetail c
where a.customerNo=b.customerNo and b.orderNo=c.orderNo
group by a.customerNo,a.customerName,b.orderNo
5 SELECT?a.customerNo,customerName?FROM?Customer?a?
WHERE?a.customerNo?NOT?IN?(SELECT?customerNo?FROM?OrderMaster?)
6．select employeeName,case sex
when M then 男
when F then 女
end as sex, b.orderDate,c.quantity 销售数量,c.quantity*c.price 金额
from Employee a,OrderMaster b,OrderDetail c
where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and c.productNo in(
select f.productNo
from OrderMaster d,OrderDetail e,Product f
where d.orderNo=e.orderNo and productNa
文档评论（0）