现有三张表分别用于记录学生信息（学号、姓名、出生日期、性别）

[问答题]

现有三张表分别用于记录学生信息（学号、姓名、出生日期、性别）、选课信息（课程编号、课程名称）、成绩信息（学号、课程编号、成绩），请给出这三张表的建表语句，并写出查找选修课程数大于等于3门且平均分数大于等于85分的学生信息的SQL语句。

kakkddfsf

-- create table course(
-- courseid int(10) not null,
-- course_name varchar(10) not null,
-- primary key(courseid)
-- );
--
-- create table grade (
-- id int(10) not null primary key, #这样也是可以的啊
-- courseid int(10) not null ,
-- grade float(25)
-- )

-- SELECT id , studentname,COUNT(courseid) course_sum,AVG(grade) avg_grade
-- from grade ,student
-- -- LEFT JOIN student on id = studentid
-- WHERE id = studentid
-- GROUP BY id;
-- HAVING course_sum > 2 and avg_grade > 85

-- SELECT id , studentname,COUNT(courseid) course_sum,AVG(grade) avg_grade
-- from grade
-- LEFT JOIN student on id = studentid
-- GROUP BY id;
-- HAVING course_sum > 2 and avg_grade > 85

发表于 2019-04-20 23:48:31 回复(0)

小熊Honey

create table student(

'id' varchar(20) NOT NULL Primary key,

'name' varchar(20) NOT NULL,

'birthday' date DEFAULT NULL,

'sex' int DEFAULT 0

)

create table classes(

'class_id' varchar(20) NOT NULL Primary key,

'class_name' varchar(20) NOT NULL

)

create table scores(

'id' varchar(20) NOT NULL Primary key,

'class_id' varchar(20) NOT NULL ,

'score' int default 0

)

select * from student s where s.id in (select t.id from (select id,count(*) as choosen_classes,avg(score) as avg_score from scores group by id ) t where t.choosen_classes>=3 and t.avg_score >=85) t2

发表于 2019-05-02 23:45:35 回复(0)