数据库的简单查询和连接查询语句

-- 1.查询全体学生的详细记录；
select * from student;

-- 2.求有选修课程的学生学号；
select studentid from sc;

-- 3.查询考试成绩不及格的学生学号；
select studentid from sc where grade<60;

-- 4.	查询计算机系年龄不大于21岁的 学生信息；
select * from student where department=计算机 and age <= 21; 
--或
select * from student where department=计算机 and age !> 21;

-- 5.	求选修课程C1且成绩在80～90之间的学生学号和成绩，并将成绩乘以系数0.8输出；
select studentid,grade*0.8 as gradenew
from sc 
where courseid = C1 and grade>=80 and grade<=90;
-- 或
select studentid,grade*0.8 as gradenew
from sc 
where courseid = C1 and grade between 80 and 90;

-- 6.	查询所有姓刘的学生姓名、学号和性别；
select student_name,studentid,gender 
from student 
where student_name like 刘%;

-- 7.	查询 数据库 课程的课程号以及先行课程号及课程名；
select f.courseid,s.courseid,s.course_name 
from course f,course s 
where f.course_name=数据库 and f.pre_id=s.courseid;

-- 8.	查询选修了C1课程的学生学号及其成绩，查询结果按分数降序排列；
select studentid,grade 
from sc 
where courseid=C1 
order by grade desc;

-- 9.	查询选修了C3课程的学生最高分数；
select max(grade) as 最高分
from sc 
where courseid=C3; 

-- 10.	查询每个学生学号、姓名及其选修的课程名和成绩；
select student.studentid,student.student_name,course.course_name,sc.grade 
from student,course,sc 
where student.studentid=sc.studentid and course.courseid=sc.courseid;

-- 11.	查询选修了课程C1或者选修了课程C2的学生姓名；
select distinct student.student_name 
from student,sc 
where (sc.courseid=C1 or sc.courseid=C2) and student.studentid=sc.studentid;

-- 12.	查询各个课程号以及其相应的选课人数；
select courseid,count(studentid) as 选课人数 
from sc 
group by courseid;

-- 13.	求选修C1课程且成绩为90分以上(含90分)的学生学号、姓名及成绩；
select student.studentid,student.student_name,sc.grade 
from student,sc 
where student.studentid=sc.studentid and sc.courseid=C1 and sc.grade>=90;

-- 14.	查询每一门课的间接先行课（即先行课的先行课）,显示课程号、课程名、间接课程号、间接课程名；
select f.course_name,t.course_name as 间接先行课 
from course f,course s,course t 
where f.pre_id=s.courseid and s.pre_id=t.courseid;

-- 15.	查询各个课程的课程号、最高分、最低分；
select courseid,max(grade) as 最高分,min(grade) as 最低分 
from sc 
group by courseid;

-- 16.	查询各个课程的课程号、课程名、平均分；
select course.courseid,course.course_name,avg(grade) as 平均分
from sc ,course
where sc.courseid = course.courseid
group by course.courseid,course.course_name

-- 17. 查询存在成绩不及格的课程的课程号、课程名、不及格人数
select course.courseid,course.course_name,count(*)  as 不及格人数
from sc ,course
where sc.courseid = course.courseid and sc.grade < 60
group by course.courseid,course.course_name

-- 18. 查询存在成绩不及格的课程的课程号、课程名、不及格人数，并对不及格人数进行降序排序
select course.courseid,course.course_name,count(*)  as 不及格人数
from sc ,course
where sc.courseid = course.courseid and sc.grade < 60
group by course.courseid,course.course_name
order by count(*) desc

-- 19. 查询存在十个以上学生成绩不及格的课程的课程号、课程名、不及格人数；
select course.courseid,course.course_name,count(*)  as 不及格人数
from sc ,course
where sc.courseid = course.courseid and sc.grade < 60
group by course.courseid,course.course_name
having count(*) > 10