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?
請教各位帮忙 ， 谢谢！
已邀請的邦友 {{ invite_list.length }}/5
sorry, my english is not good,
You can ask at stack overflow in english.
https://stackoverflow.com/
我觀落陰了一下...先放棄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
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';
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';
Thank you for your help~ unfortunately it's still not running but I will look at other resources.
Thank you very much! ^^
iThome鐵人賽
看影片追技術
