꿈꾸는 개발자, DBA 커뮤니티 구루비

MySQL좀 도와주세요ㅠㅠ

by ajunr MySQL [2021.11.30 19:49:07]

DROP DATABASE IF EXISTS univDB;
CREATE DATABASE IF NOT EXISTS univDB;

use univDB;

create table S(
	SNO char(4) not null,
    SNAME char(20) not null,
    STATUS int not null,
    CITY char(20) not null,
    primary key(SNO)
    );

create table P(
	PNO char(4) not null,
    PNAME char(20) not null,
    COLOR char(8) not null,
    WEIGHT int not null,
    CITY char(20) not null,
	primary key (PNO)
    );
create table SP(
	SNO char(4) not null,
    PNO char(4) not null,
    QTY int not null,
    primary key(SNO, PNO)
	);
    
    insert into S(SNO,SNAME, STATUS, CITY)
    values ('S1', 'Smith', 20, 'London');
    insert into S(SNO,SNAME, STATUS, CITY)
    values ('S2', 'Jones', 10, 'Paris');
    insert into S(SNO,SNAME, STATUS, CITY)
    values ('S3', 'Blakes', 30, 'Paris');
    insert into S(SNO,SNAME, STATUS, CITY)
    values ('S4', 'Clark', 20, 'London');
    insert into S(SNO,SNAME, STATUS, CITY)
    values ('S5', 'Adams', 30, 'Athenes');

    insert into P(PNO,PNAME, COLOR, WEIGHT, CITY)
    values ('P1', 'NUT', 'Red', 12, 'London');
    insert into P(PNO,PNAME, COLOR, WEIGHT, CITY)
    values ('P2', 'Bolt', 'Green', 17, 'Paris' );
    insert into P(PNO,PNAME, COLOR, WEIGHT, CITY)
    values ('P3', 'Screw', 'Blue', 17, 'Rome');
    insert into P(PNO,PNAME, COLOR, WEIGHT, CITY)
    values ('P4', 'Screw', 'Red', 14, 'London');
    insert into P(PNO,PNAME, COLOR, WEIGHT, CITY)
    values ('P5', 'Cam', 'Blue', 12, 'Paris');
    insert into P(PNO,PNAME, COLOR, WEIGHT, CITY)
    values ('P6', 'Cog', 'Red', 19, 'London');

    insert into SP(SNO,PNO, QTY)
    values ('S1', 'P1', 300);
    insert into SP(SNO,PNO, QTY)
    values ('S1', 'P2', 200);
    insert into SP(SNO,PNO, QTY)
    values ('S1', 'P3', 400);
    insert into SP(SNO,PNO, QTY)
    values ('S1', 'P4', 200);
    insert into SP(SNO,PNO, QTY)
    values ('S1', 'P5', 100);
	insert into SP(SNO,PNO, QTY)
    values ('S1', 'P6', 100);
    insert into SP(SNO,PNO, QTY)
    values ('S2', 'P1', 300);
    insert into SP(SNO,PNO, QTY)
    values ('S2', 'P2', 400);
    insert into SP(SNO,PNO, QTY)
    values ('S3', 'P2', 200);
    insert into SP(SNO,PNO, QTY)
    values ('S4', 'P2', 200);
    insert into SP(SNO,PNO, QTY)
    values ('S4', 'P4', 300);    
    insert into SP(SNO,PNO, QTY)
    values ('S4', 'P5', 400);

몇가지 연습문제를 풀고있는데 도와주세요 5시간쨰 풀고있는데 안풀립니다 ㅠㅠ

3) 공급자 ‘S1’과 같은 도시에 위치하고 있는 모든 부품의 부품번호와 부품명을 구하라
SELECT PNO,PNAME
FROM P
WHERE CITY LIKE 'LONDON';

원래는 P.CITY=S.CITY 이렇게 하려고했는데 전체도시가 나와버려서 안돼서 이렇게 밖에 안풀리네요

IN과 JOIN을 이용하려했지만 어떻게하는지 잘모르겠습니다..

5) 공급된 각 부품에 대하여 부품번호와 공급양을 구하시오 ,
SELECT PNO, QTY
FROM SP;

6) 부품 번호가 “p2"인 부품을 공급하는 공급자 이름을 구하시오 (IN 사용)
7) 부품 번호가 “p2"인 부품을 공급하는 공급자 이름을 구하시오 (JOIN 방식 사용)

IN과 JOIN방식이 너무 어렵네요

8) 부품 번호가 “p2"인 부품을 공급하는 않는 공급자 이름을 구하시오.

10) 부품 테이블에서 도시가 파리인 부품의 색상을 Yellow로 바꾸고, 무게를 5 증가시키시오.
UPDATE P SET COLOR='YELLOW' ,WEITGHT=WEITGHT+5 WHERE CITY='Paris';
WHERE CITY='Paris';

이게 맞는것 같은데 실행이 안돼고 오류코드가 뜹니다...

by isNull [2021.12.01 09:48:45]

/*
WITH S AS (
SELECT 'S1'     AS SNO
     , 'Smith'  AS SNAME
     , 20       AS STATUS
     , 'London' AS CITY
  FROM DUAL
 UNION ALL
SELECT 'S2', 'Jones' , 10, 'Paris'   FROM DUAL UNION ALL
SELECT 'S3', 'Blakes', 30, 'Paris'   FROM DUAL UNION ALL
SELECT 'S4', 'Clark' , 20, 'London'  FROM DUAL UNION ALL
SELECT 'S5', 'Adams' , 30, 'Athenes' FROM DUAL )
, P AS (
SELECT 'P1'     AS PNO
     , 'NUT'    AS PNAME
     , 'Red'    AS COLOR
     , 12       AS WEIGHT
     , 'London' AS CITY
  FROM DUAL
 UNION ALL
SELECT 'P2', 'Bolt' , 'Green', 17, 'Paris'  FROM DUAL UNION ALL
SELECT 'P3', 'Screw', 'Blue' , 17, 'Rome'   FROM DUAL UNION ALL
SELECT 'P4', 'Screw', 'Red'  , 14, 'London' FROM DUAL UNION ALL
SELECT 'P5', 'Cam'  , 'Blue' , 12, 'Paris'  FROM DUAL UNION ALL
SELECT 'P2', 'Bolt' , 'Green', 17, 'Paris'  FROM DUAL UNION ALL
SELECT 'P6', 'Cog'  , 'Red'  , 19, 'London' FROM DUAL )
, SP AS (
SELECT 'S1' AS SNO
     , 'P1' AS PNO
     , 300  AS QTY
  FROM DUAL
 UNION ALL
SELECT 'S1', 'P2', 200 FROM DUAL UNION ALL
SELECT 'S1', 'P3', 400 FROM DUAL UNION ALL
SELECT 'S1', 'P4', 200 FROM DUAL UNION ALL
SELECT 'S1', 'P5', 100 FROM DUAL UNION ALL
SELECT 'S1', 'P6', 100 FROM DUAL UNION ALL
SELECT 'S2', 'P1', 300 FROM DUAL UNION ALL
SELECT 'S2', 'P2', 400 FROM DUAL UNION ALL
SELECT 'S3', 'P2', 200 FROM DUAL UNION ALL
SELECT 'S4', 'P2', 200 FROM DUAL UNION ALL
SELECT 'S4', 'P4', 300 FROM DUAL UNION ALL
SELECT 'S4', 'P5', 400 FROM DUAL
)
*/

--3) 공급자 ‘S1’과 같은 도시에 위치하고 있는 모든 부품의 부품번호와 부품명을 구하라
SELECT P.CITY
     , PNO
     , PNAME
  FROM S
     , P
 WHERE 1 = 1
   AND S.CITY = P.CITY
   AND SNO    = 'S1'
;

--5) 공급된 각 부품에 대하여 부품번호와 공급양을 구하시오 ,
SELECT PNO
     , SUM( QTY )
  FROM SP
 WHERE 1 = 1
 GROUP BY PNO
;

--6) 부품 번호가 “p2"인 부품을 공급하는 공급자 이름을 구하시오 (IN 사용)
SELECT SNAME
  FROM S
 WHERE 1 = 1
   AND SNO IN ( SELECT SNO
                  FROM SP
                 WHERE 1 = 1
                   AND PNO = 'P2' )
;

--7) 부품 번호가 “p2"인 부품을 공급하는 공급자 이름을 구하시오 (JOIN 방식 사용)
SELECT SNAME
  FROM SP
     , S
 WHERE 1 = 1
   AND SP.SNO = S.SNO
   AND SP.PNO = 'P2'
;

--8) 부품 번호가 “p2"인 부품을 공급하는 않는 공급자 이름을 구하시오.
SELECT SNAME
  FROM S
 WHERE 1 = 1
   AND SNAME NOT IN ( SELECT SNAME
                        FROM SP
                           , S
                       WHERE 1 = 1
                         AND SP.SNO = S.SNO
                         AND SP.PNO = 'P2' )
;

--10) 부품 테이블에서 도시가 파리인 부품의 색상을 Yellow로 바꾸고, 무게를 5 증가시키시오.
UPDATE P
   SET COLOR  = 'Yellow'
     , WEIGHT = WEIGHT + 5
 WHERE 1 = 1
   AND CITY = 'Paris'
;

일단 원래 전공이 아니신거 같고... 어느정도 할수 있으신데 그냥 귀찮은거 같으면 이런건 직접 푸시는게 도움이라 답을 안드릴텐데...
문법이나 기초 부분도 어려워하시니 답을 달아드립니다.

접속 되는 테스트 MySQL이 지금 없어서 일단 오라클에서 테스트했지만 ansi 쿼리라 아마 될겁니다.

by 마농 [2021.12.01 10:44:43]

-- 3) 공급자 ‘S1’과 같은 도시에 위치하고 있는 모든 부품의 부품번호와 부품명을 구하라
SELECT pno
     , pname
  FROM p
 WHERE city = (SELECT city FROM s WHERE sno = 'S1')
;
SELECT p.pno
     , p.pname
  FROM s
 INNER JOIN p
    ON s.city = p.city
 WHERE s.sno  = 'S1'
;

-- 5) 공급된 각 부품에 대하여 부품번호와 공급양을 구하시오 ,
SELECT pno
     , SUM(qty) qty
  FROM sp
 GROUP BY pno
;

-- 6) 부품 번호가 “p2"인 부품을 공급하는 공급자 이름을 구하시오 (IN 사용)
SELECT sname
  FROM s
 WHERE sno IN (SELECT sno
                 FROM sp
                WHERE pno = 'P2'
               )
;

-- 7) 부품 번호가 “p2"인 부품을 공급하는 공급자 이름을 구하시오 (JOIN 방식 사용)
SELECT s.sname
  FROM s
 INNER JOIN sp
    ON sp.sno = s.sno
 WHERE sp.pno = 'P2'
;

-- 8) 부품 번호가 “p2"인 부품을 공급하는 않는 공급자 이름을 구하시오.
SELECT sname
  FROM s
 WHERE sno NOT IN (SELECT sno
                     FROM sp
                    WHERE pno = 'P2'
                   )
;
SELECT sname
  FROM s
 WHERE NOT EXISTS (SELECT 1
                     FROM sp
                    WHERE sp.pno = 'P2'
                      AND sp.sno = s.sno
                   )
;
SELECT s.sname
  FROM s
  LEFT OUTER JOIN sp
    ON sp.sno = s.sno
   AND sp.pno = 'P2'
 WHERE sp.pno IS NULL
;

-- 10) 부품 테이블에서 도시가 파리인 부품의 색상을 Yellow로 바꾸고, 무게를 5 증가시키시오.
-- 갱신 전 우선 확인하는 습관 필요 --
SELECT pno
     , color
     , weight
     , weight + 5 weight_new
  FROM p
 WHERE city = 'Paris'
;
UPDATE p
   SET color  = 'Yellow'
     , weight = weight + 5
 WHERE city = 'Paris'
;

-- http://gurubee.net/oracle/sql

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변