sql第二次实验

select sname
from students
where grade = '2001'
order by sid asc

select tid,cid,score,'point fo socer:',(score-50)/10 
from choices
where score>60

select cname
from courses
where hour in('48','64')

where hour = '48' or hour = '64'

select cid
from courses
where cname like '%data%'

select distinct cid 
from choices

select AVG(salary) as avg
from teachers

select *
from students

select *
from choices

select sid from choices,sname,avg(score)as avg_score
from students
group by tid
order by avg_score desc

select choices.sid ,sname, avg(score)as avg_score
from choices,students
where students.sid = choices.sid
group by choices.sid ,sname
order by avg_score desc


select *
from choices

select cid,count(no),avg(score)
from choices
group by cid

;9
select sid
from choices
group by sid
having count(*) > 3

;10
select cname,score
from choices , courses
where sid = '800009026' and choices.cid = courses.cid

select sid , cid
from choices
where sid = '800009026'


select sid
from choices,courses 
where cname = 'database' and choices.cid = courses.cid


/*13*/

select cid
from choices 
group by cid
having count(*) > 2

/*14*/

select y.sid
from choices as x,choices as y
where x.cid = y.cid and x.sid = '850955252'

/*chong fu*/

select * from students

/*15*/
select students.sid,sname,grade,cid,score
from students join choices on students.sid = choices.sid

/*16*/
select sname,cname,score
from students,choices,courses
where students.sid = choices.sid and choices.cid =  courses.cid and students.sid = '850955252'

/*17*/
select *
from students
where grade = (select grade
from students
where sid = '850955252') 

/*18*/
select *
from students
where sid in(select sid
from choices)

/*19*/
select cid
from courses
where cid not in(
select cid
from choices)

/*20*/
select sid,sname
from students
where sid IN (select sid
			from choices
			where cid IN (select cid
						from courses
						where cname = 'c++'))

/*21 ???*/
select *
from choices
where choices.score <all
		(select score
		from choices
		where score is not null
)

/*22*/
select cname
from courses
where hour = some
(select hour
from courses
where cname = 'UML' or cname = 'c++')

/*23*/
select sname
from students
where sid IN 
(select sid 
from choices
where cid = '10001')

select sname
from students
where exists
(
select *
from choices x
where x.cid = '10001' and x.sid = students.sid
)

/*24*/
select sname
from students
where not exists
(select *
from courses as x
where not exists
	(select *
	from choices as y
	where y.sid = students.sid and y.cid = x.cid
	)
)

/*25*/
select distinct sid
from choices
where cid IN
		(select cid
		from courses
		where cname = 'C++' or cname = 'Java')

select sid 
from choices
where choices.cid = 
(select courses.cid
from courses
where cname = 'C++')
union
select sid 
from choices
where choices.cid = 
(select courses.cid
from courses
where cname = 'Java')

/*26嵌套麻烦*/
select distinct x.sid
from choices as x, choices as y
where (x.cid = 
		(select cid
		from courses
		where cname = 'Java' )and y.cid = 
			(select cid 
			 from courses
			 where cname = 'c++')
		)and x.sid = y.sid

5952

select *
from courses

select *
from choices
where cid = '10004' or cid = '10005'

/*26*/
select sid 
from choices
where choices.cid = 
(select courses.cid
from courses
where cname = 'C++')
intersect
select sid 
from choices
where choices.cid = 
(select courses.cid
from courses
where cname = 'Java')
6031 6110  306 


/*27*/
select sid from choices
where choices.cid =
	(select cid
	from courses
	where cname = 'c++'
	)
except
select sid from choices
where choices.cid =
	(select cid
	from courses
	where cname = 'java'
	)
5570

select cid
from choices
where sid = '880258634 '

select *
from courses

select sid from choices
where choices.cid =
	(select cid
	from courses
	where cname = 'java'
	)
except
select sid from choices
where choices.cid =
	(select cid
	from courses
	where cname = 'c++'
	)
5646

select sid 
from choices
where choices.cid = 
(select courses.cid
from courses
where cname = 'java')
except
select sid 
from choices
where choices.cid = 
(select courses.cid
from courses
where cname = 'c++')


/*1*/
select *
from courses

/*2*/
select sid
from choices

/*3*/
select cid
from courses
where hour < 88

/*4*/
select sid,sum(score)as scores
from choices
group by sid
having sum(score) > 400

select *
from choices

/*5*/
select count(cid)as num
from courses

/*6hao*/
select courses.cid,count(sid) as num
from choices left outer join courses on(choices.cid=courses.cid)
group by courses.cid
order by courses.cid


/*6*/
select cid,count(sid) as num
from choices
group by cid
order by cid

/*7*/
select distinct sid
from choices
where score > 60
group by sid
having count(score)>2

select *
from choices
where sid = '800001216  '

select count(distinct sid)
from choices

/*8*/
select sid,count(sid) num,avg(score) avg_score
from choices
group by sid

/*9*/
select students.sid,sname
from students,choices,courses
where students.sid = choices.sid and choices.cid = courses.cid and courses.cname='java'

/*10*/
select cid,score
from choices,students
where students.sid = choices.sid and students.sname = 'sssht'

select cid,score
from choices
where sid in
		(select sid
		from students
		where sname='sssht')

/*11*/
select cname
from courses
where hour > 
		(select hour
		from courses
		where cname = 'c++')
/*12*/
select students.sid,sname
from students,choices,courses
where students.sid = choices.sid and choices.cid = courses.cid and score > 
	(select score
	from students,choices,courses
	where students.sid = choices.sid and choices.cid = courses.cid and sname = 'znkoo' and cname = 'c++')
and choices.cid = 
	(select cid
	from courses
	where cname='c++')

select *
from choices
where sid ='800014004   ' 
c++ 10005 883794999   82

/*13*/
select distinct sname
from students
where grade IN 
	(select grade
	from students
	where sid = '883794999' or sid = '850955252')


select *
from students
where sname = 'fmkjoish'
1997 2001

/*14*/
select students.sname
from students
where not exists
	(select *
	from choices
	where students.sid = choices.sid and choices.cid = 
		(select cid 
		from courses
		where courses.cname = 'java'
		)
	)

/*15*/
select *
from courses
where hour <= all
	(select hour
	from courses)

/*16*/
select c.tid,cid
from teachers as t,choices as c
where t.tid = c.tid and salary in
	(select max(salary)
	from teachers)


select*
from teachers

/*17*/
select sid
from choices,courses
where choices.cid = courses.cid and score in
	(select max(score)
	from choices,courses
	where courses.cname = 'ERP' )
and cname = 'ERP'

99 10012
select *
from choices
where sid ='844440501 '

/*18*/
select cname
from courses
where not exists
	(select *
	from choices
	where choices.cid = courses.cid)

/*19*/
select distinct cname
from choices,courses,teachers
where choices.cid = courses.cid and choices.tid = teachers.tid and choices.tid in
	(select choices.tid
	from choices,courses,teachers
	where choices.cid = courses.cid and choices.tid = teachers.tid and cname = 'uml')

select *
from choices
where cid = '10007'

10007
select distinct cid
from choices
where tid = '237947994 '
select *
from courses
where
10002 10004 operating system  java      

/*20*/                                            
select sid
from choices
where tid = '200102901'
group by sid
having count(cid)=
(select count(distinct cid)
from choices
where tid='200102901')


select count(distinct cid)
from choices
where tid='200102901'

select count(distinct sid)
from choices
where tid='200102901'

select *
from choices
where tid='200102901'
/*21*/
select sid
from choices
where cid = 
	(select cid
	from courses
	where cname = 'Database')
union
select sid
from choices
where cid = 
	(select cid
	from courses
	where cname = 'uml')


select *
from courses

select *
from choices
where sid = '840399946  '

/*22*/
select sid
from choices
where cid = 
	(select cid
	from courses
	where cname = 'Database')
intersect
select sid
from choices
where cid = 
	(select cid
	from courses
	where cname = 'uml')

/*23*/
select sid
from choices
where cid = 
	(select cid
	from courses
	where cname = 'Database')
except
select sid
from choices
where cid = 
	(select cid
	from courses
	where cname = 'uml')
 

select sid
from choices
where cid = 
	(select cid
	from courses
	where cname = 'uml') 
except
select sid
from choices
where cid = 
	(select cid
	from courses
	where cname = 'Database')
1 not 2 :5454
all :303
2 not 1 :5522
5898 303

var code = "2d5ae14f-7b13-4217-89a8-4a6b70a049a9"