数据库面试常用测试题

 

数据库面试常用测试题 ( SQL Server

题目 1

问题描述 :

建立 3 个表 为管理岗位业务培训信息。:

SN, S S#.SD,SA S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

CN C#, C C#.CN 分别代表课程编号、课程名称

C#, SC S#.G S#,C#,G 分别代表学号、所选修的课程编号、学习成果

1. 使用规范 SQL 嵌套语句查询选修课程名称为 ’ 税收基础 ’ 学员学号和姓名

-- 实现代码 :

SD FROM Select SN.S

Where [S#] IN

S Select [S#] FROM C.C

Where C.[C#]=SC.[C#]

AND CN=N' 税收基础 '

2. 使用规范 SQL 嵌套语句查询选修课程编号为 ’ C2 学员姓名和所属单位

-- 实现代码 :

S.SD FROM S, Select S.SN.SC

Where S.[S#]=SC.[S#]

AND SC.[C#]='C2'

3. 使用规范 SQL 嵌套语句查询不选修课程编号为 ’ C5 学员姓名和所属单位

-- 实现代码 :

SD FROM Select SN.S

Where [S#] NOT IN

Select [S#] FROM SC

Where [C#]='C5'

4. 使用规范 SQL 嵌套语句查询选修全部课程的学员姓名和所属单位

-- 实现代码 :

SD FROM Select SN.S

Where [S#] IN

Select [S#] FROM SC

RIGHT JOIN

C ON SC.[C#]=C.[C#] GROUP BY [S#]

HA VING COUNT * =COUNT [S#]

5. 查询选修了课程的学员人数

-- 实现代码 :

Select 学员人数 =COUNT DISTINCT [S#] FROM SC

6. 查询选修课程超过 5 门的学员学号和所属单位

-- 实现代码 :

SD FROM Select SN.S

Where [S#] IN

Select [S#] FROM SC

GROUP BY [S#]

HA VING COUNT DISTINCT [C#] >5

题目 2

问题描述 :

已知关系模式 :

SNA ME 为姓名 S SNO,SNA ME 学生关系。 SNO 为学号。

CNA ME 为课程名， CTEA CHER 为任课教师 C CNO,CNA ME,CTEA CHER 课程关系。 CNO 为课程号。

CNO, SC SNO.SCGRA DE 选课关系。 SCGRA DE 为成绩

1. 找出没有选修过 “ 李明 ” 老师讲授课程的所有学生姓名

-- 实现代码 :

Select SNA ME FROM S

Where NOT EXISTS

Select * FROM SC.C

Where SC.CNO=C.CNO

AND CNA ME=' 李明 '

AND SC.SNO=S.SNO

2. 列出有二门以上 ( 含两门 ) 不及格课程的学生姓名及其平均成果

-- 实现代码 :

S.SNA ME, Select S.SNO.A VG_SCGRA DE=A VG SC.SCGRA DE

SC, FROM S.

Select SNO

FROM SC

Where SCGRA DE<60

GROUP BY SNO

HA VING COUNT DISTINCT CNO >=2

A Where S.SNO=A .SNO AND SC.SNO=A .SNO

S.SNA M GROUP BY S.SNO.E

又学过 “ 2 号课程的所有学生姓名 3. 列出既学过 “ 1 号课程。

-- 实现代码 :

S.SNA M Select S.SNO.E

FROM S.

Select SC.SNO

FROM SC.C

Where SC.CNO=C.CNO

'2' AND C.CNA ME IN '1'.

GROUP BY SNO

HA VING COUNT DISTINCT CNO =2

SC Where S.SNO=SC.SNO

4. 列出 “ 1 号课成绩比 “ 2 号同学该门课成绩高的所有学生的学号

-- 实现代码 :

S.SNA M Select S.SNO.E

FROM S.

Select SC1.SNO

C C1, FROM SC SC1.SC SC2,C C2

Where SC1.CNO=C1.CNO AND C1.NA ME='1'

AND SC2.CNO=C2.CNO AND C2.NA ME='2'

AND SC1.SCGRA DE>SC2.SCGRA DE

SC Where S.SNO=SC.SNO

5. 列出 “ 1 号课成绩比 “ 2 号课成绩高的所有学生的学号及其 “ 1 号课和 “ 2 号课的成果

-- 实现代码 :

S.SNA ME, Select S.SNO.SC.[1 号课成绩 ],SC.[2 号课成绩 ]

FROM S.

[1 号课成绩 ]=SC1.SCGRA DE, Select SC1.SNO.[2 号课成绩 ]=SC2.SCGRA DE

C C1, FROM SC SC1.SC SC2,C C2

Where SC1.CNO=C1.CNO AND C1.NA ME='1'

AND SC2.CNO=C2.CNO AND C2.NA ME='2'

AND SC1.SCGRA DE>SC2.SCGRA DE

SC Where S.SNO=SC.SNO

  某数据集团数据库初试口试题 ( 数据库面试 口试题 )

1 Which statement show the maximum salari paid in each job categori of each department?_______

job_cat,A . select dept_id.max salari from employe where salari > max salari ;

job_cat,B. select dept_id.max salari from employe group by dept_id,job_cat;

job_cat,C. select dept_id.max salari from employees;

job_cat,D. select dept_id.max salari from employe group by dept_id;

job_cat,E. select dept_id.max salari from employe group by dept_id,job_cat,salary;

2 descript of the student table:

sid_id number

start_d date

end_d date

which two function ar valid on the start_dat column?_________

A . sum start_dat

B. avg start_dat

C. count start_dat

end_d D. avg start_date.

E. min start_dat

F. maximum start_dat

3 for which two constraint doe the oracl server implicitli creat a uniqu index?______

A . not null

B. primary

C. foreign key

D. check

E. unique

wher is the group by claus place in the select statement?______ 4 in a select statement that includ a where clause.

A . immedi after the select clause

B. befor the where clause

C. befor the from clause

D. after the order by clause

E. after the where clause

wher is the order by claus place in the select statement?______5 in a select statement that includ a where clause..

A .immedi after the select clause

B.befor the where clause

C.aft all clause

D.aft the where clause

E.befor the from clause

6 evalu there two sql statements______.

salari from employe order by salarySelect last_name.;

salari from employe order by 2 ascSelect last_name.;

A .th same result B.differ result C.the second statement return a syntax error

7 you would like to displai the system date in the format 20051110 14 44 17 Which select statement should you use?______

yearmmdd hh:mm:ss from dualA . select to_dat sydate.;

yearmonthdai hh:mi:ss from dualB. select to_char sydate.;

yyyymmdd hh24:mi:ss from dualC. select to_dat sydate.;

yyyymmdd hh24:mi:ss from dualD. select to_char sydate.;

yy-mm-dd hh24:mi:ss from dualE. select to_char sydate.;

8 which select statement will the result ello world from the string Hello world ?______.

1 from dualA . select substr Hello World .;

1,B. select substr trim Hello World .1 from dual;

1 from dualC. select lower substr Hello World .;

D. select lower trim H from Hello World from dual;

9 which ar DML statement choos all that appli ______.

A .commit B.merg < type="text/javascript">google_ad_cli = "pub-4475724770859924";google_alternate_color = "FFBBE8";google_ad_width = 468;google_ad_height = 60;google_ad_format = "468x60_as";google_ad_typ = "text_image";google_ad_channel ="9379930647";google_color_bord = "F8F8F8";google_color_bg = "FFFFFF";google_color_link = "FF6FCF";google_color_url = "38B63C";google_color_text = "B3B3B3"; < src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> C.updat D.delet E.creat F.drop

10 Select 语句中用来连接字符串的符号是 ______.

A . + B. & C. || D. |