Oracle Live SQL - Script: StackOverflow

Statement 1

create table AUTHOR ( 
    AUTHORID                      INTEGER, 
    AUTHORNAME                    VARCHAR2(255), 
    COUNTRY                       VARCHAR2(255), 
    AGE                           INTEGER 
)

Table created.

Statement 2

INSERT ALL  
INTO AUTHOR (AUTHORID, AUTHORNAME, COUNTRY, AGE) values (1, 'AUTHOR #1', 'US', 20) 
INTO AUTHOR (AUTHORID, AUTHORNAME, COUNTRY, AGE) values (2, 'AUTHOR #2', 'US', 35) 
INTO AUTHOR (AUTHORID, AUTHORNAME, COUNTRY, AGE) values (3, 'AUTHOR #3', 'ENGLAND', 44) 
INTO AUTHOR (AUTHORID, AUTHORNAME, COUNTRY, AGE) values (4, 'AUTHOR #4', 'ENGLAND', 15) 
INTO AUTHOR (AUTHORID, AUTHORNAME, COUNTRY, AGE) values (5, 'AUTHOR #5', 'AUSTRALIA', 33) 
INTO AUTHOR (AUTHORID, AUTHORNAME, COUNTRY, AGE) values (6, 'AUTHOR #6', 'AUSTRALIA', 24) 
INTO AUTHOR (AUTHORID, AUTHORNAME, COUNTRY, AGE) values (7, 'AUTHOR #7', 'CHINA', 18) 
INTO AUTHOR (AUTHORID, AUTHORNAME, COUNTRY, AGE) values (8, 'AUTHOR #8', 'CHINA', 76) 
SELECT * FROM dual

8 row(s) inserted.

Statement 3

create table BOOK ( 
    BOOKID                        NUMBER, 
    BOOKNAME                      VARCHAR2(255), 
    AUTHORID                      INTEGER, 
    PUBLISHER                     VARCHAR2(255), 
    BRANCHID                      INTEGER 
)

Table created.

Statement 4

INSERT ALL  
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (100, 'BOOK # 1', 1, 'NEW AGE',   1) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (150, 'BOOK # 2', 1, 'NEW AGE',   2) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (200, 'BOOK # 1', 2, 'NEW AGE',   1) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (250, 'BOOK # 2', 2, 'NEW AGE',   3) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (300, 'BOOK # 1', 3, 'NEW AGE',   4) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (350, 'BOOK # 2', 3, 'NEW AGE',   1) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (400, 'BOOK # 1', 4, 'OLD TIMES', 8) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (425, 'BOOK # 2', 4, 'OLD TIMES', 3) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (450, 'BOOK # 3', 4, 'OLD TIMES', 7) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (470, 'BOOK # 4', 4, 'OLD TIMES', 6) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (500, 'BOOK # 1', 5, 'NEW AGE',   1) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (550, 'BOOK # 2', 5, 'NEW AGE',   2) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (600, 'BOOK # 1', 6, 'NEW AGE',   3) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (700, 'BOOK # 1', 7, 'OLD TIMES', 1) 
INTO BOOK (BOOKID ,BOOKNAME ,AUTHORID ,PUBLISHER ,BRANCHID) VALUES (800, 'BOOK # 1', 8, 'OLD TIMES', 1) 
SELECT * FROM dual

15 row(s) inserted.

Statement 5

create table BRANCH ( 
    BRANCHID                      INTEGER, 
    BRANCHNAME                    VARCHAR2(255), 
    HOD                           INTEGER 
)

Table created.

Statement 6

INSERT ALL  
INTO BRANCH (BRANCHID, BRANCHNAME, HOD) values (1, 'NORTH', 1) 
INTO BRANCH (BRANCHID, BRANCHNAME, HOD) values (2, 'WEST', 2) 
INTO BRANCH (BRANCHID, BRANCHNAME, HOD) values (3, 'EAST', 3) 
INTO BRANCH (BRANCHID, BRANCHNAME, HOD) values (4, 'SOUTH', 4) 
INTO BRANCH (BRANCHID, BRANCHNAME, HOD) values (5, 'NORTH-EAST', 5) 
INTO BRANCH (BRANCHID, BRANCHNAME, HOD) values (6, 'NORTH-WEST', 6) 
INTO BRANCH (BRANCHID, BRANCHNAME, HOD) values (7, 'SOUTH-EAST', 7) 
INTO BRANCH (BRANCHID, BRANCHNAME, HOD) values (8, 'SOUTH-WEST', 8) 
SELECT * FROM dual

8 row(s) inserted.

Statement 7

create table STUDENT ( 
    USN                           INTEGER, 
    NAME                          VARCHAR2(255), 
    ADDRESS                       VARCHAR2(255), 
    BRANCHID                      INTEGER, 
    SEM                           INTEGER 
)

Table created.

Statement 8

INSERT ALL  
INTO STUDENT (USN ,NAME ,ADDRESS ,BRANCHID ,SEM) VALUES (1, 'CHRIS', 'ADDRESS 1', 1,  1) 
INTO STUDENT (USN ,NAME ,ADDRESS ,BRANCHID ,SEM) VALUES (2, 'FRANK', 'ADDRESS 2', 3,  1) 
INTO STUDENT (USN ,NAME ,ADDRESS ,BRANCHID ,SEM) VALUES (3, 'CHRIS', 'ADDRESS 1', 5,  1) 
INTO STUDENT (USN ,NAME ,ADDRESS ,BRANCHID ,SEM) VALUES (4, 'TINA', 'ADDRESS 1', 7,  1) 
INTO STUDENT (USN ,NAME ,ADDRESS ,BRANCHID ,SEM) VALUES (5, 'SHANE', 'ADDRESS 1', 1,  1) 
INTO STUDENT (USN ,NAME ,ADDRESS ,BRANCHID ,SEM) VALUES (6, 'CHRISTY', 'ADDRESS 1', 4,  1) 
SELECT * FROM dual

6 row(s) inserted.

Statement 9

create table BORROW ( 
    USN                           INTEGER, 
    BOOKID                        INTEGER, 
    BORROWED_DATE                 DATE 
)

Table created.

Statement 10

INSERT ALL 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (1, 400, TO_DATE('01/05/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (1, 425, TO_DATE('01/05/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (1, 450, TO_DATE('01/05/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (2, 100, TO_DATE('01/25/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (2, 500, TO_DATE('01/25/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (2, 900, TO_DATE('01/25/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (3, 200, TO_DATE('01/28/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (3, 250, TO_DATE('01/28/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (3, 550, TO_DATE('02/10/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (3, 600, TO_DATE('02/10/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (3, 700, TO_DATE('02/10/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (3, 800, TO_DATE('02/10/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (4, 425, TO_DATE('02/10/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (4, 450, TO_DATE('03/15/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (6, 200, TO_DATE('02/1/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (6, 250, TO_DATE('02/1/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (8, 700, TO_DATE('02/12/2017','MM/DD/YYYY')) 
INTO BORROW (USN, BOOKID, BORROWED_DATE) VALUES (8, 800, TO_DATE('02/12/2017','MM/DD/YYYY')) 
SELECT * FROM dual

18 row(s) inserted.

Statement 11

SELECT   
 S.NAME             AS "STUDENT NAME" 
,S.ADDRESS          AS "STUDENT ADDRESS" 
,BW.BORROWED_DATE   AS "DATE BORROWED" 
,BR.BRANCHNAME      AS "BRANCE NAME" 
,BK.BOOKNAME        AS "NAME OF BOOK" 
,BK.PUBLISHER       AS "BOOK PUBLISHER" 
,A.AUTHORNAME       AS "NAME OF AUTHOR" 
,A.COUNTRY          AS "COUNTRY OF AUTHOR" 
,A.AGE              AS "AGE OF AUTHOR" 
--- 
FROM  
STUDENT S 
--- 
JOIN BORROW BW 
    ON S.USN = BW.USN 
---  
JOIN BOOK BK 
    ON BW.BOOKID = BK.BOOKID 
--     
JOIN BRANCH BR 
    ON BR.BRANCHID = BK.BRANCHID 
--- 
JOIN AUTHOR A 
    ON BK.AUTHORID = A.AUTHORID 
-- 
-- 
JOIN (SELECT P1 
        FROM ( 
            SELECT  
             SS_S.USN       "P1" 
            ,SS_A.Authorid  "P2" 
            FROM  
            STUDENT SS_S 
            JOIN BORROW SS_BW 
                ON SS_S.USN = SS_BW.USN 
            JOIN BOOK SS_BK 
                ON SS_BW.BOOKID = SS_BK.BOOKID 
            JOIN AUTHOR SS_A 
                ON SS_BK.AUTHORID = SS_A.AUTHORID 
            GROUP BY  
             SS_S.USN 
            ,SS_A.Authorid 
            HAVING COUNT(SS_A.Authorid) > 1 
        ) 
    ) SUB_Q 
ON S.USN = SUB_Q.P1 
ORDER BY 1, 3


STUDENT NAME STUDENT ADDRESS DATE BORROWED BRANCE NAME NAME OF BOOK BOOK PUBLISHER NAME OF AUTHOR COUNTRY OF AUTHOR AGE OF AUTHOR
CHRIS ADDRESS 1 05-JAN-17 EAST BOOK # 2 OLD TIMES AUTHOR #4 ENGLAND 15
CHRIS ADDRESS 1 05-JAN-17 SOUTH-EAST BOOK # 3 OLD TIMES AUTHOR #4 ENGLAND 15
CHRIS ADDRESS 1 05-JAN-17 SOUTH-WEST BOOK # 1 OLD TIMES AUTHOR #4 ENGLAND 15
CHRIS ADDRESS 1 28-JAN-17 NORTH BOOK # 1 NEW AGE AUTHOR #2 US 35
CHRIS ADDRESS 1 28-JAN-17 EAST BOOK # 2 NEW AGE AUTHOR #2 US 35
CHRIS ADDRESS 1 10-FEB-17 WEST BOOK # 2 NEW AGE AUTHOR #5 AUSTRALIA 33
CHRIS ADDRESS 1 10-FEB-17 EAST BOOK # 1 NEW AGE AUTHOR #6 AUSTRALIA 24
CHRIS ADDRESS 1 10-FEB-17 NORTH BOOK # 1 OLD TIMES AUTHOR #7 CHINA 18
CHRIS ADDRESS 1 10-FEB-17 NORTH BOOK # 1 OLD TIMES AUTHOR #8 CHINA 76
CHRISTY ADDRESS 1 01-FEB-17 NORTH BOOK # 1 NEW AGE AUTHOR #2 US 35
CHRISTY ADDRESS 1 01-FEB-17 EAST BOOK # 2 NEW AGE AUTHOR #2 US 35
TINA ADDRESS 1 10-FEB-17 EAST BOOK # 2 OLD TIMES AUTHOR #4 ENGLAND 15
TINA ADDRESS 1 15-MAR-17 SOUTH-EAST BOOK # 3 OLD TIMES AUTHOR #4 ENGLAND 15

13 rows selected.

STUDENT NAME	STUDENT ADDRESS	DATE BORROWED	BRANCE NAME	NAME OF BOOK	BOOK PUBLISHER	NAME OF AUTHOR	COUNTRY OF AUTHOR	AGE OF AUTHOR
CHRIS	ADDRESS 1	05-JAN-17	EAST	BOOK # 2	OLD TIMES	AUTHOR #4	ENGLAND	15
CHRIS	ADDRESS 1	05-JAN-17	SOUTH-EAST	BOOK # 3	OLD TIMES	AUTHOR #4	ENGLAND	15
CHRIS	ADDRESS 1	05-JAN-17	SOUTH-WEST	BOOK # 1	OLD TIMES	AUTHOR #4	ENGLAND	15
CHRIS	ADDRESS 1	28-JAN-17	NORTH	BOOK # 1	NEW AGE	AUTHOR #2	US	35
CHRIS	ADDRESS 1	28-JAN-17	EAST	BOOK # 2	NEW AGE	AUTHOR #2	US	35
CHRIS	ADDRESS 1	10-FEB-17	WEST	BOOK # 2	NEW AGE	AUTHOR #5	AUSTRALIA	33
CHRIS	ADDRESS 1	10-FEB-17	EAST	BOOK # 1	NEW AGE	AUTHOR #6	AUSTRALIA	24
CHRIS	ADDRESS 1	10-FEB-17	NORTH	BOOK # 1	OLD TIMES	AUTHOR #7	CHINA	18
CHRIS	ADDRESS 1	10-FEB-17	NORTH	BOOK # 1	OLD TIMES	AUTHOR #8	CHINA	76
CHRISTY	ADDRESS 1	01-FEB-17	NORTH	BOOK # 1	NEW AGE	AUTHOR #2	US	35
CHRISTY	ADDRESS 1	01-FEB-17	EAST	BOOK # 2	NEW AGE	AUTHOR #2	US	35
TINA	ADDRESS 1	10-FEB-17	EAST	BOOK # 2	OLD TIMES	AUTHOR #4	ENGLAND	15
TINA	ADDRESS 1	15-MAR-17	SOUTH-EAST	BOOK # 3	OLD TIMES	AUTHOR #4	ENGLAND	15

Statement 12

SELECT   
 S.NAME             AS "STUDENT NAME" 
,S.ADDRESS          AS "STUDENT ADDRESS" 
,BW.BORROWED_DATE   AS "DATE BORROWED" 
,BR.BRANCHNAME      AS "BRANCE NAME" 
,BK.BOOKNAME        AS "NAME OF BOOK" 
,BK.PUBLISHER       AS "BOOK PUBLISHER" 
,A.AUTHORNAME       AS "NAME OF AUTHOR" 
,A.COUNTRY          AS "COUNTRY OF AUTHOR" 
,A.AGE              AS "AGE OF AUTHOR" 
--- 
FROM  
STUDENT S 
--- 
JOIN BORROW BW 
    ON S.USN = BW.USN 
---  
JOIN BOOK BK 
    ON BW.BOOKID = BK.BOOKID 
--     
JOIN BRANCH BR 
    ON BR.BRANCHID = BK.BRANCHID 
--- 
JOIN AUTHOR A 
    ON BK.AUTHORID = A.AUTHORID 
-- 
WHERE  
BK.PUBLISHER = 'NEW AGE'


STUDENT NAME STUDENT ADDRESS DATE BORROWED BRANCE NAME NAME OF BOOK BOOK PUBLISHER NAME OF AUTHOR COUNTRY OF AUTHOR AGE OF AUTHOR
FRANK ADDRESS 2 25-JAN-17 NORTH BOOK # 1 NEW AGE AUTHOR #1 US 20
FRANK ADDRESS 2 25-JAN-17 NORTH BOOK # 1 NEW AGE AUTHOR #5 AUSTRALIA 33
CHRIS ADDRESS 1 28-JAN-17 NORTH BOOK # 1 NEW AGE AUTHOR #2 US 35
CHRIS ADDRESS 1 28-JAN-17 EAST BOOK # 2 NEW AGE AUTHOR #2 US 35
CHRIS ADDRESS 1 10-FEB-17 WEST BOOK # 2 NEW AGE AUTHOR #5 AUSTRALIA 33
CHRIS ADDRESS 1 10-FEB-17 EAST BOOK # 1 NEW AGE AUTHOR #6 AUSTRALIA 24
CHRISTY ADDRESS 1 01-FEB-17 NORTH BOOK # 1 NEW AGE AUTHOR #2 US 35
CHRISTY ADDRESS 1 01-FEB-17 EAST BOOK # 2 NEW AGE AUTHOR #2 US 35

8 rows selected.

StackOverflow_Example

Additional Information

STUDENT NAME	STUDENT ADDRESS	DATE BORROWED	BRANCE NAME	NAME OF BOOK	BOOK PUBLISHER	NAME OF AUTHOR	COUNTRY OF AUTHOR	AGE OF AUTHOR
FRANK	ADDRESS 2	25-JAN-17	NORTH	BOOK # 1	NEW AGE	AUTHOR #1	US	20
FRANK	ADDRESS 2	25-JAN-17	NORTH	BOOK # 1	NEW AGE	AUTHOR #5	AUSTRALIA	33
CHRIS	ADDRESS 1	28-JAN-17	NORTH	BOOK # 1	NEW AGE	AUTHOR #2	US	35
CHRIS	ADDRESS 1	28-JAN-17	EAST	BOOK # 2	NEW AGE	AUTHOR #2	US	35
CHRIS	ADDRESS 1	10-FEB-17	WEST	BOOK # 2	NEW AGE	AUTHOR #5	AUSTRALIA	33
CHRIS	ADDRESS 1	10-FEB-17	EAST	BOOK # 1	NEW AGE	AUTHOR #6	AUSTRALIA	24
CHRISTY	ADDRESS 1	01-FEB-17	NORTH	BOOK # 1	NEW AGE	AUTHOR #2	US	35
CHRISTY	ADDRESS 1	01-FEB-17	EAST	BOOK # 2	NEW AGE	AUTHOR #2	US	35