编辑代码

create database edug;
use edug;
create table s(
    sno int(10) primary key,
    sname char(4),
    gender char(4),
    cno char(10),
    foreign key(s.sno) references c(monitor)
);

create table c(
    cno int(10) primary key,
    department char(10),
    specilality char(10),
    monitor int(10)
);

create table l(
    lno int(10) primary key,
    lname char(10),
    book char(10),
    grade int(10)
);

create table t(
    tid int(10) primary key,
    tname char(10),
    department char(10)
);

create table sc(
    cno int(10) primary key,
    lno int(10),
    tid int(10),
    syear date,
    semester date
);

create table g(
    sno int(10) primary key,
    lno int(10),
    score int(5)
);

-- 1
select c.monitor,s.sname,c.specilality from c,s where c.monitor=s.sno;
-- 2
select tid,lno from sc where syear=2024 and tid in (
    select tid from sc where syear=2024 group by tid having count(*)>=2
);




-- 3
select c.cno,c.specilality,count(s.sno) from c,s
where c.cno=s.cno and c.cno in (
    select sc.cno from sc,l where l.lname='数据库' and sc.lno=l.lno
)
and c.cno not in(
    select sc.cno from sc,l where l.lnane='MIS' and sc.lno=l.lno
) 
group by c.cno,specilality;

select c.cno,c.specilality,count(s.sno) from c,s
where c.cno=s.cno and c.cno in(
    select sc.sno from sc,l where l.lname='数据库' and sc.lno=l.lno
)
and c.cno not in(
    select sc.cno from sc,l where l.lname='MIS' and sc.lno=l.lno
)
group by(c.cno,specilality);

-- 4
create view V1 as select s.sno,s.sname,s.gender,s.cno,g.score
from s
join c on s.cno=c.cno
join g on s.sno=g.sno
and g.lno=(
    select lno from l where lname='数据库'
)
where c.department='计算机';


create view v1 as select s.sno,s.sname,s.gender,s.cno,g.score
join c on s.cno=c.cno
join g on s.sno=g.sno
and g.lno=(
    select lno from l where lname='数据库'
)
where c.department='计算机系';