Question

WRITE SQL QUERY THAT DOES THE FOLLOWING Q3| Find the customer's name and branch names of all customers who do have accounts but not a loan in the bank. And find those customers who do have loan but not have an account in the bank. GIVEN DATABASE TABLES 2 Account table Branch table Customer table Loan table BNAME 3 A# CNAME BNAME BAL BNAME ASSETS BCITY CNAME STREET CCITY CNAME AMT 1234 Baba 2222 Rahimi Sauthdale Ridgedale 150eee Minnetonka Minnetonka 1e01 Cook France 10000 1111 4 Ireland 5 2082 Rahimi a444 Cook France 6690 Main 10000e Minnetonka Cook 2222 Main MPLS Southdale seee 1234 5555 Cook York 1000е Second 1238ee Minnetonka Erickson Eagan 3003 Cook York 10ee 6 Second 7 6666 Cook 1234 Third Eagan Main 3338 Southdale 7seeee Edina Ireland 4804 Rahini France 20000 2222 5e0e Cook Ridgedale 1se France 125eee Edina James Edina sees Rahimi York 11000 8 Fourth 9 Second Main 5001 Cook 111 York 3000ee Edina. Janzen 1234 Fifth Edina 6e06 Cook 30000 Eight 7777 3ones Johnson 3333 Main 7007 Jones France 5678 450000 MPLS MPLS France 4500 10 Street 11 1111 Main 8008 Jones 9999 Jones York 456 Northtown 347000 MPLS Jones MPLS Southdale 1200 12. 1234 Ninth Minnetonka 1234 Tomczak York 3rd Street 100000 Eagan York 123.5 Love 9809 Jones 9000 Eden 1111 sixth Prairie 4567 Love France Airport 446000 Eagan Rahimi 1004 Tomczak France 34560 300 13 1234 Main 9800 Love 333 Toncat MPLS 4009 Janes France 12300 14 Seventh 1234 7765 James York Minnetonka 9008 Melcher 4450 100 Tones France 15 Eighth 1234 8888 Woods 4450 7756 Woods Yark 1eee Melcher MPLS France 16 Nineth 17 Southdale 1234 01d 7757 Woods 1000 Woods Minnetonka 1111 7758 Hoods France 1000 Tomczak Minnetonka 18 пенеst 19 3333 Rahimi York 1111 GIVEN SQL CODE FOR CREATING THE DATABASE TABLES ORACLE Version 11g / DBMS name: / Created on: drop index CONTAINS FK; drop index OWNS FK; drop table ACCOUNT cascade constraints; drop table BRANCH cascade constraints drop table CUSTOMER cascade constraints; drop index IS LOCATED_AT_FK drop index HASFK; drop table LOAN cascade constraints; alter table ACCOUNT drop constraint FK ACCOUNT_CONTAINS BRANCH; alter table ACCOUNT drop constraint FK ACCOUNT OWNS CUSTOMER alter table LOAN drop constraint FK LOAN_HAS_CUSTOMER; alter table LOAN drop constraint FK LOAN ISs LOCATE BRANCH; /* Table: ACCOUNT create table ACCOUNT not null, not null, not null, NUMBER (6) CHAR (15) CHAR (15) NUMBER (10, 2), A# CNAME BNAME BAL constraint PK ACCOUNT primary key (A# / Index: OWNS FK create index OWNS FK on ACCOUNT ( CNAME ASC Index: CONTAINS FK create index CONTAINS FK on ACCOUNT BNAME ASC / Table: BRANCH create table BRANCH CHAR (15) NUMBER(10, 2) CHAR (15), not null, BNAME ASSETS BCITY constraint PK BRANCH primary key (BNAME) /Table: CUSTOMER create table CUSTOMER CHAR (15) CNAME CHAR (30), CCITY constraint PK CUSTOMER primary key (CNAME) not null, STREET CHAR (15), /Table: LOAN create table LOAN L# NUMBER (6) CHAR(15) CHAR(15) NUMBER (8,2), not null, not null, not null, CNAME BNAME AMT constraint PK LOAN primary key (L#) /Index: HAS FK create index HAS FK on LOAN CNAME ASC /Index: IS LOCATED AT FK create index IS LOCATED AT FK on LOAN BNAME ASC alter table ACCOUNT add constraint FK ACCOUNT_CONTAINS BRANCH foreign key (BNAME) references BRANCH (BNAME) alter table ACCOUNT add constraint FK ACCOUNT OWNS CUSTOMER foreign key (CNAME) references CUSTOMER (CNAME) alter table LOAN add constraint EK LOAN HAS CUSTOMER foreign key (CNAME) references CUSTOMER (CNAME) alter table LOAN add constraint FK LOAN IS LOCATE BRANCH foreign key (BNAME) references BRANCH (BNAME); Insert into CUSTOMER Values ('Baba 'Minnetonka Insert into CUSTOMER 1234 Ireland 2222 Main Values Cook , 'MPLS '1234 Second Insert into CUSTOMER Values Erickson', Eagan Insert into CUSTOMER Eagan Insert into CUSTOMER 'Edina Insert into CUSTOMER 1234 Third Values (Ireland', Values (James 2222 Fourth Values (Janzen', '1234 Fifth 'Edina Insert into CUSTOMER Values (Johnson', 3333 Main' ,'MPLS 1111 Main Values (Jones 'MPLS Insert into CUSTOMER 1234 Ninth Values 'Love Insert into CUSTOMER 'Minnetonka values ('Rahimi', '1111 sixth Insert into CUSTOMER 'Eden Prairie Insert into CUSTOMER Values (Tomcat', '1234 Seventh 'MPLS 1234 Eighth Values ('Tones Insert into CUSTOMER 'Minnetonka Insert into CUSTOMER 'MPLS Insert into CUSTOMER Values ('Melcher' 1234 Nineth Values (Woods 1234 0ld 'Minnetonka Insert into CUSTOMER 'Minnetonka 1111 newest Values ('Tomczak', Insert into BRANCH values 'Ridgedale' ,150000.00 ,'Minnetonka Insert into BRANCH values Main 100000.00 'Minnetonka Insert into BRANCH values 'Second ,123000.00 ,Minnetonka 'Edina ,125000. n Insert into BRANCH values ( Southdale,750000.00 Edina 'MPLS into BRANCH values Eight street', Insert 450000.00 Northtown,347000.00 'MPLS 3rd Street Airport" into BRANCH values ( into BRANCH values Insert into BRANCH values Inser 100000.00 .Eagan Eagan Insert ,446000.00 into account values (2222 Rahimi, Insert Southdale' ,1111.00 Cook France ccount values (4444 Inser 6690.00 Cook Insert into account values (5555 York Cook Insert into account values (6666 Main' ,3338 insert intoa Cook Ridgedale ,150 account values (5000 'Cook insert into account values (5001 'Second" .111 Jones Insert into account values (7777 France" 5678.00 account values (9999 a56.00 Jones" York Insert Insert into account values Tomczak', York" (1234 Insert into account values (4567 Love France" ,300.00 'Love ccount values (9800 "Мain' Insert 222 00 Insert into account values (7765 James" York Insert into account values (7756 York Woods" ,1000.00 Woods Insert 00.00 values (7757 'Southdale Woods 'France Insert into account values (7758 Inser 00.00 values (3333 Rahimi York ,1111.00 Cook 'France OAN 10000 Insert lues 1001 2002 . Southdale' ,5000 ) 7 Insert into LOAN values ( Rahimi', Cook Insert values 3003 York* Rahimi Insert into LOAN values 4004 . France ,20000 Insert into LOAN values Rahimi 5005 York ,11000 Insert intoLOAN values ,30000 Cook Main 6006 Jones Insert into LOAN values ( 7007 'France Inse OAN values ,1200) ; Insert into LOAN values ( Jones 'Southdale 8008 . 9009 Jones. York France Insert into LOAN values ( 1004, Tomczak, , 34560 France OAN 4009r Insert alues 'James" .12300 Insert into LOAN values ( Insert into LOAN values ( 9008 Melcher', WOods France 4450 8888 'France" 4450); commitz -display all the created table SELECT FROM ACCOUNT SELECT. FROM BRANCH SELECT FROM CUSTOMER SELECT FROM LOAN

Answer 1

Please find the query below.

SELECT c.CNAME, a.BNAME FROM Customer c JOIN Account a ON c.CNAME = a.CNAME WHERE c.CNAME NOT IN (SELECT CNAME FROM Loan)

UNION

SELECT c.CNAME, l.BNAME FROM Customer c JOIN Loan l ON c.CNAME = l.CNAME WHERE c.CNAME NOT IN (SELECT CNAME FROM Account);