SQL整理四

学生表:xstudent
+-------+------+------+----------+-------+
|(学生ID) (名字) (性别)    (生日)   (班级)
+-------+------+------+----------+-------+
|xsid     xsnm  xssex  xsbirthday  sclass 
+-------+------+------+----------+-------+
|108      曾华    男    09/01/77    95033
+-------+------+------+----------+-------+
|105      匡明    男    10/02/75    95031
+-------+------+------+----------+-------+
|107      王丽    女    01/23/76    95033
+-------+------+------+----------+-------+
|101      李军    男    02/20/76    95033 
+-------+------+------+----------+-------+
|109      王芳    女    02/10/75    95031 
+-------+------+------+----------+-------+
|103      陆军    男    06/03/74    95031
+-------+------+------+----------+-------+
sql:
+------------------------------------------------------------------+
CREATE TABLE IF NOT EXISTS `xstudent` (
  `xsid` int(11) NOT NULL auto_increment,
  `xsnm` varchar(50) default NULL,
  `xssex` tinyint(4) default NULL,
  `xsbrithday` date default '0000-00-00',
  `sclass` varchar(11) default NULL,
  PRIMARY KEY  (`xsid`)
) ENGINE=MyISAM  DEFAULT CHARSET=gbk;

INSERT INTO `xstudent` VALUES (108, '曾华', 1, '1977-06-23', '95033');
INSERT INTO `xstudent` VALUES (105, '匡明', 1, '1975-06-03', '95031');
INSERT INTO `xstudent` VALUES (107, '王丽', 0, '1976-06-17', '95033');
INSERT INTO `xstudent` VALUES (101, '李军', 1, '1976-02-22', '95033');
INSERT INTO `xstudent` VALUES (109, '王芳', 0, '1975-02-10', '95031');
INSERT INTO `xstudent` VALUES (103, '陆军', 1, '1974-03-06', '95031');
+-------------------------------------------------------------------+
组合表:score
+-------+--------+-------+
(学生ID)  (课程ID)  (成绩) 
+-------+--------+-------+
|xsid     kuid    degree 
+-------+--------+-------+
|103      3-245    84 
+-------+--------+-------+ 
|105      3-245    67 
+-------+--------+-------+ 
|109      3-245    98 
+-------+--------+-------+ 
|103      3-105    78 
+-------+--------+-------+ 
|105      3-105    91 
+-------+--------+-------+ 
|109      3-105    68 
+-------+--------+-------+ 
|101      3-105    93 
+-------+--------+-------+ 
|107      3-105    72 
+-------+--------+-------+ 
|108      3-105    66 
+-------+--------+-------+
|101      6-166    80 
+-------+--------+-------+
|107      6-166    77 
+-------+--------+-------+
|108      6-166    62 
+-------+--------+-------+
sql:
+-----------------------------------------------+
CREATE TABLE IF NOT EXISTS `score` (
  `xsid` int(11) NOT NULL,
  `kuid` varchar(20) default NULL,
  `degree` int(11) default NULL
) ENGINE=MyISAM  DEFAULT CHARSET=gbk COMMENT='组合表';

INSERT INTO `score` VALUES (103, '3-245', 84);
INSERT INTO `score` VALUES (105, '3-245', 67);
INSERT INTO `score` VALUES (109, '3-245', 98);
INSERT INTO `score` VALUES (103, '3-105', 78);
INSERT INTO `score` VALUES (105, '3-105', 91);
INSERT INTO `score` VALUES (109, '3-105', 68);
INSERT INTO `score` VALUES (101, '3-105', 93);
INSERT INTO `score` VALUES (107, '3-105', 72);
INSERT INTO `score` VALUES (108, '3-105', 66);
INSERT INTO `score` VALUES (101, '6-166', 80);
INSERT INTO `score` VALUES (107, '6-166', 77);
INSERT INTO `score` VALUES (108, '6-166', 62);
+-----------------------------------------------+
课程表:course 
+-------+--------+--------+
(课程ID)  (课名字)  (老师ID)
+-------+--------+--------+ 
|kuid     kmname     lsid 
+-------+--------+--------+
|3-105   计算机导论    825 
+-------+--------+--------+
|3-245   操作系统      804 
+-------+--------+--------+
|6-166   数字电路      856 
+-------+--------+--------+
|9-888   高等数学      831 
+-------+--------+--------+
sql:
+----------------------------------------------------+
CREATE TABLE IF NOT EXISTS `course` (
  `kuid` varchar(20) NOT NULL,
  `kmname` varchar(50) NOT NULL,
  `lsid` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='course';

INSERT INTO `course` VALUES ('3-105', '计算机导论', 825);
INSERT INTO `course` VALUES ('3-245', '操作系统', 804);
INSERT INTO `course` VALUES ('6-166', '数字电路', 856);
INSERT INTO `course` VALUES ('9-888', '高等数学', 831);
+----------------------------------------------------+
老师表:teacher 
+-------+--------+-------+-----------+------+---------+
|(老师ID)    (名字)  (性别)   (生日)     (职称)   (系) 
+-------+--------+-------+-----------+------+---------+
|lsid      lsname  lssex  lsbirthday  lsprof lsdepart
+-------+--------+-------+-----------+------+---------+
|804        李城    男     12/02/58    副教授  计算机系
+-------+--------+-------+-----------+------+---------+
|856        张旭    男     03/12/69    讲师    电子工程系
+-------+--------+-------+-----------+------+---------+  
|825        王萍    女     05/05/72    助教    计算机系
+-------+--------+-------+-----------+------+---------+ 
|831        刘冰    女     08/14/77    助教    电子工程系
+-------+--------+-------+-----------+------+---------+ 
sql:
+-------------------------------------------------------+
CREATE TABLE IF NOT EXISTS `teacher` (
  `lsname` varchar(40) default NULL,
  `lssex` tinyint(4) default NULL,
  `lsbirthday` date default '0000-00-00',
  `lsprof` varchar(20) default NULL,
  `lsdepart` varchar(25) default NULL,
  `lsid` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='教师信息表';

INSERT INTO `teacher` VALUES('李城',1,'1958-12-02','副教授','计算机系',804);
INSERT INTO `teacher` VALUES('张旭',1,'1969-12-03','讲师','电子工程系',856);
INSERT INTO `teacher` VALUES('王萍',0,'1972-05-05','助教','计算机系',825);
INSERT INTO `teacher` VALUES('刘冰',0,'1977-08-14','助教','电子工程系',831);
INSERT INTO `teacher` VALUES ('丁力',0,'1972-05-05','教师','计算机系',825);
INSERT INTO `teacher` VALUES ('许文祥',0,'1977-08-14','教师','电子工程系',831);
+---------------------------------------------------------------------------+ 
要求:
1．在表teacher中,查询"计算机系"与"电子工程系"不同职称的教师的lsname和lsprof
方法一:
+-------------------------------------------------------+ 
|select a.lsname,a.lsprof from teacher a                |
|where (a.lsdepart ="计算机系" or a.lsdepart ="电子工程系")| 
|and a.lsprof not in (                                  |
| select lsprof                                         |
| from teacher                                          |
| group by lsprof                                       |
| having count(lsprof)>=2                               |
| )                                                     |
+-------------------------------------------------------+ 
方法二:
+-------------------------------------------------------+ 
|SELECT lsname,lsprof from teacher t                    |
|where lsdepart ="计算机系" or lsdepart ="电子工程系"      | 
|and not exists (                                       |
| select lsprof from teacher                            |
| where lsprof =t.lsprof                                |
| );                                                    |
+-------------------------------------------------------+ 
2．查询选修课编号是"3-105"课程且成绩至少高于选修课程编号为"3-245"的同学的kuid,xsid和degree，并且degree从高到低次序排列
+---------------------------------+
|SELECT a.kuid, a.xsid, a.degree  |
|FROM score a, score b            |
|WHERE a.kuid = '3-105'           |
|AND b.kuid = '3-245'             |
|AND a.xsid = b.xsid              |
|AND a.degree > b.degree          |
|order by a.degree asc;           |
+---------------------------------+
3．查询成绩比该课程平均成绩低的同学的成绩表
+-------------------------------------+
|SELECT a.xsid, a.kuid, a.degree      |
|FROM score a, (                      |
| SELECT kuid, avg( degree ) AS degree|
| FROM score                          |
| GROUP BY kuid                       |
| )b                                  |
|WHERE a.degree > b.degree            |
|AND a.kuid = b.kuid                  |                
+-------------------------------------+ 
4．查询平均成绩最低的学生姓名及其平均成绩
+-------------------------------------+
|select a.xsnm,avg(b.degree) as avg   | 
|from xstudent a,score b              |
|where a.xsid = b.xsid                |
|group by b.xsid                      |
|order by avg limit 1                 |
+-------------------------------------+
5．查询选修多门课程的同学中分数为非最高成绩的记录
+--------------------------------------+
|select DISTINCT a.xsid,a.kuid,a.degree|
|from score a,                         |
|(select kuid,max(degree) as maxdegree | 
| from score                           | 
| group by kuid) b,                    |
|(select xsid,count(kuid) as nums      |
| from score                           | 
| group by xsid) c                     |
|where c.nums>=2                       |
|and a.kuid = b.kuid                   |
|and a.degree<b.maxdegree              |
+--------------------------------------+