netsbeans中如导入war文件

[jiang5495]

 

/*20、查询如下课程平均成绩和及格率的百分数(用"1行"显示):
 企业管理（001），马克思（002），OO¨ （003），数据库（004） */


 SELECT
 SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分 ,
100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 ,
SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分 
        ,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数  ,
--SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)
SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 
        ,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数 
        ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 
        ,100* SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC 



--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
  
   SELECT t.C# AS 课程号,
   max(course.Cname)AS 课程名,
   isnull(AVG(score),0) AS 平均成绩 ,
   100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 
   FROM SC T,Course 
   where t.C#=course.C# 
   GROUP BY t.C# 
   ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 


 
select sc.c# as 课程号,
max(course.cname) as 课程名,
isnull(avg(score),0) as 平均成绩,
100*sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) as 及格百分数
from sc,course
where sc.c#=course.c#
group by  sc.c#
order by  100*sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*)  desc

--18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
select c# as 课程ID,max(score)as 量高分,min(score)as 最低分
from sc group by c#;



--题号18查询结果有误
SELECT distinct L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分 
    FROM SC L ,SC AS R 
    WHERE L.C# = R.C# and 
        L.score = (SELECT MAX(IL.score) 
                      FROM SC AS IL,Student AS IM 
                      WHERE L.C# = IL.C# and IM.S#=IL.S# 
                      GROUP BY IL.C#) 



/*17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，
按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 */

select s# as 学生ID,
 (select score from sc where sc.s#=s1.s# and sc.c#='004') as 数据库,
 (select score from sc where sc.s#=s1.s# and sc.c#='001') as 企业管理,
 (select score from sc where sc.s#=s1.s# and sc.c#='003') as 英语,
  count(distinct c#) as 有效课程数,
  avg(s1.score) as 平均成绩
 from sc s1
 group by s#
 order by avg(s1.score) desc; 

 
--题号17,有小错误，其查出的企业管理成绩有误
SELECT S# as 学生ID ,
(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库,
( SELECT score FROM SC WHERE SC.S# = t.S# AND C# = ' 001 ' ) AS 企业管理 ,
( SELECT score FROM SC WHERE SC.S# = t.S# AND C# = ' 003 ' ) AS 英语 , 
COUNT ( * ) AS 有效课程数, 
AVG (t.score) AS 平均成绩  
FROM SC AS t 
 GROUP BY S#  
 ORDER BY avg(t.score)  desc



--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select s#,sname from student
where s#<>'1002'
and not exists
(select * from sc scx
 where scx.s#=student.s#
 and not exists
 (select * from sc scy
  where scy.c#=scx.c#
  and  scy.s#='1002'))
and not exists
(select * from sc scx
 where scx.s#='1002'
 and not exists
  (select * from sc scy
    where scy.c#=scx.c#
    and scy.s#=student.s#));

 
--题号14，书上所给答案有误
 select S# from SC where C# in (select C# from SC where S#='1002') 
    group by S# having count ( * ) = ( select count ( * ) from SC where S# = ' 1002 ' );



--12、查询至少学过学号为“1001”同学所有一门课的其他同学学号和姓名； 
select s#,sname from student
where s# in
(select s# from sc where c#
  in(select c# from sc where s#='1001'));

SELECT distinct SC.S#,Sname  

    FROM Student,SC  

    WHERE Student.S#=SC.S# AND C# in (SELECT C# FROM SC WHERE S#='1001');  



--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名； 

select s#,sname from student
where exists
(select * from sc scx where s#=student.s#
 and exists
 (select * from sc where s#='1001' and c#=scx.c#));
  
--题号11,所上所给答案有小错误 
select distinct student.s#,sname from student,sc where student.s#=sc.s#
and c# in
(select c# from sc where s#='1001');

-- 10、查询没有学全所有课的同学的学号、姓名； 
 
select s#,sname from student
where exists
(select * from course
 where not exists
 (select c# from sc where s#=student.s# and c#=course.c#));


select Student.S#,Student.Sname 
    from Student,SC 
    where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 



/*  9   */
select s#,sname from student
where exists
(select * from sc
 where sc.s#=student.s#
 and sc.score<60);


/* 题号 9 些题所给答案有误 
select S#,Sname 
  from Student 
  where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 

*/


/*  8  */
select student.s#,sname from student,
sc scx,sc scy
where student.s#=scx.s#
and student.s#=scy.s#
and scx.c#='001'
and scy.c#='002'
and scx.score>scy.score;



Select S#,Sname from 
(select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 
  from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;



/*  7   */
select s#,sname from student
where not exists
(select course.c# from course,teacher
  where course .t#=teacher.t#
  and teacher.tname='叶平'
  and not exists
  (select * from sc 
   where sc.s#=student.s#
   and sc.c#=course.c#));


select S#,Sname 
  from Student 
  where S# in 
(select S# from SC ,Course ,Teacher where SC.C#=Course.C# 
and Teacher.T#=Course.T# 
and Teacher.Tname='叶平' 
group by S# 
having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T# = Course.T# and Tname = ' 叶平 ' ));



/*  6(1)  */ 
select distinct scx.s#,sname from student,sc scx,sc ,sc scy
 where student.s#=scx.s#
 and student.s#=scy.s#
 and scx.c#='001'
 and scy.c#='002'; 

/*  6(2)  */
select s#,sname from student
where s# in(select s# from sc where sc.c#='001')
and s# in(select s# from sc where sc.c#='002');

select Student.S#,Student.Sname from Student,SC 
where Student.S#=SC.S# and SC.C#='001'
and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002'); 


/*  5(1)  */
select s#,sname from student
where s# not in 
(select s# from sc
 where c# not in
  (select c# from course
   where t# not in
    (select t# from teacher
      where tname='叶平')));
    

/* 5(2)  */
select s#,sname from student
 where not exists
 (select sc.c# from sc,course,teacher
   where sc.c#=course.c#
   and course.t#=teacher.t#
   and teacher.tname='叶平' 
   and sc.s#=student.s#);


 
/*select sc.s# from sc,course,teacher
where sc.c#=course.c#
and course.t#=teacher.t#
and teacher.tname='叶平' ;*/
 

 



 select  Student.S#,Student.Sname 
    from Student  
    where S# not in (select distinct( SC.S#) from SC,Course,Teacher
    where  SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平'); 



/* 4 */
select count(distinct tname) from teacher
where tname like '李%';

 select count(distinct(Tname)) 
  from Teacher 
  where Tname like '李%'; 

/* 3 */
select student.s#,student.sname,count(student.s#) as'选课数',sum(score)as'最高分'
from sc,student
where sc.s#=student.s#
group by student.s#,sname;

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


/* 2 */
select s#,avg(score)
from sc
group by s#
having avg(score)>60;

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


/* 1(1)  */
select student.s# from student,sc scx,sc scy 
where student.s#=scx.s#
and  student.s#=scy.s#
and scx.c#='001'
and scy.c#='002'
and scx.score>scy.score;

/* 1(2) */
select scx.s# from sc scx
where scx.c#='001'
and exists
(select *from sc scy
 where scy.c#='002'
 and scy.s#=scx.s#
 and scx.score>scy.score);


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



select a.s# from 
(select s#,score from sc where c#='001')a,
(select s#,score from sc where c#='002')b
where a.score>b.score and a.s#=b.s#;

netsbeans中如导入war文件