数据库练习一

数据库sql查询语句练习 1-16题
– 1. 建表语句

 CREATE TABLE `course` (
  `cno` varchar(10) DEFAULT NULL,
  `cname` varchar(20) DEFAULT NULL,
  `tno` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `score` (
  `no` int(5) DEFAULT NULL,
  `cno` varchar(10) DEFAULT NULL,
  `degree` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `no` int(10) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `class` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `teacher` (
  `no` int(10) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `prof` varchar(10) DEFAULT NULL,
  `depart` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

– 2. 插入语句

INSERT INTO `teacher` VALUES ('1', '李卫', '男', '1957-11-5 00:00:00', '教授','电子工程系');
INSERT INTO `teacher` VALUES ('2', '刘备', '男', '1967-10-9 00:00:00', '副教授','math');
INSERT INTO `teacher` VALUES ('3', '关羽', '男', '1977-9-20 00:00:00', '讲师','sc');
INSERT INTO `teacher` VALUES ('4', '李修', '男', '1957-6-25 00:00:00', '教授','elec');
INSERT INTO `teacher` VALUES ('5', '诸葛亮', '男', '1977-6-15 00:00:00', '教授','计算机系');
INSERT INTO `teacher` VALUES ('6', '殷索索', '女', '1917-1-5 00:00:00', '副教授','sc');
INSERT INTO `teacher` VALUES ('7', '周芷若', '女', '1947-2-23 00:00:00', '教授','sc');
INSERT INTO `teacher` VALUES ('8', '赵云', '男', '1980-6-13 00:00:00', '副教授','计算机系');
INSERT INTO `teacher` VALUES ('9', '张敏', '女', '1985-5-5 00:00:00', '助教','sc');
INSERT INTO `teacher` VALUES ('10', '黄蓉', '女', '1967-3-22 00:00:00', '副教授','sc');
INSERT INTO `teacher` VALUES ('11', '张三', '男', '1967-3-22 00:00:00', '副教授','sc');

其余略

– 3. 以class降序输出student的所有记录（student表全部属性）
select * from student order by class desc;

– 4. 列出教师所在的单位depart（不重复）。
select depart from teacher group by depart;

– 5. 列出student表中所有记录的name、sex和class列
select name,sex,class from student;

– 6. 输出student中不姓王的同学的姓名
select name from student where name not like ‘王%’;

– 7. 输出成绩为85或86或88或在60-80之间的记录（no,cno,degree）
select * from score where degree = 86 or degree = 65 or degree = 88 or degree between 60 and 80;

– 8. 输出班级为95001或性别为‘女’ 的同学（student表全部属性）
select * from student where class = 95001 or sex = ‘女’;

– 9. 以cno升序、degree降序输出score的所有记录。（score表全部属性）
select * from score order by cno asc,degree desc;

– 10. 输出男生人数及这些男生分布在多少个班级中 distinct 去重
select count(no),count(distinct class) from student where sex=‘男’;

– 11. 列出存在有85分以上成绩的课程编号。
select distinct cno from score where degree>85

– 12. 输出95001班级的学生人数
select count(no) from student where class = 95001;

– 13. 输出‘3-105’号课程的平均分
select avg(degree) from score where cno = ‘3-105’;

– 14. 输出student中最大和最小的birthday日期值
select max(birthday),min(birthday) from student;

– 15. 显示95001和95004班全体学生的全部个人信息（不包括选课）。（student表全部属性）
select * from student where class = 95001 or class = 95004;

– 16. 输出至少有5个同学选修的并以3开头的课程的课程号，课程平均分，课程最高分，课程最低分。
select cno,avg(degree),max(degree),min(degree) from score where cno like ‘3%’ having count(cno)>5;