あの年、私たちがやったsqlクエリー

--1、    
--                  。
--1)              。
select sno,sname from student where sdept = '    '
--2)            。

select sno from sc where exists (select cno from sc)

--3)     100001           ,         ,             。
select sno from sc where cno = '100001' order by grade desc,sno asc

--4)     100008   ，    80~90           ,       0.8   。

select sno,grade * 0.8 grade from sc where cno = '100008' and grade>80 and grade<90

--5)                    。
select * from student  where  sname like ' %' and 
 (sdept = '    ' or sdept='    ')

--6)                 。
select sno,cno from sc where grade is null

--2、    
--                  。
--1)       、  、          。

select 
	a.sno,a.sname,b.cname,c.grade 
from 
	student a,course b,sc c 
where 
	a.sno=c.sno and b.cno = c.cno


--2)                  2        
select 
	a.*,b.*,c.grade
from 
	student a,course b,sc c 
where 
	a.sno=c.sno and b.cno = c.cno 
	and a.sdept='    ' and b.ccredit > 2

--3)            ( )           （                     ）。
select a.*,b.*,c.grade 
from (student a left join sc c on a.sno=c.sno) 
left join course b
 on  b.cno=c.cno


--4)         100001      90         、     。

select 
	a.sno,a.sname,c.cno,c.grade 
from 
	student a,sc c 
where 
a.sno = c.sno and c.grade > 90 and c.cno = '100001'

--5)            （        ）。
select a.cname,b.cpno 
from 
	course a,course b 
where 
	a.cpno = b.cname 
select cname,cpno from course

--3、    
--                  。
--1)                          。

select sno,sname from student where sno in 
(select sno from sc where cno in 
(select cno from course where cname = '        '))

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

--2)                 。

select 
	sno,sname,sage 
from 
	student 
where sage > 
	(select sage from student where sname = '  ')

select sage from student where sname = '  '


--3)    100001                  。

select sno,grade from sc where cno = '100001' and grade < 
(select grade from sc where sno = 
(select sno from student where sname='  ') and cno = '100001')

select grade from sc where sno = 
(select sno from student where sname='  ') and cno = '100001'

--4)                      。
select * from student where  sage < 
(select min(sage) from student where sdept='    ')

select min(sage) from student where sdept='    '


--5)       100001        。
select sno,sname from student where sno in 
(select sno from sc where cno = '100001')


select sno from sc where cno = '100001'



--6)        100001        。
select sno,sname from student where sno not in 
(select sno from sc where cno = '100001')


select sno from sc where cno = '100001'

--7)               。
select sname from student where 
not exists 
(select * from course where not exists 
(select * from sc where sno = student.sno and cno = course.cno))

--8)           “  ”                   。
select sno,sname from student b where not exists 
(select * from sc a where a.sno  in 
(select sno from student c where c.sname = '  ') 
and not exists 
(select cno from sc c where b.sno = c.sno and a.cno = c.cno)
) and sname <> '  '

--9）      JAVA                     。
select sno,sname from student where sno in
(select sno from sc where cno in 
(select cno from course where cname = 'JAVA'))  
and sno in 
(select sno from sc where cno = 
(select cno from course where cname = '        '))
select * from sc


--10）     JAVA                     。
select sno,cno from sc where cno in 
(select cno from course where  cname = '        ' or cname = 'JAVA')


select cno from course where  cname = '        ' or cname = 'JAVA'



--11）     JAVA                      。
select sno,cno  from sc where sno in
(select sno from sc where cno = (select cno from course where cname='JAVA')) and sno not in
(select sno from sc where cno =(select cno from course where cname='        '))

select * from sc


--12）              

select sno,cno from sc where 
sno in 
(select sno from student where ssex = ' ')

select sno from student where ssex = ' '

--4、         
--                  。
--1）            JAVA                     。
select a.sno,a.sname from 
	student a,course b,sc c where 
	a.sno = c.sno and b.cno = c.cno and b.cname = 'JAVA'
intersect
select a.sno,a.sname from 
	student a,course b,sc c where 
	a.sno = c.sno and b.cno = c.cno and b.cname = '        '

select * from sc

--2）           JAVA                     。
select c.sno,b.cname from course b,sc c 
where 
	b.cname = 'JAVA' and b.cno = c.cno 
	union 
		select c.sno,b.cname from course b,sc c 
		where 
			b.cname = '        ' and b.cno = c.cno


--3）           JAVA                       。
select c.sno from course b,sc c 
where 
	b.cname = 'JAVA' and b.cno = c.cno 
	except 
		select c.sno from course b,sc c 
		where 
			b.cname = '        ' and b.cno = c.cno

select * from sc


--4）            。
select  count(distinct sno) from sc

select distinct sno from sc

--5）              4           、   。

select sno,sum(grade)     from sc 
where 
	grade >= 60 
	group by 
		sno 
		having count(*) > 4

select * from sc


--6）          。
select 
	sdept   ,count(sdept)    
from 
	student group by sdept


--7）          。
select 
	sage   ,count(sage)   
from 
	student group by sage

--8）                  。
select a.sno,a.sname,count(c.cno),avg(c.grade) 
from 
student a left join sc c 
on a.sno=c.sno 
group by 
a.sname,a.sno
--9）                。（  ）
select b.cno,b.cname,b.ccredit,b.cpno,count(c.cno)      
from 
	course b left join sc c on b.cno = c.cno 
group by 
	b.cno,b.cname,b.ccredit,b.cpno