Database之六 sql面试题整理

题目1

表结构

Student(S#,Sname,Sage,Ssex) 学生表      

S#：学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别

Course(C#,Cname,T#) 课程表                   

C#,课程编号；Cname：课程名字；T#：教师编号

SC(S#,C#,score) 成绩表                            

S#：学号；C#,课程编号；score：成绩

Teacher(T#,Tname) 教师表                        

T#：教师编号； Tname：教师名字

 

问题：

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

select a.S# from (select s#,score from SCwhere C#='001') a,(select s#,score from SC where C#='002') b  where a.score>b.score and a.s#=b.s#;

2、查询平均成绩大于60分的同学的学号和平均成绩；

select S#,avg(score) from sc group by S#having avg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩；

 select Student.S#,Student.Sname,count(SC.C#),sum(score)   from Student left Outer join SC onStudent.S#=SC.S# group by Student.S#,Sname

题目2

表内容：

2005-05-09胜

2005-05-09胜

2005-05-09负

2005-05-09负

2005-05-10胜

2005-05-10负

2005-05-10负

 

如果要生成下列结果, 该如何写sql语句?

   rq      胜 负

2005-05-092 2

2005-05-101 2

 

SELECTa.rq, a.count '胜', b.count '负'from (SELECT rq, count(shengfu) count FROM tmp WHERE shengfu='胜')a,

(SELECTrq, count(shengfu) count FROM tmp WHERE shengfu='胜')b where a.rq = b.rq

题目3

从table1,table2中取出如table3所列格式数据

月份mon 部门dep 业绩yj

-------------------------------
一月份     01      10

一月份     02      10

一月份     03      5

二月份     02      8

二月份     04      9

三月份      03     8

table2
部门dep      部门名称dname

--------------------------------
      01      国内业务一部

     02      国内业务二部

     03      国内业务三部

     04      国际业务部

table3 （result）
    部门dep   一月份     二月份     三月份

--------------------------------------
      01      10       null      null

     02     10        8        null
      03     null      5        8
      04     null      null      9

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

 

SELECTa.dept,a.yj 一月份, b.yj 二月份,c.yj 三月份

from(select dept, yj from aa WHERE mon = '1') a

LEFTJOIN (select dept, yj from aa WHERE mon = '2') b ON a.dept = b.dept

LEFTJOIN (select dept, yj from aa WHERE mon = '3') c ON b.dept = c.dept

题目4

表形式如下：

Year     Salary 

2000       1000 

2001       2000 

2002       3000 

2003       4000 

想得到如下形式的查询结果 

Year     Salary 

2000     1000 

2001     3000 

2002     6000 

2003     10000 

 

子查询：

SELECTa.year, (SELECT SUM(salary) from aa b where b.year <= a.year) from aa a

GROUPBY a.year

 

连接查询：

SELECTa.year, sum(b.salary) FROM aa a, aa b WHERE b.year >= a.year

GROUPBY a.year

 题目5

year   monthamount

1991   1     1.1

1991   2     1.2

1991   3     1.3

1991   4     1.4

1992   1     2.1

1992   2     2.2

1992   3     2.3

1992   4     2.4

查成这样一个结果

year  m1   m2   m3   m4

1991 1.1  1.2  1.3 1.4

1992 2.1  2.2  2.3 2.4

 

子查询：

SELECTyear,

(SELECTaccount from aa a1 where month=1 and a1.year = a.year) account1,

(SELECTaccount from aa a2 where month=2 and a2.year = a.year) account2,

(SELECTaccount from aa a3 where month=3 and a3.year = a.year) account3,

(SELECTaccount from aa a4 where month=4 and a4.year = a.year) account4

FROMaa a GROUP BY a.year

 

连接查询：

SELECTa1.year year, a1.account account1, a2.account account2,a3.accountaccount3,a4.account account4

FROM(select year, account from aa WHERE month=1) a1 ,

 (select year, account from aa WHERE month=2)a2 ,

 (select year, account from aa WHERE month=3)a3 ,

 (select year, account from aa WHERE month=4)a4

 

WHEREa1.year = a2.year

anda2.year = a3.year

anda3.year = a4.year

GROUPBY a1.year

 题目6

用一条SQL语句查询出每门课都大于80分的学生姓名

name   kecheng   fenshu
张三     语文       81
张三     数学       75
李四     语文       76
李四     数学       90
王五     语文       81
王五     数学       100
王五     英语       90

selectdistinct name from table where name not in (select distinct name from tablewhere fenshu<=80)

 题目7

学生表 如下:

自动编号   学号   姓名课程编号课程名称分数

1        2005001 张三 0001      数学    69

2        2005002 李四0001      数学    89

3        2005001 张三0001      数学    69

删除除了自动编号不同,其他都相同的学生冗余信息

delete tablename where 自动编号 not in(select min(自动编号) from tablenamegroup by 学号,姓名,课程编号,课程名称,分数)

题目8

有三张表,学生表S,课程C,学生课程表SC,学生可以选修多门课程,一门课程可以被多个学生选修,通过SC表关联。

1）写出建表语句；

2）写出SQL语句,查询选修了所有选修课程的学生；

3）写出SQL语句,查询选修了至少5门以上的课程的学生。

1）建表语句如下（mysql数据库）：

create table s(id integerprimary key, name varchar(20)); create table c(id integer primary key, namevarchar(20)); create table sc( sid integer references s(id), cid integerreferences c(id), primary key(sid,cid) );

2）SQL语句如下：

select stu.id, stu.namefrom s stu where (select count(*) from sc where sid=stu.id) = (select count(*)from c);

3）SQL语句如下：

select stu.id, stu.namefrom s stu where (select count(*) from sc where sid=stu.id)>=5;

题目9 CASE

表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。

 

SELECT(CASE WHEN aa>bb THEN aa ELSE bb END),(CASE WHEN bb>cc THEN bb ELSE ccEND) FROM abc

 

有一张表，里面有3个字段：语文，数学，英语。其中有1条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来：  
   大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。  
       显示格式：

      语文             数学               英语 

      及格             优秀               不及格  

 

SELECT(CASE WHEN 语文>80 THEN '优秀'WHEN 语文 > 60 THEN '及格'ELSE '不及格' END) 语文,

(CASEWHEN 数学>80 THEN '优秀'WHEN 数学 > 60 THEN '及格'ELSE '不及格' END) 数学,

(CASEWHEN 英语>80 THEN '优秀'WHEN 英语 > 60 THEN '及格'ELSE '不及格' END) 英语

FROMaa

 题目10 DATE

请取出tmp表中日期(td字段)为当天的所有记录?

select* from tmp WHERE date(dt) =  date(now());

注：日期函数：

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

 

SQL效率问题

id有唯一索引，数据表中有几百万记录，sql语句 where id=1 or id=2 or id=3 和 where id in(1,2,3) 使用in比or高。

注：关键词越少，效率越高。

 MySQL其他知识点

1. 一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15

InnoDB是把表的主键最大值放到内存里面，所以MYSQL重启之后就会丢失（MYSQL默认）

MyISAM是把表的主键最大值放到文件里面，所以MYSQL重启之后不会丢失 

 

MySQL在什么情况下容易损坏？MySQL表损坏后的主要现象是什么？数据表损坏的修复方式有哪些

1.突然断电 

2.未关闭mysql服务就关机

MySQL表损坏后的主要现象是什么？

Table ‘p’ is marked as crashed and should be repaired

数据表损坏的修复方式有哪些？

REPAIR TABLE p

4. mysql、oracle、sqlserver的默认端口是     

3306，1521，1433