SQL实例Word版

2008-06-05 10:121sql实例（1）.查看1996年第三季度签订订单的信息select * from dbo.Orders where year(OrderDate)=1996and datepart(q,OrderDate)=32.哪些订单订购产品的平均价格小于20select OrderID,avg(UnitPrice) 平均价from dbo.Order Detailsgroup by OrderIDhaving avg(UnitPrice)<203.查找价格最低的产品信息select top 1 * from dbo.Products order by UnitPrice4.不在伦敦的雇员信息select * from dbo.Employees where City<>'london'5.9月份雇佣的雇员信息select * from dbo.Employeeswhere month(HireDate)=96.已经签单的雇员信息select * from dbo.Employeeswhere EmployeeID in (select distinct EmployeeID from dbo.Orders )7.两个平均定购价格最低的订单select top 2 OrderID,avg(UnitPrice) from dbo.Order Details group by OrderIDorder by avg(UnitPrice) 推荐精选8.5月份签订的订单并且签订订单的产品总量达于50，有哪些订单select o.OrderID ,sum(Quantity)数量from dbo.Orders o ,dbo.Order Details odwhere o.OrderID=od.OrderID and month(OrderDate)=5group by o.OrderIDhaving sum(Quantity)>=50 1.查询去年雇佣的雇佣信息select * from dbo.Employees where year(HireDate)=year(getdate()-12.查询lastname为Davolio的雇员签订了那些产品select distinct ProductID from dbo.Employees e, dbo.Orders o,dbo.Order Details odwhere e.EmployeeID=o.EmployeeID and o.OrderID=od.OrderID and LastName='Davolio'3.每一个签订订单的雇员销售产品的总量select EmployeeID,sum(Quantity) from dbo.Order Details od,dbo.Orders owhere od.OrderID=o.OrderIDgroup by EmployeeID 4.在1996年每一种产品的销售额select ProductID,sum(Quantity*UnitPrice) from dbo.Order Details od,dbo.Orders owhere year(OrderDate)=1996 and od.OrderID =o.OrderID group by ProductID5.客户ALFKI订购了那些种类的产品select distinct ProductID from dbo.Orders o,dbo.Order Details od where o.OrderID=od.OrderID and CustomerID='ALFKI'6.按每年每季度统计订购了多少订单推荐精选select year(OrderDate),datepart(q,OrderDate),count(OrderID)from dbo.Orders group by year(OrderDate),datepart(q,OrderDate)order by year(OrderDate),datepart(q,OrderDate)一：创建表test(aa,bb) 在企业管理器下建立索引字段为aa的唯一索引 在查询分析器下建立索引字段为bb的聚集索引 create table test (aa int,bb int) create clustered index bb_index on test(bb)二：创建表sc(stuid,cid,score) 创建复合索引，索引列为stuid和cid, 其中stuid 为升序，cid为降序 create table sc (stuid int, cid char(2), score smallint) create index sc_index ON sc (stuid asc, cid desc)三：在 northwind数据库下查询 1、5月份出生的雇员人数 select count(*) from dbo.Employees where month(BirthDate)=52、查询年龄最大的三个人 select top 3 EmployeeID from dbo.Employees order by BirthDate推荐精选 3、查询lastname 以字母a结束的雇员信息 select * from dbo.Employees where lastname like '%a' 4、每个雇员的年龄 select year(getdate()-year(birthdate) age from dbo.Employees 5、查询所有产品的平均价格（ product表） select avg(UnitPrice) from dbo.Products 6、查询价格最高的5种产品信息（ product表） select top 5 * from dbo.Products order by UnitPrice desc 7、查询居住在london，并且region列为空的雇员信息 select * from dbo.Employees where city='london'and region is nullsql实例（2）2008-06-05 10:12四、1、每一个订单的销售额select OrderID,sum(Quantity*UnitPrice) from dbo.Order Details group by OrderID2、统计每年每个产品的销售数量select year(OrderDate),ProductID,sum(Quantity)from dbo.Order Details od,dbo.Orders owhere od.OrderID=o.OrderIDgroup by year(OrderDate),ProductIDorder by year(OrderDate),ProductID3、居住在伦敦的每个雇员销售额select o.EmployeeID,sum(Quantity*UnitPrice) from dbo.Employees e,dbo.Orders o, dbo.Order Details od 推荐精选where e.EmployeeID=o.EmployeeID and o.OrderID=od.OrderID and e.city='london'group by o.EmployeeID4、没有签订订单的雇员的信息select * from dbo.Employees where EmployeeIDnot in(select distinct EmployeeID from dbo.Orders )1. 每一位雇员的工龄（年，月，日）select EmployeeID,datediff(month,HireDate,getdate()/12 年, datediff(month,HireDate,getdate()%12 月, (datediff(day,HireDate,getdate()%365)%30 日from dbo.Employees 或（错）SELECT EmployeeID,DATEDIFF(day,HireDate,getdate()/365 DATEDIFF(month,HireDate,getdate()%12, DATEDIFF(day,HireDate,getdate()%365%30from dbo.Employees *2. 编号为ALFKI客户签订的产品价格均下降2%update dbo.Order Details set UnitPrice=0.98*UnitPricefrom dbo.Order Details od,orders owhere o.OrderID=od.OrderID and CustomerID='ALFKI'3. 每一位雇员的lastname前面均加siasupdate dbo.Employees set LastName='sias'+LastName4. 没有订购产品名称为Chai的客户人数select count(*)from dbo.Customers where CustomerID not in(select CustomerIDfrom dbo.Order Details od, dbo.Orders o,dbo.Products pwhere od.OrderID=o.OrderID and p.ProductID=od.ProductID and ProductName='Chai')5. 按季度统计每种产品的销售量select ProductID,datepart(q,orderdate), sum(Quantity)from dbo.Order Details od, dbo.Orders owhere od.orderid=o.orderidgroup by ProductID,datepart(q,orderdate)order by ProductID,datepart(q,orderdate), sum(Quantity)6. 将每个签单的时间减去一年update dbo.Ordersset orderdate=dateadd(year,-1,OrderDate)推荐精选7. 将region为空的客户的region更改为”unknown”Update customersSet region=unkownWhere region is null8. 将公司名称的长度大于的客户信息查询出来select * from customers where len(companyname)>109. 将没有业绩的雇员开除掉delete from dbo.Employeeswhere dbo.Employees.EmployeeIDnot in(select distinct EmployeeIDfrom dbo.Orders)1、创建学生表和成绩表 输入一些信息 查询前三名同学的学生信息 （前三名成绩存在重复）create table stu (stuid int, sname varchar(20)create table sc (stuid int, grade smallint)insert into stu values(1,'aa') insert into stu values(2,'bb') insert into stu values(3,'cc') insert into stu values(4,'dd')insert into stu values(5,'ee')insert into stu values(6,'ff')insert into stu values(7,'gg')insert into sc values(1,99) insert into sc values(2,99) insert into sc values(3,98) insert into sc values(4,98) insert into sc values(5,97) insert into sc values(6,89) insert into sc values(7,90) select distinct sc.stuid,gradefrom stu,sc where grade in(select distinct top 3 grade from scorder by grade desc )2、创建加密的且具有检查参数的的视图： 视图对应的查询是每个同学的成绩推荐精选create view grade_view with encryptionasselect grade from scwith check option （注：可编辑下载，若有不当之处，请指正，谢谢!） 推荐精选