【数据库助理工程师----------面试笔试题目】100分

Answer 1

create database dbname;
create table YGB(
id int not null auto_increment primary key,
leaderlevel  int,
leadername  varchar(25),);

Answer 2

触发器和函数不会啊触发器：
CREATETRIGGER dbo.test_a ON a
    FOR INSERT, DELETE, UPDATE  --什么操作触发触发器
AS
    BEGIN
        UPDATE  b --触发器要实行的语句
        SET     productname = ( SELECT TOP 1
                                        productname
                                FROM    Inserted --这个是插入的数据，当然删除的也有deleted，看成临时表即可
                              ) 
    END
GO
函数：
CREATE FUNCTION dbo.f_test
( 
      @userId INT  --函数参数
)
RETURNS DECIMAL(20, 2)
AS
    BEGIN
       
        DECLARE @SumAll DECIMAL(20, 2) = 0 --返回值
        --写相应的sql语句这里
        RETURN ISNULL(@SumAll,0);
    END
GO

Answer 3

CREATE TABLE YGB (
ID INT NOT NULL identity (1,1) PRIMARY KEY,
NAME VARCHAR(20),
LEADERLEVEL INTEGER,
LEADE_NAME VARCHAR(20),
position varchar(20))SELECT * FROM YGB
INSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position)
 VALUES('张三',1,'','总经理')
,('李四',2,'张三','部门经理')
,('王五',2,'张三','部门经理')
,('赵六',3,'李四','职员')
,('周七',3,'李四','见习职员')
,('何八',3,'王五','见习职员')
GO
CREATE TRIGGER YGB_SAFETY ON YGB
    Instead of INSERT
    AS
    BEGIN
    DECLARE @NUM INT;
    BEGIN
    SET NOCOUNT ON;
    SELECT @NUM=(SELECT COUNT(0) FROM YGB  GROUP BY LEADE_NAME);
    IF(@NUM>3) BEGIN
    PRINT('对不起，您已超过公司3个下属的规定');
    end ;
    END;
    END ;
    
    iNSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position) VALUES
('何久',3,'李四','见习职员')
,('何久1',3,'李四','见习职员') GO   
 CREATE FUNCTION [dbo].[FIND_LEADER] (@NAME VARCHAR(20))
 RETURNS  @re TABLE(
id int IDENTITY(1,1),LEADE_NAME VARCHAR(20), LEADERLEVEL1 varchar(10),position1 varchar(20))
 AS
 BEGIN
DECLARE @pos VARCHAR='',@PP VARCHAR='';
SELECT @pos=@NAME;
SELECT @PP= LEADE_NAME FROM YGB WHERE NAME=@NAME;
WHILE LEN(@PP)>0 
BEGIN
 INSERT INTO @re(LEADE_NAME,LEADERLEVEL1,position1) SELECT NAME,LEADERLEVEL,position FROM YGB WHERE NAME=@PP;
 SELECT @pos=@PP;
 SELECT @PP=LEADE_NAME FROM YGB WHERE NAME=@pos;
 END;
 RETURN;
 END
 
 SELECT * FROM  TEST.dbo.FIND_LEADER('周七')
这个函数 我试了 不知道为啥返回不了结果。

Answer 4

CREATE FUNCTION [dbo].[FIND_LEADER] (@NAME VARCHAR(20))
 RETURNS  @re TABLE(
id int IDENTITY(1,1),LEADE_NAME VARCHAR(20), LEADERLEVEL1 varchar(10),position1 varchar(20))
 AS
 BEGIN
;
WITH CTE
AS
(SELECT NAME,LEADE_NAME,LEADERLEVEL,POSITION,0 AS LEVEL FROM YGB WHERE NAME=@NAME
 UNION ALL
 SELECT A.NAME,A.LEADE_NAME,A.LEADERLEVEL,A.POSITION,LEVEL+1 
 FROM YGB A
 JOIN CTE B ON A.NAME=B.LEADE_NAME)
 
 INSERT INTO @re(LEADE_NAME,LEADERLEVEL1,position1)
 SELECT NAME,LEADERLEVEL,POSITION 
 FROM CTE
 WHERE LEVEL<>0
 
 RETURN;
 END

Answer 5

函数 里面能用cet 表做递归吗？ 没试过这种

Answer 6

 SELECT * FROM  TEST.dbo.FIND_LEADER('赵六')

Answer 7

go
create table ZWB(
ID int identity(1,1)  primary key,
Position varchar(10) unique not null,
LEADERLEVEL int not null )create table YGB(
ID int identity(1,1) primary key,
name varchar(10) not null,
ZWid int not null foreign key references ZWB(id)
)
create table leaderB(
YGid int primary key foreign key references YGB(ID),
LDid int foreign key references YGB(ID) 
)
go
insert into YGB(name,ZWid)
 VALUES
('张三',1)
,('李四',2)
,('王五',2)
,('赵六',3)
,('周七',3)
,('何八',3)
insert into leaderB(YGid,LDid)
values
(2,1),(3,1),(4,2),(5,2),(6,2)
go
create trigger leaderB_safety on leaderB  for insert
as
begin
declare @ct int;
declare @inserteldid int;
declare @inserteygid int;
begin
select @inserteygid=ygid from leaderB
select @inserteldid=ldid from leaderB
select @ct=(select COUNT(*) from leaderB where LDid=@inserteldid group by LDid)
if(@ct>3)
begin
print('对不起，已超过限制')
delete from leaderB where YGid=@inserteygid
end
end
end
go
GO
create  function [dbo].[find_leaders](@id int)returns table
 as return
(with cte(id,ldid) as
(select * from leaderB where leaderB.YGid=@id
union all
select cte.ldid,leaderb.ldid from cte,leaderb where cte.ldid=leaderb.ygid
)
select a.id,b.name,a.ldid,c.name as leadername from cte as a  inner join YGB as B on a.id=b.ID inner join YGB as c on a.ldid=c.ID )

Answer 8

触发器部分改成
create trigger leaderB_safety on leaderB  for insert
as
begin
declare @ct int;
declare @inserteldid int;
declare @inserteygid int;
begin
select @inserteygid=ygid from inserted
select @inserteldid=ldid from inserted
select @ct=(select COUNT(*) from inserted where LDid=@inserteldid group by LDid)
if(@ct>3)
begin
print('对不起，已超过限制')
delete from leaderB where YGid=@inserteygid
end
end
end
go

Answer 9

CREATE TABLE YGB (
ID INT NOT NULL identity (1,1) PRIMARY KEY,
NAME VARCHAR(20),
LEADERLEVEL INTEGER,
LEADE_NAME VARCHAR(20),
position varchar(20))
 
SELECT * FROM YGB
INSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position)
 VALUES('张三',2,'','总经理')
,('李四',1,'张三','部门经理')
,('王五',1,'张三','部门经理')
,('赵六',0,'李四','职员')
,('周七',0,'李四','见习职员')
,('何八',0,'王五','见习职员')
 TRUNCATE TABLE YGB
 
GO
ALTER TRIGGER YGB_SAFETY ON YGB
    Instead of INSERT
    AS
    BEGIN
    DECLARE @NUM INT;
    BEGIN
    SET NOCOUNT ON;
SELECT *,CASE  WHEN ISNULL((SELECT SUM(cnt) FROM (SELECT COUNT(*) AS cnt FROM YGB b WHERE a.LEADE_NAME = b.LEADE_NAME UNION ALL SELECT COUNT(*) FROM INSERTED c WHERE a.LEADE_NAME = c.LEADE_NAME )x),0)>3 THEN 0 ELSE 1 END  isok
INTO #tmp_check FROM INSERTED a

INSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position) 
SELECT NAME,LEADERLEVEL,LEADE_NAME,POSITION FROM #tmp_check WHERE  isok = 1
    END;
    END ;
 
     
    iNSERT INTO YGB(NAME,LEADERLEVEL,LEADE_NAME,position) VALUES
('何久',3,'李四','见习职员')
,('何久1',3,'李四','见习职员')
 
 GO   
 ALTER FUNCTION [dbo].[FIND_LEADER] (@NAME VARCHAR(20))
 RETURNS  @re TABLE(
        id int IDENTITY(1,1),LEADE_NAME VARCHAR(20), LEADERLEVEL1 varchar(10),position1 varchar(20))
 AS
 BEGIN
 
  WITH emp AS (
SELECT b.NAME,b.LEADERLEVEL,b.position FROM YGB a JOIN YGB b ON a.LEADE_NAME = b.name WHERE a.NAME='周七' UNION ALL 
SELECT c.NAME,c.LEADERLEVEL,c.position FROM emp a JOIN YGB b  ON a.NAME = b.name JOIN YGB c ON b.LEADE_NAME = c.name
)
INSERT into @re(LEADE_NAME,LEADERLEVEL1,position1)
SELECT * FROM emp
RETURN 
END
  
 SELECT * FROM   dbo.FIND_LEADER('周七')