Mysql练习题

现在有一教学管理系统，具体的关系模式如下：

Student (no, name, sex, birthday, class)

Teacher (no, name, sex, birthday, prof, depart)

Course (cno, cname, tno)

Score (no, cno, degree)

其中表中包含如下数据：

 

根据上面描述完成下面问题：

1.写出上述表的建表语句。

Course表：

 create table course(

    -> cno varchar(20),

    -> cname varchar(20),

-> tno int not null);

score表：

 create table score(

    -> no int,

    -> cno varchar(20),

    -> degree int);

Student表：

> create table student(

    -> no int,

    -> name varchar(20),

    -> sex varchar(20),

    -> birthday datetime not null,

    -> class int);

teacher表：

    create table teacher(

    -> no int,

    -> name varchar(20),

    -> sex varchar(20),

    -> birthday datetime not null,

    -> prof varchar(20),

    -> depart varchar(20));

2.给出相应的INSERT语句来完成题中给出数据的插入。

Course:

insert into course(cno,cname,tno) values(’3-101’,’数据库’，1);

insert into course(cno,cname,tno) values(’5-102,’数学'，3);

insert into course(cno,cname,tno) values(’3-103’,’信息系统’，4);

insert into course(cno,cname,tno) values(’3-104’,’操作系统’，6);

insert into course(cno,cname,tno) values(’3-105’,’数据结构’，4);

insert into course(cno,cname,tno) values(’3-106’,’数据处理’，5);

insert into course(cno,cname,tno) values(’4-107’,’pascal语言’，5);

insert into course(cno,cname,tno) values(’4-108’,’c++’，7）;

insert into course(cno,cname,tno) values(’4-109’,’java’，8);

insert into course(cno,cname,tno) values(’3-245’,’数据挖掘’，10);

insert into course(cno,cname,tno) values(’3-111’,’软件工程’，11);

Score:

Insert into score(no,cno,degree) values(5001，’3-105’，69);

Insert into score(no,cno,degree) values(5001，’5-102’，55);

Insert into score(no,cno,degree) values(503，’4-108’，85);

Insert into score(no,cno,degree) values(5004，’3-105’，77);

Insert into score(no,cno,degree) values(5005，’3-245’，100);

Insert into score(no,cno,degree) values(5006，’3-105’，53);

Insert into score(no,cno,degree) values(5003，’4-109’，45);

Insert into score(no,cno,degree) values(5008，’3-105’，98);

Insert into score(no,cno,degree) values(5004，’4-109’，68);

Insert into score(no,cno,degree) values(5010，’3-105’，88);

Insert into score(no,cno,degree) values(5003，’3-105’，98);

Insert into score(no,cno,degree) values(5005，’4-109’，68);

Insert into score(no,cno,degree) values(5002，’3-105’，88);

Insert into score(no,cno,degree) values(107，’3-105’，98);

Insert into score(no,cno,degree) values(108, ’4-109’，68);

Insert into score(no,cno,degree) values(109，’3-105’，88);

Insert into score(no,cno,degree) values(109，’4-109’，80);

Insert into score(no,cno,degree) values(107，’3-111’，88);

Insert into score(no,cno,degree) values(5003，’3-111’，80);

Student:

Insert into student(no,name,sex,birthday,class) values(5001，’李勇’,’男’，’1987-7-22 00:00:00’，95001);

Insert into student(no,name,sex,birthday,class) values(5002，’刘晨’,’女’，’1987-11-15 00:00:00’，95002);

Insert into student(no,name,sex,birthday,class) values(5003，’王敏’,’女’，’1987-10-05 00:00:00’，95001);

Insert into student(no,name,sex,birthday,class) values(5004，’李好尚’,’男’，’1987-9-25 00:00:00’，95003);

Insert into student(no,name,sex,birthday,class) values(5005，’李军’,’男’，’1987-7-17 00:00:00’，95004);

Insert into student(no,name,sex,birthday,class) values(5006，’范新位’,’女’，’1987-6-18 00:00:00’，95005);

Insert into student(no,name,sex,birthday,class) values(5007，’张霞东’,’女’，’1987-8-29 00:00:00’，95006);

Insert into student(no,name,sex,birthday,class) values(5008，’赵薇’,’男’，’1987-6-15 00:00:00’，95007);

Insert into student(no,name,sex,birthday,class) values(5009，’钱民将’,’女’，’1987-6-23 00:00:00’，95008);

Insert into student(no,name,sex,birthday,class) values(5010，’孙俪’,’女’，’1987-6-24 00:00:00’，95002);

Insert into student(no,name,sex,birthday,class) values(108，’赵里’,’男’，’1987-6-15 00:00:00’，95007);

Insert into student(no,name,sex,birthday,class) values(109，’丘处机’,’男’，’1987-6-23 00:00:00’，95008);

Insert into student(no,name,sex,birthday,class) values(107，’杨康’,’男’，’1987-6-24 00:00:00’，95001);

Teacher:

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’李卫’，’男 ’,’1957-11-5 00:00:00’,’教授’,’电子工程系’);

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’刘备’，’男 ’,’1967-10-9 00:00:00’,’副教授’,’math’);

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’关羽’，’男 ’,’1977-9-20 00:00:00’,’讲师’,’sc’);

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’李修’，’男 ’,’1957-6-25 00:00:00’,’教授’,’elec’);

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’诸葛亮’，’男 ’,’1977-6-15 00:00:00’,’教授’,’计算机系’);

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’殷素素’，’女 ’,’1967-1-15 00:00:00’,’副教授’,’sc’);

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’周芷若’，’女 ’,’1947-2-23 00:00:00’,’教授’,’sc’);

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’赵云’，’男 ’,’1980-6-13 00:00:00’,’副教授’,’计算机系’);

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’张敏’，’女 ’,’1985-5-05 00:00:00’,’助教’,’sc’);

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’黄蓉’，’女 ’,’1967-3-22 00:00:00’,’副教授’,’sc’);

Insert into teacher(no,name,sex,birthday,prof,dapart) values(1,’张三’，’男 ’,’1967-3-22 00:00:00’,’副教授’,’sc’);

3.以class降序输出student的所有记录（student表全部属性）

命令：select * from student ORDER BY class DESC;

4.列出教师所在的单位depart（不重复）。

命令：select DISTINCT depart from teacher;

5.列出student表中所有记录的name、sex和class列

命令： select name,sex,class from student;

6.输出student中不姓王的同学的姓名。

命令：SELECT * from student WHERE name not like '王%';

7.输出成绩为85或86或88或在60-80之间的记录（no,cno,degree）

命令：select * from score where degree=85 or degree=86 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,degree DESC;

10.输出男生人数及这些男生分布在多少个班级中 

命令： select COUNT(*),count(DISTINCT class) FROM student where sex='男';

11.列出存在有85分以上成绩的课程编号。

命令： select distinct cno from score where degree>85;

12.输出95001班级的学生人数

命令： select COUNT(*) from student where class=95001;

13.输出‘3-105’号课程的平均分

命令：select AVG(degree) from score where cno="3-105";

14.输出student中最大和最小的birthday日期值

命令：select MIN(birthday),MAX(birthday) FROM student;

15.显示95001和95004班全体学生的全部个人信息（不包括选课）。（student表全部属性）

  命令：select * from student where class=95001 OR class=95004;