数据库练习

请创建如下表：并创建相关约束

CREATE table class(
cid int PRIMARY key,
caption varchar(10)
);
INSERT INTO class VALUES(1,'三年二班');
INSERT INTO class VALUES(2,'一年三班');
INSERT INTO class VALUES(3,'三年一班');

create table student(
sid int PRIMARY key,
sname varchar(10),
gender varchar(10),
class_id int,
FOREIGN KEY(class_id) REFERENCES class(cid)
);
insert into student VALUES(1,'张三','女',1);
insert into student VALUES(2,'李四','女',1);
insert into student VALUES(3,'王五','男',2);

create table teacher(
tid int primary key,
tname varchar(10)
);

insert into teacher VALUES(1,'马云');
insert into teacher VALUES(2,'马化腾');
insert into teacher VALUES(3,'俞敏洪');

CREATE table course(
cid int primary KEY,
cname varchar(10),
teacher_id int ,
FOREIGN KEY(teacher_id) REFERENCES teacher(tid)
);

insert into course VALUES(1,'生物',1);
insert into course VALUES(2,'体育',1);
insert into course VALUES(3,'物理',2);

create table score(
sid int PRIMARY key,
student_id int,
FOREIGN key(student_id) REFERENCES student(sid),
course_id int,
FOREIGN key(course_id) references course(cid),
number int
);
insert into score VALUES(1,1,1,58);
insert into score VALUES(2,1,2,68);
insert into score VALUES(3,2,2,89);

 

操作表：

1.查询平均成绩大于60分的同学的学号和平均成绩；

SELECT
t1.sid 学生编号,
AVG( t2.number ) 平均分
FROM
student t1
LEFT JOIN score t2 ON t1.sid = t2.student_id
GROUP BY
t1.sid
HAVING
AVG( t2.number ) > 60

 

2.查询所有同学的学号、姓名、选课数、总成绩；

SELECT
t1.sid "编号",
t1.sname "姓名",
t2.temp1 "选课数",
t2.temp2 "总分"
FROM
student t1,
( SELECT student_id, COUNT( sid ) temp1, SUM( number ) temp2 FROM score GROUP BY student_id ) t2
WHERE
t1.sid = t2.student_id;

 

3..查询姓“马”的老师的个数；

SELECT
COUNT( tid )
FROM
teacher
WHERE
tname LIKE "马%"

4.查询有课程成绩小于60分的同学的学号、姓名；

SELECT DISTINCT
t1.sid "学号",
t1.sname "姓名"
FROM
student t1
LEFT JOIN score t2 ON t2.student_id = t1.sid
WHERE
t2.number < 60;

5查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

SELECT DISTINCT
t1.student_id,
t2.sname
FROM
score t1
LEFT JOIN student t2 ON t1.student_id = t2.sid
WHERE
course_id IN ( SELECT course_id FROM score WHERE student_id = 2 )；