DBA考题2(转)

表构造如下 number date 8 二〇〇八/1/11 2:00 7 2008/1/11 5:00 6 二〇一〇/1/11
12:00 5 二零零六/1/11 18:00 4 2010/1/12 4:00 3 二〇〇九/1/12 10:00 2 二零一零/1/12
12:00 1 二〇一〇/1/11 17:00 想博妥帖天的最初时间与最迟时光的number的差值,
即如下的结果: 差 2 3 复制代码 代码如下:
create table #date ( number int identity(1,1) primary key, date
datetime ) insert into #date select ‘2009/1/11 17:00’ insert into
#date select ‘2009/1/12 12:00’ insert into #date select ‘2009/1/12
10:00’ insert into #date select ‘2009/1/12 4:00’ insert into #date
select ‘2009/1/11 18:00’ insert into #date select ‘2009/1/11 12:00’
insert into #date select ‘2009/1/11 5:00’ insert into #date select
‘2009/1/11 2:00’ select (d2.number-d1.number) number from ( select
number,date from #date where date in (select max(date) from #date
group by convert(varchar(10),date,120) ) ) d1 , ( select number,date
from #date where date in (select min(date) from #date group by
convert(varchar(10),date,120) ) ) d2 where
convert(varchar(10),d1.date,120)=convert(varchar(10),d2.date,120) number
———– 2 3

一、选择题(1-25没题1分26-30每题2分共35分)
1State of Qatar假定有一个顾客表,表中带有字段:userid (intState of Qatar、username (varcharState of Qatar、
password(varchar卡塔尔(قطر‎、等,该表须求安装主键,以下说法科学的是()。(采用两项)
 
 a卡塔尔倘若不可能有同偶然间再次的username和password,那么username和password能够构成在一齐作为主键。 
 
 
 b卡塔尔此表设计主键时,遵照选用主键的最小性原则,最棒利用userid作为主键。
 
 c卡塔尔国此表设计主键时,依照接收主键的最小性原则,最棒应用username和password作为组合键。
   dState of Qatar 若是利用userid作为主键,那么在userid列输入的数值,允许为空。

此刻假如有的时候表中有双重数据,无论是主键字段businessid有再次,照旧一整行有再度都会报出违背独一主键限制错误。

2卡塔尔Microsoft SQL Server 二〇〇〇在安装的时候就创办的示范顾客数据库包蕴()。(选拔两项)
   a)Pubs
   b)Master   
   c)Northwind
   d)Msdb
3卡塔尔国以下()语句从表TABLE_NAME中领取前10条记下。(选取一项)
   a)select * from TABLE_NAME where rowcount=10  
   b)select TOP 10 * from TABLE_NAME  
   c)select TOP of 10 * from TABLE_NAME
   d)select * from TABLE_NAME where rowcount<=10
4卡塔尔(قطر‎从“付加物”表里查询出价格超越产物名称叫“叁次性单耳杯”的出品的笔录,此SQL语句为()。(选拔一项)
   a)SELECT * FROM 产物WHERE 价格>‘叁次性塑料杯’
   b)SELECT * FROM 产品WHERE 价格>(SELECT * FROM 付加物WHERE
成品名称>’ 叁回性单耳杯’  
   c)SELECT * FROM 付加物WHERE EXISTS 产物名称=’ 一次性高脚杯’  
   d)SELECT * FROM 产物WHERE 价格>(SELECT 价格FROM 产物WHERE
成品名称=’ 壹次性保温杯’
5卡塔尔查找
student表中具有电话号码(列名:telephone卡塔尔国的第一人为8或6,第三个人为0的电话号码()。(选择一项)
   a)SELECT telephone FROM student WHERE telephone LIKE ‘[8,6]%0*’
   b)SELECT telephone FROM student WHERE telephone LIKE ‘(8,6)*0%’
   c)SELECT telephone FROM student WHERE telephone LIKE ‘[8,6]_0%’ 
 
   d)SELECT telephone FROM student WHERE telephone LIKE ‘[8,6]_0*’

方法:group by XX having count(*)>1,rowid,distinct,temporary
table,procedure

6卡塔尔(قطر‎现成表book,字段:id (int卡塔尔,title (varchar卡塔尔(قطر‎, price (floatState of Qatar;
此中id字段设为标志,
使用insert语句向book表中插入数据,以下语句错误的是()。(采用一项)
   a)insert into book (id,title,price) values(1,’java’,100)  
   b)insert into book (title,price) values(‘java’,100)
   c)insert into book values (‘java’,100)
   d)insert book values(‘java’,100)

1、查询表中的重复数据
a.重复二个字段

7State of Qatar现存表Employee,字段:id (int卡塔尔(قطر‎,firstname(varchar卡塔尔, lastname(varchar卡塔尔;
以下sql语句错误的是()。(接收一项)
   a)select firstname+’.’+lastname as ‘name’ from employee )
   b)select firstname+’.’+lastname=’name’ from employee
   c)select ‘name’=firstname+’.’+lastname from employee
   d)select firstname,lastname from employee

b.重复四个字段

8卡塔尔在sql server 2003中,关于数据库说法以白为黑的是()。(选用一项)
   aState of Qatar数据库在磁盘上暗中认可的仓库储存地点是:SQL Server安装路径\Microsoft SQL
Server\MSSQL\Data
 
 bState of Qatar叁个数据库起码应包蕴一个数据库文件(*.mdf)和一个业务日志文件(*.ldf)
   c卡塔尔唯有当数据库中不设有数据的景况下,才得以开展数据库的减少操作。
 
 dState of Qatar能够透过从A机器拷贝数据库文件和作业日志文件至B机器,然后通过在B机器上实行相应的附加数据库操作,达成数据库从A机器到B机器的复制。

c.重复一整行

9State of Qatar若要删除book表中存有数据,以下语句错误的是()。(选取两项)
   a)truncate table book  
   b)delete * from book  
   c)drop table book
   d)delete from book

创制测验表:

10State of Qatar学子战表表grade中有字段score(float),将来要把持有在55分至60中间的分数进步5分,以下sql语句准确的是()。(选拔两项)
   a)Update grade set score=score+5
   b)Update grade set score=score+5 where score>=55 or score
<=60
   c)Update grade set score=score+5 where score between 55 and 60  
   d)Update grade set score=score+5 where score >=55 and score
<=60

复制代码 代码如下:
create table cfa (businessid number,customer varchar2(50),branchcode
varchar2(10),data_date varchar2(10));
insert into cfa values (1,’Albert’,’SCB’,’2011-11-11′);
insert into cfa values (2,’Andy’,’DB’,’2011-11-12′);
insert into cfa values (3,’Allen’,’HSBC’,’2011-11-13′);

11卡塔尔(قطر‎现存书目表book,包括字段:price (float卡塔尔国;
今后查询一条书价最高的书目标详细新闻,以下语句精确的是()。(选拔两项)
   a)select top 1 * from book order by price asc   
   b)select top 1 * from book order by price desc   
   c)select top 1 * from book where price= (select max (price)from
book)
   d)select top 1 * from book where price= max(price)

—————以下为重新数据———————————————-
insert into cfa values (1,’Alex’,’ICBC’,’2011-11-14′);
insert into cfa values (1,’Albert’,’CTBK’,’2011-11-15′);
insert into cfa values (1,’Albert’,’SCB’,’2011-11-11′);

12卡塔尔现存书目表book,包罗字段:价格price (float卡塔尔,体系type(charState of Qatar;
以后查询各类品类的平平均价格值、系列称号,以下语句正确的是()。(选择一项)
   a)select avg(price),type from book group by type
   b)select count(price),type from book group by price  
   c)select avg(price),type from book group by price
   d)select count (price),type from book group by type

对于a的情况,只有businessid重复

13State of Qatar查询student表中的全部非空email音讯,
以下语句正确的是()。(选取一项)
   a)Select email from student where email !=null
   b)Select email from student where email not is null  
   c)Select email from student where email <> null
   d)Select email from student where email is not null

复制代码 代码如下:
select * from cfa where businessid in (select businessid from cfa group
by businessid having count(businessid)>1);

14State of Qatar战绩表grade中字段score代表分数,以下()语句重回战绩表中的最低分。(选择两项)
   a)select max(score) from grade
   b)select top 1 score from grade order by score asc
   c)Select min(score) from grade   
   d)select top 1 score from grade order by score desc

若是是b之处,businessid 和name相同的时间存在双重
复制代码 代码如下:
select * from cfa where (businessid,customer) in (select
businessid,customer from cfa group by businessid,customer having
count(*)>1);

15State of Qatar现存订单表orders,包罗客户音讯userid, 产物新闻 productid,
以下()语句能够回到至少被预定过五次的productid? (选择一项)
   a)select productid from orders where count(productid)>1
   b)select productid from orders where max(productid)>1   
   c)select productid from orders where having count(productid)>1
group by productid_  
   d)select productid from orders group by productid having
count(productid)>1

对此c的景况,重复一整行

16卡塔尔关于聚合函数,以下说法颠倒阴阳的是()。(选择一项)
   a卡塔尔Sum重临表明式中全数数的总合,由此必须要用来数字类型的列。
   b卡塔尔国Avg重临表明式中全部数的平均值,可以用来数字型和日期型的列。
   cState of QatarMax和Min能够用来字符型的列。  
   d卡塔尔Count能够用来字符型的列。

参考b的方法:
复制代码 代码如下:
select * from cfa where (businessid,customer,branchcode,data_date) in
(select * from cfa group by businessid,customer,branchcode,data_date
having count(*)>1);

17State of Qatar使用以下()不得以开展模糊查询。(选拔一项)
   a)OR
   b)Not between  
   c)Not IN  
   d)Like

2、删除表中的再次数据
a情形,删除表中多余的重复记录,重复记录是依照单个字段(businessid)来剖断,只留有rowid最小的笔录

18State of Qatar关于多表联接查询,以下()描述是错误的。(选用一项)
   a卡塔尔(قطر‎外联接查询重回的结果集行数恐怕超越全数切合联接条件的结果集行数。
   bState of Qatar多表联接查询必需利用到JOIN关键字
   c卡塔尔内对接查询重临的结果是:全数相符联接条件的数额。
   dState of Qatar在where子句中钦命联接条件得以兑现内连接查询。

也足以只保留rowid不是纤维记录,供给把代码中的min改为max这里不再赘言。

19)Sql语句:select * from students where SNO like
‘010[^0]%[A,B,C]%’,大概会询问出的SNO是()。(接收两项)
   a)01053090A #Hm3?
   b)01003090A01
   c)01053090D09  
   d)0101A01

复制代码 代码如下:
delete from cfa
where businessid in (select businessid
from cfa
group by businessid
having count(businessid) > 1)
and rowid not in (select min(rowid)
from cfa
group by businessid
having count(businessid) > 1);

20卡塔尔关于Truncate table, 以下()描述是谬误的。(选取两项)
   a卡塔尔Truncate table 可跟Where从句,依据准则举行删减。
   b卡塔尔(قطر‎Truncate table 用来删除表中全部数据。
   cState of Qatar触发器对Truncate table无效。
   d)delete 比Truncate table速度快。

大概,使用上边更简便高效的言语