Final Project - Maria DB

 

 

CREATE DATABASE SalesDB;

USE salesdb;
CREATE TABLE SalesPerson (NAME CHAR(8) NOT NULL PRIMARY KEY, age INT NOT NULL,salary INT NOT NULL);

INSERT INTO salesperson VALUES('sally', 33, 35000);
INSERT INTO salesperson VALUES('justin', 29, 29000);
INSERT INTO salesperson VALUES('tom', 25, 20000);
INSERT INTO salesperson VALUES('paul', 39, 39000);
INSERT INTO salesperson VALUES('jennie', 32, 28400);
INSERT INTO salesperson VALUES('benaya', 24, 51000);
INSERT INTO salesperson VALUES('suzie', 60, 33000);
INSERT INTO salesperson VALUES('lizelle', 42, 23000);
INSERT INTO salesperson VALUES('ellie', 22, 44000);
INSERT INTO salesperson VALUES('helena', 30, 42000);
INSERT INTO salesperson VALUES('alvin', 50, 32000);

CREATE TABLE Ordertbl (NUMBER INT, custName CHAR(8) NOT NULL PRIMARY KEY, salesPerson CHAR(8), amount INT,
FOREIGN KEY (salesperson) REFERENCES salesperson(NAME), FOREIGN KEY (custname) REFERENCES customer(NAME));
INSERT INTO ordertbl VALUES(1, 'mimi','sally', 2);
INSERT INTO ordertbl VALUES(2, 'vince','justin', 5);
INSERT INTO ordertbl VALUES(3, 'helen','tom', 1);
INSERT INTO ordertbl VALUES(4, 'lamaya','ellie', 2);
INSERT INTO ordertbl VALUES(5, 'graind','jennie', 12);
INSERT INTO ordertbl VALUES(6, 'saara','alvin', 8);
INSERT INTO ordertbl VALUES(7, 'jenny','helena', 6);
INSERT INTO ordertbl VALUES(8, 'claire','ellie', 4);
INSERT INTO ordertbl VALUES(9, 'jessie','paul', 2);
INSERT INTO ordertbl VALUES(10, 'maria','deil', 2);

CREATE TABLE customer(NAME CHAR(8) NOT NULL PRIMARY KEY, city CHAR(8), industryType CHAR(8));
INSERT INTO customer VALUES('mimi', 'LA', 'edu');
INSERT INTO customer VALUES('maria', 'LasVegas', 'beauty');
INSERT INTO customer VALUES('claire', 'atlanta', 'shoes');
INSERT INTO customer VALUES('vince', 'NewYork', 'sales');
INSERT INTO customer VALUES('helen', 'Manhat', 'bank');
INSERT INTO customer VALUES('saara', 'Paris', 'marketer');
INSERT INTO customer VALUES('graind', 'Sydney', 'officer');
INSERT INTO customer VALUES('lamaya', 'Suwon', 'book');
INSERT INTO customer VALUES('jessie', 'Seoul', 'IT');
INSERT INTO customer VALUES('jenny', 'Lagos', 'game');

-- (2)모든 판매원의 이름과 급여를 보이시오, 단 중복 행은 제거
SELECT DISTINCT NAME,salary FROM salesperson; 
-- (3)나이가 30세 미만인 판매원의 이름을 보이시오.
SELECT NAME FROM salesperson WHERE age < 30;
-- (4) 'S'로 끝나는 도시에 사는 고객의 이름을 보이시오.
SELECT * FROM customer WHERE city LIKE '%s';

-- (5) 주문을 한 고객의 수(서로 다른 고객만)를 보이시오.
SELECT DISTINCT COUNT(custName) AS '주문을 한 고객수' FROM ordertbl;
-- (6) 판매원 각각에 대하여 주문의 수를 계산하시오.
SELECT salesperson, SUM(amount) AS '총 판매 개수' FROM ordertbl GROUP BY salesperson ORDER BY SUM(amount) desc;
-- (7) LA에 사는 고객으로부터 주문을 받은 판매원의 이름과 나이를 보이시오.(부속질의를 사용)
 

-- SELECT Name FROM customer WHERE city LIKE 'LA'; -- 첫번째,  customer tbl 에서 LA사는 사람 불러오고
-- 
-- SELECT custName FROM ordertbl WHERE custName LIKE 'mimi';   -- 두번째,그 사람의 이름을 ordertbl에서 판매원을 불러오고
-- 
-- SELECT salesperson from ordertbl 
-- WHERE custName LIKE (SELECT Name FROM customer WHERE city LIKE 'LA');  -- 첫번째, + 두번째 합친거

-- (7) 최종 답SQL 
SELECT NAME, age FROM salesperson 
WHERE NAME LIKE 
(SELECT salesperson from ordertbl WHERE custName LIKE 
(SELECT Name FROM customer WHERE city LIKE 'LA'));  --  그리고 최종

-- (8) 두 번 이상 주문을 받은 판매원의 이름을 보이시오.
SELECT salesperson AS '두 번 이상 주문받은 직원' , COUNT(salesperson) AS '횟수'
FROM ordertbl GROUP BY salesperson HAVING COUNT(salesperson) >=2;
-- (9)판매원 'TOM'의 봉급을 45,000원으로 변경하는 sql문을 작성하시오.
UPDATE salesperson SET salary = 45000 WHERE NAME='tom';
-- (10) 'LA'에 사는 고객으로부터 주문을 받은 판매원의 이름과 나이를 보이시오(조인을 사용)

SELECT s.name, age FROM salesperson AS s
JOIN ordertbl AS o ON o.salesPerson = s.name
JOIN customer AS c ON o.custName = c.NAME
WHERE city LIKE 'LA';