--1 Create third table to store relationship between student and class.
--建立三表来存储学生与课程的关系.create table student(s_id varchar(10),name varchar(20),birthday datetime)
create table class(c_id varchar(10),name varchar(20))
create table studentclass(sc_id varchar(20),s_id varchar(20),c_id varchar(20),
constraint FK_SC_s_id foreign key(s_id) references student(s_id),
constraint FK_SC_C_id foreign key(c_id) references class(c_id))
--2 Build query to calculate how many classes does student “Danny” attend.
--建立查询,查询Danny要上多少不同的课程
select count(*) from
class a where exists (select * from studentclass b where exists (select * from
student c where c.s_id=b.s_id and c.name='Danny' and a.c_id=b.c_id))--3 Build query to list all classes by name and total no of students attending each class:
--建立查询,统计课程名和学习课程的学生数
select [class-name]=a.name,[NO]=count(*) from class a,studentclass b where a.c_id=b.c_id
group by a.name--4 List all students attending class “English” who's born in 1970 or earlier.
--建立查询,查询所有学习英语课程,生日在1970或者更早的学生.select * from student a where
birthday<'1970-01-01'
and not exists (select * from class b where not exists (select * from studentclass c
where a.s_id=c.s_id and b.c_id=c.c_id))
--建立三表来存储学生与课程的关系.create table student(s_id varchar(10),name varchar(20),birthday datetime)
create table class(c_id varchar(10),name varchar(20))
create table studentclass(sc_id varchar(20),s_id varchar(20),c_id varchar(20),
constraint FK_SC_s_id foreign key(s_id) references student(s_id),
constraint FK_SC_C_id foreign key(c_id) references class(c_id))
--2 Build query to calculate how many classes does student “Danny” attend.
--建立查询,查询Danny要上多少不同的课程
select count(*) from
class a where exists (select * from studentclass b where exists (select * from
student c where c.s_id=b.s_id and c.name='Danny' and a.c_id=b.c_id))--3 Build query to list all classes by name and total no of students attending each class:
--建立查询,统计课程名和学习课程的学生数
select [class-name]=a.name,[NO]=count(*) from class a,studentclass b where a.c_id=b.c_id
group by a.name--4 List all students attending class “English” who's born in 1970 or earlier.
--建立查询,查询所有学习英语课程,生日在1970或者更早的学生.select * from student a where
birthday<'1970-01-01'
and not exists (select * from class b where not exists (select * from studentclass c
where a.s_id=c.s_id and b.c_id=c.c_id))
--建立查询,查询所有学习英语课程,生日在1970或者更早的学生.select * from student a where
birthday<='1970-12-31'
and exists (select * from studentclass b where exists
(select * from class c where c.name='English' and a.s_id=b.s_id and b.c_id=c.c_id))