create table student( uid string, name string, sex string, age string, xi string ) row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile; load data local inpath '/my_student.txt' into table student; create table course( cid string, cname string ) row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile; load data local inpath '/my_course.txt' into table course; create table score1( uid string, cid string, score string ) row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile; load data local inpath '/my_score.txt' into table score1; 表的关联(两张表关联) 查询选修了课程的学生姓名 计算数据库课程的学生平均成绩(谁选择了数据库课程,选择的那个人的平均成绩,并且按照成绩进行排序) 1.根据课程查询课程id 2.课程id查询学生id 3.根据学生们查询出来这些学生的所有成绩 4.按照学生分组求出每个学生的平均分数 5.order by score desc 查询选修操作系统课程的学生最高分数和人 1.根据操作系统查询出来课程id 2.根据课程id查询出来所有的学生和分数 3.查询出来所有分数中的最高分 4.谁的分数和最高分是一样的 5.求出这个人 求各个课程名及相应的选课人数 查询选修了4门以上的课程的学生姓名
my_course.txt
1,数据库 2,数学 3,信息系统 4,操作系统 5,数据结构 6,数据处理
my_score.txt
95001,1,81 95001,2,85 95001,3,88 95001,4,70 95002,2,90 95002,3,80 95002,4,71 95002,5,60 95003,1,82 95003,3,90 95003,5,100 95004,1,80 95004,2,92 95004,4,91 95004,5,70 95005,1,70 95005,2,92 95005,3,99 95005,6,87 95006,1,72 95006,2,62 95006,3,100 95006,4,59 95006,5,60 95006,6,98 95007,3,68 95007,4,91 95007,5,94 95007,6,78 95008,1,98 95008,3,89 95008,6,91 95009,2,81 95009,4,89 95009,6,100 95010,2,98 95010,5,90 95010,6,80 95011,1,81 95011,2,91 95011,3,81 95011,4,86 95012,1,81 95012,3,78 95012,4,85 95012,6,98 95013,1,98 95013,2,58 95013,4,88 95013,5,93 95014,1,91 95014,2,100 95014,4,98 95015,1,91 95015,3,59 95015,4,100 95015,6,95 95016,1,92 95016,2,99 95016,4,82 95017,4,82 95017,5,100 95017,6,58 95018,1,95 95018,2,100 95018,3,67 95018,4,78 95019,1,77 95019,2,90 95019,3,91 95019,4,67 95019,5,87 95020,1,66 95020,2,99 95020,5,93 95021,2,93 95021,5,91 95021,6,99 95022,3,69 95022,4,93 95022,5,82 95022,6,100
my_student.txt
95001,李勇,男,20,CS 95002,刘晨,女,19,IS 95003,王敏,女,22,MA 95004,张立,男,19,IS 95005,刘刚,男,18,MA 95006,孙庆,男,23,CS 95007,易思玲,女,19,MA 95008,李娜,女,18,CS 95009,梦圆圆,女,18,MA 95010,孔小涛,男,19,CS 95011,包小柏,男,18,MA 95012,孙花,女,20,CS 95013,冯伟,男,21,CS 95014,王小丽,女,19,CS 95015,王君,男,18,MA 95016,钱国,男,21,MA 95017,王风娟,女,18,IS 95018,王一,女,19,IS 95019,邢小丽,女,19,IS 95020,赵钱,男,21,IS 95021,周二,男,17,MA 95022,郑明,男,20,MA