数据库——新建查询，select查询

1. 查询选修1号课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列

Select sno,grade

From sc

Where cno =’1’

Order by grade DESE,sno ASC

    

2. 查询缺少了成绩的学生的学号和课程号。

       Select sno,cno

       From sc

       Where grade is null

       

3. 查询每个学生的学号，姓名，选修的课程名，成绩

     Select student.sno,sname,cname,grade

     From student,sc,course

     Where student.sno=sc.cno and sc.cno=course.cno

     

      

4. 查询每门课程的先行课程的课程名称，学分

        Select cpno,ccredit

        From course

     

5. 查询每一门课的间接先行课的课程名称

     Select first.cname

     From course first,course second

     Where first.cpno=second.cno

      

6. 查询所在系部为“MA”且选修了高等数学课程的学生姓名，年龄，性别

Select student.sname,ssex,sage

     From student,course,sc

     Where student.sno=sc.sno and course.cno=sc.cno and cname=’高等数学’

           And sdept=’MA’

     

7. 查询选修了数据结构课程，且成绩在90分以上的学生姓名，年龄

     Select student.sname,sage

     From sc,course,student

     Where sc.cno=course.cno and student.sno and cname=’数据结构’and grade>=90

      

8. 查询选修了全部课程的学生的姓名

     Select sname

     From student,sc

     Where student.sno=sc.sno

     Group by sc.sno,sname

     Having count(cno)=7

       

9. 查询至少选修了学号为“201215121”的学生所选修的全部课程的学生学号和姓名

     Select distinct sno

     From sc ,scx

     Where not exists

           (select *

             From sc,scy

             Where scy.sno=’201215121’ and

                Not exists

(select *

  From sc,scz

  Where scz.sno=scx.sno and scz.cno=scy.cno))

        

10. 查询选修了操作系统课程的学生人数

     Select count (distinct sno) as zs

     From sc,course

     Where sc.cno=course.cno and cname=’操作系统’ 

     

11. 查询选修了数据库课程的最高分，平均分

   Select max(grade) as m,avg (grade) as g

from sc,course

where sc.cno=course.cno and cname='数据库'

        

12. 查询每个学生的学号，姓名，所获得的总学分（成绩大于等于60，则获得该门课程的学分）

     select student.sno,sname,sum(ccredit)

from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno and sc.grade>=60

group by student.sno ,student.sname

 

二、

  1.找出工程项目J2使用的各种零件的名称及其数量

select pname,qty

from spj,p

where spj.pno=p.pno and jno='j2'

 

2. 找出使用上海产的零件的工程名称

         select jname

from spj,s,j

where spj.sno=s.sno and s.city='上海' and spj.jno=j.jno

        

3. 找出供应工程J1零件为红色的供应商号码

     select sno

from spj,p

where spj.pno=p.pno and p.color='红' and spj.jno='J1'

     

4. 找出没有使用天津供应商生产的红色零件的工程名称

      select distinct jname

from spj,s,p,j

where spj.sno=s.sno and spj.pno=p.pno and spj.jno=j.jno and spj.pno != all

        (select spj.pno

    from spj,s,p

 where spj.sno=s.sno and spj.pno=p.pno and s.city='天津'

       and p.color='红')

      

5. 找出提供零件种类超过了2种的供应商号码

      select spj.sno

from spj

group by sno

having count(jno)>=2

      

6.找出为3个以上的工程提供零件的供应商名称

    select s.sname

from spj,s

where spj.sno=s.sno

group by s.sname

having count(jno)>=3