(上)今天去面试的题，我贴出来，大家也看看。
1：我想把当前正在运行这的MSSQLServer中的一个数据库改名，请问怎么办？（不能删除数据库）
2：Customer(客户)的表结构
CustomerID nchar(5) NOT NULL primary key,
CustomerName nvarchar(50) NOT NULL ,
CompanyName nvarchar(40) NOT NULL ,
ContactName nvarchar(30) NULL,
ContactTitle nvarchar(30) NULL,
Address nvarchar(60) NULL
Orders(订单)的表结构
OrderID nvarchar(20) NOT NULL,
CustomerID nchar(5) NOT NULL ,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippenDate datetime NULL,
primary key(OrderID,CustomerID)
Customer 与 Orders的关系如下
1 0..n
Customer------------> Orders
请写一个SQL语句，列出没有订单的客户的名称?
3:
A. Stock的表结构
StkID varchar(10) not null primary key, --(商品编号)
CName varchar(50) not null, --(品名)
Price numeric(12,2) null --(商品价格)
B. CustPrice的结构
CustID varchar(5) not null,
StkID varchar(10) not null foreign key REFERENCES Stock(StkID),
Price numeric(12,2) not null,
Primary key(CustID,StkID)
Stock 与 CustPrice 的关系为
1 1
Stock-----------> CustPrice
写一个SQL语句，用CustPrice中的 Price 更新 Stock 的 Price
4:
A. Study 的表结构
StudentID varchar(10) not null foreign key references Student(StudentID)
CouseID varchar(4) not null --课程代号
B. Student 的表结构
StudentID varchar(10) not null,
StudentName varchar(20) not null
写一个SQL语句，找出修2—3门的学生的名字
5: Customers的表结构如下：
CustomerID nchar(5) NOT NULL primary key,
CustomerName nvarchar(50) NOT NULL ,
CompanyName nvarchar(40) NOT NULL ,
CustType nvarchar(1) NULL,
ContactName nvarchar(30) NULL,
ContactTitle nvarchar(30) NULL,
Address nvarchar(60) NULL
请创建视图，要求：
a:需要CustomerID,Address,CustType列
b:CustomerID 的列名为 Customer
c:CustType的取值范围是
实际值 显示值
'I '-important,
'N '-normal,
请显示CustType列的显示值，而不是实际值
[解决办法]
--4
select * from Student as tmp
where (select count(*) from Study where StudentID=tmp.StudentID) between 2 and 3
--5
create view vi
as
select
Customer=CustomerID,
Address,
CustType=case CustType when 'I ' then 'important ' when 'N ' then 'normal ' end
from Customers
where CustType in( 'I ', 'N ')
[解决办法]
1、用sp_renamedb
2、select CustomerName from Customer where not exists(select * from orders where customerid=customer.customerid)
3、update a set price=b.price
from stock a,custprice b
where a.stkid=b.stkid
4、select a.studentname from student a,
(select studentid,count(*) from study
group by studentid having count(*) between 2 and 3) b
where a.studentid=b.studentid
5、create view 视图名 as select customerid customer
address,custtype=case when custtype= 'I ' then 'important '
when custtype= 'N ' then 'normal ' end
from customers
