CREATE TABLE Course
(
cnum NUMBER(5) PRIMARY KEY ,
cname VARCHAR2(20)NOT NULL,
hrs NUMBER (1),
credit NUMBER(1)
);
CREATE TABLE DEPARTMENT
(
DNUM NUMBER(2) PRIMARY KEY,
DNAME VARCHAR2(20) NOT NULL,
DPHONE NUMBER(4) ,
DLOC NUMBER(5)
);
CREATE TABLE PROFESSOR
(
PNUM NUMBER(4) PRIMARY KEY,
BELONG NUMBER(2),
PNAME VARCHAR2(20) NOT NULL,
PPOS NUMBER(2),
PPHONE NUMBER(4),
FOREIGN KEY (BELONG) REFERENCES DEPARTMENT (DNUM)
);
CREATE TABLE STUDENT
(
SNUM NUMBER(7) PRIMARY KEY,
SNAME VARCHAR2(20) NOT NULL,
MAJOR NUMBER(2),
ADVISE NUMBER(4),
QPA NUMBER (3,2),
YEAR NUMBER (1),
FOREIGN KEY (MAJOR) REFERENCES DEPARTMENT (DNUM),
FOREIGN KEY (ADVISE) REFERENCES PROFESSOR (PNUM)
);
CREATE TABLE ENROL(
CNUM NUMBER(5) REFERENCES COURSE(CNUM),
SNUM NUMBER(7) REFERENCES STUDENT(SNUM),
GRADE NUMBER(3) CHECK (GRADE BETWEEN 0 AND 100),
PRIMARY KEY(CNUM, SNUM)
);
CREATE TABLE LECTURE(
CNUM NUMBER(5) REFERENCES COURSE(CNUM),
PNUM NUMBER(4) REFERENCES PROFESSOR(PNUM),
TIME NUMBER(2),
ROOM NUMBER(5),
PRIMARY KEY (CNUM,PNUM)
);
학과별 상위 4%에 해당되는 학생의 이름(sname)과 학번(snum), 학점(qpa)을 구하세요.
select snum rank() over (partition by snum order by qpa) as "rank"
from student
where student in (select dnum
from department
order by dname);
뭐가 오류인지 모르겠는데 설명해주실 수 있나요 ㅠㅠ
Comment 1
-
지영아빠
2022.12.02 18:10
과제인가요? 꼭 고민을 한번 더 해보세요.
작성하신 쿼리에는
1. 학과별을 잘 못 풀이한듯..
2. 4%라는 어디에...
3. sname, snum, qpa를 나오게 해달라고 했는데...
참고요
select
sname, snum, qpa
from
(
select
sname, snum, qpa, PERCENT_RANK() over(partition by MAJOR order by qpa desc) as prank
from
student
) a
where
cast(prank * 100 as int) <= 4
;