数据库面试题(七)

16、求出小于45岁的各个老师所带的大于12岁的学生人数

数据库中有3个表 teacher 表，student表，tea_stu关系表。
teacher 表 teaID name age
student 表 stuID name age
teacher_student表 teaID stuID
要求用一条sql查询出这样的结果
1.显示的字段要有老师name, age 每个老师所带的学生人数
2 只列出老师age为40以下，学生age为12以上的记录

预备知识：

1.sql语句是对每一条记录依次处理，条件为真则执行动作（select,insert,delete,update）

2.只要是迪卡尔积，就会产生“垃圾”信息，所以，只要迪卡尔积了，我们首先就要想到清除“垃圾”信息

实验准备：

drop table if exists tea_stu;

drop table if exists teacher;

drop table if exists student;

create table teacher(teaID int primarykey,name varchar(50),age int);

create table student(stuID int primarykey,name varchar(50),age int);

create table tea_stu(teaID int referencesteacher(teaID),stuID int references student(stuID));

insert into teacher values(1,'zxx',45),(2,'lhm',25) , (3,'wzg',26) , (4,'tg',27);

insert into student values(1,'wy',11),(2,'dh',25) , (3,'ysq',26) , (4,'mxc',27);

insert into tea_stu values(1,1), (1,2),(1,3);

insert into tea_stu values(2,2), (2,3),(2,4);

insert into tea_stu values(3,3), (3,4),(3,1);

insert into tea_stu values(4,4), (4,1),(4,2) , (4,3);

结果：2à3,3à2,4à3

解题思路：（真实面试答题时，也要写出每个分析步骤，如果纸张不够，就找别人要）

1要会统计分组信息，统计信息放在中间表中：

select teaid,count(*) from tea_stu group by teaid;

2接着其实应该是筛除掉小于12岁的学生，然后再进行统计，中间表必须与student关联才能得到12岁以下学生和把该学生记录从中间表中剔除，代码是：

select tea_stu.teaid,count(*) total from student,tea_stu where student.stuid=tea_stu.stuidand student.age>12 group by tea_stu.teaid

3.接着把上面的结果做成虚表与teacher进行关联，并筛除大于45的老师

select teacher.teaid,teacher.name,total from teacher ,(select tea_stu.teaid,count(*)total from student,tea_stu where student.stuid=tea_stu.stuid and student.age>12group by tea_stu.teaid) as tea_stu2 where teacher.teaid=tea_stu2.teaid and teacher.age<45;

17.求出发帖最多的人：

select authorid,count(*) total from articles

group by authorid

having total=

(select max(total2) from (select count(*) total2 from articles group by authorid) as t);

select t.authorid,max(t.total) from

（select authorid,count(*) total from articles ）as t

这条语句不行，因为max只有一列，不能与其他列混淆。

select authorid,count(*) total from articles

group by authorid having total=max(total)也不行。

18、一个用户表中有一个积分字段，假如数据库中有100多万个用户，若要在每年第一天凌晨将积分清零，你将考虑什么，你将想什么办法解决?

alter table drop column score;

alter table add colunm score int;

可能会很快，但是需要试验，试验不能拿真实的环境来操刀，并且要注意，

这样的操作时无法回滚的，在我的印象中，只有inert update delete等DML语句才能回滚，

对于create table,drop table ,alter table等DDL语句是不能回滚。

解决方案一，update user set score=0;

解决方案二，假设上面的代码要执行好长时间，超出我们的容忍范围，那我就alter table user drop column score;alter table user add column score int。

下面代码实现每年的那个凌晨时刻进行清零。

Runnable runnable =

      new Runnable(){

             public void run(){

                    clearDb();

                    schedule(this,new Date(new Date().getYear()+1,0,0));

             }            

      };

schedule(runnable,

      new Date(new Date().getYear()+1,0,1));

19、一个用户具有多个角色，请查询出该表中具有该用户的所有角色的其他用户。

select count(*) as num,tb.id

from  tb, (select role from tb where id=xxx) as t1

where tb.role = t1.role and tb.id != t1.id

group by tb.id

having num = select count(role) from tb where id=xxx;

20. xxx公司的sql面试

Table EMPLOYEES Structure:

EMPLOYEE_ID      NUMBER        Primary Key,

FIRST_NAME       VARCHAR2(25),

LAST_NAME       VARCHAR2(25),

Salary number(8,2),

HiredDate DATE,

Departmentid number(2)

Table Departments Structure:

Departmentid number(2)        Primary Key,

DepartmentName  VARCHAR2(25).

(2）基于上述EMPLOYEES表写出查询：写出雇用日期在今年的，或者工资在[1000,2000]之间的，或者员工姓名（last_name）以’Obama’打头的所有员工，列出这些员工的全部个人信息。（4分）

select * from employees

where Year(hiredDate) = Year(date()) or (salary between 1000 and 200) or left(last_name,3)='abc';

(3) 基于上述EMPLOYEES表写出查询：查出部门平均工资大于1800元的部门的所有员工，列出这些员工的全部个人信息。（4分）

mysql> select id,name,salary,deptid did from employee1 where (select avg(salary)

from employee1 where deptid = did) > 1800;

(4) 基于上述EMPLOYEES表写出查询：查出个人工资高于其所在部门平均工资的员工，列出这些员工的全部个人信息及该员工工资高出部门平均工资百分比。（5分）

select employee1.*,(employee1.salary-t.avgSalary)*100/employee1.salary

from employee1,

      (select deptid,avg(salary) avgSalary from employee1 group by deptid) as t

where employee1.deptid = t.deptid and employee1.salary>t.avgSalary;

21、注册Jdbc驱动程序的三种方式

本文链接：领悟书生教程网-面试题大全，转载请注明出处