테이블은 다음과 같구요
create database shop;
/*salesman 테이블 생성*/
drop table if exists salesman;
create table salesman(
salesmanid int,
sname char(10),
addr char(20),
telno char(10),
salary int,
career int,
primary key(salesmanid)
);
insert into salesman values(1, '박문수', '서울시 서초구', '533-9813', 1500000, 5);
insert into salesman values(2, '홍길동', '서울시 강남구', '451-4321', 1300000, 4);
insert into salesman values(3, '성춘향', '서울시 서초구', '542-1990', 1200000, 4);
insert into salesman values(4, '이몽룡', '서울시 강북구', '245-6666', 1000000, 3);
insert into salesman values(5, '심청', '경기도 수원시', '201-2992', 900000, 2);
/*product 테이블 생성*/
drop table if exists product;
create table product(
pname varchar(10),
modelNo varchar(10),
producer varchar(10),
priceBuy int,
stockNo int,
primary key(modelNo)
);
insert into product values('TV', 'CTV29', '삼성전자', 640000, 5);
insert into product values('TV', 'CTV19', '대우전자', 350000, 3);
insert into product values('TV', 'CTV11', '현대전자', 220000, 2);
insert into product values('냉장고', 'RF900', '삼성전자', 1900000, 4);
insert into product values('냉장고', 'RF650', '삼성전자', 700000, 3);
insert into product values('냉장고', 'RF300', 'LG전자', 230000, 2);
insert into product values('에어컨', 'AC42', 'LG전자', 14500000, 3);
insert into product values('에어컨', 'AC43', '삼성전자', 1470000, 4);
insert into product values('에어컨', 'AC44', '대우전자', 1370000, 6);
/*sale 테이블 생성*/
drop table if exiSts sale;
create table sale(
saleSerial int,
salesmanID int,
modelNo varchar(10),
priceSale int,
saleNo int,
totalPriceSale int,
primary key(saleSerial)
);
insert into sale values(1, 1, 'CTV29', 740000, 2, null);
insert into sale values(2, 1, 'AC43', 1670000, 5, null);
insert into sale values(3, 1, 'RF650', 830000, 2, null);
insert into sale values(4, 2, 'CTV29', 760000, 3, null);
insert into sale values(5, 2, 'AC43', 1650000, 2, null);
insert into sale values(6, 3, 'CTV29', 700000, 1, null);
insert into sale values(7, 3, 'AC43', 1590000, 3, null);
insert into sale values(8, 4, 'RF300', 280000, 3, null);
insert into sale values(9, 5, 'RF650', 800000, 5, null);
/* 총판매실적을 작성하는 update구문*/
update sale
set totalPriceSale = priceSale*saleNo;
위에 해당하는 sql을 가르쳐 주세요~
