INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);
#2、查询Student表的第2到第4条数据
select * from student WHERE id>=902 AND id<=904;
#3、从Student表查询所有学生的学号，姓名和院系
SELECT id,name,depatment FROM student;
#4、从Student表中查询计算机系和英语系的学生
select * from student WHERE department='计算机系'or department='英语系';
#5、从Student表中查询年龄在18~22岁的学生信息
select * from student WHERE 2019-birth>=18 and 2019-birth<=22;
#6、从student表中查询每个院系有多少人
SELECT department, COUNT(id) FROM student GROUP BY department;
#7、从Score表中查询每个科目的最高分
SELECT c_name,MAX(grade) from score GROUP BY c_name;
#8、查询李四的考试科目
select c_name,grade from score where stu_id = (SELECT id from student where name = '李四');
#9、用连接的方式查询所有学生的姓名、院系、科目和考试成绩
SELECT student.id,name,department,c_name,grade FROM student,score WHERE student.id=score.stu_id;
#10、计算每个学生的总成绩
SELECT name,student.id,SUM(grade) from student,score where score.stu_id = student.id GROUP BY id;
#11、计算每个考试科目的平均成绩
SELECT c_name,AVG(grade) from score GROUP BY c_name;
#12、查询计算机成绩低于95分的学生信息
select * FROM student WHERE id IN(SELECT stu_id FROM score where c_name = '计算机'and grade<95);
#13、查询同时参加计算机和英语考试的学生信息
SELECT * FROM STUDENT WHERE id in(select stu_id from score where stu_id in(select stu_id from score where C_NAME='计算机') and C_NAME='英语') ;
#14、将计算机考试成绩按照从高到低进行排序
SELECT C_NAME '科目',GRADE '考试成绩' FROM SCORE WHERE C_NAME='计算机' ORDER BY(GRADE) DESC;
#15、从student表和score表中查询出学生的学号，然后合并查询结果
SELECT DISTINCT NAME '姓名',ST.ID+STU_ID '学号' FROM STUDENT ST,SCORE SC WHERE ST.ID=SC.STU_ID;
#16、查询姓张或者姓王的同学的姓名、院系和考试科目以及成绩（提示，模糊查询关键字like 例如查询以A开头的姓名 selec * from 表名 where name like ‘A%’）
SELECT NAME '姓名',DEPARTMENT '院系',C_NAME '科目',GRADE '考试成绩' FROM STUDENT stu,SCORE sc WHERE stu.ID=sc.STU_ID AND NAME LIKE OR '王%';
#17、查询都是湖南的学生的姓名、年龄、院系和考试科目以及成绩
SELECT NAME '姓名',2019-BIRTH '年龄',DEPARTMENT '院系',C_NAME '科目',GRADE '考试成绩' FROM STUDENT ST,SCORE SC WHERE ST.ID=SC.STU_ID AND ADDRESS LIKE '湖南%';
