MySQL数据库原理习题---SQL连接查询

SQL连接查询

1）查询每个学生及其选课情况

Select student.*,sc.*
From student, sc
Where student.sno=sc.sno;

2）分别用左外连接和右外连接实现查询所有学生信息及其选修成绩的情况（包括没有选课的学生信息）

Select student.*,cno,grade
From student left outer join sc on (student.sno=sc.sno);

3）查询选修2号课程且成绩在90分以上的学生姓名和系别信息

Select sname,sdept 
From student,sc
Where student.sno=sc.sno and cno='002' and grade>90;

4）查询每个学生的学号、姓名、选修的课程名及成绩

Select student.sno,sname,cname,grade
From student,sc,course
Where student.sno=sc.sno and course.cno=sc.cno;

5）查询计算机系的学生所学课程的课程名。

Select distinct cname 
From course,sc,student
Where sc.sno=student.sno and sc.cno=course.cno and Student.sdept='cs';

6）查询姓张的同学学的课程的课程名称。

Select distinct cname 
From student,sc,course
Where student.sno=sc.sno and sc.cno=course.cno and sname like '张%';

7）查询至少选修两门课程的学生姓名

Select sname 
From student
Where sno in
	(Select sno 
	From sc group by sno having count(*)>=2);

或者

SELECT Sname,COUNT(SC.Sno) 
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno
GROUP BY SC.Sno HAVING count(SC.Sno)>=2;

8）查询课程平均成绩大于80的课程名称

Select cname 
From course 
Where cno in
    (Select cno 
    From sc group by cno having avg(grade)>=80);

9）查询选修人数最多的课程名称

Create view v_cnors(cno,xkrs) as 
Select cno,count(*) From sc  group by cno;
Select cname From course where cno in(
Select cno From sc group by cno 
having count(*)= (select max(xkrs) From v_cnors));

或者

SELECT Cname,COUNT(SC.Sno) AS 人数 
FROM Course,SC 
WHERE Course.Cno=SC.Cno
HAVING COUNT(SC.Cno)>= ALL (SELECT COUNT(SC.Cno) 
FROM SC GROUP BY SC.Cno);

10）查询计算机系的学生选修课程中选修人数前三名的课程名称

Select cname,count(*) 
From course,sc,student
Where student.sno=sc.sno and course.cno=sc.cno and Sdept='cs'
Group by cname  Order by count(*) desc
Limit 3;

11）查询每门课程的先修课程名称和学分信息

Select c1.cpno,c2.cname,c2.credit
From course c1,course c2
Where c1.cpno=c2.cno;

12）分别用左外连接和右外连接实现查询所有课程信息和其先修课程信息（包括没有先修课程的课程信息）

Select c1.*,c2.*
From course c1 left outer join course c2 on c1.cpno=c2.cno;

SELECT Course.*,SC.* 
FROM Course RIGHT OUTER JOIN SC ON SC.Cno=Course.Cno;

13）查询每门课程的间接先修课的信息（先修课程的先修课程）

Select c1.cno,c2.cpno 
From course c1,course c2
Where c1.cpno=c2.cno;

14）查询有不及格的学生姓名和所在系

Select sname,sdept 
From student
Where sno in (select distinct sno from sc where grade<60);

或者

SELECT DISTINCT Sname,Sdept 
FROM Student,SC 
WHERE Student.Sno=SC.Sno AND(Grade<60 or Grade IS NULL);

15）查询所有成绩为优秀（大于90分）的学生姓名

Select sname 
From student
Where sno in(select sno from sc group by sno having min(grade)>=90);

或者

SELECT DISTINCT Sname 
FROM Student,SC 
WHERE Student.Sno=SC.Sno AND(Grade>90 or Grade IS NULL);

16）增加一个表格成绩等级表，表里记录成绩等级编号和每个等级对应的最低成绩和最高成绩

Create table dengji
(id tinyint primary key,
Lowgrade int not null,
Highgrade int not null)
Insert into dengji values (1,90,100)
Insert into dengji values(2,80,89)
Insert into dengji values(3,70,79)
Insert into dengji values(4,60,69)
Insert into dengji values(5,0,59);

或者

CREATE TABLE Glevel(
ID TINYINT PRIMARY KEY,
Lowgrade INT NOT NULL,
Highgrade INT NOT NULL);
Insert INTO Glevel VALUES(1,90,100),(2,80,89),(3,70,79),(4,60,69),(5,0,59);

17）对每个成绩等级进行计数（按等级进行分组）

Select id,count(*) 
From dengji,sc
Where grade between lowgrade and highgrade
Group by id;

18）查询每个学生每个成绩等级有多少计数

Select sno,id,count(*) 
From sc,dengji
Where grade between lowgrade and highgrade
Group by sno,id;

19）查询每门课程每个成绩等级有多少计数

Select cno,id,count(*) 
From sc,dengji
Where grade between lowgrade and highgrade
Group by cno,id;

20）查询每个学生选修成绩的对应等级

Select  sno,grade,id 
From sc,dengji
Where grade between lowgrade and highgrade
Group by sno,grade,id;

21）查询选修成绩等级是5的课程名称和学生姓名

Select distinct cname,sname,sc.*
From student,course,sc,dengji
Where student.sno=sc.sno and course.cno=sc.cno and grade between lowgrade and highgrade and id=5;