ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL題

SQL題

原创 Linux操作系统 作者:keeking 时间:2009-08-05 17:34:38 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: SQL題_20090805
下一篇: 索引學習筆記
请登录后发表评论 登录
全部评论

注册时间:2009-07-23

  • 博文量
    194
  • 访问量
    242217