잡코딩 정보 블로그

SELECT PRODUCT_CODE, PRODUCT_NAME, UNIT_PRICE
FROM   EC_Product
WHERE  UNIT_PRICE > (SELECT AVG(UNIT_PRICE)
                     FROM   EC_Product);

SELECT ORDER_ID, PRODUCT_CODE, ORDER_QTY,CSEL,CMONEY
  FROM EC_Order
 WHERE CMONEY = (SELECT MAX(CMONEY)
                   FROM EC_Order);

SELECT CSEL "결제종류",ORDER_ID, CMONEY "입급액", CDATE "입급일자"
FROM   EC_Order
WHERE  CMONEY IN (SELECT MAX(CMONEY)
                   FROM EC_Order
                   GROUP BY CSEL);

SELECT USERID, NAME,REGIST_NO,TELEPHONE
FROM   EC_Member M
WHERE  EXISTS (SELECT *
                   FROM   EC_Order O
                   WHERE  M.USERID= O.ORDER_ID)
ORDER BY 2;

SELECT USERID, NAME,REGIST_NO,TELEPHONE
FROM   EC_Member M
WHERE  NOT EXISTS (SELECT *
                   FROM   EC_Order O
                   WHERE  M.USERID= O.ORDER_ID)
ORDER BY 2;

SELECT ORDER_NO, ORDER_ID, PRODUCT_CODE, ORDER_QTY, ORDER_DATE
FROM  EC_BASKET;

INSERT INTO EC_Order(ORDER_NO,ORDER_ID,PRODUCT_CODE,ORDER_QTY,CDATE)
SELECT*
FROM  EC_BASKET;

SELECT ORDER_NO, ORDER_ID, PRODUCT_CODE,ORDER_QTY, CMONEY
FROM   EC_Order
WHERE  CMONEY IS NULL;

DELETE FROM EC_Basket;

commit;

UPDATE EC_Order O
SET    CMONEY =ORDER_QTY*(SELECT UNIT_PRICE
                          FROM   EC_Product P
                          WHERE  O.PRODUCT_CODE=P.PRODUCT_CODE)
       ,Gubun='미결'
WHERE  CMONEY IS NULL;

select ORDER_NO, PRODUCT_CODE, ORDER_QTY, CMONEY, GUBUN
from   EC_Order
WHERE  Gubun='미결';

commit;

SELECT ROWNUM "순위", a.*
FROM (SELECT Product_CODE, Product_Name, UNIT_PRICE,COMPANY
      FROM   EC_Product
      ORDER  BY 3 DESC)a
WHERE ROWNUM<=5;

SELECT*
FROM (SELECT ROWNUM num, a.*
      FROM (SELECT Product_CODE, Product_Name, UNIT_PRICE,COMPANY
            FROM   EC_Product
            ORDER  BY 3 DESC)a
            )
WHERE num BETWEEN 11 AND 15;

CREATE TABLE Empty_Order
AS
       SELECT *
       FROM   EC_Order
       WHERE  1=2;
       
DESC Empty_Order

SELECT Order_ID, Product_Code, (SELECT COUNT(*)
                                FROM EC_Order A
                                WHERE A.Product_code=B.Product_code
                                ) "동일상품_구매자수"
FROM   EC_Order B                                
WHERE  Order_ID = 'jupark';

UPDATE EC_Member M
SET    BuyCash = BuyCash +
                 (SELECT SUM(CMoney)
                  FROM EC_Order O
                  WHERE O.Order_ID= M.UserID
                  AND CDate BETWEEN '2018/01/01' AND '2018/07/30'
                  AND Gubun IN ('배달', '결제'));

SELECT UserID, Name, TO_CHAR(BuyCash, 'L99,999,999')
FROM   EC_Member
WHERE  Buycash IS NOT NULL;

SELECT ROWNUM "순위", a.*
FROM  (SELECT Name "회원명", SUM(CMoney) "결제합계금액"
       FROM EC_Order O INNER JOIN EC_Member M ON O.Order_ID= M.UserID
       GROUP BY Name
       ORDER BY 2 DESC ) a
WHERE  ROWNUM <= 5;

SELECT ROWNUM "순위", a.*
FROM  (SELECT Product_Code, Product_Name, Order_QTY, Csel, CMoney, CDate
       FROM EC_Order INNER JOIN EC_Product USING (Product_Code)
       WHERE CMoney IS NOT NULL AND CDate IS NOT NULL
       ORDER BY CDate DESC ) a
WHERE  ROWNUM <= 5;

SELECT AVG(Course_Fees) FROM T_Course;

SELECT *
FROM Course
WHERE Course_Fees >= (SELECT AVG(Course_Fees) FROM Course)
ORDER BY 1;

SELECT *
FROM  T_Course
WHERE Course_ID LIKE 'L1%'
AND   Course_fees > ANY (SELECT Course_fees
                       FROM T_Course)
ORDER BY Course_Fees Desc;

SELECT Course_ID, Student_ID, Score, Score_Assigned
FROM   T_SG_Scores
WHERE (Course_ID, Score) IN (SELECT Course_ID, MAX(Score)
                             FROM T_SG_Scores
                             WHERE TO_CHAR(Score_Assigned, 'YY/MM') = '18/06'
                             GROUP BY Course_ID)
ORDER BY Course_ID;

SELECT *
FROM T_Course C
WHERE EXISTS (SELECT *
              FROM T_SG_Scores SG
              WHERE SG.Course_ID = C. Course_ID);

SELECT *
FROM   T_Course C
WHERE NOT EXISTS(SELECT *
                 FROM T_SG_Scores SG
                 WHERE SG.Course_ID = C.Course_ID)
ORDER BY 1;

CREATE TABLE SG_Score_2018
AS
   SELECT *
   FROM   SG_Scores
   WHERE  TO_CHAR(Score_Assigned, 'YY') = '18'
     AND  Score IS NOT NULL;

SELECT Student_ID, Course_ID, Score, Grade, Score_Assigned
FROM   SG_Score_2018
ORDER  BY 1, 2;

INSERT INTO T_Course
SELECT *
FROM Course
WHERE Course_Fees IS NULL;

SELECT ROWNUM "순위", a.*
FROM (SELECT Dept_ID, student_ID, Name, COUNT(Course_ID), ROUND(AVG(Score),2) "평균"
      FROM SG_Scores INNER JOIN Student USING (Student_ID)
      GROUP BY dept_ID, Student_ID, Name
      HAVING COUNT(Course_ID) > 3
      ORDER BY 5 DESC )a
WHERE ROWNUM <= 3;

SELECT *
FROM (SELECT Title, Grade
      FROM SG_Scores INNER JOIN Course USING (Course_ID) )
PIVOT (
       COUNT(*)
       FOR grade IN ('A+', 'A', 'B+', 'B', 'C+', 'C', 'D+', 'D', 'F ')
       )
ORDER BY 1;

SELECT ORDER_ID,P.PRODUCT_CODE, PRODUCT_NAME,ORDER_QTY,CMONEY "주문금액"
FROM   EC_Order P, EC_Product S
WHERE  P.PRODUCT_CODE=S.PRODUCT_CODE
  AND  ORDER_ID='jupark'
ORDER  BY 1;

SELECT ORDER_ID,PRODUCT_CODE, PRODUCT_NAME,ORDER_QTY,CMONEY "주문금액"
FROM   EC_Order INNER JOIN EC_Product USING(PRODUCT_CODE)
WHERE  ORDER_ID='jupark'
ORDER  BY 1;

COLUMN 배달상품 FORMAT A14
SELECT NAME, TELEPHONE, ADDRESS, PRODUCT_NAME "배달상품", ORDER_QTY "수량"
FROM   EC_Order P, EC_Member S, EC_Product Y
WHERE  P.ORDER_ID=S.USERID
  AND  P.PRODUCT_CODE=Y.PRODUCT_CODE
  AND  GUBUN ='결제'
ORDER  BY 1;

SELECT NAME, TELEPHONE, ADDRESS, PRODUCT_NAME "배달상품", ORDER_QTY "수량"
FROM   EC_Order P INNER JOIN EC_Member S
                    ON(P.ORDER_ID=S.USERID)
                  INNER JOIN EC_Product Y
                    ON(P.PRODUCT_CODE=Y.PRODUCT_CODE)
WHERE  GUBUN ='결제'
ORDER  BY 1;

SELECT PRODUCT_NAME "주문상품",ORDER_QTY "수량",
       TO_CHAR(UNIT_PRICE,'L99,999,999') "단가",
       ORDER_ID, 
       TO_CHAR(ORDER_QTY*UNIT_PRICE,'L99,999,999') "주문금액"
FROM   EC_PRODUCT P, EC_ORDER C
WHERE  P.Product_CODE=C.PRODUCT_CODE(+)
ORDER  BY 4;

SELECT PRODUCT_NAME "주문상품",ORDER_QTY "수량",
       TO_CHAR(UNIT_PRICE,'L99,999,999') "단가",
       ORDER_ID, 
       TO_CHAR(ORDER_QTY*UNIT_PRICE,'L99,999,999') "주문금액"
FROM   EC_Product LEFT OUTER JOIN EC_Order USING(Product_CODE)
ORDER  BY 4;

SELECT PRODUCT_CODE,PRODUCT_NAME,UNIT_PRICE,LEFT_QTY
FROM   EC_Product
WHERE  PRODUCT_CODE NOT IN (SELECT PRODUCT_CODE
                            FROM   EC_Order)
ORDER BY 1;

SELECT USERID, NAME, TIMESTAMP
FROM EC_Member
WHERE  USERID NOT IN (SELECT ORDER_ID
                            FROM   EC_Order)
ORDER BY 1;

SELECT Name, Regist_No, O.Product_Code, Order_Qty, Cmoney
FROM EC_Member M INNER JOIN EC_Order O ON (M.UserID = O.Order_ID) 
ORDER BY 1;

SELECT Name, Regist_No, Telephone
FROM EC_Member M LEFT OUTER JOIN EC_Order O ON (M.UserID = O.Order_ID) 
WHERE O.Order_ID is NULL
ORDER BY 1;

SELECT Name, Regist_No, Product_Name, Order_Qty, Unit_Price, SUBSTR(Address, 1,2) "거주지"
FROM EC_Member M INNER JOIN EC_Order O ON (M.UserID=O.Order_ID)
                 INNER JOIN EC_Product P ON (O.Product_Code =
P.Product_Code)
WHERE Address LIKE '서울%'
ORDER BY 1;

SELECT Dept_ID, Year, TS.Student_ID, Name, TS. Course_ID, Title, C_Number
FROM   T_SG_Scores TS, Student S, T_Course TC
WHERE  TS.Student_ID = S.Student_ID
  AND  TS.Course_ID = TC. Course_ID
  AND  TS. Student_ID = 'C1801'
ORDER  BY 5;

SELECT Dept_ID, Year, Student_ID, Name, Course_ID, Title, C_Number, Grade
FROM T_SG_Scores INNER JOIN Student USING (Student_ID)
                 INNER JOIN T_Course USING (Course_ID)
WHERE Student_ID = 'C1801'
ORDER BY 5;

SELECT Student_ID, Course_ID, Title, C_Number "학점", Grade,
       CASE Grade WHEN 'A+' THEN 4.5 WHEN 'A' THEN 4.0
                  WHEN 'B+' THEN 3.5 WHEN 'B' THEN 3.0
                  WHEN 'C+' THEN 2.5 WHEN 'C' THEN 2.0
                  WHEN 'D+' THEN 1.5 WHEN 'D' THEN 1.0
                            ELSE 0.0 
        END "등급평점",
        CASE Grade WHEN 'A+' THEN 4.5 WHEN 'A' THEN 4.0
                   WHEN 'B+'  THEN 3.5 WHEN 'B' THEN 3.0
                   WHEN 'C+' THEN 2.5 WHEN 'C' THEN 2.0
                   WHEN 'D+' THEN 1.5 WHEN 'D' THEN 1.0 
                            ELSE 0.0
        END * C_Number "과목평점"
FROM    T_SG_Scores INNER JOIN Student USING (Student_ID)
                    INNER JOIN T_Course USING (Course_ID)
WHERE   Student_ID = 'C1802'
ORDER   BY Course_ID;

SELECT Dept_ID, Year, Student_ID, Name, Count (1) "과목수", SUM(Score) "총점",
       TO_CHAR(ROUND (AVG(Score), 2), '999.99') "평균"
FROM   T_SG_Scores SG INNER JOIN Student S USING (Student_ID)
WHERE  Score IS NOT NULL
GROUP  BY Dept_ID, Year, Student_ID, Name
Order  BY 3 DESC;

SELECT Dept_Name "소속", decode (Duty,'총장',''
                            ,'학과장','   L______'
                            , NULL, '           L______') || Duty ||
     '  '|| Name || '  ' || Position "직책 및 성명"
FROM Professor P INNER JOIN Department D ON (P.Dept_ID = D.Dept_ID)
START WITH mgr is NULL
CONNECT BY prior Professor_id = Mgr ;

SELECT Course_ID, Title, C_Number
FROM   Course
INTERSECT
SELECT Course_ID, Title, C_Number
FROM   SG_Scores INNER JOIN Course USING (Course_ID)
ORDER BY 1;

SELECT Course_ID, Title, C_Number
FROM SG_Scores INNER JOIN Course USING (Course_ID)
WHERE Student_ID LIKE 'C18%'
UNION
SELECT Course_ID, Title, C_Number
FROM SG_Scores INNER JOIN Course USING (Course_ID)
WHERE Student_ID LIKE 'C17%'
MINUS
SELECT Course_ID, Title, C_Number
FROM SG_Scores INNER JOIN Course USING (Course_ID)
WHERE Student_ID LIKE 'C16%'
ORDER BY 1;

SELECT COUNT(*)"총회원수"
FROM   EC_Member;

SELECT MIN(CMONEY),MAX(CMONEY)
FROM   EC_Order;

SELECT   ORDER_ID "주문자 ID", TO_CHAR(SUM(CMONEY),'L999,999,999')"결제합계"
FROM     EC_Order
GROUP BY ORDER_ID;

SELECT PRODUCT_CODE, PRODUCT_NAME, UNIT_PRICE,
         RANK() OVER (ORDER BY UNIT_PRICE DESC) "상품단가순위"
FROM     EC_Product
ORDER BY 3 DESC;

SELECT UNIQUE SUBSTR(ADDRESS,1,2)"시도별",
COUNT(*) "인원수"
FROM EC_Member
GROUP BY ROLLUP(SUBSTR(ADDRESS,1,2))
ORDER BY 1;

SELECT CASE GROUPING(SUBSTR(CDATE,1,5)) WHEN 0 THEN SUBSTR(CDATE,1,5)
                                        WHEN 1 THEN ' 합계금액'
       END "결제년월",
       TO_CHAR(SUM(CMONEY),'L999,999,999') "판매금액계"
FROM EC_Order
GROUP BY ROLLUP(SUBSTR(CDATE,1,5))
ORDER BY GROUPING(SUBSTR(CDATE,1,5)),1;

SELECT CASE GROUPING_ID(ORDER_ID, PRODUCT_CODE) WHEN 0 THEN ORDER_ID
                                                WHEN 1 THEN NULL
                                                WHEN 2 THEN ' 상품 집계'
                                                WHEN 3 THEN ' 전체 합계'
       END "주문자_ID",
       CASE GROUPING_ID(ORDER_ID, PRODUCT_CODE) WHEN 0 THEN PRODUCT_CODE
                                                WHEN 1 THEN ' 회원집계'
                                                WHEN 2 THEN PRODUCT_CODE
                                                WHEN 3 THEN NULL
                                                
                                       
       END "주문상품", TO_CHAR(SUM(CMONEY),'L999,999,999') "결제금액"
FROM   EC_Order
WHERE TO_CHAR(CDATE,'YY/MM') >='18/01' AND TO_CHAR(CDATE,'YY/MM')<='18/03'
GROUP  BY CUBE(ORDER_ID,PRODUCT_CODE)
ORDER BY ORDER_ID;

SELECT TO_CHAR(Cdate, 'YYYY/MM') "결제년월", Count(2) "주문건수",
       SUM(Order_QTY) "주문수량 합계", TO_CHAR(SUM(CMONEY),'L999,999,999')"결제합계"
FROM EC_ORDER
WHERE Cdate IS NOT NULL
GROUP BY TO_CHAR(Cdate, 'YYYY/MM')
ORDER BY 1;

SELECT DECODE(SUBSTR(REGIST_NO,8,1),1,'남자',2,'여자',3,'남자',4,'여자')
"성별", COUNT(*) "회원수"
FROM EC_MEMBEr
GROUP BY DECODE(SUBSTR(REGIST_NO,8,1),1,'남자',2,'여자',3,'남자',4,'여자');

SELECT TO_CHAR(Cdate, 'YYYY/MM') "결제년월", TO_CHAR(MAX(CMoney),'L999,999,999') "결제최대금액"
FROM   EC_ORDER
WHERE  CDATE IS NOT NULL
GROUP  BY TO_CHAR(Cdate, 'YYYY/MM')
ORDER BY 1;

SELECT CASE GROUPING_ID(Order_ID, Product_Code) WHEN 1 THEN NULL
                                                       ELSE Order_ID
            END "주문자_ID",
            CASE GROUPING_ID(Order_ID, Product_Code) WHEN 1 THEN '     소계'
                                                    WHEN 3 THEN '전체합계'
                                                        ELSE Product_Code
            END "주문상품",
            COUNT(*) "구매횟수", TO_CHAR(SUM(Cmoney),'L999,999,999')"결제금액"
FROM       EC_Order
WHERE      CDate IS NOT NULL
GROUP      BY ROLLUP(Order_ID, Product_Code)
ORDER      BY ORder_ID;

SELECT Order_ID, Product_Code, TO_CHAR(SUM(Cmoney),'L999,999,999') "결제금액"
FROM   EC_Order
WHERE CDAte IS NOT NULL
GROUP BY GROUPING SETS(Order_ID, Product_Code)
ORDER BY 1,2;

SELECT Order_ID, CDATE "결제일자", Csel "결제방법", TO_CHAR(CMoney,'L999,999,999') "결제금액",
       DENSE_RANK() OVER(ORDER BY CMoney DESC) "순위"
FROM   EC_Order
WHERE  CDate IS NOT NULL
ORDER BY 4 DESC;

SELECT Student_ID, COUNT(1) "과목수", SUM(Score) "총점", AVG(Score) "평균"
FROM   T_SG_Scores
WHERE  Score IS NOT NULL
GROUP  BY STudent_ID
ORder  BY 3 DESC;

SELECT Student_ID, SUM(Score) "총점", TO_CHAR(AVG(Score), '99.9') "평균",
       RANK()  OVER(ORDER BY AVG(Score) DESC) "석차"
FROM   SG_Scores
GROUP  BY Student_ID
ORDER  BY 4;

SELECT COUNT(*) "총게시물수" FROM FRee_Board;
SELECT CASE WHEN MAX(B_ID) IS NULL THEN 1
                                    ELSE MAX(B_ID)+1
       END "게시물번호"
FROM   Free_BOARD;

SELECT NAME, REGIST_NO,DECODE(SUBSTR(REGIST_NO,8,1),'1','남','2','여',
                                                    '3','남','4','여')
                                                    AS 성별
FROM EC_MEMBER
ORDER BY 3;

SELECT NAME,REGIST_NO,EXTRACT(YEAR FROM SYSDATE)
       -(DECODE(SUBSTR(REGIST_NO,8,1),'1', '19','2','19','20') || 
       SUBSTR(REGIST_NO,1,2)) +1 AS 나이,
       TO_CHAR(SYSDATE,'YYYY/MM/DD') AS 기준년월일
FROM EC_Member
ORDER BY 3;

SELECT ORDER_ID, PRODUCT_CODE AS 주문상품,ORDER_QTY AS 수량,
       TO_CHAR(CMONEY,'L99,999,999') AS 결제금액, 
       TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD') 
       - TO_DATE(TO_CHAR(CDATE,'YYYYMMDD'),'YYYYMMDD') "결제 후 일자",
       TO_CHAR(SYSDATE,'YYYY/MM/DD') AS 기준일
FROM EC_Order
WHERE GUBUN='결제' AND MDATE IS NULL;

SELECT PRODUCT_CODE,PRODUCT_NAME,
       TO_CHAR(UNIT_PRICE,'L99,999,999') AS 단가,
       TO_CHAR(UNIT_PRICE*0.9,'L99,999,999') "10%할인된금액"
FROM EC_Product
WHERE UNIT_PRICE>=1000000;

SELECT ORDER_ID,PRODUCT_CODE,CMONEY AS 결제금액,TO_CHAR(CDATE,'YY/MM/DD') AS 결제일자,
       DECODE(SUBSTR(GUBUN,1,2),'결제','배달준비'
                                ,'미결','결제대기'
                                ,'취소','반품','대기')AS 처리내용
FROM EC_Order
WHERE NOT GUBUN='배달';

SELECT NAME, SUBSTR(NAME,1,1) AS 성,TELEPHONE,ADDRESS
FROM   EC_Member
WHERE Address LIKE '서울%'
ORDER BY 1;

SELECT Product_Code,Standard, CONCAT(Left_Qty,Unit) "재고수량",
Company
FROM Ec_product
WHERE Product_Name LIKE '%컴퓨터%';

SELECT Order_ID, Product_Code, CMoney,CDate "결제일자",
       TO_CHAR(Cdate, 'YYYY/MM') "결제년월"
FROM   EC_Order
WHERE  CDATE IS NOT NULL AND CMONEY >=1000000
ORDER  BY CDate;

SELECT Name, REGIST_NO, TimeStamp "가입일자",
       Trunc(MONTHS_Between(Current_DATE,Timestamp) /12) || '년' ||
       MOD(Trunc(MONTHS_Between(Current_Date,Timestamp)),12)||'월' AS 가입기간,
       CURRENT_DATE "기준일자"
FROM   EC_MEMBER
WHERE  Trunc(MONTHS_Between(Current_Date, Timestamp)/12) >=1
ORDER  BY 1;

SELECT Order_ID, CMoney, Csel, Cdate,
       ExTRACT(YEAR FROM CDATE) "결제년도",
       EXTRACT(MONTH FROM CDATE) "결제 월"
FROM   EC_Order
WHERE  CDATE IS NOT NULL AND Csel='신용카드'
ORDER  BY 4;

UPDATE T_SG_Scores
SET    GRADE = CASE WHEN Score BETWEEN 95 AND 100 THEN 'A+'
                    WHEN Score BETWEEN 90 AND 94  THEN 'A'
                    WHEN Score BETWEEN 85 AND 89  THEN 'B+'
                    WHEN Score BETWEEN 80 AND 84  THEN 'B'
                    WHEN Score BETWEEN 75 AND 79  THEN 'C+'
                    WHEN Score BETWEEN 70 AND 74  THEN 'C'
                    WHEN Score BETWEEN 65 AND 69  THEN 'D+'
                    WHEN Score BETWEEN 60 AND 64  THEN 'D'
                                                  ELSE 'F'
                    END
WHERE  GRADE IS NULL;

SELECT*FROM T_SG_SCores;
COMMIT;

SELECT Student_ID, Course_ID, Score, Floor(Score/5)"몫",MOD(Score,5)"나머지"
FROM   T_SG_Scores
WHERE  Student_ID='C1801'
ORDER  BY 3 DESC;

SELECT Student_ID, Course_ID, Grade,
       CASE Grade When 'A+' THEN '4.5' WHEN 'A ' THEN '4.0'
                  When 'B+' THEN '3.5' WHEN 'B ' THEN '3.0'
                  When 'C+' THEN '2.5' WHEN 'C ' THEN '2.0'
                  When 'D+' THEN '1.5' WHEN 'D ' THEN '1.0'
                            ELSE '0.0'
       END "평점"
FROM   T_SG_Scores
WHERE  Student_ID='C1801';

SELECT Course_ID, Title,C_Number,Professor_ID, NVL(Course_fees,0)
FROM   T_Course
WHERE  Course_fees IS NULL;

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

SELECT CURRENT_DATE "기준일자와 기준시간", CURRENT_DATE+5 "5일 후",
       CURRENT_DATE+4/24 "4시간 후"      FROM DUAL;

SELECT Product_Code, Product_Name, Unit_Price, Company
FROM   EC_Product
ORDER  BY CASE Product_Name WHEN '개인용컴퓨터' THEN 1
                            WHEN '노트북컴퓨터' THEN 2
                            WHEN '프린터'      THEN 3
                            WHEN 'TV'         THEN 4
                                              ELSE 5
          END, 3 DESC;

INSERT INTO EC_Product
(PRODUCT_CODE,PRODUCT_NAME,STANDARD,UNIT_PRICE,LEFT_QTY,COMPANY,IMAGENAME)
VALUES
('SP01','아이폰','IPHONES8+64G',816000,10,'APPLE','sp01.jpg');
INSERT INTO EC_Product
(PRODUCT_CODE,PRODUCT_NAME,STANDARD,UNIT_PRICE,LEFT_QTY,COMPANY,IMAGENAME)
VALUES
('SP02','갤럭시노트','NOTE8+256G',829000,15,'SAMSUNG','sp02.jpg');
INSERT INTO EC_Product
(PRODUCT_CODE,PRODUCT_NAME,STANDARD,UNIT_PRICE,LEFT_QTY,COMPANY,IMAGENAME)
VALUES
('SP03','G7 ThinQ','G7+128G',822000,10,'LG전자','sp03.jpg');

SELECT PRODUCT_CODE,PRODUCT_NAME,STANDARD,UNIT_PRICE,LEFT_QTY,COMPANY,IMAGENAME
FROM EC_Product
WHERE PRODUCT_CODE IN('SP01','SP02','SP03');

UPDATE EC_Order
SET   MDATE='18/05/10', GUBUN='배달'
WHERE  ORDER_ID='kcchoi';

SELECT ORDER_NO,PRODUCT_CODE,ORDER_QTY,CMONEY,MDATE,GUBUN
FROM   EC_Order
WHERE  MDATE='18/05/10';

UPDATE EC_Order
SET CSEL='신용카드',CDATE='18/07/12',GUBUN='결제'
WHERE ORDER_NO='180707001';

UPDATE EC_Order
SET CSEL='신용카드',CDATE='18/07/12',GUBUN='결제'
WHERE ORDER_NO='180707002';

UPDATE EC_Order
SET CSEL='신용카드',CDATE='18/07/12',GUBUN='결제'
WHERE ORDER_NO='180707003';

SELECT ORDER_NO,PRODUCT_CODE,ORDER_QTY,CSEL,CMONEY,CDATE
FROM EC_Order
WHERE CDATE='18/07/12'
ORDER BY 1;

UPDATE EC_Member
SET    BUYCASH=0
WHERE  NVL(BUYCASH,0)=0;

COMMIT;

INSERT INTO T_Course
(Course_ID, Title, C_Number,Professor_ID,Course_fees)
VALUES
('L3001','스프링 프로젝트',3,'P11',50000);

INSERT INTO T_Course
(Course_ID, Title, C_Number,Professor_ID,Course_fees)
VALUES
('L3002','모바일프로그래밍',3,'P12',null);

INSERT INTO T_Course
(Course_ID, Title, C_Number,Professor_ID,Course_fees)
VALUES
('L3003','데이터베이스튜닝',2,'P13',50000);

INSERT INTO T_Course
(Course_ID, Title, C_Number,Professor_ID,Course_fees)
VALUES
('L3004','빅데이터 개론',2,'P14',30000);

SELECT * FROM T_Course;

INSERT INTO T_SG_Scores (Student_ID,Course_ID,Score_Assigned)
                 VALUES ('C1801','L3001','18/02/23');              
INSERT INTO T_SG_Scores (Student_ID,Course_ID,Score_Assigned)
                 VALUES ('C1801','L3002','18/02/23');              
INSERT INTO T_SG_Scores (Student_ID,Course_ID,Score_Assigned)
                 VALUES ('C1801','L3003','18/02/23');                
INSERT INTO T_SG_Scores (Student_ID,Course_ID,Score_Assigned)
                 VALUES ('C1801','L3004','18/02/23');  
                 
INSERT INTO T_SG_Scores (Student_ID,Course_ID,Score_Assigned)
                 VALUES ('C1802','L3001','18/02/24');                
INSERT INTO T_SG_Scores (Student_ID,Course_ID,Score_Assigned)
                 VALUES ('C1802','L3002','18/02/24');               
INSERT INTO T_SG_Scores (Student_ID,Course_ID,Score_Assigned)
                 VALUES ('C1802','L3003','18/02/24');                
INSERT INTO T_SG_Scores (Student_ID,Course_ID,Score_Assigned)
                 VALUES ('C1802','L1051','18/02/24');   
                 
SELECT*FROM T_SG_Scores ORDER BY 1,2;

UPDATE T_SG_Scores
SET COURSE_ID='L3004'
WHERE STUDENT_ID='C1802' AND COURSE_ID='L1051';

UPDATE T_SG_Scores
SET Score=98,Score_Assigned='2018/06/28'
WHERE Student_ID='C1801' AND Course_ID='L3001';
UPDATE T_SG_Scores
SET Score=87,Score_Assigned='2018/06/28'
WHERE Student_ID='C1801' AND Course_ID='L3002';
UPDATE T_SG_Scores
SET Score=92,Score_Assigned='2018/06/28'
WHERE Student_ID='C1801' AND Course_ID='L3003';
UPDATE T_SG_Scores
SET Score=89,Score_Assigned='2018/06/28'
WHERE Student_ID='C1801' AND Course_ID='L3004';

UPDATE T_SG_Scores
SET Score=88,Score_Assigned='2018/06/28'
WHERE Student_ID='C1802' AND Course_ID='L3001';
UPDATE T_SG_Scores
SET Score=92,Score_Assigned='2018/06/28'
WHERE Student_ID='C1802' AND Course_ID='L3002';
UPDATE T_SG_Scores
SET Score=93,Score_Assigned='2018/06/28'
WHERE Student_ID='C1802' AND Course_ID='L3003';
UPDATE T_SG_Scores
SET Score=97,Score_Assigned='2018/06/28'
WHERE Student_ID='C1802' AND Course_ID='L3004';

SELECT*FROM T_SG_Scores ORDER BY 1,2;

DELETE FROM T_Course
WHERE Course_ID='L2033';

UPDATE T_Course
SET Course_fees=30000
WHERE Course_ID='L1031';

COMMIT;
SET AUTOCOMMIT OFF;

DELETE FROM T_Course
WHERE Course_ID LIKE 'L4%';

ROLLBACK;

SELECT PRODUCT_CODE,PRODUCT_NAME,UNIT_PRICE
FROM EC_Product
WHERE UNIT_PRICE >1000000;

SELECT USERID,NAME,REGIST_NO,ADDRESS
FROM EC_Member
WHERE ADDRESS LIKE '서울%';

SELECT ORDER_NO, ORDER_ID, PRODUCT_CODE,ORDER_QTY,ORDER_DATE
FROM EC_Basket
WHERE ORDER_DATE='18/07/11';

SELECT ORDER_NO, PRODUCT_CODE,ORDER_QTY,CSEL,CMONEY,CDATE,GUBUN
FROM EC_Order
WHERE GUBUN ='결제'  AND GUBUN != '배달' ;

SELECT USERID, NAME,REGIST_NO
FROM EC_Member
WHERE NAME LIKE '%우%';

SELECT ORDER_NO, ORDER_ID, PRODUCT_CODE,CSEL,GUBUN
FROM EC_Order
WHERE GUBUN ='배달' AND CSEL !='신용카드'
ORDER BY 2;

SELECT PRODUCT_CODE,PRODUCT_NAME,UNIT_PRICE,LEFT_QTY,COMPANY
FROM EC_Product
WHERE UNIT_PRICE BETWEEN 300000 AND 500000
ORDER BY 3 DESC;

SELECT ORDER_NO,ORDER_ID,CMONEY,CDATE,CSEL
FROM EC_Order
WHERE CSEL='신용카드';

SELECT ORDER_NO,ORDER_ID,PRODUCT_CODE,ORDER_QTY,CMONEY,GUBUN
FROM EC_Order
WHERE GUBUN IS NULL;

SELECT ORDER_ID,PRODUCT_CODE,ORDER_QTY,CSEL,CMONEY,GUBUN
FROM EC_Order
WHERE ORDER_ID = 'usko';

SELECT PRODUCT_CODE,PRODUCT_NAME,STANDARD,UNIT_PRICE,LEFT_QTY,COMPANY
FROM EC_Product
WHERE COMPANY IN('SAMSUNG','Samsung','samsung')
ORDER BY 2;

SELECT PRODUCT_CODE,PRODUCT_NAME,STANDARD, UNIT_PRICE,LEFT_QTY,COMPANY
FROM EC_Product
WHERE PRODUCT_NAME='프린터' AND Unit_Price >500000
ORDER BY 4 DESC;

SELECT NAME,REGIST_NO,TELEPHONE,TIMESTAMP
FROM EC_Member
WHERE NAME LIKE '정%';

SELECT ORDER_NO,PRODUCT_CODE,CSEL,CMONEY,CDATE
FROM EC_Order
WHERE CSEL IN('현금입금','계좌이체')
ORDER BY 3,5;

SELECT PRODUCT_CODE,PRODUCT_NAME,UNIT_PRICE,LEFT_QTY
FROM EC_Product
WHERE COMPANY='HP' AND PRODUCT_NAME='프린터'
ORDER BY 3;

SELECT PRODUCT_CODE,PRODUCT_NAME,STANDARD,UNIT_PRICE,LEFT_QTY,COMPANY
FROM EC_Product
WHERE COMPANY NOT IN ('SAMSUNG','Samsung','samsung','LG전자')
ORDER BY 6,4;

SELECT NAME,REGIST_NO,TELEPHONE,ADDRESS
FROM EC_Member
WHERE ADDRESS NOT LIKE '서울%' AND ADDRESS NOT LIKE '대구%'
ORDER BY 4;

SELECT PRODUCT_CODE,PRODUCT_NAME,STANDARD,UNIT_PRICE,LEFT_QTY,COMPANY
FROM EC_Product
WHERE LEFT_QTY <10
ORDER BY 2;

SELECT NAME,REGIST_NO,TELEPHONE,ADDRESS
FROM EC_Member
WHERE TELEPHONE LIKE '%666%'
ORDER BY 1;

SELECT NAME,REGIST_NO, TELEPHONE, ADDRESS, TIMESTAMP
FROM EC_Member
WHERE TIMESTAMP >'2018/05/01'
ORDER BY 5;

SELECT PRODUCT_CODE,PRODUCT_NAME,STANDARD,UNIT_PRICE,LEFT_QTY,COMPANY
FROM EC_Product
WHERE PRODUCT_NAME LIKE '노트%'
ORDER BY 6;

SELECT PRODUCT_CODE,PRODUCT_NAME,STANDARD,UNIT_PRICE,LEFT_QTY,COMPANY
FROM EC_Product
WHERE COMPANY LIKE 'LG%' AND UNIT_PRICE>800000
ORDER BY 1;

SELECT PRODUCT_CODE,PRODUCT_NAME,STANDARD,UNIT_PRICE,LEFT_QTY,COMPANY
FROM EC_Product
WHERE COMPANY IS NULL
ORDER BY 1;

SELECT ORDER_NO,ORDER_ID,PRODUCT_CODE,ORDER_QTY,CMONEY
FROM EC_Order
WHERE Product_CODE='CM01' AND ORDER_QTY>1
ORDER BY 2;

SELECT NAME,REGIST_NO,TELEPHONE,ADDRESS
FROM EC_Member
WHERE NAME NOT BETWEEN '고' AND '쵸'
ORDER BY 1;

SELECT COURSE_ID, STUDENT_ID,SCORE,SCORE_ASSIGNED
FROM SG_Scores
WHERE COURSE_ID IN('L1011','L1021')
ORDER BY 1,2;

SELECT STUDENT_ID, COURSE_ID, SCORE,SCORE_ASSIGNED
FROM SG_Scores
WHERE NOT SCORE >=80;

SELECT DEPT_ID, YEAR, STUDENT_ID,NAME,TELEPHONE
FROM Student
WHERE DEPT_ID NOT IN('컴공','경영')
ORDER BY 1,3;

CREATE TABLE EC_Product (
Product_Code VARCHAR2(10),
Product_Name VARCHAR2(20) NOT NULL,
Standard     VARCHAR2(20),
Unit         VARCHAR2(10),
Unit_Price   NUMBER(7)    NOT NULL,
Left_Qty     NUMBER(5)    NOT NULL,
Company      VARCHAR2(20),
ImageName    VARCHAR2(20),
Info         VARCHAR2(50),
Detail_Info  VARCHAR2(255),
CONSTRAINT EC_Product_pk PRIMARY KEY(Product_Code));

CREATE TABLE EC_Member (
UserID    VARCHAR(10),
Passwd    VARCHAR(10)   NOT NULL,
Name      VARCHAR(10)   NOT NULL,
Regist_No VARCHAR(14)   CONSTRAINT EC_Member_uk UNIQUE NOT NULL,
Email     VARCHAR(20),
Telephone VARCHAR(13)   NOT NULL,
Address   VARCHAR(40),
Buycash   NUMBER(9)     DEFAULT 0,
Timestamp DATE          DEFAULT SYSDATE,
CONSTRAINT EC_Member_pk PRIMARY KEY(UserID));

CREATE TABLE EC_Basket(
Order_No     VARCHAR(10),
Order_ID     VARCHAR(10) NOT NULL,
Product_Code VARCHAR(10) NOT NULL,
Order_Qty    NUMBER(3)   NOT NULL,
Order_Date   DATE        DEFAULT SYSDATE,
CONSTRAINT EC_Basket_pk  PRIMARY KEY(Order_No),
CONSTRAINT EC_Basket_fk1 FOREIGN KEY(Order_ID)     REFERENCES EC_Member(UserId),
CONSTRAINT EC_Basket_fk2 FOREIGN KEY(Product_Code) REFERENCES EC_Product(Product_Code));

CREATE TABLE EC_Order(
Order_No     VARCHAR2(10),
Order_ID     VARCHAR2(10) NOT NULL,
Product_Code VARCHAR2(10) NOT NULL,
Order_Qty    NUMBER(3)    NOT NULL,
Csel         VARCHAR2(10),
Cmoney       NUMBER(9),
Cdate        DATE,
Mdate        DATE,
Gubun        VARCHAR2(10),
CONSTRAINT EC_Order_pk PRIMARY KEY(Order_No));

SELECT * FROM TAB;

DESC EC_Basket

ALTER TABLE EC_Product DROP COLUMN Detail_info;

ALTER TABLE EC_Product MODIFY(INFO VARCHAR2(40));

ALTER TABLE EC_Order MODIFY(Gubun VARCHAR2(20));

ALTER TABLE EC_Order
ADD CONSTRAINT EC_Order_fk
    FOREIGN KEY(Product_Code) REFERENCES EC_Product(Product_Code);

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME ='EC_ORDER';

SELECT CONSTRAINT_NAME,COLUMN_NAME,POSITION
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME ='EC_ORDER';

CREATE TABLE Board(
B_Id      NUMBER(5),
B_Name    VARCHAR2(20)   NOT NULL,
B_Pwd     VARCHAR2(20)   NOT NULL,
B_Email   VARCHAR2(20)   NOT NULL,
B_Title   VARCHAR2(80)   NOT NULL,
B_Content VARCHAR2(2000) NOT NULL,
B_Date    DATE           DEFAULT SYSDATE,
B_Hit     NUMBER(5)      DEFAULT 0,
B_IP      VARCHAR2(15),
CONSTRAINT Board_pk PRIMARY KEY(B_Id));

ALTER TABLE Board
ADD(B_Ref  NUMBER(5) DEFAULT 0,
    B_Step NUMBER(5) DEFAULT 0, 
    B_Order NUMBER(5) DEFAULT 0);

ALTER TABLE Board
MODIFY(B_TiTLE VARCHAR2(100));

ALTER TABLE Board
MODIFY(B_Pwd VARCHAR2(20) NULL);

ALTER TABLE Board
DROP COLUMN B_Ip;

DESC Board

ALTER TABLE EC_MEMBER
ADD(CONSTRAINT Member_CK CHECK(UserID BETWEEN 'a' AND 'z'));
INSERT INTO EC_Member(UserId,Passwd,Name,Regist_No,Telephone)
VALUES('srlee','1234','이소라','821001-2******','010-1234-1234');
INSERT INTO EC_Member(UserId,Passwd,Name,Regist_No,Telephone)
VALUES('20park','1234','박연수','810604-1******','010-2345-2345');

ALTER TABLE Board
RENAME COLUMN B_Step TO B_Level;

ALTER TABLE EC_Order DROP PRIMARY KEY;

ALTER TABLE Board
MODIFY(B_Email VARCHAR2(20) CONSTRAINT BOARD_uk UNIQUE);

RENAME Board TO FREE_Board;

DROP TABLE EC_Basket;

PURGE RECYCLEBIN;