SQL_实验1.3 清华大学出版社

SQL_实验1.3 清华大学出版社

/*1.*/
insert
into students
values('70045678','LiMing','LX@cdemg.com',1992);

/*2.*/
create table choices_result
(
cid char(10),
students smallint,
avgscore smallint
);
insert into choices_result
select cid, count(sid), avg(score)
from choices
group by cid;

/*3*/
update students
set grade = '2002'
where sname = 'LiMing';

/*4.*/
update teachers
set salary=salary+500;


/*5.*/
update choices
set score=score+5
where choices.no=(
	select choices.no
	from choices,courses,students
	where courses.cid=choices.cid and students.sid = choices.sid
		and courses.cname = 'c' and students.sname = 'zapyv');

/*6.*/
delete students
where sname = 'LiMing';

/*7.*/
delete
from choices
where 'java' = (
	select cname
	from courses
	where courses.cid = choices.cid);

/*8.*/
delete
from choices
where choices.cid in(
select courses.cid
from courses
where courses.hour < 48);

delete courses
where hour < 48;