编辑代码

create database 教学;
use 教学;
create table T
(tno char(5) primary key,
 tname char(6),
 title char(6)
);

create table C
(cno char(5) primary key,
 cname varchar(20),
 tno char(5)
 );

create table S
 (sno char(5) primary key,
  sname char(6),
  age int,
  sex char(2)
  );

create table SC
  (sno char(5),
   cno char(5),
   score int
  );

  insert into T values('t1','张伟','讲师');
  insert into T values('t2','刘老师','副教授');
  insert into T values('t3','刘辉','教授');
  insert into T values('t4','王明中','教授');

  insert into C values('c1','英语','t1');
  insert into C values('c2','Java','t2');
  insert into C values('c3','数据库','t3');
  insert into C values('c4','物理','t4');

  
  insert into S values('s1','王珂',21,'男');
  insert into S values('s2','李淑琴',18,'女');
  insert into S values('s3','李敏',18,'女');
  insert into S values('s4','王海涛',18,'男');
  insert into S values('s5','赵子明',18,'男');
  insert into S values('s6','刘美丽',19,'女');

  insert into SC values('s1','c1',80);
  insert into SC values('s2','c1',82);
  insert into SC values('s3','c1',81);
  insert into SC values('s4','c1',85);
  insert into SC values('s5','c1',89);
  insert into SC values('s6','c1',90);
  insert into SC values('s1','c2',87);
  insert into SC values('s2','c2',87);
  insert into SC values('s3','c2',50);
  insert into SC values('s4','c3',53);
  insert into SC values('s5','c3',90);
  insert into SC values('s3','c4',74);
  insert into SC values('s6','c4',80);

select Sno,sname
from S
where Age >20 and Sex='男';

select distinct C.Cno,Cname
from S,SC,C
where S.Sno =SC.Sno and SC.Cno=C.Cno and S.Sex='女';

SELECT distinct T.Tno, T.Tname
FROM S, SC, C, T
WHERE S.Sno = SC.Sno AND SC.Cno = C.Cno AND C.Tno = T.Tno AND S.Sex = '女';

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(Cno) >= 2;

SELECT Cno
FROM SC
WHERE Sno IN ('S3', 'S6')
GROUP BY Cno
HAVING COUNT(DISTINCT Sno) = 2;

SELECT Cno
FROM C
WHERE Cno NOT IN (
    SELECT Cno
    FROM S, SC
    WHERE S.Sno = SC.Sno AND S.Sname = '王珂'
);

SELECT C.Cno, C.Cname
FROM C
WHERE NOT EXISTS (
    SELECT *
    FROM S
    WHERE NOT EXISTS (
        SELECT *
        FROM SC
        WHERE SC.Sno = S.Sno AND SC.Cno = C.Cno
    )
);

SELECT Sno
FROM SC
WHERE Cno IN (
    SELECT Cno
    FROM C, T
    WHERE C.Tno = T.Tno AND T.Tname LIKE '刘老师'
)
GROUP BY Sno
HAVING COUNT(DISTINCT Cno) = (
    SELECT COUNT(DISTINCT Cno)
    FROM C, T
    WHERE C.Tno = T.Tno AND T.Tname LIKE '刘老师'
);