必赢365net手机版MySQL练习题参考答案

必赢365net手机版 2

目录

导出现有数据库数据:

  • SQL query practice with
    MySQL

    • 0.create
      table
    • 1. find student_id where
      bio score higher than phy score
    • 2.
      查询平均战绩超过60分的同窗的学号和平均成绩
    • 3.询问全部同学的学号、姓名、选课数、总战表
    • 4.
      查询姓“李”的民间兴办教授的个数
    • 5.查询没学过“李平”老师课的同学的学号、姓名
    • 6.
      查询学过“001”况兼学过编号“002”课程的同桌的学号、姓名
    • 7.
      查询所有学科成绩小于60分的同班的学号、姓名
    • 8.
      查询未有学全部课的同桌的学号、姓名
    • 9.查询至罕见一门课与学号为“001”的校友所学一样的校友的学号和姓名
    • 10.
      询问起码学过学号为“001”同学所选课程中自便一门课的其他校友学号和人名;
    • 11.
      去除学习“李平”老师课的SC表记录
    • 12.
      向SC表中插入一些笔录,这一个记录必要符合以下条件:
    • 13.按平均成绩从低到高展现全体学生的“语文”、“数学”、“阿拉伯语”三门的教程成绩,
    • 14.询问各科成绩最高和最低的分:以如下情势显得:课程ID,最高分,最低分
    • 15.
      按各科平均战表从低到高和及格率的比重从高到低依次
    • *** 16.
      学科平均分从高到低展现(展现任课老师)
    • ***
      17.询问各科成绩前三名的记录(不思考战绩并列情状)
    • 18.询问每门课程被选修的学生数
    • 19.查询只选修了一门课程的整套上学的儿童的学号和姓名
    • 20.
      查询男生、女孩子人数
    • 21.
      查询姓“张”的学生名单
    • 22.
      询问同名同姓学生名单,并总结同名家数
    • 23.
      查询每门课平均战表,结果按平均成绩升序排列;

      • 平均战表同样时,按学科号降序排列
    • 24.
      查询平均成绩超越85的持有学员的学号、姓名和平均成绩
    • ***** 25.
      查询课程名为“物理”,且分数低于60的学习者姓名和分数
    • 26.查询课程编号为003且课程战表在80分以上的学习者的学号和姓名
    • 27.求选了学科的学习者人数
    • 28.查询选修“刘海”老师所授课程的学童中,成绩最高的学习者姓名及其战绩
    • 29.查询各类学科及相应的选修人数
    • ***
      30.询问分化科目但战绩同样的上学的儿童的学号、课程号、学生战绩
    • *****
      31.查询每门科目成绩最佳的前两名(同17題)
    • 32.物色最少选修两门课程的上学的小孩子学号
    • 33.查询全体上学的小孩子都选修的科目标课程号和课程名
    • 34.
      查询没学过“李平”老师教授的任一门学科的学员姓名
    • 35.
      查询两门以上比不上格课程的校友的学号及其平均成绩
    • 36.
      寻找课程”4″分数小于90,按分数降序排列的同学学号
    • 37.删减“002”同学的“001”课程的成就
  • mysqldump -u顾客名 -p密码 数据库名称 >导出文件路线           #
    结构+数据
  • mysqldump -u客户名 -p密码 -d 数据库名称 >导出文件路线       #
    结构 

SQL query practice with MySQL

导入现成数据库数据:

0.create table

必赢365net手机版 1

必赢365net手机版 2

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),          -- create index
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊'), ('2', '李平'), ('3', '刘海'), ('4', '朱云'), ('5', '李杰');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
  • mysqldump -uroot -p密码  数据库名称 < 文件路线  

1. find student_id where bio score higher than phy score

    -- stp0: list colums
SELECT aa.student_id,aa.num AS BIO,bb.num AS PHY 
FROM
    -- stp1: temporary table aa
    (SELECT student_id,num
    FROM score
    LEFT JOIN course
    ON score.`course_id`= course.`cid`
    WHERE course.`cname`="生物") AS aa   
    -- stp3: aa left join bb
LEFT JOIN                
    -- stp2: tempo table bb
    (SELECT student_id,num
    FROM score
    LEFT JOIN course
    ON score.`course_id`= course.`cid`
    WHERE course.`cname`="物理") AS bb    
ON aa.student_id = bb.student_id  
    --stpt4: filter
WHERE aa.num > IF(ISNULL(bb.num),0,bb.num);

必赢365net手机版 3必赢365net手机版 4

2. 询问平均战表超乎60分的同桌的学号和平均战表

SELECT student_id,AVG(num) AS avsc   -- as
FROM score
GROUP BY student_id  -- group by
HAVING avsc > 60;   -- having
/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

表结构和数据

3.查询全数同学的学号、姓名、选课数、总成绩

SELECT stu.sid,stu.sname,bb.counter,bb.total
FROM student AS stu
LEFT JOIN        -- stp2: join
(SELECT student_id,COUNT(course_id) AS counter,SUM(num) AS total
FROM score
GROUP BY student_id) AS bb   -- stp1: temp table bb
ON stu.`sid` = bb.`student_id`;

表结谈判多少

4. 查询姓“李”的教员的个数

SELECT COUNT(tid)
FROM teacher AS tc
WHERE tname LIKE '李%';  -- like %

2、查询“生物”课程比“物理”课程成绩高的具有学生的学号;

5.询问没学过“李平”老师课的同室的学号、姓名

SELECT sid,sname
FROM student AS stu
WHERE sid NOT IN            -- not in
 (
 SELECT student_id           -- select stu_id
 FROM score AS sc
 LEFT JOIN                   -- stp2: join
    (SELECT cid                  -- just need cid,not teacher_id
     FROM course AS cs 
     LEFT JOIN teacher AS tc
     ON cs.teacher_id = tc.`tid`
     WHERE tc.`tname`="李平") bb    -- stp1: temp tbl bb

 ON sc.`course_id`= bb.cid
 GROUP BY student_id
 );
select A.student_id from 
(select student_id,num from score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="生物") as A
INNER JOIN
(select student_id,num from score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="物理") as B
ON A.student_id=B.student_id WHERE A.num > B.num

6. 查询学过“001”何况学过编号“002”课程的同校的学号、姓名

SELECT sid, sname
FROM student
WHERE sid IN
    (SELECT student_id
    FROM score
    WHERE course_id IN (1,2)        -- in (1,2)
    GROUP BY student_id
    HAVING COUNT(course_id) = 2
    );

SELECT sid, sname
FROM student
WHERE sid IN
    (
    SELECT aa.student_id
    FROM 
        (SELECT student_id
        FROM score AS sc
        WHERE sc.`course_id`="1"
        ) AS aa
    INNER JOIN
        (SELECT student_id
        FROM score AS sc
        WHERE sc.`course_id`="2"
        ) AS bb
    ON aa.student_id = bb.student_id
    );

 3、查询平均战表超越60分的同窗的学号、名字和平均成绩;

7. 查询所有课程战绩小于60分的同校的学号、姓名

SELECT sid,sname
FROM student
WHERE sid IN         -- in
(
SELECT student_id
FROM score
GROUP BY student_id
HAVING MIN(num) < 60   -- having min()
);

SELECT sid,sname
FROM student
WHERE sid IN
(
SELECT student_id
FROM score
WHERE num < 60        
GROUP BY student_id        -- group by
);
select student.sid,student.sname,B.numname from 
(select student_id,avg(num) as numname from score GROUP BY student_id HAVING AVG(num)>60) as B
LEFT JOIN student on B.student_id=student.sid

8. 查询未有学全体课的同班的学号、姓名

SELECT sid,sname
FROM student
WHERE sid IN       -- in
(   
SELECT student_id
FROM score
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) <   -- count()
    (SELECT COUNT(DISTINCT cid)
     FROM course)
);

select sid,sname from student where sid not in 
(select student_id 
 from score                 
 group by student_id
 having count(course_id)=
    (select count(cid) from course)
)

4、查询全体同学的学号、姓名、选课数、总战绩;

9.询问最少有一门课与学号为“001”的校友所学一样的校友的学号和人名

SELECT 
  sid,
  sname 
FROM
  student 
WHERE sid IN           -- in 
  (SELECT 
    DISTINCT student_id 
  FROM
    score 
  WHERE course_id IN        -- in 
    (SELECT 
      course_id 
    FROM
      score 
    WHERE student_id = "1")) ;
SELECT student_id,student.sname,COUNT(course_id),SUM(num) from score LEFT JOIN student on score.student_id=student.sid GROUP BY student_id

10. 询问起码学过学号为“001”同学所选课程中大肆一门课的其他同桌学号和人名;

note:
个数相同;
002学过的也学过

SELECT 
  student_id,
  sname 
FROM
  score 
  LEFT JOIN student 
    ON score.student_id = student.sid 
WHERE student_id IN               -- 1
  (SELECT 
    student_id 
  FROM
    score 
  WHERE student_id != 1 
  GROUP BY student_id 
  HAVING COUNT(course_id) =        -- 11
    (SELECT 
      COUNT(1) 
    FROM
      score 
    WHERE student_id = 1))       -- 111
  AND course_id IN               -- 1
  (SELECT 
    course_id 
  FROM
    score 
  WHERE student_id = 1)          -- 11
GROUP BY student_id             -- 1
HAVING COUNT(course_id) =       -- 1
  (SELECT 
    COUNT(1) 
  FROM
    score 
  WHERE student_id = 1)

5、查询姓“李”的教师的资质的个数;

11. 刨除学习“李平”老师课的SC表记录

delete               -- delete from tblname
from
  score 
where course_id in           
  (select 
    cid 
  from
    course 
  where teacher_id in 
    (select 
      tid 
    from
      teacher 
    where tname = "李平")) ;
select * from teacher WHERE tname like "李%"

12. 向SC表中插入一些笔录,那么些记录需要切合以下原则:

— ①未有上过编号“002”课程的同桌学号;
— ②插入“002”号课程的平均成绩

INSERT INTO score (student_id, course_id, num)          -- insert into select from where
SELECT 
  sid,
  2,
  (SELECT 
    AVG(num) 
  FROM
    score 
  WHERE course_id = "2")                    -- select avg(num)
FROM
  student 
WHERE sid NOT IN 
  (SELECT 
    student_id 
  FROM
    score 
  WHERE course_id != "2") ;

6、查询没学过“李平”老师课的校友的学号、姓名;

13.按平均成绩从低到高展现全数学员的“语文”、“数学”、“马耳他语”三门的课程战表,

select student_id,
    (select num from score left join aa on student_id = aa.student_id and course_id = (select cid from course where cname = "生物")) as biosc,
    (SELECT num FROM score LEFT JOIN aa ON student_id = aa.student_id AND course_id = (SELECT cid FROM course WHERE cname = "物理")) as physc,
    (SELECT num FROM score LEFT JOIN aa ON student_id = aa.student_id AND course_id = (SELECT cid FROM course WHERE cname = "美术")) as picsc,
    subs,
    avsc
from 
    (select student_id,count(course_id) as subs, avg(num) as avsc
     from score
    group by student_id
    order by avsc desc
    ) as aa;                 -- temp tbl
SELECT student.sid,student.sname from student WHERE student.sid not in (
SELECT student_id from score WHERE score.course_id in 
(select course.cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE teacher.tname="李平老师") GROUP BY student_id)

14.查询各科成绩最高和压低的分:以如下方式突显:课程ID,最高分,最低分

select course_id,max(num),min(num)     -- max min
from score
group by course_id;              -- group by

 7、查询学过“001”何况也学过数码“002”课程的同校的学号、姓名;

必赢365net手机版,15. 按各科平均成绩从低到高和及格率的比重从高到低依次

思路:case when .. then .. END

select 
  course_id,
  avg(num),
  sum(
    case
      when num >= 60                  -- CASE WHEN exp    
      then 1                                -- THEN value1
      else 0                                -- ELSE value2
    end) / count(1) * 100 as percent    -- END
from
  score 
group by course_id 
order by avg(num) asc,
  percent desc ;

wrong answer:

SELECT 
  course_id,
  AVG(num),
  (SELECT 
    (
      (SELECT 
        COUNT(1) 
      FROM
        score 
      WHERE num >= "60"           
      GROUP BY course_id) /    --  note: return array but value attributed to group by
      (SELECT                  -- no array devides array opration in mysql  
        COUNT(1) 
      FROM
        score 
      GROUP BY course_id)
    )) AS percent 
  FROM
    score 
  GROUP BY course_id 
  ORDER BY AVG(num) ASC,
    percent DESC ;
select C.student_id,student.sname from 
(SELECT A.student_id from 
(SELECT student_id from score WHERE course_id=1) as A
INNER JOIN
(SELECT student_id from score WHERE course_id=2) as B
on A.student_id=B.student_id) as C
LEFT JOIN student on C.student_id=student.sid

*** 16. 学科平均分从高到低展现(展现任课老师)

key:
3 tables join

SELECT 
  tname,    -- tname of 3rd tbl
  AVG(num)  -- avg of 1st tbl
FROM
  score 
  LEFT JOIN course 
    ON score.course_id = course.cid         -- tb1 left join tb2       
  LEFT JOIN teacher 
    ON course.teacher_id = teacher.tid      -- tb2 left join tb3 in one select
GROUP BY course_id 
ORDER BY AVG(num) DESC ;

wrong answer:

select 
  course_id,
  tname,
  avsc 
from
  (select 
    course_id,
    teacher_id,
    avg(num) as avsc 
  from
    score 
    left join course 
      on course_id = cid 
  group by course_id 
  order by avsc desc) as aa 
left join teacher           --  wrong: left join 2nd
on aa.teacher_id = tid ;

8、查询学过“李平”老师所教的全部课的同班的学号、姓名;

*** 17.查询各科战表前三名的笔录(不思念战表并列境况)

NOTE

the field after select must be same as group by sentence

  select 
    course_id,
    (select 
      num                           -- the field after SELECT must be same as group by sentence
    from
      score 
    WHERE course_id = aa.course_id 
    GROUP BY num                          -- group by `num` : num is same as select `num`
    ORDER BY num desc 
    LIMIT 0, 1) as st,
    (select 
      num 
    from
      score 
    WHERE course_id = aa.course_id 
    group by num 
    order by num desc 
    limit 1, 1) as nd,
    (select 
      num 
    from
      score 
    WHERE course_id = aa.course_id 
    group by num 
    order by num desc 
    limit 2, 1) as rd 
  FROM
    score as aa 
  group by course_id ;
SELECT A.student_id,student.sname from 
(select student_id from score WHERE score.course_id in 
(SELECT cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE tname LIKE "李平老师")
GROUP BY student_id HAVING COUNT(student_id)=(SELECT COUNT(cid) from course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE tname LIKE "李平老师")
) as A
LEFT JOIN student on  A.student_id=student.sid

18.查询每门学科被选修的上学的儿童数

select course_id, count(1) as stus        -- count(distinct col)
from score
group by course_id;

9、查询课程编号“002”的战绩比课程编号“001”课程低的兼具同学的学号、姓名;

19.查询只选修了一门学科的全部学员的学号和姓名

select student_id,sname,count(1)
from score left join student           -- from A left join B
on student_id = sid                     -- on A. = B.
group by student_id
having count(1) = 1;
SELECT student.sid,student.sname from 
(select A.student_id from 
(select student_id,num from score WHERE score.course_id=1 ) as A
INNER JOIN
(select student_id,num from score WHERE score.course_id=2 ) as B
on A.student_id=B.student_id WHERE B.num < A.num) as C
LEFT JOIN student on C.student_id = student.sid

20. 查询男士、女子人数

select gender,count(1) as persons
from student
group by gender;

10、查询有学科战表小于60分的同桌的学号、姓名;

21. 查询姓“张”的学生名单

SELECT *
FROM student
WHERE sname LIKE "张%";
select student.sid,student.sname from 
(select student_id from score where num < 60 GROUP BY student_id) as A
LEFT JOIN student on A.student_id=student.sid

22. 询问同名同姓学生名单,并总括同名家数

select sname,count(1) as NUM                   
from student
group by sname               -- group by sname
having count(1) > 1
order by num desc;

11、查询未有学全全部课的同校的学号、姓名;

23. 询问每门课平均成绩,结果按平均成绩升序排列;

select student.sid,student.sname from 
(select score.student_id,COUNT(student_id) from score WHERE score.course_id in 
(select course.cid from course) 
GROUP BY score.student_id HAVING COUNT(student_id)<4) as A
LEFT JOIN student on A.student_id=student.sid

平均战绩同样时,按学科号降序排列

note

order by c1,c2 desc

SELECT course_id,avg(num) AS avsc
FROM score
GROUP BY course_id
ORDER BY avsc,course_id DESC;        -- order by col1,col2 : clo2 take effect only when col1 are same

12、查询至稀有一门课与学号为“001”的同班所学一样的校友的学号和姓名;

24. 询问平均成绩超过85的装有学生的学号、姓名和平均成绩

better ans.

select student_id,sname,avg(num) as avsc
from score as sc
left join student as stu           -- use left join on, not subquery
on sc.student_id = stu.sid        -- must be full name for ON clause
group by student_id
having AVG(num) > 85;

my ans.

SELECT aa.student_id,sname,aa.avsc
FROM
(select student_id,avg(num) AS avsc
FROM score
GROUP BY student_id
HAVING avsc > 85) AS aa
LEFT JOIN student               -- must be aa LEFT JOIN student,or many NULL yield
ON aa.student_id = sid;
SELECT student.sid,student.sname from 
(select student_id,COUNT(student_id) from score WHERE course_id in 
(SELECT course_id from score WHERE student_id=1)
GROUP BY student_id) as A
LEFT JOIN student on A.student_id=student.sid WHERE student.sid!=1

***** 25. 查询课程名称叫“物理”,且分数低于60的学员姓名和分数

note

join 3 tables along with WHERE clause
先join,再where過濾

SELECT 
  sname,
  num 
FROM
  score 
  LEFT JOIN course 
    ON score.`course_id` = course.`cid` 
  LEFT JOIN student 
    ON score.`student_id` = student.`sid` 
WHERE course.`cname` = "物理"                           -- two LEFT JOIN with WHERE 
  AND score.`num` < 60 ;

13、查询起码学过学号为“001”同学所选课程中私行一门课的其余同学学号和人名;

26.查询课程编号为003且课程战绩在80分以上的上学的小孩子的学号和姓名

note

  • on子句必須用全名;
  • 先join,再where過濾

select student_id,sname,num
from score as sc left join student as st
on sc.`student_id` = st.`sid`
where course_id ="3" and num > 80;
SELECT student.sid,student.sname from 
(SELECT student_id from score WHERE score.course_id in
(SELECT course_id from score WHERE student_id=1)
GROUP BY student_id) as A
LEFT JOIN student on A.student_id=student.sid WHERE student.sid!=1

27.求选了学科的学习者人数

select 
  count(sid)             -- 是學生
from
  student 
where sid in 
  (select 
    student_id           -- 并且選了課的學生
  from
    score) ;

my ans.

select count(distinct student_id)
from score;

14、查询和“002”号的校友学习的课程大同小异的其余同学学号和人名;

28.查询选修“刘海”老师所授课程的学习者中,战绩最高的学习者姓名及其成绩

三表直接用逗號隔開,用where替代join更簡潔
note: 四表关联,三表join一表in

select 
  st.sname,
  max(sc.num)
from
  course as cs 
  left join score as sc 
    on sc.`course_id` = cs.`cid` 
  left join student as st 
    on sc.`student_id` = st.`sid` 
where cs.`teacher_id` in 
  (select 
    cid 
  from
    teacher 
  where tname = "刘海") ;
SELECT student.sid,student.sname from 
(SELECT student_id from score WHERE course_id in 
(SELECT course_id from score WHERE student_id=2)
GROUP BY student_id HAVING COUNT(student_id)=3) as A
LEFT JOIN student on A.student_id=student.sid WHERE A.student_id!=1 

29.询问种种学科及相应的选修人数

SELECT course_id,COUNT(DISTINCT student_id)
FROM score
GROUP BY course_id;

15、删除学习“李平”老师课的SC表记录;

*** 30.查询不相同学科但战绩一样的学习者的学号、课程号、学生战表

SELECT 
  aa.course_id,
  aa.student_id,
  aa.num,                -- aa.num
  bb.student_id,      
  bb.num                 -- bb.num
FROM
  score AS aa,
  score AS bb 
where aa.student_id != bb.student_id         -- aa.id != bb.id
  AND aa.course_id != bb.course_id 
  AND aa.num = bb.num ;

等价写法

select 
  aa.course_id,
  aa.student_id,
  aa.num,
  bb.student_id,
  bb.num 
from
  score as aa inner join               -- inner join on
  score as bb 
on aa.student_id != bb.student_id 
  and aa.course_id != bb.course_id 
  and aa.num = bb.num ;
DELETE from score WHERE course_id in 
(SELECT course.cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE teacher.tname LIKE "李平老师")

***** 31.询问每门学科战表最棒的前两名(同17題)

GROUP by合比量齐观复行,同select DISTINCT
course_id | 1 st num | 2 nd num

select 
  course_id,

  (select 
    num 
  from
    score 
  where course_id = aa.course_id 
  group by num              -- group by num: 去除重複分數
  order by num desc 
  limit 0, 1) as st,

  (select 
    num 
  from
    score 
  where course_id = aa.course_id 
  group by num 
  order by num desc 
  limit 1, 1) as nd 
from
  score as aa 
group by course_id ;           -- 按course_id归并,去重复       

16、向SC表中插入一些笔录,那几个记录供给相符以下规范:①一贯不上过编号“002”课程的校友学号;②插入“002”号课程的平分战表; 

32.物色起码选修两门课程的上学的小孩子学号

select student_id,count(1)
from score
group by student_id
having count(1) > 2;
INSERT INTO score(student_id,course_id,num)
SELECT student_id,2,(SELECT AVG(num) from score WHERE course_id=2) from score WHERE course_id!=2 GROUP BY student_id

33.询问任何上学的儿童都选修的科指标课程号和学科名

两表或三表关联,不用join更简短

select 
  course_id,
  cname 
from
  score as sc,              -- 两表直接用逗号
  course as cs 
where cs.`cid` = sc.`course_id` 
group by course_id 
having count(1) = 
  (select 
    count(1) 
  from
    student) ;

17、按平均成绩从低到高显示全体学生的“生物”、“物理”、“体育”三门的教程成绩,按如下方式显得:
学生ID,生物,物理,体育,有效课程数,有效平均分;

34. 询问没学过“李平”老师教师的任一门学科的上学的小孩子姓名

select 
  st.sid,
  sname 
from
  score as sc,
  student as st 
where sc.`student_id` = st.`sid` 
  and sc.`course_id` not in 
  (select 
    cid 
  from
    course as cs,
    teacher as tc 
  where cs.`teacher_id` = tc.`tid` 
    and tc.`tname` = "李平") 
group by st.`sid` ;        -- group by去除重名
SELECT 
student_id,
(SELECT num from score as s2 WHERE s2.student_id=s1.student_id and course_id=1) as 生物,
(SELECT num from score as s2 WHERE s2.student_id=s1.student_id and course_id=2) as 物理,
(SELECT num from score as s2 WHERE s2.student_id=s1.student_id and course_id=3) as 体育
from 
score as s1  

35. 查询两门以上不比格课程的同校的学号及其平均战表

key:
CASE WHEN THEN ELSE END

select 
  student_id,
  avg(num),
  SUM(
    CASE
      WHEN num < 60 
      THEN 1 
      ELSE 0 
    END) as failed 
from
  score 
group by student_id 
having failed > 2 ;

18、查询各科成绩最高和最低的分:以如下方式显得:课程ID,最高分,最低分;

36. 找出课程”4″分数小于90,按分数降序排列的同窗学号

so easy

select student_id,num
from score
where course_id= 4 and num < 90
order by num desc;
SELECT course.cid,MAX(num),MIN(num) from course
LEFT JOIN score on course.cid=score.course_id GROUP BY course_id 

37.删减“002”同学的“001”课程的成就

too easy

delete 
from
  score 
where student_id = 2 
  and course_id = 1 ;

19、按各科平均成绩从低到高和及格率的比例从高到低依次;

select A.course_id,course.cname,A.numm,A.percent from 
(select course_id,avg(num) as numm,SUM(case when num<60 then 0 else 1 END)/COUNT(1)*100 as percent from score GROUP BY course_id 
ORDER BY avg(num) ASC,percent DESC) as A
LEFT JOIN course on A.course_id=course.cid

20、课程平均分从高到低展现(现实任课老师);

SELECT course_id,AVG(if(ISNULL(num),0,score.num)),course.cname,teacher.tname from score 
LEFT JOIN course on score.course_id=course.cid
LEFT JOIN teacher on course.teacher_id=teacher.tid
GROUP BY course_id ORDER BY AVG(num) DESC;

21、查询各科成绩前三名的记录:(不思量战表并列情状)

SELECT score.sid,score.course_id,score.num,T.first_num,T.second_num from score LEFT JOIN 
(SELECT sid,
(SELECT num from score as s2 WHERE s2.course_id=s1.course_id ORDER BY num DESC LIMIT 0,1) as first_num,
(SELECT num from score as s2 WHERE s2.course_id=s1.course_id GROUP BY num ORDER BY num DESC LIMIT 3,1) as second_num
from score as s1
) as T
on score.sid=T.sid
WHERE score.num <= T.first_num and score.num >= T.second_num

22、查询每门学科被选修的上学的小孩子数;

SELECT course_id,COUNT(student_id) from score GROUP BY course_id

23、查询出只选修了一门学科的所有的事学员的学号和姓名;

SELECT student_id,student.sname from score
LEFT JOIN student on score.student_id=student.sid
GROUP BY student_id HAVING COUNT(student_id)=1

24、查询男子、女孩子的人口;

SELECT * from 
(SELECT COUNT(1) as man from student WHERE gender="男") as A, 
(SELECT COUNT(1) as feman from student WHERE gender="女") as B

25、查询姓“张”的学生名单;

SELECT * from student WHERE student.sname like "张%"

26、查询同名同姓学生名单,并总结同有名的人数;

SELECT COUNT(1) from student GROUP BY student.sname

27、查询每门科指标平分成绩,结果按平均战表升序排列,平均成绩同样时,按学科号降序排列;

SELECT course_id,avg(if(ISNULL(num),0,num)) as avg from score GROUP BY course_id ORDER BY avg ASC,course_id DESC 

28、查询平均成绩超过85的富有学员的学号、姓名和平均成绩;

SELECT score.student_id,student.sname,avg(if(ISNULL(num),0,num)) from score
LEFT JOIN student on score.student_id=student.sid
GROUP BY student_id HAVING avg(num)>85

29、查询课程名叫“数学”,且分数低于60的学员姓名和分数;

第一种:
SELECT student.sname,score.num from score 
LEFT JOIN student on score.student_id=student.sid
WHERE score.course_id=(SELECT cid from course WHERE course.cname LIKE "生物") and num<60

第二种
SELECT student.sname,score.num from score 
LEFT JOIN student on score.student_id=student.sid
LEFT JOIN course on score.course_id=course.cid
WHERE course.cname="生物" and num<60

30、查询课程编号为003且课程战表在80分以上的上学的小孩子的学号和姓名;

SELECT score.student_id,student.sname from score
LEFT JOIN student on score.student_id=student.sid
WHERE course_id=3 and num > 80

31、求选了课程的学员人数

第一种
SELECT COUNT(1) from 
(SELECT student_id from score GROUP BY student_id) as A

第二种
SELECT COUNT(DISTINCT student_id) from score 

32、***查询选修“杨艳”老师所授课程的学员中,战表最高的学员姓名及其战表;

SELECT student_id,student.sname,score.num from course
LEFT JOIN teacher on course.teacher_id=teacher.tid
LEFT JOIN score ON course.cid=score.course_id
LEFT JOIN student ON score.student_id=student.sid
WHERE teacher.tname="李平老师"
ORDER BY score.num DESC LIMIT 1;


(SELECT course_id,MAX(num) as max from score
LEFT JOIN student on score.student_id=student.sid
 WHERE score.course_id in
SELECT course.cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE teacher.tname="李平老师")
GROUP BY course_id

33、查询种种学科及相应的选修人数;

SELECT course_id,course.cname,COUNT(student_id) from score 
LEFT JOIN course on score.course_id=course.cid
GROUP BY course_id

34、查询不一样学科但战表同样的学员的学号、课程号、学生战表;

SELECT s1.student_id,s1.course_id,s1.num from score as s1,score as s2 WHERE s1.num = s2.num and s1.course_id != s2.course_id 

35、***查询每门课程战绩最好的前两名;

SELECT score.sid,score.course_id,T.first_num,T.second_num,score.num from score LEFT JOIN 
(SELECT sid,
(SELECT num from score as s2 WHERE s2.course_id=s1.course_id ORDER BY num DESC LIMIT 0,1) as first_num,
(SELECT num from score as s2 WHERE s2.course_id=s1.course_id ORDER BY num DESC LIMIT 1,1) as second_num
from score as s1) as T
on score.sid=T.sid
WHERE score.num<=T.first_num and score.num >= T.second_num

36、检索起码选修两门课程的学生学号;

SELECT student_id from score GROUP BY student_id HAVING COUNT(student_id)>1

37、查询全体学生都选修的教程的课程号和学科名;

SELECT course_id,course.cname from score
LEFT JOIN course on score.course_id=course.cid
GROUP BY course_id HAVING COUNT(1)=(SELECT COUNT(1) from student)

38、查询没学过“叶平”老师授课的任一门科目标学习者姓名;

SELECT student.sname from score
LEFT JOIN student on score.student_id=student.sid
WHERE score.course_id not in 
(SELECT cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE teacher.tname="李平老师")
GROUP BY student_id 

39、查询两门以上不比格课程的同室的学号及其平均战表;

SELECT student_id,AVG(num) from score  WHERE num < 60 GROUP BY student_id HAVING COUNT(1) > 2

40、检索“004”课程分数小于60,按分数降序排列的校友学号;

SELECT * from score WHERE course_id=4 and num < 90 ORDER BY num DESC

41、删除“002”同学的“001”课程的实际业绩;

DELETE from score WHERE student_id=2 and course_id=1

参照链接

Leave a Comment.