一些sqll測試題

表：A01(人員基本情況表)和A32(職務資訊表)  
  A01(主表)結構  
  欄位名稱 資料型別 備註  
  ========================================  
  Z0001 int 主關鍵字  
  a0101 varchar 姓名  
  a0102 varchar 地址  
  a0103 int 性別  
  a0104 char 出生年月  
   
  A32(子表)結構  
  Z0001 int 關鍵字(外來鍵)  
  a0105 varchar 所在職務  
  a0106 char 上任日期  
  b0001 varchar 職務單位  
問題（3）查詢出職務變更最頻繁的人，顯示出此人的姓名  
  提示：例如A01和A32中有如下資料時，職務變更最頻繁的是人是“魏新”  
  A01表中資料  
  z0001 a0101 a0102 a0103 a0104  
  10000 魏新 男 黑龍江 19800110  
  10001 林強 男 內蒙古 19801221  
  a32表中資料  
  z0001 a0105 a0106 b0001  
  10000 科長 19990110 A單位  
  10000 處長 20000123 B單位  
  10001 科長 19980213 E單位  
  10000 局長 20021104 C單位  

 

create table  A01(Z0001 int primary key,a0101 varchar,a0102 varchar,a0103 int,
a0104 char)

create table A32(Z0001 int foreign key references A01(Z0001) ,a0105 varchar, a0106 char,b0001 varchar)

insert into A01 values(10000, '魏新' ,'黑龍江','1' ,'19800110')
insert into A01 values(10001, '林強' ,'內蒙古','1' ,'19801221')

insert into A32 values(10000, '科長' ,'19990110' ,'A單位')
insert into A32 values(10000, '處長' ,'20000123' ,'B單位')
insert into A32 values(10000, '科長' ,'19980213' ,'E單位')
insert into A32 values(10000, '局長' ,'20021104' ,'C單位')
---問題（3）查詢出職務變更最頻繁的人，顯示出此人的姓名  

SELECT * FROM A01
WHERE Z0001=(
SELECT TOP 1 Z0001 FROM A32  GROUP BY Z0001 ORDER BY COUNT(*) DESC
)

 第二題、現有一個表table1只有一個欄位F1,表中資料如下：  
  Z0001  
  0  
  1  
  2  
  3  
  4  
  5  
  6  
  7  
  8  
  9  
  問題：用現有的資料用一個SQL語句構造出一個1-9999的結果集，類似如下：  
  1  
  2  
   
  ...   ...  
  9999 

select   
convert(int,convert(char(1),a.name)+
convert(char(1),b.name)+
convert(char(1),c.name)+
convert(char(1),d.name))+1   as   cc 
 from   dbo.test   a,dbo.test   b,dbo.test   c,dbo.test   d   order   by   cc  
 
----------------------------------------------
WITH CTE AS(
SELECT Name+1 AS N  from  dbo.test
UNION all
SELECT N+10 AS N FROM CTE WHERE N+10<=9999)

SELECT * FROM CTE order by N OPTION (MAXrecursion 0)
第三題  
  如下表(T2)為某班級某一學期的數學成績：  
  學號 成績  
  199801001 85  
  199801002 76  
  199801003 98  
  199801004 54  
  199801005 39  
  199801006 61  
  問題：請根據t2表的內容輸出如下兩行資料，要求SQL語句最簡。（60分以上為及格）  
  類別 人數  
  ============  
  及格 4  
  不及格 2  

-------------------

select '及格' as [類別],count(*)as [人數] from Student where [成績]>=60
union
select '不及格'as [類別],count(*)as [人數] from Student where [成績]<60
 第四題  
  有如下表T記錄人員的情況：  
  /*create   table   t_t  
  (RenID   INT   PRiMARY   KEY,  
  RenMing   CHAR(8),  
  RenParentID   INT   REFERENCES   t_t(RenID))*/  
  =======================================  
  RenID RenMing RenParentID  
  2 張一 10  
  3 張二 10  
  5 王一 11  
  8 王二 11  
  10 張_父 15  
  11 王_父 NULL  
  15 張_父_父 16  
  16 NULL  
  問題：  
  1、選擇出所有沒有父的人，注意查詢效能。  
select * from dbo.bonuses where ParentID IS NULL
  2、選擇出所有人名中含有“_”的人。  
SELECT * FROM dbo.bonuses WHERE NAME LIKE '%[_]%'
  3、選擇出“張父”的子。 
SELECT * FROM  dbo.bonuses  WHERE ParentID=(SELECT EMPLOYEE_ID FROM dbo.bonuses WHERE NAME='張_父')
  4、選擇出有“孫子”的人。  
WITH C(EMPLOYEE_ID,name,ParentID,LVL,sort) AS
(SELECT *,0 AS LVL,convert(nvarchar,NAME+'--&gt')  FROM dbo.bonuses WHERE ParentID IS NULL
 UNION ALL
SELECT A.*,LVL+1 ,convert(nvarchar,(C.sort + A.name+'--&gt')) as sort FROM dbo.bonuses A JOIN  C ON C.EMPLOYEE_ID=A.ParentID
)
SELECT DISTINCT left(sort,charindex('-',sort)-1) AS NAME FROM C WHERE LVL=2
-------------
select  * from dbo.bonuses where EMPLOYEE_ID in
(select ParentID FROM dbo.bonuses WHERE  EMPLOYEE_ID IN (SELECT ParentID FROM dbo.bonuses ))
----------------
SELECT * FROM dbo.bonuses WHERE NAME LIKE '%_父_父%'
  5、選擇出如下結果：  
  專案 張 王  
  人數 4 3

select '人數' as [專案], sum(case  when name like '%張%' then 1 else 0 end) as [張],
sum(case  when name like '%王%' then 1 else 0 end) as [王]
 from  dbo.bonuses
-------
  6、選擇出如下結果：  
  專案 人數  
  王 3  
  張 4  

select '王' as [專案],count(*) as [人數] from dbo.bonuses  where name like '%王%'
union
select '張' as [專案],count(*) as [人數] from dbo.bonuses  where name like '%張%'
  7、將T表中的RenID從1~8排列，不要打亂現有表中資料的關係。 
  select   (select   count(*)   from  dbo.bonuses   where   EMPLOYEE_ID<=tem.EMPLOYEE_ID)   rowid,*   from   dbo.bonuses   tem  
-------------------
select row_number() over (order by EMPLOYEE_ID) AS NUM,* from dbo.bonuses
  8、選擇出“張父”的子（一列一行），兩個人名中用“，”相隔。
select name=
stuff(replace(replace((select name  from   dbo.bonuses  where ParentID=10 for xml auto),'',''),1,1,'')
  9、編寫一儲存過程可以列出給定RenID的所有子（包括子的子）。\
create proc getChildren
(@ID int)
AS
begin

select * from dbo.bonuses where ParentID=@ID
union
select B.* from dbo.bonuses  A join  dbo.bonuses  B ON A.EMPLOYEE_ID=B.ParentID
WHERE A.ParentID=@ID

end

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7899089/viewspace-611317/，如需轉載，請註明出處，否則將追究法律責任。