面试中遇到sql,进行查询

在面试中遇到了这样的问题，不知道大家是怎么解决的，是关于sql的。

我是这么写的。

在数据库库中建这样一张表

/****** Object:  Table [dbo].[Student]    Script Date: 2009-1-15 16:46:41 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Student]
GO

/****** Object:  Table [dbo].[Student]    Script Date: 2009-1-15 16:46:41 ******/
CREATE TABLE [dbo].[Student] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [lesson] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [mark] [int] NULL
) ON [PRIMARY]
GO

里面的值：有很多的我只写了部分。

select * from Student

--1.有不及格学科学生名字
select distinct name from Student where mark<60
--2.超出一门学科不及格的学生名字
select distinct name from Student where mark<60

--3.所有学科都不及格
  ---第一种
 select distinct name from Student as s where  name in(select distinct name from Student)  and (select mark from Student where lesson= 'Chinese'  and name=s.name ) <60  and
(select mark from Student where lesson='Math' and name=s.name ) <60  and (select mark from Student where lesson='English' and name=s.name ) <60

--4.Math排名前三包括并列
 
select top 3 name, lesson,mark from Student  where lesson='Math' order by(mark)  desc

--5.总分排名前三（包括并列）
select top 3 name ,sum(mark) as total from Student group by(name) order by(total) desc

--6.列出每一科分数最高者名字及分数
 select name ,l.lesson,l.maxmark  from ( select lesson,  max(mark) as maxmark  from Student group by(lesson)) as l,Student s
where s.mark=l.maxmark and s.lesson=l.lesson

改成这样是可以的，select name from Student where mark < 60 group by name having count(name) > 1 ，刚才那个我没用mysql试过，这次保证正确。

超出一门学科不及格的学生名字
select distinct name from Student where mark<60
这个写错了

select name from Student group by name having count(name) > 1 and mark<60

--3.所有学科都不及格
这个你写的非常不好。

select name from Student group by name having count(name) > 1 and mark 1 where mark < 60