几道问题，希望对你有用（mysql）

Question

问几道问题
1：查询和080603学生相同课程的学生名单怎么写？
我写的这个好像是至少选修了080603学生的学生名单
select  distinct stu.* from stu inner join sc  using(sid) where 
cid in ( select cid from sc where sid='080603') and sid<>'080603' group by sid having count(*) 
= (select count(cid) from sc where sid='080603') 
2：查询出来各科成绩的前两名和后两名的学生的信息（包括并排名次）
3：查询出来学生的学号，姓名，平均成绩和按照平均成绩排序
select a.*,(select count(*)+1 from t where a.avge>avge) as num from 
(select stu.sid,sname,ifnull(sum(score)/count(cid),0) as avge
from stu left join sc
on stu.sid=sc.sid
group by stu.sid)t as a这样写报错"as a "出，什么错误，应该怎么写？？
4：网上搜的一个人培训用的几道题，有点看不懂
有两表a和b，前两字段完全相同：(id int,name varchar(10)...)，都有下面的数据（当然还有其它字段，这里不列出来了）：
id          name     
----------- ----------
1          a         
2          b         
3          c         以下的查询语句，你知道它的运行结果吗？：
1.
select * from a left join b on a.id=b.id where a.id=1
2.
select * from a left join b on a.id=b.id and a.id=1
3.
select * from a left join b on a.id=b.id and b.id=1
4.
select * from a left join b on a.id=1 
5.
select * from a left join b on a.id=1 where a.id=1

Answer 1

我其实还看了一下，你要整理的话我多给你发一些材料。我网上保存了一些材料，看的进度有点慢。
这是网上的一篇帖子，我还没来的及看，你看一下。
一些高难度的SQL面试题
2008 年 11月 7 日 | 分类：数据库面试题 | 暂无评论 | 601 次阅读| 来源:Net 
1.根据如下表的查询结果，那么以下语句的结果是（知识点：not in/not exists+null）
以下的null代表真的null.
SQL> select * from usertable;
USERID USERNAME
———– —————-
1 user1
2 null
3 user3
4 null
5 user5
6 user6
SQL> select * from usergrade;
USERID USERNAME GRADE
———- —————- ———-
1 user1 90
2 null 80
7 user7 80
8 user8 90执行语句：
select count(*) from usergrade where username not in (select username from usertable);
select count(*) from usergrade g where not exists
(select null from usertable t where t.userid=g.userid and t.username=g.username);
结果为：语句1( 0 ) 语句2 ( 3 )
A: 0 B:1 C:2 D:3 E:NULL
2. 在以下的表的显示结果中，以下语句的执行结果是（知识点：in/exists+rownum）
SQL> select * from usertable;
USERID USERNAME
———– —————-
1 user1
2 user2
3 user3
4 user4
5 user5
SQL> select * from usergrade;
USERNAME GRADE
—————- ———-
user9 90
user8 80
user7 80
user2 90
user1 100
user1 80
执行语句
Select count(*) from usertable t1 where username in
(select username from usergrade t2 where rownum <=1);
Select count(*) from usertable t1 where exists
(select ‘x’ from usergrade t2 where t1.username=t2.username and rownum <=1);
以上语句的执行结果是：( ) ( )
A: 0 B: 1 C: 2 D: 3
根据以下的在不同会话与时间点的操作，判断结果是多少，其中时间T1<……
原始表记录为；
select * from emp;
EMPNO DEPTNO SALARY
—– —— ——
100 1 55
101 1 50
select * from dept;
DEPTNO SUM_OF_SALARY
—— ————-
1 105
2 
可以看到，现在因为还没有部门2的员工，所以总薪水为null，现在，
有两个不同的用户（会话）在不同的时间点（按照特定的时间顺序）执行了一系列的操作，那么在其中或最后的结果为：
time session 1 session2
———– ——————————- ———————————–
T1 insert into emp
values(102,2,60)
T2 update emp set deptno =2
where empno=100
T3 update dept set sum_of_salary =
(select sum(salary) from emp
where emp.deptno=dept.deptno)
where dept.deptno in(1,2);
T4 update dept set sum_of_salary =
(select sum(salary) from emp
where emp.deptno=dept.deptno)
where dept.deptno in(1,2);
T5 commit;
T6 select sum(salary) from emp group by deptno;
问题一：这里会话2的查询结果为：
T7 commit;
=======到这里为此，所有事务都已完成，所以以下查询与会话已没有关系========
T8 select sum(salary) from emp group by deptno;
问题二：这里查询结果为
T9 select * from dept;
问题三：这里查询的结果为
问题一的结果( ) 问题二的结果是( ) 问题三的结果是( )
A: B:
—————- —————-
1 50 1 50
2 60 2 55
C: D:
—————- —————-
1 50 1 115
2 115 2 50
E: F:
—————- —————-
1 105 1 110
2 60 2 55
有表一的查询结果如下，该表为学生成绩表（知识点：关联更新）
select id,grade from student_grade
ID GRADE
——– ———–
1 50
2 40
3 70
4 80
5 30
6 90
表二为补考成绩表
select id,grade from student_makeup
ID GRADE
——– ———–
1 60
2 80
5 60
现在有一个dba通过如下语句把补考成绩更新到成绩表中，并提交：
update student_grade s set s.grade =
(select t.grade from student_makeup t
where s.id=t.id);
commit;
请问之后查询：
select GRADE from student_grade where id = 3;结果为：
A: 0 B: 70 C: null D: 以上都不对
根据以下的在不同会话与时间点的操作，判断结果是多少，
其中时间T1<……
session1 session2
————————————– —————————————-
T1 select count(*) from t;
–显示结果(1000)条
T2 delete from t where rownum <=100;
T3 begin
delete from t where rownum <=100;
commit;
end;
/
T4 truncate table t;
T5 select count(*) from t;
–这里显示的结果是多少
A: 1000 B: 900 C: 800 D: 0
标签: 数据库, 高难度

Answer 2

1,2,3 没有表结构啊。4. 你自己试一下这些SQL语句不就知道结果了吗？

Answer 3

to ACMAIN_CHM 
我有根据题意设计的表，这是我看小梁的博客50道常见sql查询时的疑问。耽误你的时间了。非常感谢你的帮忙。
mysql> select * from sc2 order by sid,cid;
+--------+--------+-------+
| sid    | cid    | score |
+--------+--------+-------+
| 080601 | 180101 |    80 |
| 080601 | 180102 |    85 |
| 080601 | 180103 |    82 |
| 080602 | 180101 |    85 |
| 080602 | 180102 |    84 |
| 080602 | 180103 |    86 |
| 080602 | 180104 |    53 |
| 080603 | 180101 |    85 |
| 080603 | 180102 |    82 |
| 080603 | 180103 |    34 |
| 080604 | 180101 |    81 |
| 080604 | 180102 |    83 |
| 080605 | 180101 |    81 |
| 080605 | 180102 |    82 |
+--------+--------+-------+
第一道题，查询和080601选修相同课程的学生信息
select * from sc2  where 
cid in ( select cid from sc2 where sid='080601') and sid <>'080601' group by sid having count(*) 
= (select count(cid) from sc2 where sid='080601') 
080603和080601一样但是上边的写法把080602也选了出来
第二：查询至少有一门课不及格的学生名单这样怎么会有错
select sid,sname from stu where  exists (select count(*) >=1 from sc where stu.sid=sc.sid and score<60 );
这样是对的：select sid,sname from stu where  1<=(select count(*) from sc where stu.sid=sid and score<60 );
第三：查询出来180101和180102的前两名和后两名（考虑并排）
比如结果
080601   180101      80  
080605   180101      81  
080604   180101      81  
080603   180101      85  
080602   180101      85  
080605   180102      82  
080603   180102      82  
080602   180102      84  
080601   180102      85 
第四：
可作为安全机制使用。     
即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。（如果有存储过程的权限而存储过程有增加删除
那么该用户也有增加删除权限了么？？）
第四：获得每个人的平均成绩在加上其位序这样写有错
select  a.* , (select count(*)+1 from t where a.avge>avge) as num from 
 ( select sid,avg(score) avge from sc group by sid order by avge) t a，应该怎么写？？？

Answer 4

[code=BatchFile]mysql> select * from sc2;
+--------+--------+-------+
| sid    | cid    | score |
+--------+--------+-------+
| 080601 | 180101 |    80 |
| 080601 | 180102 |    85 |
| 080601 | 180103 |    82 |
| 080602 | 180101 |    85 |
| 080602 | 180102 |    84 |
| 080602 | 180103 |    86 |
| 080602 | 180104 |    53 |
| 080603 | 180101 |    85 |
| 080603 | 180102 |    82 |
| 080603 | 180103 |    34 |
| 080604 | 180101 |    81 |
| 080604 | 180102 |    83 |
| 080605 | 180101 |    81 |
| 080605 | 180102 |    82 |
+--------+--------+-------+
14 rows in set (0.00 sec)mysql>[/code]第一道题，查询和080601选修相同课程的学生信息
mysql> select distinct sid
    -> from sc2 a
    -> where not exists (select cid from sc2 where sid=a.sid and  cid not in (select cid from sc2 where sid='080601'))
    -> and not exists (select cid from sc2 where sid='080601' and cid not in (select cid from sc2 where sid=a.sid));
+--------+
| sid    |
+--------+
| 080601 |
| 080603 |
+--------+
2 rows in set (0.00 sec)mysql>

Answer 5

第二：查询至少有一门课不及格的学生名单这样怎么会有错mysql> select distinct sid
    -> from sc2
    -> where score<60;
+--------+
| sid    |
+--------+
| 080602 |
| 080603 |
+--------+
2 rows in set (0.00 sec)mysql>

Answer 6

第三：查询出来180101和180102的前两名和后两名（考虑并排）mysql> select *
    -> from sc2 a
    -> where (cid='180101' or cid='180102')
    -> and 2>(select count(*) from sc2 where cid=a.cid and score>a.score)
    -> union all
    -> select *
    -> from sc2 a
    -> where (cid='180101' or cid='180102')
    -> and 2>(select count(*) from sc2 where cid=a.cid and score<a.score)
    -> order by cid,score
    -> ;
+--------+--------+-------+
| sid    | cid    | score |
+--------+--------+-------+
| 080601 | 180101 |    80 |
| 080604 | 180101 |    81 |
| 080605 | 180101 |    81 |
| 080602 | 180101 |    85 |
| 080603 | 180101 |    85 |
| 080603 | 180102 |    82 |
| 080605 | 180102 |    82 |
| 080602 | 180102 |    84 |
| 080601 | 180102 |    85 |
+--------+--------+-------+
9 rows in set (0.00 sec)mysql>

Answer 7

第四：获得每个人的平均成绩在加上其位序这样写有错mysql> select a.sid,a.avg_score,count(*)
    -> from (
    ->  select sid,avg(score) as avg_score
    ->  from sc2
    ->  group by sid
    -> ) a, (
    ->  select sid,avg(score) as avg_score
    ->  from sc2
    ->  group by sid
    -> ) b
    -> where a.avg_score<=b.avg_score
    -> group by a.sid,a.avg_score
    -> order by a.avg_score desc;
+--------+-----------+----------+
| sid    | avg_score | count(*) |
+--------+-----------+----------+
| 080601 |   82.3333 |        1 |
| 080604 |   82.0000 |        2 |
| 080605 |   81.5000 |        3 |
| 080602 |   77.0000 |        4 |
| 080603 |   67.0000 |        5 |
+--------+-----------+----------+
5 rows in set (0.00 sec)