[쿼리문제]4.12

 

select*
from student
where avr between 3.0 and 4.0;

--2
select *
from PROFESSOR
where HIREDATE between to_date('1994-01-01', 'yyyy-MM-dd HH24:mi:ss') and to_date('1995-01-01' ,'yyyy-MM-dd HH24:mi:ss');



select* from STUDENT
where MAJOR in ('물리', '화학');

select * from PROFESSOR
where ORDERS in ('정교수','조교수');

select * from COURSE
where ST_NUM in (3);

select * from student
where SYEAR in (1,2) and AVR between 2.0 and 3.0;


select * from STUDENT
where MAJOR in ('화학', '물리') and SYEAR in (1,2);

select * from PROFESSOR
where HIREDATE< to_date('1995-01-01','yyyy-MM-dd HH24:mi:ss');

---------------------------------------------
select *
from COURSE
where pno = (
select PNO
from PROFESSOR
where PNAME = '송강'
);

select c.*, p.pname
from PROFESSOR p, COURSE c
where c.PNO = p.PNO and PNAME = '송강';

select c.*, p.pname
from COURSE c
join PROFESSOR P on c.PNO = P.PNO and p.PNAME = '송강';


select p.pname, c.cname
from PROFESSOR p, COURSE c
where c.pNO = p.PNO and c.CNAME like '%화학%';

select p.pname ,c.cname
from PROFESSOR p
join COURSE c
on p.pno = c.PNO and c.CNAME like '%화학%';

--5
select s.SNAME,sc.result
from student s, score sc
where s.sno = sc.SNO;

select s.sname, sc.result
from student s
join score sc
on s.SNO = sc.SNO;

select sc.result, c.CNAME
from score sc, course c
where sc.CNO = c.CNO and c.CNAME = '일반화학';
--6
select c.CNAME, sc.RESULT
from course c
join score sc
on sc.CNO = c.CNO and c.CNAME = '일반화학' ;
--7
select score.result , student.sname, student.SYEAR, course.CNAME
from score score, student student, course course
where score.SNO = student.SNO and score.CNO = course.CNO and course.CNAME = '일반화학' and student.SYEAR = 1;



select student.sname, course.cname, score.result
from student student, course course, score score
where student.SNO = score.SNO and course.CNO =score.CNO and student.SYEAR=1 and course.CNAME='일반화학';


select student.sname, student.syear, course.cname, score.result
from student student
join score score
on student.SNO = score.SNO and student.SYEAR = 1
join course course
on course.CNO = score.CNO and course.CNAME='일반화학';


select course.cname, student.syear, student.major
from course course, student student, score score
where course.CNO = score.CNO and student.SNO = score.SNO and student.MAJOR = '화학' and student.SYEAR=1
group by course.cname, student.syear, student.major
;

select distinct course.cname, student.syear, student.major
from course course, student student, score score
where course.Cno = score.cno and student.SNO = score.SNO and student.SYEAR = 1 and student.MAJOR = '화학'

select Distinct student.syear, student.major, course.cname
from student student
join score score
on student.sno = student.sno and student.SYEAR = 1 and student.MAJOR = '화학'
join course course
on course.CNO = score.cno;

select student.sname, course.cname, scgrade.grade
from student student, course course, scgrade scgrade, score score
where student.sno = score.SNO and score.RESULT between scgrade.LOSCORE and scgrade.HISCORE and course.CNO = score.CNO and

course.CNAME='유기화학' and GRADE = 'F'

;



select student.sname, scgrade.grade, course.cname ,score.result
from student student
join score score
on student.sno = score.sno
join course course
on course.CNO = score.CNO and course.CNAME = '유기화학'
join SCGRADE scgrade
on score.RESULT between scgrade.LOSCORE and scgrade.HISCORE and scgrade.GRADE = 'F'


select Distinct student.sname 진짜이름, student.SNO
from student student, student student2
where student.SNAME = student2.SNAME and student.sno <> student2.sno;

select DISTINCT student.sname, student.sno
from student student
join student student2
on student.SNAME=student2.SNAME and student.SNO<>student2.SNO;


--동명이인을 구하는법
-- SELECT DISTINCT s1.sname AS 진짜이름, s1.sno
-- FROM student s1
-- INNER JOIN (
-- SELECT sname
-- FROM student
-- GROUP BY sname
-- HAVING COUNT(*) > 1
-- ) s2
-- ON s1.sname = s2.sname 이렇게도 풀수있음

--------------------------------------------------------중요
select student.sname, student.sno
from student student
join (
select sname from student
group by sname
having count(*)>1


) s2
on student.sname = s2.SNAME;


------------------------------------------------------------------중요

select distinct student.sname, student.sno
from student student, student student2
where student.SNAME=student2.SNAME and student.sno<>student2.sno

select student.sname, student.sno
from student student
join (
select SNAME
from student
group by sname
having count(*)>1

)s2 on student.SNAME = s2.sname;



select DISTINCT professor.pname,course.cname, student.major
from PROFESSOR professor, course course, student student, score score
where professor.PNO=course.PNO and student.SNO=score.SNO and score.CNO=course.CNO
Order by MAJOR;

select course.cname, professor.pname
from course course, PROFESSOR professor
where course.PNO=professor.PNO
order by course.ST_NUM


-------------------------------------11번

select DISTINCT course.cname, professor.pname
from PROFESSOR professor, course course, score score
where professor.PNO = course.PNO and score.CNO = course.CNO;

select student.sname, student.major, score.result, course.CNAME
from student student, score score ,course course
where student.SNO = score.SNO and score.CNO = course.cno and student.MAJOR='화학' and student.SNAME='유선';

select course.cname, scgrade.grade, student.sname
from course course, scgrade scgrade, student student, score score
where course.CNO = score.CNO and score.sno = student.sno and score.RESULT between scgrade.LOSCORE and scgrade.HISCORE and student.SNAME = '노소'

select professor.pname,student.MAJOR,course.CNAME, student.SNAME
from professor professor, student student, course course, score score
where professor.PNO = course.PNO and student.SNO=score.SNO and course.CNO = score.CNO and student.SNAME = '노식' and student.sno = 925601;

select professor.pname, course.cname
from PROFESSOR professor, course course
where professor.PNO(+) = course.PNo


-- ALTER SESSION set NLS_date_format = 'yyyyMMdd HH24:mi:Ss'