SQL題

IF OBJECT_ID('Sales') > 0 DROP TABLE Sales
 GO
IF OBJECT_ID('Customers') > 0
DROP TABLE Customers
GO
IF OBJECT_ID('Products') > 0 DROP TABLE Products
GO
CREATE TABLE Customers ( CustomerID INT IDENTITY PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), City VARCHAR(50), State CHAR(2), Zip VARCHAR(10) )
GO
CREATE TABLE Products (ProductID TINYINT IDENTITY PRIMARY KEY, ProductName VARCHAR(20),RecommendedPrice MONEY,Category VARCHAR(10) )
GO
 CREATE TABLE Sales(SaleID INT IDENTITY PRIMARY KEY,ProductID TINYINT NOT NULL REFERENCES Products(ProductID),CustomerID INT NOT NULL REFERENCES Customers(CustomerID),SalePrice MONEY NOT NULL,SaleDate SMALLDATETIME NOT NULL)
GO
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('DVD',105,'LivingRoom')
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Microwave',98,'Kitchen')
INSERT INTO Products(ProductName, RecommendedPrice, Category)VALUES('Monitor',200,'Office')
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Speakers',85,'Office')
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Refrigerator',900,'Kitchen')
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('VCR',165,'LivingRoom')
 INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('CoffeePot',35,'Kitchen')
GO
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('John','Miller','Asbury','NY','23433')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Fred','Hammill','Basham','AK','85675')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Stan','Mellish','Callahan','WY','38556')
 INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Adrian','Caparzo','Denver','CO','12377')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Mike','Horvath','Easton','IN','47130')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Irwin','Wade','Frankfurt','KY','45902')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('George','Marshall','Gallipoli','ND','34908')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Frank','Costello','Honolulu','HI','23905')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Billy','Costigan','Immice','SC','75389')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Shelly','Sipes','Lights','AZ','35263')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Chirsty','Melton','Spade','CA','97505')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Amanda','Owens','Flask','CN','50386')
 INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Brittany','Smits','Bourbon','KY','24207')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Kristy','Bryant','Tarp','FL','58960')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Kelly','Street','TableTop','ID','57732')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Tricia','Hill','Camera','ME','46738')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Holly','Raines','Compact','MS','35735')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Natalie','Woods','Woods','IN','87219')
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Wendy','Hilton','Action','KY','47093')
GO
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,1,130,'2/6/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,2,97,'1/7/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,3,200,'8/8/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,4,80,'4/9/2005')
 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,899,'10/10/2005')
 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,6,150,'10/11/2005')
 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,7,209,'12/12/2005')
 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,8,90,'5/13/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,9,130,'6/14/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,14,85,'6/19/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,15,240,'9/20/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,16,99,'7/21/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,17,87,'3/22/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,18,99,'1/23/2005')
 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,19,150,'3/24/2005')
 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,900,'3/10/2005')
 INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,6,86,'8/11/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,7,88,'8/12/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,8,198,'12/13/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,9,150,'5/14/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,14,99,'7/19/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,15,104,'9/20/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,16,270,'2/21/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,17,90,'7/22/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,1,130,'3/6/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,2,102,'4/7/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,3,114,'11/8/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,4,1000,'5/9/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,1100,'10/10/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,6,285,'6/11/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,7,87,'10/12/2005')
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,8,300,'7/13/2005')
GO

 

 

 

 

 

測試專案#1：返回在2005年10月售出的所有產品的名稱、價格和客戶姓名

select a.ProductName,b.SalePrice,c.FirstName,c.LastName
from dbo.Products a left join dbo.Sales b
on a.ProductID=b.ProductID
JOIN dbo.Customers C
ON b.CustomerID=C.CustomerID
WHERE year(b.SaleDate)=2005 and month(b.SaleDate)=10
--------------------------------------------------------------------
SELECT c.FirstName, c.LastName, p.ProductName, s.SalePrice
 FROM Sales s
INNER JOIN Customers c ON s.CustomerID = c.CustomerID
INNER JOIN Products p ON s.ProductID = p.ProductID
WHERE s.SaleDate >= '10/1/2005' AND s.SaleDate < '11/1/2005'

測試專案#2：返回沒有購買產品並且位於客戶表格上的人的姓名及其客戶ID
select c.FirstName,c.LastName,c.CustomerID from dbo.Customers c
WHERE c.CustomerID NOT IN (SELECT CustomerID from dbo.Sales)
------------------------------------------------------------------
SELECT c.CustomerID, c.FirstName, c.LastName FROM Sales s
RIGHT OUTER JOIN Customers c ON s.CustomerID = c.CustomerID
WHERE s.CustomerID IS NULL
測試專案#3：返回客戶姓名、銷售價格、建議售價、建議售價和實際價格的差額，該差額必需是正數

select c.FirstName,c.LastName,s.SalePrice,p.RecommendedPrice,abs(s.SalePrice-p.RecommendedPrice) as discount
 from dbo.Sales s
INNER JOIN Customers c ON s.CustomerID = c.CustomerID
INNER JOIN Products p ON s.ProductID = p.ProductID

測試專案#4：根據產品類別計算平均價格

select p.Category,sum(s.SalePrice)/count(p.Category) as avgPrice from Products p
join Sales s on s.ProductID=p.ProductID
group by p.Category
----------------------------------------
SELECT p.Category, AVG(s.SalePrice) AS AverageSalePrice
FROM Sales s INNER JOIN Products p ON s.ProductID = p.ProductID GROUP BY p.Category
測試專案#5：將以下的客戶和銷售資訊加入到資料庫中：

FirstName: Chris
LastName: Kringle
City: Henryville
State: IN
Zip: 47126
ProductID: 3
SalePrice: 205
SaleDate: 12/31/2005

insert into Customers VALUES('Chris','Kringle','Henryville','IN','47126')
insert into Sales VALUES('3',@@IDENTITY ,205,'12/31/2005')

-------------------------------------------------------------------
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Chris', 'Kringle', 'Henryville', 'IN', '47126')
INSERT INTO Sales(CustomerID, ProductID, SalePrice, SaleDate) VALUES(SCOPE_IDENTITY(), 3, 205, '12/31/2005')

測試專案#6：從資料庫中刪除來自緬因洲(‘ME’)的客戶

delete s from Sales s left join Customers  c on s.CustomerID=c.CustomerID
where c.State='me'
delete from dbo.Customers where State='me'

測試專案#7：返回客戶購買了兩個或多個產品的平均售價和產品類別

select * from dbo.Customers
select * from dbo.Products
select * from dbo.Sales

select p.Category,s.SalePrice,s.ProductI from Products p
join Sales s on p.ProductID=s.ProductID
 group by p.Category
---------------------------------------------------------------------------
SELECT p.Category, AVG(s.SalePrice) FROM Sales s
INNER JOIN ( SELECT s.CustomerID FROM Sales s
GROUP BY s.CustomerID
HAVING COUNT(CustomerID) >= 2 ) x ON s.CustomerID = x.CustomerID
INNER JOIN Products p ON s.ProductID = p.ProductID GROUP BY p.Category

 

測試專案#8：將銷售在2005年6月10日到6月20日之間的產品的銷售價格升級為建議售價，答案見列表I：

UPDATE s SET SalePrice = p.RecommendedPrice FROM Sales s INNER JOIN Products p ON s.ProductID = s.ProductID WHERE SaleDate >= '6/10/2005' AND SaleDate < '6/21/2005'

測試專案#9：根據產品種類計算建議售價超過實際售價10元及以上的銷售數量，答案見列表J：

SELECT p.Category, COUNT(*) AS NumberOfSales FROM Sales s INNER JOIN Products p ON s.ProductID = p.ProductID GROUP BY p.Category HAVING AVG(p.RecommendedPrice) >= AVG(s.SalePrice)+10

測試專案#10：不使用疊代構建，返回所由銷售產品的銷售日期，並按照該日期升序排列，答案見列表K：

SELECT s.SaleDate, s.SalePrice, ( SELECT SUM(SalePrice) FROM Sales s2 WHERE s2.SaleDate <= s.SaleDate ) AS RunningTotal FROM Sales s ORDER BY s.SaleDate ASC

評分

我曾經使用類似的考題去考察很多應聘SQL Server資料庫開發職位的人，但是迄今為止，只有2個人可以正確地回答出所有的問題。

平均分大約為50-60%，如果應聘者的表現高於這個平均分，那麼我就認為他或她是一位優秀的T-SQL程式設計師，如果應聘者獲得了90%以上的得分，那麼他或她就是一位非常優異的程式設計師。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7899089/viewspace-611429/，如需轉載，請註明出處，否則將追究法律責任。