SQL Server.doc

第一章Select * from Sales.Customer-第二题Select CreditCardID Credit Card ID,CardType Credit Card Type,CardNumber Credit Card Number,ExpYear Expiry Yearfrom Sales.CreditCard-第三题Select * from Sales.Customerwhere TerritoryID = 4-第四题Select * from Sales.SalesOrderHeaderwhere TotalDue 2000-第五题Select * From Sales.SalesOrderDetailwhere SalesOrderDetailID = 843-第六题Select * From Sales.SalesOrderDetailWhere ModifiedDate = 2004-06-06 00:00:00.000-第七题Select SalesOrderID Order ID, OrderQty Order Quantity , Unitprice Unit price, LineTotal Total cost from Sales.SalesOrderDetail-第八题Select * from Sales.SalesOrderDetailwhere UnitPrice Between 2000 AND 2100-第九题Select TerritoryID TerritoryID, Name Name, CountryRegionCode Country Region Code , SalesYTD Sales Year To Date from Sales.SalesTerritorywhere TerritoryID =1-第十题Select * from Sales.SalesOrderHeaderwhere TaxAmt 10000-第十一题Select * from Sales.SalesTerritorywhere Name=Canada or Name=France or Name=Germany-第十二题Select SalesPersonID SalesPersonID,TerritoryID TerritoryIDfrom Sales.SalesTerritoryHistorywhere TerritoryID = 2 or TerritoryID=4-第十三题Select *From Sales.CreditCardWhere ExpYear = 2006 AND CardType = Vista-第十四题Select *From Sales.SalesOrderHeaderwhere ShipDate 2004-7-12-第十五题Select OrderNumber=SalesOrderNumber,OrderDate,Status,TotalCost=TotalDue from Sales.SalesOrderHeader where OrderDate=2001-07-01 AND SubTotal 10000-第十六题Select * from Sales.SalesOrderHeaderwhere OnlineOrderFlag=1-第十七题Select Order ID=SalesOrderID,TotalDue from Sales.SalesOrderHeader order by TotalDue DESC-第十八题Select OrderID=SalesOrderID,TaxAmt from Sales.SalesOrderHeader where TaxAmt 1Group BY ProductID having sum(LineTotal) 10000-第28题Select productID ,Linetotal as totalfrom sales.salesOrderDetailCOMPUTE sum (Linen total) BY ProductID少了order by语句-第29题Select top 3 *,dense_rank() over(order by Bonus desc)from Sales.SalesPerson-第30题select * from Sales.Storewhere Name Like %Bike%-第31题select sum(OrderQty)TotalOrderQty,ModifiedDate from Sales.SalesOrderDetail group by ModifiedDate-第32题Select UnitPrice,OrderQty from Sales.SalesOrderDetailwhere ProductID = 774 or ProductID = 777-第33题Select SalesOrderID, max = max(OrderQty) ,min = min(OrderQty)from Sales.SalesOrderDetailwhere (UnitPrice*OrderQty) 5000Group BY SalesOrderID-第34题Select Sales Order ID = SalesOrderID,Average Value = avg(UnitPrice * OrderQty)from Sales.SalesOrderDetailGroup by SalesOrderID having avg(UnitPrice * OrderQty) 5000-第35题Select CardType from Sales.CreditCard-第36题select CustomerID,left(Name,15),SalesPersonID from Sales.Store-第37题select Order Number = SalesOrderNumber,Total Due = TotalDue,Day of Order = OrderDate,Week day = Datename(dw,OrderDate)from Sales.SalesOrderHeader-第38题Select SalesOrderID,OrderQty,UnitPrice,rank() over (order by UnitPrice)from Sales.SalesOrderDetail-第39题select EmployeeID,HireDate = str(year(HireDate) + str(month(HireDate)from HumanResources.Employee第二章-第一题select sp.SalesPersonID , st.Name TerritoryName from Sales.SalesPerson sp join Sales.SalesTerritory st on sp.TerritoryID=st.TerritoryID-No2select sp.SalesPersonID PersonID ,st.TerritoryID, st.Name from Sales.SalesPerson sp join Sales.SalesTerritory st on sp.TerritoryID=st.TerritoryID-No3select sod.SalesOrderID OrderID,sod.ProductID,soh.OrderDate from Sales.SalesOrderDetail sod Join Sales.SalesOrderHeader soh on sod.SalesOrderID=soh.SalesOrderID-No4select sp.SalesPersonID,st.Name TerritoryName from Sales.SalesPerson sp left outer Join Sales.SalesTerritory st on sp.TerritoryID=st.TerritoryID-No5select distinct sod.SalesOrderID,soh.TerritoryID TerritoryName,month(soh.OrderDate) Month,Year(soh.OrderDate) Year from Sales.SalesOrderDetail sod Join Sales.SalesOrderHeader soh on sod.SalesOrderID = soh.SalesOrderID-No6select soh.SalesOrderID,st.Name TerritoryName,OrderDate,DatePart(QQ,OrderDate) Quarter from Sales.SalesOrderHeader soh join Sales.SalesTerritory st on soh.TerritoryID = st.TerritoryID-No7select distinct soh.SalesOrderID,CardType,round(TotalDue,0) TotalDue from Sales.SalesOrderHeader soh Join Sales.CreditCard cc on soh.CreditCardID = cc.CreditCardID order by soh.SalesOrderID-No8select TerritoryID,CountryRegionCode From Sales.SalesTerritory-No9select *from Sales.SalesOrderHeaderselect SalesOrderID , round (TotalDue,2) TotalDue from Sales.SalesOrderHeader-No10select soh.SalesOrderID,st.Name TerritoryName,str (datepart(yy,OrderDate),4) +/+ str(datepart(mm,OrderDate),2) +/+ str(datepart(dd,OrderDate),2) OrderDate from Sales.SalesOrderHeader soh Join Sales.SalesTerritory st on soh.TerritoryID=st.TerritoryID-11select soh.SalesOrderID,Name TerritoryNamefrom Sales.SalesTerritory t join Sales.SalesOrderHeader soh on soh.TerritoryID = t.TerritoryIDwhere month(soh.OrderDate)=5 and year(soh.OrderDate) = 2004-12select ccc.ContactIDfrom Sales.ContactCreditCard ccc join Sales.CreditCard cc on ccc.CreditCardID = cc.CreditCardIDwhere cc.CardType = Vista-13select soh.SalesOrderIDfrom Sales.SalesOrderHeader soh join Sales.SalesTerritory t on soh.TerritoryID = t.TerritoryIDwhere t.Name = Northeast-14select SalesOrderID from Sales.SalesOrderHeaderwhere TotalDue (select avg(TotalDue) from Sales.SalesOrderHeader)-15select sod.SalesOrderID , sod.SalesOrderDetailID ,soh.TotalDue from Sales.SalesOrderDetail sod join Sales.SalesOrderHeader soh on sod.SalesOrderID = soh.SalesOrderIDwhere soh.TotalDue (select max(TotalDue) from Sales.SalesOrderHeader where SalesOrderID =43662)-16select soh.SalesOrderID,c.CreditCardID from Sales.CreditCard c join Sales.SalesOrderHeader soh on c.CreditCardID = soh.CreditCardIDwhere c.ExpYear = 2007-17select cc.CardNumber from Person.Contact c join Sales.ContactCreditCard ccc on c.ContactID = ccc.ContactIDjoin Sales.CreditCard cc on cc.CreditCardID = ccc.CreditCardID where c.FirstName + c.LastName = CatherineAbel-18select * from Sales.SalesOrderDetail where UnitPriceDiscount = 0.00-19select sod.SalesOrderID , sod.SalesOrderDetailID ,soh.TotalDue from Sales.SalesOrderDetail sod join Sales.SalesOrderHeader soh on sod.SalesOrderID = soh.SalesOrderIDwhere soh.TotalDue (select avg(TotalDue) from Sales.SalesOrderHeader)-20select soh.SalesOrderID from Sales.SalesOrderHeader soh join Sales.CreditCard cc on soh.CreditCardID = cc.CreditCardIDwhere CardType = SuperiorCard-21select cr.AverageRate from Sales.Currency c join Sales.CurrencyRate cr on c.CurrencyCode =cr.ToCurrencyCodewhere cr.CurrencyRateDate = 2004-07-01 and c.Name = Australian Dollar第四章-第一题Create Table Recipient( OrderNumber char(6), FirstName varchar(20), LastName varchar(20), Address varchar(50), City char(15), State char(15), CountryCode char(3), ZipCode char(10), Phone char(15),)drop Table Recipient-第二题Create Table Country( CountryID varchar(2), Country char(25),)drop Table Country-第三题Alter Table RecipientAlter Column OrderNumber char(6)-第四题drop Table Recipient-第五题-1Create table Category(CategoryID char(3) Constraint pk_CategoryID primary key(CategoryID),Category char(20) Constraint uk_Category Unique(Category),Description varchar(100) )drop table Category-2Create table ProductBand(BrandID char(3) constraint pk_BrandID primary key(BrandID),BrandName char(20) Constraint uk_BrandName Unique(BrandName)drop table ProductBand-3Create table NewProduct(ProductID char(6) Constraint pk_ProductID primary key(ProductID),ProductName varchar(20) not null,ProductDescription varchar(250) not null,CategoryID char(3) Foreign key References Category(CategoryID),ProductRate money,BrandID char(3),Photo image null,Qoh smallint Constraint chk_Qoh check(Qoh between 0 and 200),ProductImgPathvarchar(50) null)drop table NewProduct -4Alter Table NewProductAdd constraint fk_BrandId foreign key (BrandId) references dbo.ProductBrand(BrandId)-5不能用DELETE 删除，应该改为dropdrop TABLE SuperiorCreditCard第五章-第一题Create Table ProductBrand( BrandID char(3), BrandName char(20)drop Table ProductBrandselect * from dbo.ProductBrandInsert ProductBrandvalues (B01,Lee)Insert ProductBrandvalues (B02,Nike)Insert ProductBrandvalues (B03,Reebok)-第三题select * from Person.Address where city = Atlanta and AddressLine1 = 4151 Oliveraselect * from Purchasing.Vendorselect * from Purchasing.VendorAddress update Person.Address Set AddressLine1 = 4151 Olivera,city = Atlanta,StateProvinceID = 17,PostalCode = 30308Where AddressID = (Select AddressID from Purchasing.VendorAddress Where VendorID = (Select VendorID from Purchasing.Vendor Where Name =Comfort Road Bicycles )-第四题Delete from ProductBrandselect * from dbo.ProductBrand第六章-第一题select sd.SalesOrderID,sum(LineTotal) AS Total AmountFrom Sales.SalesOrderDetail sd join Sales.SalesOrderHeader sh on sd.SalesOrderID = sh.SalesOrderIDgroup by sd.SalesOrderIDcreate index IX_SalesOrderDetail_SalesOrderIDon Sales.SalesOrderDetail(SalesOrderID)-第二题select * from Sales.Storecreate index IX_Store_CustomerIDon Sales.Store(CustomerID)-第三题create index IX_SalesOrderDetail_SalesOrderIDon Sales.SalesOrderDetail(SalesOrderID-第一题select sd.SalesOrderID,sum(LineTotal) AS Total AmountFrom Sales.SalesOrderDetail sd join Sales.SalesOrderHeader sh on sd.SalesOrderID = sh.SalesOrderIDgroup by sd.SalesOrderIDcreate index IX_SalesOrderDetail_SalesOrderIDon Sales.SalesOrderDetail(SalesOrderID) -第二题select * from Sales.Storecreate index IX_Store_CustomerIDon Sales.Store(CustomerID) -第三题create index IX_SalesOrderDetailon Sales.SalesOrderDetail(SalesOrderID,SalesOrderDetailID)-第一题-错误：View or function vwSalesOrderDetail is not updatable - because the modification affects multiple base tables.-解决方案：update Sales.SalesOrderDetailset OrderQty = 2from Sales.SalesOrderDetailwhere SalesOrderID = 43659update Sales.SalesOrderHeaderset TerritoryID = 4from Sales.SalesOrderHeaderwhere SalesOrderID = 43659-第二题select * from Sales.Storewhere Namelike%Bike%select * from Sales.Storewhere Namelike%Bike Store%-第三题create view vCreditCardasselect * from Sales.CreditCardwhere CardType = SuperiorCardselect * from vCreditCard第七章-第一题Declare Rate int Select Rate=(select avg(rate) from HumanResources.EmployeePayHistory)select * from HumanResources.EmployeePayHistoryWhere rate Rate-第二题Create function ShipmentDate(SalesOrderID int)returns DatetimeasBegin declare ShipDate datetime Select ShipDate = ShipDate Fro