题目3：MySQL基本练习【单、多表】

题目1

有如下表：

S（sno,sname）学生表，sno为学号，sname为学生姓名；

C（cno，cname）课程表，cno为课程号，cname为课程名；

SC（sno,cno,scgrade）选课成绩表，scgrade为成绩。

使用SQL语句查询下面条件给出的信息：

1. 有两门以上（含两门）不及格课程的学生姓名及其平均成绩。
2. 既学过“语文”又学过“英语”的所有学生姓名

select s.sno,s.sname,avg(sc.scgrade) avgs from S s,SC sc
where s.sno = sc.sno and sc.scgrade<60
group by s.sno
having count(*)>1;

select sname from (
 	select sc.sno from C c,SC sc
    where c.cno = sc.cno
    and c.cname in('数学','语文')
    group by sc.sno
    having count(*)=2
)A a,S s
where a.sno = s.sno

题目2：根据下述条件正确【完美】创建表

student 学生表

属性名	数据类型	可否为空	含义
sno	varchar(20)	否	学号（主键）
sname	varchar(20)	否	学生姓名
ssex	varchar(20)	否	学生性别
sbirthday	datetime	可	学生出生年月
class	varchar(20)	可	学生所在班级

course 课程表

属性名	数据类型	可否为空	含义
cno	varchar(20)	否	课程号（主键）
cname	varchar(20)	否	课程名称
tno	varchar(20)	否	教工编号（外键）

score 成绩表

属性名	数据类型	可否为空	含义
sno	varchar(20)	否	学号（主键）
cno	varchar(20)	否	课程号（主键）
degree	decimal(4,1)	可	成绩

情况说明
主键：sno+cno

teacher 教师表

属性名	数据类型	可否为空	含义
tno	varchar(20)	否	教工编号（主键）
tname	varchar(20)	否	教工姓名
tsex	varchar(20)	否	教工性别
tbirthday	datetime	可	教工出生年月
prof	varchar(20)	可	职称
depart	varchar(20)	可	教工所在部门

create table student(
sno varchar(20)not null primary key,
sname varchar(20)not null,
ssex varchar(20)not null,
sbirthday datetime null,
class varchar(20)null
);

create table course(
cno varchar(20)null primary key,
cname varchar(20)null,
tno varchar(20)null
);

create table score(
sno varchar(20)null,
cno varchar(20)null,
degree decimal(4,1)null,
primary key(sno,cno)
);

create table teacher(
tno varchar(20) not null primary key,
tname varchar(20)not null,
tsex varchar(20)not null,
tbirthday datetime null,
prof varchar(20)null,
depart varchar(20)not null
);

题目3:根据以下信息，插入数据

student 学生表

sno	sname	ssex	sbirthday	class
108	曾华	男	1977-09-01	95033
105	匡明	男	1975-10-02	95031
107	王丽	女	1976-01-23	95033
101	李军	男	1976-02-20	95033
109	王芳	女	1975-02-10	95031
103	陆君	男	1974-06-03	95031

course 课程表

cno	cname	tno
3-105	计算机导论	825
3-245	操作系统	804
6-166	数字电路	856
9-888	高等数学	831

score 成绩表

sno	cno	degree
103	3-245	86
105	3-245	75
109	3-245	68
103	3-105	92
105	3-105	88
109	3-105	76
101	3-105	64
107	3-105	91
108	3-105	78
101	6-166	85
107	6-166	79
108	6-166	81

teacher 教师表

tno	tname	tsex	tbirthday	prof	depart
804	李诚	男	1958-12-02	副教授	计算机系
856	张旭	男	1969-03-12	讲师	电子工程系
825	王萍	女	1972-05-05	助教	计算机系
831	刘冰	女	1977-08-14	助教	电子工程系

insert into student values('108','曾华','男','1977-09-01',95033);
insert into student values('105','匡明','男','1975-10-02',95031);
insert into student values('107','王丽','女','1976-01-23',95033);
insert into student values('101','李军','男','1976-02-20',95033);
insert into student values('109','王芳','女','1975-02-10',95031);
insert into student values('103','陆军','男','1974-09-03',95031);

insert into course values('3-105','计算机导论',825);
insert into course values('3-245','操作系统',804);
insert into course values('6-166','数字电路',856);
insert into course values('9-888','高等数学'