--数据库题目
--学生表
CREATE TABLE [students] (
[stu_id] [int] IDENTITY (1, 1) NOT NULL ,--学生
[stu_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--学生姓名
CONSTRAINT [PK_students] PRIMARY KEY CLUSTERED
(
[stu_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--课程表
CREATE TABLE [courses] (
[cs_id] [int] IDENTITY (1, 1) NOT NULL ,--课程ID
[cs_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--课程名
CONSTRAINT [PK_courses] PRIMARY KEY CLUSTERED
(
[cs_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--成绩表
CREATE TABLE [grade] (
[grade_id] [int] IDENTITY (1, 1) NOT NULL ,--id
[grade_result] [float] NULL ,--分数
[stu_id] [int] NULL ,-- 学生ID
[cs_id] [int] NULL ,--课程ID
CONSTRAINT [PK_grade] PRIMARY KEY CLUSTERED
(
[grade_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_grade_courses] FOREIGN KEY
(
[cs_id]
) REFERENCES [courses] (
[cs_id]
),
CONSTRAINT [FK_grade_students] FOREIGN KEY
(
[stu_id]
) REFERENCES [students] (
[stu_id]
)
) ON [PRIMARY]
GO
insert into students values('张三')
insert into students values('李四')
insert into students values('王武')
insert into students values('赵谦')
insert into students values('钱六')
select * from students
insert into courses values('语文')
insert into courses values('外语')
insert into courses values('数学')
insert into courses values('化学')
select * from courses
insert into grade values(90,1,1)
insert into grade values(56,2,2)
insert into grade values(90,3,3)
insert into grade values(60,1,3)
insert into grade values(70,4,2)
insert into grade values(90,1,2)
insert into grade values(90,3,1)
insert into grade values(90,4,1)
insert into grade values(80,1,3)
insert into grade values(80,2,4)
select * from grade
--题目要求:
--注意：要求数据库中的数据改变时（例如：新添加了一门科目，同时新添加 了一个学生的情况），查询语句仍然是有效的！同时，不能使用变量！
--（没有参加考试的学员成绩表里没有）
--1.查询所有参加了考试的学员的姓名，并按降序排列姓名
--2.查询考试了3门科目（包含只考了3门科目的）学生的姓名
--3.查询出 参加了考试的学员的姓名（学生表）、科目（课程表）、考试分数（分数表）（最少用两种不同的方式）
--4..查找出即参加了语文考试，又参加了外语考试的学生的姓名
--1小题
select stu_Name from students where stu_id in(select stu_id from grade) order by stu_name desc
--2小题
select stu_Name from students where stu_id in(select stu_id from grade group by stu_id having count(cs_id)>=3)
--3小题
---方法一
select stu_Name,cs_Name,grade_result from students
inner join grade on students.stu_id= grade.stu_id
inner join courses on courses.cs_id=grade.cs_id
---方法二
select stu_Name,cs_Name,grade_result from students,courses,grade where students.stu_id= grade.stu_id and courses.cs_id=grade.cs_id
--4小题
select stu_name
from students
where stu_id in (select stu_id
from (select stu_id,grade.cs_id
from courses,grade
where courses.cs_id = grade.cs_id and courses.cs_Name in ('语文', '外语')) as a
group by stu_id
having count(*) > 1)
1. select stu_name from students where stu_id in (select stu_id from grade) order by stu_name
2.select s.stu_name from students s,grade g,courses c where s.stu_id=g.stu_id and g.cs_id=c.cs_id
group by s.stu_name having count(s.stu_name)>2
3.1select s.stu_name,c.cs_name,g.grade_result from students s , courses c , grade g
where s.stu_id=g.stu_id and g.cs_id =c.cs_id order by c.cs_name
3.2select s.stu_name,c.cs_name,g.grade_result from students s join grade g
on(s.stu_id=g.stu_id) join courses c on(g.cs_id =c.cs_id) order by stu_name
--distinct()重复的数据只显示一条
4.select distinct(s.stu_name) from students s join grade g
on(s.stu_id=g.stu_id) join courses c on(g.cs_id =c.cs_id) where c.cs_name='外语' or c.cs_name='语文'
今天第二次帮人做作业。。。:-)
--1.查询所有参加了考试的学员的姓名，并按降序排列姓名
select stu_name from students where stu_id in (select stu_id from grade);
--2.查询考试了3门科目（包含只考了3门科目的）学生的姓名
select stu_name
from students
where stu_id in
(select stu_id from grade group by stu_id having count(*) > 2);
--3.查询出 参加了考试的学员的姓名（学生表）、科目（课程表）、考试分数（分数表）（最少用两种不同的方式）
select stu_name, cs_Name, grade_result
from grade, courses, students
where grade.stu_id = students.stu_id
and grade.cs_id = courses.cs_id;
select stu_name, cs_Name, grade_result
from students a,
(select cs_Name, grade_result, stu_id
from (from grade, courses where grade.cs_id = courses.cs_id)) b
where a.stu_id = b.stu_id
--4..查找出即参加了语文考试，又参加了外语考试的学生的姓名
select stu_name
from students
where stu_id in (select stu_id
from (select stu_id, b.cs_id
from courses a, grade b
where a.cs_id = b.cs_id
and c.cs_Name in ('语文', '外语'))
group by stu_id
having count(*) > 1)
1、
select * from students where stu_id in (select distinct stu_id from grade) order by stu_name desc
2、
select * from students where stu_id in (select stu_id from grade group by stu_id having count(stu_id )>=3)
3、
select a.grade_result,b.cs_Name,c.stu_name from grade a,courses b,students c where a.stu_id=c.stu_id and a.cs_id=b.cs_id
select a.grade_result,b.cs_Name,c.stu_name from grade a
left outer join courses b on a.stu_id=c.cs_id
left outer join students c on a.cs_id=c.stu_id
1. select [stu_name] from [students] where [stu_id] in (select [stu_id] from [grade])