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'
[4월13일]중요한 수업내용 서브쿼리 차집합 교집합 합집합 (0) | 2023.04.13 |
---|---|
[오라클]4월 13일 문제와 문제풀이 (0) | 2023.04.13 |
[오라클]4월 13일 수업 예제 문제만 (1) | 2023.04.13 |
[4월 12일 쿼리]수업내용 (0) | 2023.04.12 |
[sql intellij 연동문제] (0) | 2023.04.10 |
댓글 영역