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 的 Price4:
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列的显示值,而不是实际值
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 的 Price4:
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列的显示值,而不是实际值
sp_renamedb
更改数据库的名称。语法
sp_renamedb [ @dbname = ] 'old_name' ,
[ @newname = ] 'new_name'参数
[@dbname =] 'old_name'是数据库的当前名称。old_name 为 sysname 类型,无默认值。[@newname =] 'new_name'是数据库的新名称。new_name 必须遵循标识符规则。new_name 为 sysname 类型,无默认值。返回代码值
0(成功)或非零数字(失败)权限
只有 sysadmin 和 dbcreator 固定服务器角色的成员才能执行 sp_renamedb。
select * from Customer
where not exists(select 1 from Orders where CustomerID=tmp.CustomerID)--3
update A set A.Price=B.Price
from Stock as A, CustPrice as B
where A.StkID=B.StkID
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')
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