一公司的oracle sql面试题

Question

客户表a(id name address) 登陆流水表b(id time) 购物流水表c(id time productid productnum)
1.求每个客户的最新登陆时间time,姓名name,客户id?
2.查最新登陆并且已经购买商品的客户id,name,登陆的时间time(一条sql语句)
一个表student中有班级classid,学号id,成绩grade
1.计算各个班的平均成绩
2.查找比该班平均成绩高的学生的班级classid,学号id,成绩grade

Answer 1

1.求每个客户的最新登陆时间time,姓名name,客户id
select a.id,a.name,d.time as time 
from a left join (select id,max(time) as time from b group by id) d
on a.id =d.id ;2.查最新登陆并且已经购买商品的客户id,name,登陆的时间time(一条sql语句)
select a.id,a.name,d.time as time 
from a,(select id,max(time) as time from b group by id) d
where a.id =d.id 
and exists (select * from c where id = a.id);1.计算各个班的平均成绩
select classid,avg(grade)
from student
group by classid;2.查找比该班平均成绩高的学生的班级classid,学号id,成绩grade
select a.classid,a.id,a.grade 
from student a 
where a.grade > (select avg(grade) from student where classid = a.classid);

Answer 2

select a.id, a.name, max(b.time) from a ,b
where a.id = b.id
group by a.id, a.name
select a.id, a.name, c.time, max(b.time) from a ,b, c
where a.id = b.id
      and c.id = a.id
group by a.id, a.name, c.time 
having c.time > max(b.time)
对于
select a.id,a.name,d.time as time
from a,(select id,max(time) as time from b group by id) d
where a.id =d.id
and exists (select * from c where id = a.id);
不能保证最近一次购买了商品。应加入
and exists (select * from c where id = a.id and c.time>d.time);

Answer 3

1. select k.time,a,name,a.id  from 
 (select  id, max(time)  from  b goup  by b.id)k
, a where a.id=k.id2. 
  select  c.id,a.name,b.time  from  a,b,c
where  a.id=b.id and  b.id=c.id and b.time=max(b.time)  and c.id  not null ;

Answer 4

个人理解: 客户的登录时间(b表中的time)与购买时间(c表中的time)是不同的, 因此, 关于第一题第一问, 应该是:
select id, name, time
from (
      select a.id, a.name, b.time , 
             row_number() over (partition by a.id order by b.time) as seq
      from a
      inner join b on a.id = b.id
      left join c on a.id = c.id and b.time <= c.time
     )
where seq = 1 第一题第二问则将第一问中的left join改为inner join就可以了第二题第一问:没什么异议第二题第二问:用分析函数较好, 如下:
select classid, id, grade
from (
     select classid,id,grade,
            avg(grade) over (partition by classid order by classid, id) as avg_grade
     from student 
     )
where grade > avg_grade

Answer 5

1.
Create table scott.Customer
(
ID int primary key,
name varchar2(50),
address varchar2(100)
)Create table scott.CustomerLogin
(
ID references scott.Customer(ID),
loginTime date
)Create table scott.CustomerPurchase
(
ID references scott.Customer(ID),
purchaseTime date,
productID varchar(20),
productnum int
)1.1
select aa.ID,aa.name,bb.lastTime
from scott.Customer aa,
(
select a.ID,max(b.loginTime) lastTime
from scott.Customer a,scott.CustomerLogin b
where a.ID=b.ID
group by a.ID,b.loginTime
) bb
where aa.ID=bb.ID1.2
select aa.ID,aa.name,bb.lastTime
from scott.Customer aa,
(
select a.ID,max(b.loginTime) lastTime
from scott.Customer a,scott.CustomerLogin b
where a.ID=b.ID
group by a.ID,b.loginTime
) bb
where aa.ID=bb.ID
and exists (select ID from scott.CustomerPurchase c where aa.ID=c.ID and c.purchaseTime>bb.lastTime)2.
Create table student
(
classid int,
ID int,
grade numeric
)2.1
select classID,avg(grade)
from student
group by classID2.2
select classID,ID,grade
from student a
where grade>(select avg(grade) from student where a.classID=classID)

Answer 6

1.1--------------------select a.id,a.name,d.time time
  from a,
       (
        select b.*,row_number() over(partition by b.id order by b.time desc) rn
          from b,a
          where b.id = a.id 
       )d
  where rn = 1;1.2--------------------select a.id,a.name,d.time time
  from a,
       (
        select b.*,row_number() over(order by b.time desc) rn
               from b,a
              where exists
                    (
                    select 1
                      from c,a
                     where c.id = b.id
                       and c.id = a.id
                    )
       )d
 where rn = 1;2.1--------------------select s.classid,avg(sum(s.grade)) avg_grade
  from student s
 group by classid;2.2--------------------select c.classid,c.id,c.grade
  from (
        select s.*,avg(sum(s.grade)) avg_grade
          from student s
         group by classid
       )c
 where c.grade > c.avg_grade;

Answer 7

exits语句里的那个购物时间 写成 >= 吧
不过，也没太所谓，不可能一登陆就买了东西

Answer 8

谁叫stickyman 我压根就不认识他我的ID怎么都错乱了
我用我的id登陆后，怎么表示的是他的账号