Demo entry 6687948

sql

   

Submitted by anonymous on Dec 31, 2017 at 13:58
Language: MySQL. Code size: 1.8 kB.

#用SQL完成以下任务(使用STC数据库):
#1、检索所有姓赵的教师编号及姓名
 SELECT
  `Tno`,
  `Tname`
FROM
  `teacher`
WHERE `Tname` LIKE '赵%';

#2、按‘高等数学’课程成绩由高到低顺序显示所有同学姓名
 SELECT
  `student`.`Sname`
FROM
  `student`
  INNER JOIN `sc` USING (`Sno`)
  INNER JOIN `Course` USING (`Cno`)
WHERE `course`.`Cname` = '高等数学'
ORDER BY `sc`.`score` DESC;

#3、求‘002’号课程有成绩差的任意两位同学
 SELECT
  `stu1`.`Sname`,
  `stu2`.`Sname`
FROM
  `sc` AS `s1`
  INNER JOIN `sc` AS `s2`
  INNER JOIN `student` AS `stu1`
  INNER JOIN `student` AS `stu2`
WHERE `s1`.`score` > `s2`.`score`
  AND `s1`.`cno` = '002'
  AND `s2`.`cno` = '002'
  AND `stu1`.`Sno` = `s1`.`sno`
  AND `stu2`.`Sno` = `s2`.`sno` #4、找出工资最高的教师姓名
   SELECT
    `Tname`
  FROM
    `teacher`
  WHERE `Salary` IN
    (SELECT
      MAX(`Salary`)
    FROM
      `teacher`);

#5、找出98030102号同学成绩最低的课程号
 SELECT
  `cno`
FROM
  `sc`
WHERE `score` IN
  (SELECT
    MIN(`score`)
  FROM
    `sc`
  WHERE `sc`.`sno` = '98030102');

#6、列出至少学过98030102号同学学过所有课程的同学的学号
 SELECT DISTINCT
  `sno`
FROM
  `sc` AS `sc1`
WHERE NOT EXISTS
  (SELECT
    *
  FROM
    `sc` AS `sc2`
  WHERE `sno` = '98030102'
    AND NOT EXISTS
    (SELECT
      *
    FROM
      `sc`
    WHERE `cno` = `sc2`.`cno`
      AND `sno` = `sc1`.`sno`));

#7、求有1门及以上70分或高于70分课程的同学的学号及其平均成绩
 SELECT
  `student`.`Sno`,
  AVG(`sc1`.`score`) AS `average_score`
FROM
  `student`
  INNER JOIN `sc` AS `sc1` USING (`Sno`)
WHERE `sc1`.`Sno` IN
  (SELECT DISTINCT
    `Sno`
  FROM
    `sc` AS `sc2`
  WHERE `sc2`.`score` >= 70)
GROUP BY (`student`.`Sno`) #8、求所有学生的选课情况(没有选课的学生也需列在表中)
   SELECT
    `student`.`Sname`,
    `course`.`Cname`
  FROM
    `student`
    LEFT OUTER JOIN `sc`
      ON `sc`.`sno` = `student`.`Sno`
    LEFT OUTER JOIN `course`
      ON `sc`.`cno` = `course`.`Cno`;

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).