iT邦幫忙

0

Oracle SQL

Thank you for your responses!
Sorry, I can't type much in Chinese, I am studying IT in Monash University @ Melbourne, Australia ^^'

I will provide the tables:

1- BRANCH
https://i.gyazo.com/d451e1a76ddb4665e2ca4abb387d3647.png

2- BOOK COPY
https://i.gyazo.com/9b2ee98adff84fdb3ba1c180377f5fc6.png

3- RESERVE
https://i.gyazo.com/6249711e8fe3cf58108358af2babd5b4.png

I needed to get values from some tables (branch, book copy) and put it into the reserve table.

/* Insert the following columns

BRANCH CODE: obtain by selecting from branch via contact number
BC_ID: get from book_copy table for a specific book_copy_id
DATE_TIME PLACED
BOR_NO */

The question was:

'Immediately after becoming a member, at 4PM, Ada places a reservation on a book at the Mulgrave branch (Ph: 8888888883). Some
of the details of the book that Ada has placed a reservation on are:

Call Number: 005.74 C822D 2018
Title: Database Systems: Design, Implementation, and Management
Publication Year: 2018
Edition: 13

You may assume:
MonLib has not purchased any further copies of this book, beyond those which you inserted in Task 2.1
that nobody has become a member of the library between Ada becoming a member and this reservation.'

Could anyone tell me how to properly format an 'INSERT INTO' statement with multiple SELECT subqueries?

請教各位帮忙 , 谢谢!

rogeryao iT邦大師 1 級 ‧ 2018-10-16 09:37:02 檢舉
BOR_NO ?
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
1
小魚
iT邦大師 1 級 ‧ 2018-10-16 07:23:21

sorry, my english is not good,
You can ask at stack overflow in english.
https://stackoverflow.com/

這個他只是想知道查詢的資料如何新增吧@@..
insert into xxx select * from xxx

2
純真的人
iT邦大師 1 級 ‧ 2018-10-16 10:25:37

我觀落陰了一下...先放棄BOR_NO 是甚麼..

你看看insert into 吧...

create table BRANCH(
  BRANCH_CODE number(10)
  ,BRANCH_NAME varchar2(50)
  ,BRANCH_ADDERSS varchar2(50)
  ,BRANCH_CONTACT_NO varchar2(50)
  ,BRANCH_COUNT_BOOKS number(10)
  ,MAN_ID number(10)
);

insert into BRANCH(BRANCH_CODE,BRANCH_NAME,BRANCH_ADDERSS,BRANCH_CONTACT_NO,BRANCH_COUNT_BOOKS,MAN_ID) 
values(10,'aaa','bbb','8888888881',5000,1);
insert into BRANCH(BRANCH_CODE,BRANCH_NAME,BRANCH_ADDERSS,BRANCH_CONTACT_NO,BRANCH_COUNT_BOOKS,MAN_ID) 
values(12,'ccc','ggg','8888888883',45000,1);

create table BOOK_COPY(
 BRANCH_CODE number(10)
 ,BC_ID number(10)
 ,BC_PURCHASE_PRICE number(10)
 ,BC_RESERVE_FLAG varchar2(5)
 ,BOOK_CALL_NO varchar2(50)
);

insert into BOOK_COPY(BRANCH_CODE,BC_ID,BC_PURCHASE_PRICE,BC_RESERVE_FLAG,BOOK_CALL_NO)
values(10,6,120,'Y','005.74 C822D 2018');
insert into BOOK_COPY(BRANCH_CODE,BC_ID,BC_PURCHASE_PRICE,BC_RESERVE_FLAG,BOOK_CALL_NO)
values(11,6,120,'Y','005.74 C822D 2018');
insert into BOOK_COPY(BRANCH_CODE,BC_ID,BC_PURCHASE_PRICE,BC_RESERVE_FLAG,BOOK_CALL_NO)
values(12,6,120,'Y','005.74 C822D 2018');

create table RESERVE(
 BRANCH_CODE number(10)
 ,BC_ID number(10)
 ,RESERVE_Date TIMESTAMP
 ,BOR_NO varchar2(5)
);

insert into RESERVE(BRANCH_CODE
,BC_ID
,RESERVE_Date
) 
select (
  SELECT branch_code FROM branch WHERE branch_contact_no = '8888888883'
) branch_code
,bc_id
,TO_DATE('2018/09/20 16:00:00', 'yyyy/mm/dd hh24:mi:ss') test_date
FROM book_copy a
LEFT JOIN branch b on a.branch_code = b.branch_code
WHERE book_call_no = '005.74 C822D 2018'
AND branch_contact_no = '8888888881';

test url:
http://sqlfiddle.com/#!4/2f38b4/2

windaura iT邦新手 5 級 ‧ 2018-10-17 19:37:02 檢舉

Thanks for this! I really appreciate it :)
Unfortunately it's still not running, but I'll keep on trying ^^

嗯嗯~加油~

0
rogeryao
iT邦大師 1 級 ‧ 2018-10-16 12:01:46

CREATE TABLE BORROWER (
BOR_NO NUMBER(10),
BOR_NAME VARCHAR2(50));

INSERT INTO RESERVE(BRANCH_CODE,BC_ID,RESERVE_DATE,BOR_NO)
SELECT A.BRANCH_CODE,B.BC_ID,
TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS') RESERVE_DATE,
C.BOR_NO
FROM BRANCH A
LEFT JOIN BOOK_COPY B ON B.BRANCH_CODE=A.BRANCH_CODE
,BORROWER C
WHERE 1=1
AND A.BRANCH_NAME='Mulgrave'
AND A.BRANCH_CONTACT_NO='8888888883'
AND C.BOR_NAME='Ada';

rogeryao iT邦大師 1 級 ‧ 2018-10-16 14:16:49 檢舉

CREATE TABLE seq_borrower (
currVal NUMBER(10),
currName VARCHAR2(50));

INSERT INTO RESERVE(BRANCH_CODE,BC_ID,RESERVE_DATE,BOR_NO)
SELECT A.BRANCH_CODE,B.BC_ID,
TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS') RESERVE_DATE,
C.currVal
FROM BRANCH A
LEFT JOIN BOOK_COPY B ON B.BRANCH_CODE=A.BRANCH_CODE
,seq_borrower C
WHERE 1=1
AND A.BRANCH_NAME='Mulgrave'
and B.BOOK_CALL_NO='005.74 C822D 2018'
AND C.currName='Ada';

windaura iT邦新手 5 級 ‧ 2018-10-17 19:36:26 檢舉

Thank you for your help~ unfortunately it's still not running but I will look at other resources.

Thank you very much! ^^

我要發表回答

立即登入回答