DB 데이터베이스 컬럼 행 열 만들어서 10개의 데이터 삽입 숙제완료

서버/DB

DB 데이터베이스 컬럼 행 열 만들어서 10개의 데이터 삽입 숙제완료

민밥통 2023. 10. 20. 01:28

create database inhouse3;
use inhouse3; 
-- 테이블삭제
drop table EmployeeRegistration;

-- 거래처정보
CREATE TABLE CustomerInformation(
id INT NOT NULL AUTO_INCREMENT,
BRN VARCHAR(20),
CRN CHAR(20),
bnsiness VARCHAR(20),
address VARCHAR(100),
customer_tell CHAR(20),
salesincharge VARCHAR(30),
primary key(id)
);

-- 정보 데이터 확인하는 곳
select * from CustomerInformation;
-- 거래처정보 데이터
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge) 
values ('칼포니치킨', '169-44-00749', '주영삼', '서울특별시 금천구 가산동 142-47 지상 1층', '02111111', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge) 
values('누구나홀딱반한닭', '221-47-12113', '한광일', '서울특별시 금천구 가산동 142-52 지상1층(가산동)', '02111112', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge) 
values('스타에델치킨본점', '295-38-00348', '송명재', '서울특별시 금천구 가산동 46-6 1층 7,8호(가산동)', '02111113', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge) 
values('BHC가산디지털점', '489-21-01245', '김희성', '서울특별시 금천구 가산동 371-106 지상2층 213호)', '02111114', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge) 
values('정석닭강정 구로점', '372-87-02302', '채미애', '서울특별시 구로구 구로동 1280 상가 2동 1층 218호,219호', '02111115', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge) 
values('치코치코', '133-09-36757', '하덕일', '경기도 광명시 철산동 441', '02111116', '치킨');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge) 
values('대한민족', '499-58-00405', '강미정', '서울특별시 구로구 구로동 1124-55 1층(구로동)', '02111117', '족발');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge) 
values('광복왕족발', '140-08-08913', '권혁중', '경기도 광명시 철산동 56-15 1층', '02111118', '족발');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge) 
values('우정수육', '632-06-02447', '전영호', '서울특별시 구로구 구로동 1125-10 1층(구로동)', '02111119', '족발');
INSERT into CustomerInformation (BRN, CRN, bnsiness, address, customer_tell, salesincharge) 
values('조선귀족 구로점', '537-56-00697', '변영익', '서울특별시 구로구 굴동 487-76 1층 102호(구로동)', '02111110', '족발');

-- 임직원등록 (거래처정보)
CREATE TABLE EmployeeRegistration(
id INT NOT NULL AUTO_INCREMENT,
Employee_name VARCHAR(20),
Employee_age INT,
Employee_gender VARCHAR(20),
Employee_Teamaffiliation VARCHAR(30),
Employee_Permissions VARCHAR(20),
primary key(id),
c_id int,
foreign key (`c_id`) references `CustomerInformation`(`id`)
);

-- 정보 데이터 확인하는 곳
select * from EmployeeRegistration;
-- 임직원 데이터
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id) 
values('주영삼', 26, '남', 'B2B', 'Y', 1);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id) 
values('한광일', 27, '남', 'B2B', 'Y', 2);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id) 
values('송명재', 28, '남', 'BTS', 'N', 3);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id) 
values('김희성', 30, '남', 'B2B', 'Y', 4);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id) 
values('채미애', 22, '여', 'B2B', 'N', 5);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id) 
values('하덕일', 39, '남', 'Sales', 'N', 6);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id) 
values('강미정', 27, '여', 'Sales', 'Y', 7);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id) 
values('권혁중', 35, '남', 'BTS', 'N', 8);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id) 
values('전영호', 32, '남', 'B2B', 'Y', 9);
INSERT into EmployeeRegistration (Employee_name, Employee_age, Employee_gender, Employee_Teamaffiliation, Employee_Permissions, c_id) 
values('변영익', 40, '남', 'Sales', 'Y', 10);

-- 매출조회 (거래처 정보)
CREATE TABLE Salesinquiry(
num INT NOT NULL AUTO_INCREMENT,
sales_account VARCHAR(30),
sales_date DATE,
sales_depositdate DATE,
quantity INT,
unitprice INT,
finalamount INT,
Tradingstatement VARCHAR(30),
primary key(num),
c_id int,
foreign key(`c_id`) references `CustomerInformation`(`id`)
);
-- 정보 데이터 확인하는 곳
select * from Salesinquiry;
-- 매출조회 데이터
INSERT INTO Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id) 
values('칼포니','2023-10-1','2023-10-10', 300, 210, 63000, 20231001, 1);
INSERT INTO Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id) 
values('누구나홀딱반한닭','2023-10-5','2023-10-20', 900, 4933, 4439700, 20231005, 2);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id) 
values('스타에델치킨','2023-10-1','2023-10-10', 500, 3959, 1979599, 20231001, 3);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id) 
values('BHC','2023-10-5','2023-10-15', 600, 4933, 3466500, 20231005, 4);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id) 
values('정석닭강정','2023-10-7','2023-10-23', 400, 3959, 1583666, 20231007, 5);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id) 
values('치코치코','2023-10-3','2023-10-30', 500, 3959, 1979500, 20231003, 6);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id) 
values('대한민족','2023-10-9','2023-10-15', 400, 3959, 1583600, 20231009, 7);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id) 
values('광복왕족발','2023-10-4','2023-10-25', 300, 210, 63000, 20231004, 8);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id) 
values('우정수육','2023-10-1','2023-10-19', 500, 4933, 2466500, 20231001, 9);
INSERT into Salesinquiry (sales_account, sales_date, sales_depositdate, quantity, unitprice, finalamount, Tradingstatement, c_id) 
values('조선귀족 구로점','2023-10-2','2023-10-20', 800, 4933, 3946400, 20231002, 10);


-- 임직원과 매출조회 새 테이블
create table Es(
e_id int,
s_num int,
day date,
primary key(`e_id`,`s_num`),
foreign key(`e_id`) references `EmployeeRegistration`(`id`),
foreign key(`s_num`) references `Salesinquiry`(`num`)
);
-- 정보 데이터 확인하는 곳
select * from Es;
-- 임직원, 매출조회 데이터 (이테이블 단점은 하나하나 확인하면서 테이블 정상적으로 들어갔는 지 확인불가?)
INSERT INTO Es	(e_id, s_num, day)
VALUES			(2 , 2, '2023-10-19'),
				(3 , 5, '2023-10-18'),
                (6 , 1, '2023-10-17'),
                (3 , 2, '2023-10-15'),
                (6 , 9, '2023-10-10'),
                (10 , 3, '2023-10-12'),
                (11 , 7, '2023-10-15'),
                (9 , 4, '2023-10-19'),
                (7 , 8, '2023-10-19'),
                (8 , 6, '2023-10-18');               

-- 매입발주 (거래처 정보)
CREATE TABLE purchaseorder(
num INT NOT NULL AUTO_INCREMENT,
Order_date DATE,
Order_item VARCHAR(20),
Orderer VARCHAR(30),
Order_quantity INT,
Order_unitprice INT,
Order_finalamount INT,
Order_finalinventory INT,
primary key(num),
c_id int,
foreign key (`c_id`) references `CustomerInformation`(`id`)
);
-- 정보 데이터 확인하는 곳
select * from purchaseorder;
-- 매입발주 데이터
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-1','닭','생생농장',350,4990,1746500,50,1);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-5','닭','태백산',900,4920,3546900,50,2);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-1','닭','거봉',600,3950,1948090,50,3);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-5','닭','생생농장',600,3950,2048540,50,4);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-7','닭','꽁알꽁알농장',500,4998,2386600,50,5);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-3','돼지족','피피',600,4960,1767550,50,6);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-9','돼지족','SBS',900,4980,3546500,50,7);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-4','돼지족','PIG',200,4500,946500,50,8);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-1','돼지족','BIGpig',500,3600,1746500,50,9);
INSERT INTO purchaseorder(Order_date, Order_item, Orderer, Order_quantity, Order_unitprice, Order_finalamount, Order_finalinventory, c_id)
VALUES ('2023-9-2','돼지족','HIHI',600,5000,3746500,50,10);

-- 임직원과 매입발주 새 테이블
create table Ep(
e_id int,
p_num int,
day date,
primary key(`e_id`, `p_num`),
foreign key(`e_id`) references `EmployeeRegistration`(`id`),
foreign key(`p_num`) references `purchaseorder`(`num`)
);
-- 테이블 안에 새로운 컬럼 추가
ALTER TABLE ep
ADD Checklist VARCHAR(20);
ALTER TABLE ep
ADD relay VARCHAR(20);

-- 정보 데이터 확인하는 곳
select * from ep;
-- 임직원과 매입발주 데이터
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (2,2,'2023-10-05','Y','Y');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (3,3,'2023-10-07','Y','Y');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (4,4,'2023-10-10','N','Y');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (5,5,'2023-10-11','N','N');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (6,6,'2023-10-3','Y','N');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (7,7,'2023-10-13','Y','Y');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (8,8,'2023-10-01','Y','N');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (9,9,'2023-10-15','N','Y');
INSERT INTO Ep(e_id, p_num, day, Checklist, relay)
VALUES (10,10,'2023-10-14','N','Y');

-- 수금관리(거래처정보, 매입발주, 임직원 정보)
CREATE TABLE Collectionmanagement(
num INT NOT NULL AUTO_INCREMENT,
MGMT_Customerinformation VARCHAR(20),
MGMT_salestuff VARCHAR(20),
MGMT_Expecteddepositdate VARCHAR(20),
MGMT_Actualdeposit DATE,
MGMT_Incorrectdepositmanagement DATE,
MGMT_Salesincharge INT,
primary key(num),
c_id int,
p_num int,
e_id int,
foreign key(`c_id`) references `CustomerInformation`(`id`),
foreign key(`p_num`) references `purchaseorder`(`num`),
foreign key(`e_id`) references `EmployeeRegistration`(`id`)
);
-- 정보 데이터 확인하는 곳
select * from Collectionmanagement;
-- 수금관리 데이터
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('신한은행','식품유통','8845550','2023-09-20','2023-09-21',560,2,3,5);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('농협은행','식품유통','95656110','2023-09-24','2023-09-25',412,3,3,9);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('하나은행','식품유통','73445550','2023-09-23','2023-09-24',522,2,9,5);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('카카오뱅크','식품유통','9556284','2023-09-22','2023-09-23',604,10,3,7);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('신한은행','식품유통','7832610','2023-09-26','2023-09-25',755,7,8,6);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('하나은행','식품유통','6767558','2023-09-27','2023-09-26',852,8,6,2);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('국민은행','식품유통','3697865','2023-09-28','2023-09-27',900,5,4,1);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('카카오뱅크','식품유통','8569874','2023-09-29','2023-09-29',542,7,6,6);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('현대카드','식품유통','5869744','2023-09-30','2023-09-30',423,3,4,4);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('삼성카드','식품유통','5869745','2023-09-22','2023-09-22',152,4,3,6);
INSERT INTO Collectionmanagement(MGMT_Customerinformation, MGMT_salestuff, MGMT_Expecteddepositdate, MGMT_Actualdeposit, MGMT_Incorrectdepositmanagement,MGMT_Salesincharge,c_id, p_num,e_id)
VALUES ('신한은행','식품유통','68745789','2023-09-27','2023-09-27',289,5,8,6);

-- 매출조회와 수금관리의 새 테이블
create table SC(
s_num int,
c_num int,
day date,
foreign key(`s_num`) references `Salesinquiry`(`num`),
foreign key(`c_num`) references `Collectionmanagement`(`num`)
);
-- 정보 데이터 확인하는 곳
select * from SC;
-- 매출조회와 수금관리 데이터
INSERT INTO SC(s_num, c_num, day)
VALUES (1,1,'2023-10-01');
INSERT INTO SC(s_num, c_num, day)
VALUES (3,1,'2023-10-04');
INSERT INTO SC(s_num, c_num, day)
VALUES (2,8,'2023-10-06');
INSERT INTO SC(s_num, c_num, day)
VALUES (4,5,'2023-10-08');
INSERT INTO SC(s_num, c_num, day)
VALUES (2,10,'2023-10-02');
INSERT INTO SC(s_num, c_num, day)
VALUES (9,3,'2023-10-04');
INSERT INTO SC(s_num, c_num, day)
VALUES (2,8,'2023-10-07');
INSERT INTO SC(s_num, c_num, day)
VALUES (5,5,'2023-10-08');
INSERT INTO SC(s_num, c_num, day)
VALUES (6,7,'2023-10-09');
INSERT INTO SC(s_num, c_num, day)
VALUES (8,3,'2023-10-10');


-- 정보 데이터 확인하는 곳
select * from EmployeeRegistration;
-- 데이터 삭제하는 곳
DELETE FROM EmployeeRegistration
WHERE id=1;

1. 거래처 정보
- 사업자 등록 명
- 사업자 등록 번호
- 대표자 성함
- 사업장 주소
- 연락처
- 담당 영업

2. 매출 조회
- 거래처
- 매출 날짜
- 입금 예정일자
- 수량
- 단가
- 최종 금액
- 거래명세서

3. 매입 발주
- 발주 일자
- 발주 물건
- 발주처
- 수량
- 단가
- 최종 매입금액
- 최종 재고

4. 수금관리
- 거래처 정보
- 판매 물건
- 입금 예정일자
- 실제 입금 일자
- 오입금 관리
- 담당 영업

5. 임직원 등록
- 이름
- 나이
- 성별
- 소속 팀
- 사용 권한

'서버 > DB' 카테고리의 다른 글

대소문자 변환함수 (0)	2023.10.20
오름차순, WHERE절, 문자와 날짜 검색, 산술 연산자 등 연습 (0)	2023.10.20
DB 수정중 (0)	2023.10.18
10/13 DB SQL 총정리 (외래키 추가 primary key()) (0)	2023.10.13
10/20까지 DB 숙제 (0)	2023.10.13

현재글DB 데이터베이스 컬럼 행 열 만들어서 10개의 데이터 삽입 숙제완료

[이력] 기초 풀 스택 9/25~

프론트엔드초보, TRAILING, 어떻게하나요, 자바스크립스배열자르기 #배열자르기, span기능, MySQL 활용하기 기초, MYsQLTRIM, 프론트엔드입문, 각문자의개수구하기, html뜻, css뜻, charCounts, mysql설치하는방법, 프론트엔드입문자강의추천, Character.toLowerCase, html초보, 외래키추가하는법, 대소문자구분, MYSQL공식문서, MYSQL특정철자,

Today :
Yesterday :

민지 개발자 성장 일기

DB 데이터베이스 컬럼 행 열 만들어서 10개의 데이터 삽입 숙제완료

'서버 > DB' 카테고리의 다른 글

'서버/DB'의 다른글

티스토리툴바

« 2025/06 »
일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

DB 데이터베이스 컬럼 행 열 만들어서 10개의 데이터 삽입 숙제완료

'서버 > DB' 카테고리의 다른 글

'서버/DB'의 다른글

관련글

티스토리툴바