传媒公司合同模板_天津全包圆装饰公司官网_新冠疫情最新消息今天_湖南专业seo公司

画一画：部门与职工之间的E-R图、歌迷与歌手之间的E-R图、学生与图书之间的E-R图。

针对上一题（第2题）中的每一种情况，撤销为各用户所授予的权限。

一、定义表

create database spj;

use spj;

create table s(sno char(2),sname varchar(20),status smallint,city varchar(20));

create table p(pno char(2),pname varchar(20),color varchar(10),weight smallint);

create table j(jno char(2),jname varchar(20),city varchar(20));

create table spj(sno char(2),pno char(2),jno char(2),qty int);

alter table s add primary key (sno);

alter table p add primary key (pno);

alter table j add primary key (jno);

alter table spj add primary key (sno,pno,jno);

alter table spj add foreign key (sno) references s(sno);

alter table spj add foreign key (pno) references p(pno);

alter table spj add foreign key (jno) references j(jno);

二、插入数据

insert into s values('s1','精益',20,'天津');

insert into s values('s2','盛锡',10,'北京');

insert into s values('s3','东方红',30,'北京');

insert into s values('s4','丰泰盛',20,'天津');

insert into s values('s5','为民',30,'上海');

insert into p values('p1','螺母','红',12);

insert into p values('p2','螺栓','绿',17);

insert into p values('p3','螺丝刀','蓝',14);

insert into p values('p4','螺丝刀','红',14);

insert into p values('p5','凸轮','蓝',40);

insert into p values('p6','齿轮','红',30);

insert into j values('j1','三建','北京');

insert into j values('j2','一汽','长春');

insert into j values('j3','弹簧厂','天津');

insert into j values('j4','造船厂','天津');

insert into j values('j5','机车厂','唐山');

insert into j values('j6','无线电厂','常州');

insert into j values('j7','半导体厂','南京');

insert into spj values('s1','p1','j1',200);

insert into spj values('s1','p1','j3',100);

insert into spj values('s1','p1','j4',700);

insert into spj values('s1','p2','j2',100);

insert into spj values('s2','p3','j1',400);

insert into spj values('s2','p3','j2',200);

insert into spj values('s2','p3','j4',500);

insert into spj values('s2','p3','j5',400);

insert into spj values('s2','p5','j1',400);

insert into spj values('s2','p5','j2',100);

insert into spj values('s3','p1','j1',200);

insert into spj values('s3','p3','j1',200);

insert into spj values('s4','p5','j1',100);

insert into spj values('s4','p6','j3',300);

insert into spj values('s4','p6','j4',200);

insert into spj values('s5','p2','j4',100);

insert into spj values('s5','p3','j1',200);

insert into spj values('s5','p6','j2',200);

insert into spj values('s5','p6','j4',500);

三、数据操作

（1）select sname,city from s;

（2）select pname,color,weight from p;

（3）select distinct jno from spj where sno='S1';

（4）select pname,qty from p,spj where p.pno=spj.pno and jno='J2';

（5）select distinct pno from s,spj where s.sno=spj.sno and city='上海';

（6）select distinct jname from s,spj,j where s.sno=spj.sno and spj.jno=j.jno and s.city='上海';

（7）select jno from j where NOT EXISTS(select * from spj where jno=j.jno and sno in (select sno from s where city='天津'));

（8）update p set color='蓝' where color='红';

（9）update spj set sno='S3' where sno='S5' and jno='J4' and pno='P6';

（10）delete from spj where sno='S2'; delete from s where sno='S2';

（11）insert into s values('s2','盛锡',10,'北京'); insert into spj values('s2','p4','j6',200);

四、视图

create view v_spj as select sno,pno,qty from spj,j where spj.jno=j.jno and jname='三建';

select pno,sum(qty) from v_spj group by pno;

select pno,qty from v_spj where sno='S1';

嵌套查询：基于如下所示的student、course、sc表（表中数据仅为部分数据），先用带有IN谓词的子查询完成下列各题，再用带有EXISTS谓词的子查询完成下列各题。

（1）查询选修了2号课程的学生的姓名。

（2）查询没有选修2号课程的学生的姓名。

（3）查询“201215121”选修了的课程的名称。

（4）查询“201215121”没有选修的课程的名称。

（1）select sname from student where sno in (select sno from sc where cno='2');

（2）select sname from student where sno not in (select sno from sc where cno='2');

（3）select cname from course where cno in (select cno from sc where sno='201215121');

（4）select cname from course where cno not in (select cno from sc where sno='201215121');

（1）select sname from student where EXISTS (select * from sc where sno=student.sno and cno='2');

（2）select sname from student where NOT EXISTS (select * from sc where sno=student.sno and cno='2');

（3）select cname from course where EXISTS (select * from sc where cno=course.cno and sno='201215121');

（4）select cname from course where NOT EXISTS (select * from sc where cno=course.cno and sno='201215121');

嵌套查询：基于第1题中的student、course、sc表，分别使用带有IN谓词的子查询和带有EXISTS谓词的子查询完成下列各题。

（1）查询选修了“信息系统”的学生的姓名。

（2）查询没有选修“信息系统”的学生的姓名。

（3）查询“刘晨”选修了的课程的名称。

（4）查询“刘晨”没有选修的课程的名称。

（1）

select sname from student where sno in (select sno from sc where cno in (select cno from course where cname='信息系统'));

select sname from student where EXISTS(select * from sc where sno=student.sno and cno in(select cno from course where cname='信息系统'));

select sname from student where EXISTS(select * from sc where sno=student.sno and EXISTS(select * from course where cno=sc.cno and cname='信息系统'));

（2）

select sname from student where sno not in (select sno from sc where cno in (select cno from course where cname='信息系统'));

select sname from student where NOT EXISTS(select * from sc where sno=student.sno and cno in(select cno from course where cname='信息系统'));

select sname from student where NOT EXISTS(select * from sc where sno=student.sno and EXISTS(select * from course where cno=sc.cno and cname='信息系统'));

（3）

select cname from course where cno in (select cno from sc where sno in (select sno from student where sname='刘晨'));

select cname from course where EXISTS(select * from sc where cno=course.cno and sno in(select sno from student where sname='刘晨'));

select cname from course where EXISTS(select * from sc where cno=course.cno and EXISTS(select * from student where sno=sc.sno and sname='刘晨'));

（4）

select cname from course where cno not in (select cno from sc where sno in (select sno from student where sname='刘晨'));

select cname from course where NOT EXISTS(select * from sc where cno=course.cno and sno in(select sno from student where sname='刘晨'));

select cname from course where NOT EXISTS(select * from sc where cno=course.cno and EXISTS(select * from student where sno=sc.sno and sname='刘晨'));

嵌套查询：基于第1题中的student、course、sc表，分别使用带有IN谓词的子查询和带有EXISTS谓词的子查询完成下列各题。（选做）

（1）查询选修了“信息系统”的学生的学号。

（2）查询没有选修“信息系统”的学生的学号(该生选了课的)。

（3）查询“刘晨”选修了的课程的课程号。

（4）查询“刘晨”没有选修的课程的课程号(该课程有人选了的)。

（1）

select sno from sc where cno in (select cno from course where cname='信息系统');

select sno from sc where EXISTS(select * from course where cno=sc.cno and cname='信息系统');

（2）

select distinct sno from sc where sno not in (select sno from sc where cno in (select cno from course where cname='信息系统'));

select distinct sno from sc scx where NOT EXISTS (select * from sc where sno=scx.sno and cno in (select cno from course where cname='信息系统'));

select distinct sno from sc scx where NOT EXISTS (select * from sc scy where sno=scx.sno and EXISTS (select * from course where cno=scy.cno and cname='信息系统'));

（3）

select cno from sc where sno in (select sno from student where sname='刘晨');

select cno from sc where EXISTS(select * from student where sno=sc.sno and sname='刘晨');

（4）

select distinct cno from sc where cno not in (select cno from sc where sno in (select sno from student where sname='刘晨'));

select distinct cno from sc scx where NOT EXISTS (select * from sc where cno=scx.cno and sno in (select sno from student where sname='刘晨'));

select distinct cno from sc scx where NOT EXISTS (select * from sc scy where cno=scx.cno and EXISTS (select * from student where sno=scy.sno and sname='刘晨'));

连接查询：基于如下所示的student、course、sc表（表中数据仅为部分数据），使用SQL语句完成下列各题。

（1）查询选修了2号课程的学生的姓名。

（2）查询201215121选修了的课程的课程名称。

（3）查询刘晨选修了的课程的课程号。

（4）查询选修了信息系统的学生的学号。

（5）查询刘晨选修了的课程的课程名称。

（6）查询选修了信息系统的学生的姓名。

（7）查询选修了2号课程且成绩在90分以上的学生的学号和姓名。

select sname from student,sc where student.sno=sc.sno and cno='2';

select cname from sc,course where sc.cno=course.cno and sno='201215121';

select cno from student,sc where student.sno=sc.sno and sname='刘晨';

select sno from sc,course where sc.cno=course.cno and cname='信息系统';

select cname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and sname='刘晨';

select sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and cname='信息系统';

select student.sno,sname from student,sc where student.sno=sc.sno and cno='2' and grade>=90;

连接查询：基于第1题中的student、course、sc表，使用SQL语句完成下列各题。

（1）查询选修2号课程的学生的平均年龄。

（2）查询学生刘晨的平均成绩。

（3）查询学生201215121选修课程的总学分数。

（4）查询课程"信息系统"的平均成绩。

（5）查询计算机系选修2号课程的学生平均成绩。

（6）查询计算机系选修2号课程的学生人数。

（7）查询学生201215121选修的学分大于3的课程的平均成绩。

（8）查询学生201215121选修的学分大于3的课程门数。

select avg(sage) from student,sc where student.sno=sc.sno and cno='2';

select avg(grade) from student,sc where student.sno=sc.sno and sname='刘晨';

select sum(ccredit) from sc,course where sc.cno=course.cno and sno='201215121';

select avg(grade) from sc,course where sc.cno=course.cno and cname='信息系统';

select avg(grade) from student,sc where student.sno=sc.sno and sdept='CS' and cno='2';

select count(*) from student,sc where student.sno=sc.sno and sdept='CS' and cno='2';

select avg(grade) from course,sc where course.cno=sc.cno and sno='201215121' and ccredit>=3;

select count(*) from course,sc where course.cno=sc.cno and sno='201215121' and ccredit>=3;

连接查询：基于第1题中的student、course、sc表，使用SQL语句完成下列各题。

（1）查询选修每门课程的学生的平均年龄（即课程号、平均年龄）。

（2）查询计算机系选修每门课程的学生的平均年龄（即课程号、平均年龄）。

（3）查询课程号，选修该课程的学生的平均年龄在18岁以上。

（4）查询课程号，计算机系选修该课程的学生的平均年龄在18岁以上。

select cno,avg(sage) from student,sc where student.sno=sc.sno group by cno;

select cno,avg(sage) from student,sc where student.sno=sc.sno and sdept='CS' group by cno;

select cno from student,sc where student.sno=sc.sno group by cno having avg(sage)>=18;

select cno from student,sc where student.sno=sc.sno and sdept='CS' group by cno having avg(sage)>=18;

不相关嵌套查询：基于第1题中的student、course、sc表，使用带有比较运算符或IN谓词的子查询完成下列各题。

（1）查询选修了2号课程的学生的姓名。

（2）查询201215121选修了的课程的课程名称。

（3）查询刘晨选修了的课程的课程号。

（4）查询选修了信息系统的学生的学号。

（5）查询刘晨选修了的课程的课程名称。

（6）查询选修了信息系统的学生的姓名。

（7）查询选修了2号课程且成绩在90分以上的学生的学号和姓名。

select sname from student where sno in (select sno from sc where cno='2');

select cname from course where cno in (select cno from sc where sno='201215121');

select cno from sc where sno = (select sno from student where sname='刘晨');

select sno from sc where cno = (select cno from course where cname='信息系统');

select cname from course where cno in (select cno from sc where sno = (select sno from student where sname='刘晨'));

select sname from student where sno in (select sno from sc where cno = (select cno from course where cname='信息系统'));

select sno,sname from student where sno in (select sno from sc where cno='2' and grade>=90);

不相关嵌套查询：基于第1题中的student、course、sc表，使用带有比较运算符、any或all的子查询完成下列各题。

（1）查询与“刘晨”在同一个系学习的学生。

（2）查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄。

（3）查询非计算机系中比计算机系所有学生年龄都小的学生姓名和年龄。

（4）查询至少有两门课成绩在90分以上的学生的学号、平均成绩。

（1）select sno,sname,sdept from student where sdept = (select sdept from student where sname='刘晨');

（2）select sname,sage from student where sdept<>'CS' and sage<ANY(select sage from student where sdept='CS');

（3）select sname,sage from student where sdept<>'CS' and sage<ALL(select sage from student where sdept='CS');

（4）select sno,avg(grade) from sc where sno in (select sno from sc where grade>=90 group by sno having count(*)>=2) group by sno;

简单查询：基于如下所示的student、course、sc表（表中数据仅为部分数据），使用SQL查询语句完成下列各题（即写出SQL语句）。

（1）查询学生的学号、姓名、所在系。

（2）查询学生的学号、姓名、出生年份。

（3）查询出所有选了课的学生的学号。

（4）查询出所有被学生选了课的课程号。

select sno,sname,sdept from student;

select sno,sname,2022-sage from student;

select distinct sno from sc;

select distinct cno from sc;

简单查询：基于第1题中的student、course、sc表，使用SQL查询语句完成下列各题。

（1）查询计算机系的学生的学号、姓名。

（2）查询计算机系年龄小于20岁的学生的学号、姓名。

（3）查询计算机系、数学系、信息系这三个系姓刘的、年龄在18至23岁之间的女同学的学号和姓名。

（4）查询没有先修课的课程的名称。

（5）查询学生201215121的2号课程的成绩。

（6）查询出成绩不及格(低于60分)的学生的学号。

select sno,sname from student where sdept='CS';

select sno,sname from student where sdept='CS' and sage<20;

select sno,sname from student where sdept in('CS', 'MA', 'IS') and sname like '刘%' and sage between 18 and 23 and ssex='女';

select cname from course where cpno is null;

select grade from sc where sno='201215121' and cno='2';

select distinct sno from sc where grade<60;

简单查询：基于第1题中的student、course、sc表，使用SQL查询语句完成下列各题。

（1）统计学生的平均年龄。

（2）统计计算机系学生的平均年龄。

（3）统计计算机系男生的平均年龄。

（4）统计平均成绩。

（5）统计201215121的平均成绩。

（6）统计2号课程的平均成绩。

（7）统计学生人数。

（8）统计计算机系的学生人数。

（9）统计计算机系的男生人数。

（10）统计选了课的学生人数。

（11）统计选了2号课程的学生人数。

（12）统计有成绩不及格(低于60分)的学生人数。

select avg(sage) from student;

select avg(sage) from student where sdept='CS';

select avg(sage) from student where sdept='CS' and ssex='男';

select avg(grade) from sc;

select avg(grade) from sc where sno='201215121';

select avg(grade) from sc where cno='2';

select count(*) from student;

select count(*) from student where sdept='CS';

select count(*) from student where sdept='CS' and ssex='男';

select count(distinct sno) from sc;

select count(*) from sc where cno='2';

select count(distinct sno) from sc where grade<60;

分组查询：基于第1题中的student、course、sc表，使用SQL查询语句完成下列各题。

（1）统计各系学生的平均年龄。

（2）统计各系男生的平均年龄。

（3）查询学生平均年龄在18岁以上的系。

（4）查询男生平均年龄在18岁以上的系。

（5）统计各系的学生人数。

（6）统计各系的男生人数。

（7）查询学生数超过200人的系。

（8）查询男生数超过200人的系。

（9）统计各系的男生、女生人数。

（10）统计CS系的男生、女生人数。

select sdept,avg(sage) from student group by sdept;

select sdept,avg(sage) from student where ssex='男' group by sdept;

select sdept from student group by sdept having avg(sage)>=18;

select sdept from student where ssex='男' group by sdept having avg(sage)>=18;

select sdept,count(*) from student group by sdept;

select sdept,count(*) from student where ssex='男' group by sdept;

select sdept from student group by sdept having count(*)>=200;

select sdept from student where ssex='男' group by sdept having count(*)>=200;

select sdept,ssex,count(*) from student group by sdept,ssex;

select ssex,count(*) from student where sdept='CS' group by ssex;

分组查询：基于第1题中的student、course、sc表，使用SQL查询语句完成下列各题。

（1）查询每个同学的平均成绩。

（2）查询每个同学60分以上的课程的平均成绩。

（3）查询平均成绩在86分以上的学生的学号。

（4）查询学生的学号，其60分以上课程的平均成绩在86分以上。

（5）统计每个同学的选课门数。

（6）统计每个同学成绩在90分以上的课程门数。

（7）查询至少选了两门课的学生的学号。

（8）查询至少有两门课成绩在90分以上的学生的学号。

select sno,avg(grade) from sc group by sno;

select sno,avg(grade) from sc where grade>=60 group by sno;

select sno from sc group by sno having avg(grade)>=86;

select sno from sc where grade>=60 group by sno having avg(grade)>=86;

select sno,count(*) from sc group by sno;

select sno,count(*) from sc where grade>=90 group by sno;

select sno from sc group by sno having count(*)>=2;

select sno from sc where grade>=90 group by sno having count(*)>=2;

排序：基于第1题中的student、course、sc表，使用SQL查询语句完成下列各题。

（1）查询学生信息，查询结果按系升序、年龄降序排序。

（2）查询选修3号课程的学生及其成绩，查询结果按成绩降序排序。

（3）查询每个学生的平均成绩，查询结果按平均成绩升序排序。

（4）查询平均成绩在86分以上的学生的学号、平均成绩，查询结果按平均成绩升序排序。

select * from student order by sdept,sage desc;

select sno,grade from sc where cno='3' order by grade desc;

select sno,avg(grade) from sc group by sno order by avg(grade);

select sno,avg(grade) from sc group by sno having avg(grade)>=86 order by avg(grade);

基于上一题中的student表、course表、sc表，写出相应的SQL语句，然后在MySQL中进行验证。

（1）向student表增加入学时间列，列名为sentrance，数据类型为日期型。

（2）删除student表中的入学时间列，列名为sentrance。

（3）修改student表中的sage列，列名为ssage，类型为INT，不允许取空值，默认值为21。

（4）为course表增加表级完整性约束条件——cname列的值不允许重复。

（1）ALTER TABLE student ADD sentrance DATE;

（2）ALTER TABLE student DROP sentrance;

（3）ALTER TABLE student CHANGE COLUMN sage ssage INT NOT NULL DEFAULT 21;

（4）ALTER TABLE course ADD UNIQUE(cname);

有如下三个关系（其中数据仅为部分数据），请用关系代数完成其后各题。