윤태영블로그

※ 정답은 맨 아래에 있습니다.

01.데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL은?

① 트리거(Trigger) ② 무결성(Integrity)

③ 잠금(Lock)        ④ 복귀(Rollback)

해설  : 

02.SQL에서 스키마(Schema),도메인(Domain), 테이블(Table), 뷰(View), 인덱스(Index)를 정의하거나 변경 또는 삭제할 때 사용하는 언어는?

① DML(Data Manipulation Language)

② DDL(Data Definition Language)

③ DCL (Data Control Language)

④ IDL(Interactive Data Language)

해설 :

03. 다음 BETWEEN 연산의 의미와 동일한 것은?

① 점수 >= 90 AND 점수 <= 95

② 점수 > 90 AND 점수 < 95

③ 점수 > 90 AND 점수 <= 95

④ 점수 >= 90 AND 점수 < 95

해설 :

04. SQL의 논리 연산자가 아닌 것은?

① AND

② OTHER

③ OR

④ NOT

해설 :

05.player 테이블에는 player_name, team_id, heigh 컬럼이 존재한다. 아래 SQL 문에서 문법적 오류가 있는 부분은?

해설 :

06."회사원"이라는 테이블에서 "사원명"을 검색할 때, "연락번호"가 NULL 값이 아닌 "사원명"을 모두 찾을 경우의 SQL 질의로 옳은 것은?

① SELECT 사원명 FROM 회사원 WHERE 연락번호 != NULL;

② SELECT 사원명 FROM 회사원 WHERE 연락번호 <>= NULL;

③ SELECT 사원명 FROM 회사원 WHERE 연락번호 IS NOT NULL;

④ SELECT 사원명 FROM 회사원 WHERE 연락번호 DON'T NULL;

해설 :

07.학적 테이블에서 전화번호가 NULL 값이 아닌 학생명을 모두 검색 할 때, SQL 구문으로 옳은 것은?

① SELECT 학생명 FROM 학적 WHERE 전화번호 DON'T NULL;

② SELECT 학생명 FROM 학적 WHERE 전화번호 != NOT NULL;

③ SELECT 학생명 FROM 학적 WHERE 전화번호 IS NOT NULL;
④ SELECT 학생명 FROM 학적 WHERE 전화번호 IS NULL;

해설 :

08.아래와 같은 결과를 만들어내는 SQL 문은?

[공급자 테이블]

[결과]

① SELECT * FROM 공급자 WHERE 공급자명 LIKE '%신%'

② SELECT * FROM 공급자 WHERE 공급자명 LIKE '대%'

③ SLEECT * FROM 공급자 WHERE 공급자명 LIKE '%사'

④ SELECT * FROM 공급자 WHERE 공급자명 LIKE '_사'

해설 : 

정답 : 

Source:  2023 수제비 정보처리기사

https://product.kyobobook.co.kr/detail/S000200275590

< Book Table >

< Cumtomer Table >

< Orders Table >

※ 해당 교재에 해설 및 풀이가 없으므로, 제가 푼 풀이가 틀릴 수도 있습니다.

1. 대한민국에 거주하는 고객의 고객명,주소,연락처를 보이는 프로시저를 작성하고 동작됨을 보이시오.

--프로시저 생성
CREATE OR REPLACE PROCEDURE GET_KR_USER_PRC(rc OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN rc FOR
   SELECT NAME,
         ADDRESS,
         PHONE
     FROM CUSTOMER c  WHERE ADDRESS LIKE '%대한민국%';
END GET_KR_USER_PRC;

--동작 확인
DECLARE
   rc  SYS_REFCURSOR;
   NAME CUSTOMER.NAME%TYPE;
   ADDRESS CUSTOMER.ADDRESS%TYPE;
   PHONE CUSTOMER.PHONE%TYPE;
BEGIN
   GET_KR_USER_PRC(rc);
   LOOP
    FETCH rc INTO NAME, ADDRESS, PHONE;
    EXIT
     WHEN rc%notfound;
    dbms_output.put_line(NAME || ',' || ADDRESS || ',' || PHONE);
  END LOOP;
END;

2. 도서 테이블에 동일도서(출판사,책이름이 동일)가 있으면 도서의 가격만 새롭게 변경하고 그렇지 않으면 새로운 행을 삽입하는 프로시저를 작성하고 동작됨을 보이시오.

--프로시저 생성
CREATE OR REPLACE PROCEDURE BOOK_PRICE_PRC(in_publisher IN BOOK.PUBLISHER%TYPE,
in_bookname IN BOOK.BOOKNAME%TYPE,
in_price BOOK.PRICE%TYPE)
AS d_book_id BOOK.BOOKID%TYPE;
d_max_book_id BOOK.BOOKID%TYPE;
   BEGIN
   BEGIN
   SELECT bookid INTO d_book_id
     FROM BOOK b  
    WHERE b.BOOKNAME = in_bookname
      AND b.PUBLISHER = in_publisher;
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
       d_book_id := 0;
    END;
   BEGIN
    SELECT MAX(bookid)+1 INTO d_max_book_id
     FROM BOOK b;
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
       d_max_book_id := 1;
   END;
   IF(d_book_id = 0)THEN
      INSERT INTO BOOK (BOOKID, BOOKNAME, PUBLISHER, PRICE)
      VALUES(d_max_book_id, in_bookname, in_publisher, in_price);
   ELSE
      UPDATE BOOK
      SET  PRICE=in_price
      WHERE BOOKID=d_book_id;
   END IF;
END BOOK_PRICE_PRC;

--동작 확인
SELECT * FROM BOOK;
call BOOK_PRICE_PRC('test3','test2',4000);
call BOOK_PRICE_PRC('대한미디어','축구의 이해',1000);
SELECT * FROM BOOK;

3. 특정 고객의 주문 총액을 계산하여 3만원 이상이면 ‘우수’, 3만원 미만이면 ‘일반’을 반환하는 함수 grade()를 작성하고 동작됨을 보이시오.

CREATE OR REPLACE FUNCTION grade(v_custid NUMBER)
    RETURN VARCHAR
IS
    v_grade varchar(100);
BEGIN
   SELECT CASE WHEN SUM(o.SALEPRICE) >= 30000
         THEN '우수'
         ELSE '일반'
         END INTO v_grade
      FROM ORDERS o 
    WHERE o.CUSTID=v_custid;

  RETURN v_grade;
END;

SELECT grade(2) AS grade
FROM dual;

4. 3번문에에서 작성한 사용자정의 함수 grade()를 호출하여 고객의 이름과 등급을 보이는 SQL문을 작성하고 실행결과를 보이시오.

SELECT c.NAME,
      grade(c.CUSTID) AS grade
  FROM CUSTOMER c;

 5. 고객별로 도서를 몇 권 구입 했는지와 총구매액을 보이는 프로시저를 작성하고 동작됨을 보이시오.

 --프로시저 생성
CREATE OR REPLACE PROCEDURE GET_BOOK_ORDER_BYCUST_PRC(rc OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN rc FOR
   SELECT o.CUSTID,
        COUNT(1) AS BOOK_COUNT,
        SUM(o.SALEPRICE) AS SALEPRICE 
   FROM ORDERS o 
  GROUP BY o.CUSTID ;
END GET_BOOK_ORDER_BYCUST_PRC;

--동작 확인
DECLARE
   rc  SYS_REFCURSOR;
   BOOK_COUNT number;
   CUSTID number;
   SALEPRICE number;
BEGIN
   GET_BOOK_ORDER_BYCUST_PRC(rc);
   LOOP
    FETCH rc INTO CUSTID, BOOK_COUNT, SALEPRICE;
    EXIT
     WHEN rc%notfound;
    dbms_output.put_line(CUSTID || ',' || BOOK_COUNT || ',' || SALEPRICE);
  END LOOP;
END;

6. 고객이 주문한 내용의 변경이 발생할 경우 로그테이블에 변경전 변경후 데이터를 기록하는 트리거를 작성하고 동작됨을 보이시오.

CREATE TABLE ORDERS_LOG
   (   
    "NO" NUMBER(10,0) PRIMARY KEY,
    "ORDERID" NUMBER(2,0), 
   "BEFORE_CUSTID" NUMBER(2,0), 
   "AFTER_CUSTID" NUMBER(2,0), 
   "BEFORE_BOOKID" NUMBER(2,0), 
   "AFTER_BOOKID" NUMBER(2,0), 
   "BEFORE_SALEPRICE" NUMBER(8,0), 
   "AFTER_SALEPRICE" NUMBER(8,0), 
   "BEFORE_ORDERDATE" DATE,
   "AFTER_ORDERDATE" DATE,
   "UPDATE" DATE
   ) ;
  
  --시퀀스 생성 
CREATE SEQUENCE ORDERS_LOG_NO_SEQ INCREMENT BY 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

--트리거 생성
CREATE OR REPLACE TRIGGER c##sql3.ORDERS_UPDATE
AFTER UPDATE 
ON ORDERS
FOR EACH ROW
BEGIN
   INSERT INTO ORDERS_LOG
   (
   "NO", 
   ORDERID, 
   BEFORE_CUSTID, 
   AFTER_CUSTID, 
   BEFORE_BOOKID, 
   AFTER_BOOKID, 
   BEFORE_SALEPRICE, 
   AFTER_SALEPRICE, 
   BEFORE_ORDERDATE, 
   AFTER_ORDERDATE, 
   "UPDATE")
   VALUES(
   ORDERS_LOG_NO_SEQ.nextval, 
   :OLD.ORDERID, 
   :OLD.CUSTID, 
   :NEW.CUSTID, 
   :OLD.BOOKID, 
   :NEW.BOOKID, 
   :OLD.SALEPRICE, 
   :NEW.SALEPRICE, 
   :OLD.ORDERDATE, 
   :NEW.ORDERDATE, 
   SYSDATE);
END;

UPDATE ORDERS
SET CUSTID=5, BOOKID=6, SALEPRICE=13000, ORDERDATE=TIMESTAMP '2022-07-03 00:00:00.000000'
WHERE ORDERID=11;

SELECT * FROM ORDERS_LOG;

7. 그룹함수를 이용하여 출판사별 고객별 판매액을 소계와 총계가 보이도록 DML문을 작성하고 실행해 보이시오. 

SELECT b.PUBLISHER, o.CUSTID, SUM(o.SALEPRICE) AS PRICE
  FROM ORDERS o 
  LEFT JOIN BOOK b ON o.BOOKID = b.BOOKID 
 GROUP BY ROLLUP(b.PUBLISHER, o.CUSTID) ;

 8. 윈도우 함수를 이용하여 출판사별 총판매금액, 판매순위를 산출하는 DML문 작성하고 실행해 보이시오.

SELECT b.PUBLISHER, SUM(o.SALEPRICE) AS PRICE,
RANK() OVER (ORDER BY SUM(o.SALEPRICE) DESC) AS RANKING
  FROM ORDERS o 
  LEFT JOIN BOOK b ON o.BOOKID = b.BOOKID 
 GROUP BY b.PUBLISHER ;

Reference : 오라클로 배우는 데이터베이스 개론과 실습

https://www.hanbit.co.kr/store/books/look.php?p_code=B2082711299

DEPARTMENT TABLE

EMPLOYELL TABLE

PROJECT TABLE

WORKS TABLE

※ 해당 교재에 해설 및 풀이가 없으므로, 제가 푼 풀이가 틀릴 수도 있습니다.

1. 테이블을 생성하는 create문을 작성하고 구조를 보이시오.

--구조보기
desc department;
desc employee;
desc project;
desc works;
--제약조건보기
select *
  from user_constraints
 where table_name in ('DEPARTMENT','EMPLOYEE','PROJECT','WORKS')
   AND constraint_type = 'P'; 
   
select * 
  from user_indexes;

2. 여자 사원의 이름, 연락처, 주소를 보이시오. 

select name "이름" ,phoneno "연락처" ,address "주소"
  from employee
 where sex = '여';

3. 팀장의 급여를 10%인상해 보이시오. (단, department테이블을 활용하시오.)

update employee t1
set salary = salary * 1.1
where empno = ( select t2.manager
                 from department t2
                where t2.deptno = t1.deptno);                
update employee t1
   set salary = salary * 1.1
 where empno in ( select t2.manager
                    from department t2);
commit;
select * from employee;

4. 사원 중 같은 성(姓)을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오.

select substr(t1.name,1,1) "성", count(*) "인원수"
from employee t1
group by substr(t1.name,1,1);

5. ‘영업팀’ 부서에서 일하는 사원의 이름, 연락처, 주소를 보이시오. (단 연락처 없으면 ‘연락처 없음’ , 연락처 끝4자리 중 앞2자리는 별표 * 로 표시하시오. 예) 010-111-**78 )

select t2.name "이름", 
       nvl2(phoneno,substr(phoneno,1,8) || '**' || substr(phoneno,11,2),'연락처없음') "연락처", 
       t2.address "주소"
  from department t1, employee t2
 where t1.deptno = t2.deptno
   and t1.deptname = '영업팀';

6. ‘홍길동7’ 팀장(manager) 부서에서 일하는 팀원의 수를 보이시오. 

select count(*) "팀원 수"
  from employee
 where deptno = ( select t2.deptno
                    from department t1, employee t2
                   where t1.deptno = t2.deptno
                     and t2.name = '홍길동7' )
   and name <> '홍길동7';

7. 프로젝트에 참여하지 않은 사원의 이름을 보이시오.

left outer join 사용

  select t1.name
    from employee t1, works t2
   where t1.empno = t2.empno(+)
     and t2.projno is null;

상관쿼리 사용

  select t1.name
    from employee t1
   where not exists ( select *
                        from works t2
                       where t2.empno = t1.empno );

not in 사용

  select name
    from employee
   where empno not in ( select distinct empno
                          from works );

차집합 사용

select name
  from employee
 where empno in (  select empno
                     from employee
                   minus
                    select distinct empno
                      from works );

8. 급여 상위 TOP 3를 순위와 함께 보이시오.

select rownum, t1.*
  from (  select *
            from employee
        order by salary desc ) t1
 where rownum <=3;

9. 사원들이 일한 시간 수를 부서별, 사원 이름별 오름차순으로 보이시오. 

    select t1.deptname "부서명", t2.name "이름", nvl(sum(t3.hoursworked),0) "일한 시간"
      from department t1, employee t2, works t3
     where t2.deptno = t1.deptno(+)
       and t2.empno  = t3.empno(+)
  group by t1.deptname, t2.name
  order by t1.deptname, t2.name;

10. 2명 이상의 사원이 참여한 프로젝트의 번호, 프로젝트명, 사원의 수를 보이시오. 

  select t2.projno "프로젝트 번호", 
         t2.projname "프로젝트명", 
         count(*) "사원수"
    from works t1, project t2
   where t1.projno = t2.projno 
group by t2.projno, t2.projname
  having count(*) >= 2;

11. 3명 이상의 사원이 있는 부서의 사원 이름을 보이시오. 

select t3.deptname "부서명", t4.name "이름"
  from department t3, employee t4
 where t3.deptno = t4.deptno
   and t3.deptno in  ( select t1.deptno
                         from department t1, employee t2
                        where t1.deptno = t2.deptno 
                     group by t1.deptno
                       having count(*) >= 3 );

12. 프로젝트에 참여시간이 가장 많은 사원과 적은 사원의 이름을 보이시오.

  select t2.empno "사번", t1.name "이름", sum(t2.hoursworked) "시간"
    from employee t1 , works t2
   where t1.empno = t2.empno 
group by t2.empno, t1.name
  having sum(t2.hoursworked) in ( (select max(sum(hoursworked))
                                     from works
                                 group by empno),
                                  (select min(sum(hoursworked))
                                     from works
                                 group by empno));


  select empno "사번",
         (select name 
            from employee 
           where empno = works.empno) "이름",
         sum(hoursworked) "시간"
    from works
group by empno
  having sum(hoursworked) in    ( (select max(sum(hoursworked))
                                     from works
                                 group by empno),
                                  (select min(sum(hoursworked))
                                     from works
                                 group by empno));

합집합 사용

 select t2.empno, t1.name, sum(t2.hoursworked)
    from employee t1 , works t2
   where t1.empno = t2.empno 
group by t2.empno, t1.name
  having sum(t2.hoursworked) in ( select max(sum(hoursworked))
                                     from works
                                 group by empno )
union                                 
  select t2.empno, t1.name, sum(t2.hoursworked)
    from employee t1 , works t2
   where t1.empno = t2.empno 
group by t2.empno, t1.name
  having sum(t2.hoursworked) in ( select min(sum(hoursworked))
                                     from works
                                 group by empno );

13. 사원이 참여한 프로젝트에 대해 사원명, 프로젝트명, 참여시간을 보이는 뷰를 작성하시오.

create or replace view vw_proj (name, projectname, hours)
as
    select t2.name, t3.projname, sum(t1.hoursworked)
      from works t1, employee t2, project t3
     where t1.empno  = t2.empno
       and t1.projno = t3.projno
  group by t2.name, t3.projname     
  order by t2.name, t3.projname;
select name "이름",projectname "프로젝트명",hours "참여시간"
  from vw_proj;

14. EXISTS 연산자로 ‘빅데이터 구축’ 프로젝트에 참여하는 사원의 이름을 보이시오.

select t1.name "이름" 
  from employee t1
 where exists ( select *
                  from project t2, works t3
                 where t2.projno = t3.projno
                   and t3.empno  = t1.empno 
                   and t2.projname = '빅데이터구축' );

15. employee 테이블의 name,phoneno 열을 대상으로 인덱스를 생성하시오. (단.인덱스명은 ix_employee2)

create index ix_employee2 on employee(name,phoneno);
select *
  from user_indexes
 where table_name = 'EMPLOYEE' 
   and index_name = 'IX_EMPLOYEE2';

Reference : 오라클로 배우는 데이터베이스 개론과 실습

https://www.hanbit.co.kr/store/books/look.php?p_code=B2082711299

※ 해당 교재에 해설 및 풀이가 없으므로, 제가 푼 풀이가 틀릴 수도 있습니다.

1. 고객의 이름, 주소, 연락처를 보이시오

select name "이름", address "주소", phone "연락처"
  from customer;

2. 대한민국에 거주하는 고객을 보이시오.

select name "이름", address "주소"
  from customer
 where address like '%대한민국%';

3. 연락처가 없는 고객의 이름을 보이시오.

select name "이름", phone "연락처"
  from customer
 where phone is null;

4. 서점에서 취급하는 출판사의 총 갯수를 보이시오.

select count(distinct publisher) "출판사 총개수"
  from book;

5. 도서 제목에 “골프”를 포함하는 도서를 보이시오.

select bookname "도서명"
  from book
 where bookname like '%골프%';

6. 주문건수, 평균판매액, 최대판매액, 최소판매액, 총 판매액을 보이시오.

select count(orderid) "주문건수", 
       avg(saleprice) "평균판매액", 
       max(saleprice) "최대판매액", 
       min(saleprice) "최소판매액", 
       sum(saleprice) "총 판매액"
  from orders;

7. 출판사별 도서건수를 내림차순으로 보이시오.

  select publisher "출판사", count(bookid) "도서건수"
    from book
group by publisher
order by count(bookid) desc;

8. 출판사별 도서 건수, 최고가격, 최저가격, 도서가격의 합을 출판사 이름순으로 보이시오.

  select publisher "출판사", 
         count(bookid) "도서건수", 
         max(price) "최고가격", 
         min(price) "최저가격", 
         sum(price) "도서가격의 합"
    from book
group by publisher
order by publisher;

9. 도서가격이 가장 비싼 도서와 가장 싼도서의 가격차이를 보이시오.

select max(price) "가장 비싼 도서 가격", 
       min(price) "가장 싼 도서가격",  
       max(price)-min(price) "차이"
  from book;

10. 고객 중 구매건수가 2회 이상인 고객번호, 구매건수를 구매건수 순으로 보이시오.

  select custid "고객번호", count(orderid) "구매건수"
    from orders
group by custid 
having count(orderid) >= 2
order by  count(orderid);

11. 2020년 7월 4일~7월 7일 사이에 주문 받은 도서를 제외한 도서의 주문번호를 보이시오.

select orderid "도서의 주문번호"
  from orders
 where not (orderdate between '20200704' and '20200707');
select orderid "도서의 주문번호"
  from orders
 where not (orderdate >= '20200704' and orderdate <= '20200707');
select orderid "도서의 주문번호"
  from orders
 where orderdate < '20200704' or orderdate > '20200707';

12. 주문일자별 매출액을 매출액 내림차순으로 보이시오.

  select orderdate "주문일자", sum(saleprice) "매출액"
    from orders
group by orderdate
order by sum(saleprice) desc;

13. 2020년 7월2일 이후에 주문일별 매출액이 20000원을 초과하는 주문일자를 최근 일자순 보이시오.

  select orderdate "주문일자", sum(saleprice) "매출액"
    from orders
   where orderdate > '20200702'   
group by orderdate
having sum(saleprice) > 20000
order by orderdate desc;

14. 출판사별 도서건수가 2건 이상인 출판사를 보이시오.

    select publisher "출판사", count(bookid) "도서건수"
      from book
  group by publisher
  having count(bookid) >= 2;

15. 새로운 도서가 아래 입고 되었다. 추가된 결과를 보이시오.

--    제목 : 데이터베이스, 출판사 : 한빛, 가격 : 30000
insert into book values(11,'데이터베이스','한빛',30000);
commit;
select * from book;

16. 출판사 “대한미디어”가 “대한출판사‘로 이름이 바뀌었다. 변경된 결과를 보이시오.

update book
   set publisher = '대한출판사'
 where publisher = '대한미디어';  
commit; 
select * from book;

17. 굿스포츠 출판사 도서의 가격을 10% 인상하였다. 변경된 결과를 보이시오.

update book
   set price = price * 1.1   -- price + price * 0.1
 where publisher = '굿스포츠';   
commit;
select * from book;

18. 추신수 고객의 주소가 “대한민국 울산”으로 변경되었다. 변경된 결과를 보이시오.

update customer
   set address = '대한민국 울산'
 where name = '추신수';
commit; 
select * from customer;

19. 전화번호가 없는 고객을 삭제하고 반영된 결과를 보이시오.

delete from customer
      where phone is null;
commit;
select * from customer;

20. ‘박지성’ 고객을 삭제해야 한다. 삭제가 안 될 경우 이유를 작성하시오.

delete from customer where name = '박지성';

자식 테이블에서 고객테이블에 박지성 고객의 튜블(행)을 참조하고 있어 참조 무결성 제약조건에 위배된다.

Reference : 오라클로 배우는 데이터베이스 개론과 실습

https://www.hanbit.co.kr/store/books/look.php?p_code=B2082711299 

Reference : https://school.programmers.co.kr/

[그룹별 조건에 맞는 식당 목록 출력하기]

다음은 고객의 정보를 담은 MEMBER_PROFILE테이블과 식당의 리뷰 정보를 담은 REST_REVIEW 테이블입니다. MEMBER_PROFILE 테이블은 다음과 같으며 MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH는 회원 ID, 회원 이름, 회원 연락처, 성별, 생년월일을 의미합니다.

REST_REVIEW 테이블은 다음과 같으며 REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT,REVIEW_DATE는 각각 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미합니다.

문제

MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

SELECT A.MEMBER_NAME, B.REVIEW_TEXT, TO_CHAR(B.REVIEW_DATE,'YYYY-MM-DD') AS REVIEW_DATE
FROM MEMBER_PROFILE A, REST_REVIEW B
WHERE A.MEMBER_ID = B.MEMBER_ID
AND A.MEMBER_ID IN (
SELECT MEMBER_ID FROM 
(
    -- 유저 ID 및 유저별 리뷰 수를 구하는 쿼리
    SELECT MEMBER_ID, COUNT(*) AS CNT FROM REST_REVIEW
    GROUP BY MEMBER_ID)
WHERE CNT = 
(
    -- 최대 리뷰 수 구하는 쿼리
    SELECT MAX(COUNT(*)) AS MAXCNT FROM REST_REVIEW
    GROUP BY MEMBER_ID)
)
ORDER BY B.REVIEW_DATE, B.REVIEW_TEXT;

OR

With을 써서, 이름을 직접 부여도 가능하다.

WITH review_counts AS (
  SELECT MEMBER_ID, COUNT(*) AS review_count
  FROM REST_REVIEW
  GROUP BY MEMBER_ID
),

max_count AS (
  SELECT MAX(review_count) AS max_review_count
  FROM review_counts
),

top_reviewers AS (
  SELECT MEMBER_ID
  FROM review_counts
  WHERE review_count = (SELECT max_review_count FROM max_count)
)

SELECT A.MEMBER_NAME, B.REVIEW_TEXT, TO_CHAR(B.REVIEW_DATE,'YYYY-MM-DD') AS REVIEW_DATE
FROM MEMBER_PROFILE A, REST_REVIEW B
WHERE A.MEMBER_ID = B.MEMBER_ID
AND A.MEMBER_ID IN (SELECT MEMBER_ID FROM top_reviewers)
ORDER BY B.REVIEW_DATE, B.REVIEW_TEXT;

출처 :https://programmers.co.kr/

[3월에 태어난 여성 회원 목록 출력하기]

[mysql]

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, "%Y-%m-%d")
FROM MEMBER_PROFILE
WHERE DATE_FORMAT(DATE_OF_BIRTH, '%m') = '03' AND GENDER = 'W' AND TLNO IS NOT NULL
ORDER BY MEMBER_ID
;

[oracle]

SELECT
    MEMBER_ID,
    MEMBER_NAME,
    GENDER,
    TO_CHAR(DATE_OF_BIRTH, 'YYYY-MM-DD') AS DATE_OF_BIRTH
FROM
    MEMBER_PROFILE
WHERE
    TLNO is not NULL AND
    TO_CHAR(DATE_OF_BIRTH, 'MM') = 3 AND
    GENDER = 'W'
ORDER BY
    MEMBER_ID ASC

[모든 레코드 조회하기]

문제 설명

동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요. SQL을 실행하면 다음과 같이 출력되어야 합니다.

[oracle],[mysql]

SELECT *
FROM ANIMAL_INS 
WHERE ANIMAL_ID like '%A%' 
ORDER BY ANIMAL_ID

SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_INS.ANIMAL_ID;

혹은 ORDER BY 절에서 ANIMAL_INS를 제외해도 된다.

[오프라인/온라인 판매 데이터 통합하기]

[ORACLE]

SELECT TO_CHAR(SALES_DATE,'YYYY-MM-DD') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM (
SELECT SALES_DATE, PRODUCT_ID, USER_ID AS USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE BETWEEN TO_DATE('2022-03-01','YYYY-MM-DD') AND TO_DATE('2022-03-31','YYYY-MM-DD')

UNION ALL

SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE BETWEEN TO_DATE('2022-03-01','YYYY-MM-DD') AND TO_DATE('2022-03-31','YYYY-MM-DD')
    )
ORDER BY TO_DATE(SALES_DATE,'YYYY-MM-DD') , PRODUCT_ID, USER_ID

[MYSQL]

SELECT 
    DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE
    ,PRODUCT_ID
    ,USER_ID
    ,SALES_AMOUNT 
FROM ONLINE_SALE 
WHERE 
    SALES_DATE >='2022-03-01 00:00:00' AND SALES_DATE <'2022-04-01 00:00:00'

UNION ALL
SELECT 
    DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE
    ,PRODUCT_ID
    , null AS user_id
    , SALES_AMOUNT 
FROM OFFLINE_SALE
WHERE 
    SALES_DATE >='2022-03-01 00:00:00' AND SALES_DATE <'2022-04-01 00:00:00'
ORDER BY 1,2,3

[인기있는 아이스크림]

[ORACLE],[MYSQL]

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;

[아픈 동물 찾기]

동물 보호소에 들어온 동물 중 아픈 동물의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.

예시

예를 들어 ANIMAL_INS 테이블이 다음과 같다면

ANIMAL_IDANIMAL_TYPEDATETIMEINTAKE_CONDITIONNAMESEX_UPON_INTAKE

이 중 아픈 동물은 Miller와 Cherokee입니다. 따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.

[ORACLE,MYSQL]

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE INTAKE_CONDITION = 'Sick' 
ORDER BY ANIMAL_ID

< low 추가 방법 >

1.번호 1부터 마지막줄까지 드래그 후 빨간 박스(row추가)를 누르면, 드래그 한 수만큼 추가가 된다. 

2.드래그 없이 로우 추가 시, 1줄씩 추가 된다. 

이후 원하는 Data를 입력하고, ctrl+s 혹은  빨간 박스 옆 쪽의 "Save"를 통해 저장 한다. 

< 테이블의 특정 컬럼 추출 방법 >

< Syntax >"SELECT 컬럼명 FROM 테이블명"

이후 빨간 박스를 눌러 실행.

< ORDER BY >

"카테고리"를 가나다 순으로 정렬 해보기

"카테고리"를 가나다 순으로 정렬 후, 가격순 정렬 하기

< 문자형 함수(Character Function) >

변환 함수

LOWER - 알파벳 값을 소문자로 변환

UPPER - 알파벳 값을 대문자로 변환

INITCAP - 첫번째 글자만 대문자로 변환

< 문자 조작 함수 >

CONCAT - 두 문자열을 연결(합성)

SUBSTR - 문자열 중 특정 문자 또는 문자열 일부분을 선택

LENGTH - 명명된 문자의 위치를 구함

LPAD - 왼쪽 문자 자리 채움

RPAD - 오른쪽 문자 자리 채움

LTRIM - 왼쪽 문자를 지움

RTRIM - 오른쪽 문자를 지움

TRANSLATE - 특정 문자열을 대체

REPLACE - 특정 문자열을 대신

문제 ) EMP 테이블에서 scott의 정보를 사원번호, 성명, 담당업무(소문자로),부서번호를 출력하여라.

SELECT empno,ename,LOWER(job),deptno
FROM emp
WHERE LOWER(ename) = 'scott';

< UPPER 함수 >

전부 다 대문자 변환 

문제 ) EMP 테이블에서 scott의 정보를 사원번호,성명,담당업무,부서번호를 출력하여라.

SELECT empno,ename,job,deptno
FROM emp
WHERE ename = UPPER('scott');

< INITCAP 함수 >

각 단어의 첫번째 문자를 대문자로, 나머지는 소문자로

문제 ) DEPT 테이블에서 첫 글자만 대문자로 변환하여 모든 정보를 출력하여라.

SELECT deptno,INITCAP(dname),INITCAP(Ioc)
FROM dept

< CONCAT 함수 >

두 개의 문자열을 합친다. (CONCAT은 두 개의 매개변수만 사용 가능하다.)

< LPAD 함수 >

문자 값을 우측부터 채운다.

Order By 문법

SELECT [DISTINCT] {*, column [alias], . . .}
       FROM table_name
       [WHERE condition]
       [ORDER BY {column, expression} [ASC | DESC]];

ORDER BY  : 검색된 행이 출력되는 순서를 명시한다.

ASC : 오름차순

DESC : 내림차순

문제 ) EMP 테이블에서 입사일자 순으로 정렬하여 사원번호, 이름, 업무, 급여, 입사일자, 부서번호를 출력하여라.

SELECT hiredate,empno,ename,job,sal,deptno
FROM emp
ORDER BY hiredate;

문제 ) EMP 테이블에서 가장 최근에 입사한 순으로 사원번호, 이름, 업무, 급여, 입사일자, 부서번호를 출력하여라.

SELECT hiredate,empno,ename,job,sal, deptno
FROM emp
ORDER BY hiredate DESC;

문제) EMP 테이블에서 부서번호로 정렬한 후 부서번호가 같을 경우 급여가 많은 순으로 정렬하여 사원번호, 성명, 업무, 부서번호, 급여를 출력하여라.

SELECT deptno,sal,empno,ename,job
FROM emp
ORDER BY deptno, sal DESC;

문제 ) EMP 테이블에서 첫번째 정렬은 부서번호로 두번째 정렬은 업무로 세번째 정렬은 급여가 많은 순으로 정렬하여 사원번호, 성명, 입사일자, 부서번호, 업무, 급여를 출력하여라.

SELECT deptno,job,sal,ename,hiredate
FROM emp
ORDER BY deptno,job,sal DESC;

< LIKE 연산자 >

문제 ) EMP 테이블에서 입사일자가 82년도에 입사한 사원의 정보를 사원번호, 성명, 담당 업무, 급여,입사일자, 부서번호를 출력하여라.

SELECT empno,ename,job,sal,hiredate,deptno
FROM emp
WHERE hiredate LIKE '%82';

< IS NULL 연산자 >

NULL값은 값이 없거나, 알 수 없거나, 적용할 수 없다는 의미이므로 NULL값을 조회하고자 할 경우 사용한다.

문제 ) EMP 테이블에서 보너스가 NULL인 사원의 정보를 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하여라.

SELECT empno,ename,job,sal,hiredate,deptno
FROM emp
WHERE comm IS NULL;

< AND 연산자 > 

양쪽의 조건이 참이어야 TRUE를 RETURN한다.

문제 ) EMP 테이블에서 급여가 1100 이상이고 JOB이 Manager인 사원의 정보를 사원번호,성명, 담당업무, 급여, 입사일자, 부서번호를 출력하여라.

SELECT empno, ename, job,sal,hiredate,deptno
FROM emp
WHERE sal >= 1100 AND job = 'MANAGER';

< OR 연산자 >

문제 ) EMP 테이블에서 급여가 1100이상이거나 JOB이 Manager인 사원의 정보를 사원번호,성명,담당업무,급여,입사일자,부서번호를 출력하여라.

SELECT empno,ename,job,sal,hiredate,deptno
FROM emp
WHERE sal >= 1100 OR job = 'MANAGER';

< NOT 연산자 >

1) NOT연산자의 우측의 값이 거짓이면 TRUE를 Return

문제 )  EMP테이블에서 급여가 JOB이 Manager,Clear,Analyst가 아닌 사원의 정보를 사원번호,성명,담당업무,급여,부서번호를 출력하여라.

SELECT empno,ename,job,sal,deptno
FROM emp
WHERE job NOT IN ('MANAGER','CLERK','ANALYST');

문제 ) 업무가 PRESIDENT이고 급여가 1500 이상이거나 업무가 SALESMAN인 사원의 정보를 사원번호,이름,업무,급여를 출력 하라.

SELECT empno,ename,job,sal
FROM emp
WHERE job = 'SALESMAN' OR job = 'PRESIDENT' AND sal > 1500;

 문제 ) 업무가 PRESIDENT 또는 SALESMAN이고 급여가 1500 이상이거나 사원의 정보를 사원 번호,이름,업무, 급여를 출력하여라.

SELECT empno,ename,job,sal
FROM emp
WHERE (job = 'SALESMAN' OR job = 'PRESIDENT') AND sal > 1500;