createdatabase edug;
use edug;
createtable s(
sno int(10) primary key,
sname char(4),
gender char(4),
cno char(10),
foreignkey(s.sno) references c(monitor)
);
createtable c(
cno int(10) primary key,
department char(10),
specilality char(10),
monitor int(10)
);
createtable l(
lno int(10) primary key,
lname char(10),
book char(10),
grade int(10)
);
createtable t(
tid int(10) primary key,
tname char(10),
department char(10)
);
createtable sc(
cno int(10) primary key,
lno int(10),
tid int(10),
syear date,
semester date
);
createtable g(
sno int(10) primary key,
lno int(10),
score int(5)
);
-- 1select c.monitor,s.sname,c.specilality from c,s where c.monitor=s.sno;
-- 2select tid,lno from sc where syear=2024and tid in (
select tid from sc where syear=2024groupby tid havingcount(*)>=2
);
-- 3select 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 notin(
select sc.cno from sc,l where l.lnane='MIS'and sc.lno=l.lno
)
groupby 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 notin(
select sc.cno from sc,l where l.lname='MIS'and sc.lno=l.lno
)
groupby(c.cno,specilality);
-- 4createview V1 asselect 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='计算机';
createview v1 asselect 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='计算机系';