SQL query practice with MySQL [toc] 0.create table / Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server ...

SQL query practice with MySQL

[toc]

0.create table

table structure

ER diagram

/*
 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;

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);

2. 查詢平均成績大於60分的同學的學號和平均成績

SELECT student_id,AVG(num) AS avsc   -- as
FROM score
GROUP BY student_id  -- group by
HAVING avsc > 60;   -- having

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 %

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
 );

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
    );

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
);

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)
)

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")) ;

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)

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 = "李平")) ;

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") ;

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

14.查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分

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

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 ;

*** 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 ;

*** 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 ;

18.查詢每門課程被選修的學生數

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

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;

20. 查詢男生、女生人數

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

21. 查詢姓“張”的學生名單

SELECT *
FROM student
WHERE sname LIKE "張%";

22. 查詢同名同姓學生名單，並統計同名人數

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

23. 查詢每門課平均成績，結果按平均成績升序排列;

平均成績相同時，按課程號降序排列

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

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;

***** 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 ;

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;

27.求選了課程的學生人數

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

my ans.

select count(distinct student_id)
from score;

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 = "劉海") ;

29.查詢各個課程及相應的選修人數

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

*** 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 ;

***** 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歸併，去重覆

32.檢索至少選修兩門課程的學生學號

select student_id,count(1)
from score
group by student_id
having count(1) > 2;

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) ;

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去除重名

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 ;

36. 檢索課程"4"分數小於90，按分數降序排列的同學學號

so easy

select student_id,num
from score
where course_id= 4 and num < 90
order by num desc;

37.刪除“002”同學的“001”課程的成績

too easy

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

SQL query practice with MySQL

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”課程的成績

1. find `student_id` where `bio score higher than phy score`

6. 查詢學過“001”並且`也`學過編號“002”課程的同學的學號、姓名

7. 查詢`所有`課程成績小於60分的同學的學號、姓名

8. 查詢沒有學`全`所有課的同學的學號、姓名

10. 查詢至少學過學號為“001”同學所選課程中任意一門課的`其他`同學學號和姓名；