数据库系统原理与设计第二版实验教程实验一到实验三.docx

实验一1：1.select employeeName,headship,salaryfrom Employee2select CustomerName,addressfrom Customerwhere 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 employeewhere (address like %上海%or address like %南昌%)and sex=F5select * from employee where (headship=职员 or headship=科长) and sex=F6Select * 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=50008 SELECT?TOP?10?PERCENT?*??from?orderdetail?order?by?price?DESC实验二1select a.employeeNo, a.employeeName,a.hireDatefrom employee a,Employee bwhere a.employeeNo!=b.employeeNo and a.employeeName!=b.employeeNameand a.hiredate=b.hireDate order by b.employeeName2 select a.employeeName,a.sex,a.department,a.headShipfrom 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 productName=32M DRAM)7 select orderNo,sum(quantity*price) orderSum from OrderDetail group by orderNo having sum(quantity*price)=(select max(ord