SQL面试题

1

我想把当前正在运着的MSSQLServer中的一个数据库改名,请问怎么办(不能删除数据库)


2
customer(客户)的表结构
customeris nchar(5) not null primary key,
customerName nvarchar(50) not null,
contactname 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 unll,
requiredate datetime unll,
shippeddate datetime null,
primary key(orderid,customerid)


customer与orders的关系如下
customer(1)-----orders(0.....n)
请写一个SQL语句,列出没有订单的客户的名称



3
A.study的表结构
studentid varchar(10)not null foreign key references student(studentid) 学生代号
couseid varchar(4) not null 课程代号
B.student的表结构
studentID varchar(10) not null,
studenName varchar(20) not null
写一个SQL语句,找出修2-3门的学生的名称
select S.studenname from B.student where studentid in
(select E.studentid from a.study as E where )

4
A.Stock的表结构
StkID varchar(10) not null primary key,--(商品编号)
CName varchar(50) not null,--(品名)
Price numerric(12,2) null --(商品价格)
B.CustPrice的表结构
CustID varchar(5) not null,
StkID varchar(10) not null foreign key refreences Stock(StkID)
Price numeric(12,2) not null,
Primary key (CustID,StkID)
Stock与CustPrice的关系为
Stock(1)------------>CustPrice(1)
写一个SQL语句,用CustPrice中的price更新Stock的Price
5
Customers的表结构如下:
CustomerID nchar(5) not null Primary key,
CompanyName nvarchar(40) not unll,
contactName nvarchar(30) null,
contactTitle nvarchar(30) null,
CustType nvarchar(1) null,
Address nvarchar(60) null,
City nvarchar(15) null,
Region nvarchar(15) null,
PostalCode nvarchar(10) null,
Country nvarchar(15) null,
Phone nvarchar(24) null,
Fax nvarchar(24) null,
请创建一视图,要求:
A.需要CustomerID,address,CustType列
B.CustomerID的列名为Customer
C.CustType的取值范围的
  实际值   显示值
  'I'   -- important,
  'N' --- normal,
请显示CustType列的显示值,而不是实际值


6.(optional/可选的)
A.Salary(薪水)
EmpID varchar(10) not null,
DepartID varchar(5) not null foreign key references department(DepartID),
Salary numeric(12) null
B.Department(部门)
DepartID varchar(5),
DepartName varchar(50)
C.Employee(雇员)
EmpID varchar(10) not null primary key,
EmpName varchar(20) not null
写一个SQL 语句,找出薪水为第三的雇员 的名称


上机题
1,写一个存储过程,把输入的一个参数(类型varchar(4000))的按每行40个字符进行分解,并且不能把整个字分开,最后返回一个一个字段的结果集.
A.不考虑中文问题
B.不用考虑一个字的长度大于每行的最大长度问题
例:str(varchar(4000))->"brand are among the most trusted names in business"
按每行10个字符,则分解结果为
brand are
among the
most
trusted
names in
business

---------------------------------------------------答案-----------------------------------------------------

1.
use master
go
EXEC sp_renamedb 'old_dbname','new_dbname'
go

2.
select * from customer where customerid not in(select customerid from orders)
go

3.
select studenName from student where studentID in
(select studentID from study group by studentID having (count(studentID)>=2 and count(studentID)<=3))
go

4.
Update stock set price=b.price
From stock a, (select price from custprice ) b
Where a.stkid=b.stkid
go

5.
create view view_name
As
select CustomerID as Customer ,address,(case CustType when 'N' then 'normal'  when 'I' then 'important' end )as CustType from Customers
Where custtype in('I', 'N')
go

 

 

 

---个人的一些解法^_^
-------------------------------------------------------------------------------
1---
use master
go
EXEC sp_renamedb 'old_dbname','new_dbname'
go
-------------------------------------------------------------------------------
2.
select * from customer where customerid not in(select customerid from orders)
-------------------------------------------------------------------------------
3.
select a.* from  student a ,study b group by b.studentid having count(b.studentid) between 2 and 3
-------------------------------------------------------------------------------
4.

        Update a
        set a.Price=b.Price
        from Stock a,CustPrice b
        where a.StkID=b.StkID
-------------------------------------------------------------------------------
5.
IF EXISTS(select 1 from dbo.sysobjects where id = object_id(N'[dbo].[cvm_customers]')
        and XTYPE='V')
drop TABLE [dbo].[cvm_customers]
GO
create view cvm_customers

as
        select   customerid as customer
                ,address
                ,case when CustType='I' then 'important'
                      when CustType='n' then 'normal'  end  as CustType
        from customers

go
-------------------------------------------------------------------------------
6.

select A.* from
                Employee A , Salary B
        where       B.Salary in (select min(a.Salary) AS  Salary from (select top 3*  from Salary order by Salary desc) a )
                AND A.EmpID=B.EmpID
-------------------------------------------------------------------------------
7.
if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[eSP_Select]')
        and XTYPE='p')
drop TABLE [dbo].[eSP_Select]
GO

create  proc eSP_Select
        @str varchar(4000)
as

begin
        create table #temp_select (string varchar(200))
        delete from #temp_select

declare @a varchar(200)

while  charindex(' ',@str)<>0
begin
        if charindex(' ',@str)<=10
        begin       
                set @a=null
                set @a = left(@Str,charindex(' ',@Str))
                set @Str = right(@Str,len(@Str)-charindex(' ',@Str))
                set @a= @a+ left(@str,charindex(' ',@str))
                insert into #temp_select select rtrim(@a)
                set @Str=right(@Str,len(@Str)-charindex(' ',@Str))
               
                if charindex(' ',@str)=0
                begin
                        insert into #temp_select select @Str
                end
        end
end

        select * from #temp_select
       
        drop table #temp_select

end

GO

EXEC eSP_Select 'brand are among the most trusted names in business'

GO