I need the Oracle-VM SQL Query for the following queries. Please help with as ma
ID: 3730939 • Letter: I
Question
I need the Oracle-VM SQL Query for the following queries. Please help with as many possible. Please do not give me answers in ALG (relational algebra) or TRC (Tuple relational calculus).
Get the name of the customer who banks in Royal or America bank.
Get the customer name/bank name pairs such that the indicated customer has an account in the indicated
bank.
Get the customer name/bank name pairs such that the indicated customer does not an account in the
indicated bank.
Get the name of the customer who has an account in every bank except France Bank.
Get the name of the customer who has an account in every bank that Clark banks without using NOT EXISTS (B MINUS A).
Get the name of the customer who has an account in every bank that Clark banks using NOT EXISTS (B MINUS A).
Get the name of the customer who banks only in the banks that Clark banks.
Get the name of the customer, the number of banks he/she banks, and total balance he/she has.
Get complete information of each customer such that when the customer has an bank account, list bank
detail and balance; when the customer does not have an account, just list the customer information.
Get customer names and total balance in all banks. If the customer has no account, leave it null.
B Name City Bl England London B2 America New York B3 Royal Toronto Customer Name Age City C1 Adams 20 London 30 Paris C4 Your Lastname 20 London C5 Smith 30 TorontoExplanation / Answer
Firstly we have to create Account,Bank and Customer tables and insert values in that tables using insert clause....
Create Bank Table...............
SQL> create table Bank
2 (
3 b# varchar2(3) primary key,
4 bname varchar2(20),
5 bcity varchar2(20)
6 );
Table created.
SQL> desc Bank;
Name Null? Type
----------------------------------------- -------- ----------------------------
B# NOT NULL VARCHAR2(3)
BNAME VARCHAR2(20)
BCITY VARCHAR2(20)
Creatie Customer table.................
SQL> create table Customer
2 (
3 c# varchar2(3) primary key,
4 cname varchar2(20),
5 age number(3),
6 city varchar2(20)
7 );
Table created.
SQL> desc Customer;
Name Null? Type
----------------------------------------- -------- ----------------------------
C# NOT NULL VARCHAR2(3)
CNAME VARCHAR2(20)
AGE NUMBER(3)
CITY VARCHAR2(20)
Create Account table...................
SQL> create table Account
2 (
3 c# varchar2(3),
4 b# varchar2(3),
5 balance number(9,3),
6 foreign key(c#) references Customer(c#),
7 foreign key(b#) references Bank(b#)
8 );
Table created.
SQL> desc Account;
Name Null? Type
----------------------------------------- -------- ----------------------------
C# VARCHAR2(3)
B# VARCHAR2(3)
BALANCE NUMBER(9,3)
SQL> insert into Bank values('B1','England','London');
1 row created.
SQL> insert into Bank values('B2','America','New York');
1 row created.
SQL> insert into Bank values('B3','Royal','Toronto');
1 row created.
SQL> insert into Bank values('B4','France','Paris');
1 row created.
SQL> select * from Bank;
B# BNAME BCITY
--- -------------------- --------------------
B1 England London
B2 America New York
B3 Royal Toronto
B4 France Paris
SQL> insert into Customer values('C1','Adams',20,'London');
1 row created.
SQL> insert into Customer values('C2','Blake',30,'Paris');
1 row created.
SQL> insert into Customer values('C3','Clark',25,'Paris');
1 row created.
SQL> insert into Customer values('C4',null,20,'London');
1 row created.
SQL> insert into Customer values('C5','Smith',30,'Toronto');
1 row created.
SQL> select * from Customer;
C# CNAME AGE CITY
--- -------------------- ---------- --------------------
C1 Adams 20 London
C2 Blake 30 Paris
C3 Clark 25 Paris
C4 20 London
C5 Smith 30 Toronto
SQL> insert into Account values('C1','B1',1000);
1 row created.
SQL> insert into Account values('C1','B2',2000);
1 row created.
SQL> insert into Account values('C1','B3',3000);
1 row created.
SQL> insert into Account values('C1','B4',4000);
1 row created.
SQL> insert into Account values('C2','B1',2000);
1 row created.
SQL> insert into Account values('C2','B2',3000);
1 row created.
SQL> insert into Account values('C3','B2',3000);
1 row created.
SQL> insert into Account values('C3','B3',4000);
1 row created.
SQL> insert into Account values('C4','B2',4000);
1 row created.
SQL> insert into Account values('C4','B3',5000);
1 row created.
SQL> select * from Account;
C# B# BALANCE
--- --- ----------
C1 B1 1000
C1 B2 2000
C1 B3 3000
C1 B4 4000
C2 B1 2000
C2 B2 3000
C3 B2 3000
C3 B3 4000
C4 B2 4000
C4 B3 5000
10 rows selected.
[1]
Get the name of the customer who banks in Royal or America bank.
SQL> select distinct cname
2 from Bank,Customer,Account
3 Where Bank.b#=Account.b#
4 and Customer.c#=Account.c#
5 and bname='America' or bname='Royal';
CNAME
--------------------
Adams
Blake
Clark
Smith
OR
SQL> select cname from Customer where c# in (select c# from Bank where bname in ('Royal','America'));
CNAME
--------------------
Adams
Blake
Clark
Smith
[2]Get the customer name/bank name pairs such that the indicated customer has an account in the indicated
bank.
SQL> select cname,bname from Account,Bank,Customer where Account.b#=Bank.b# and
2 Account.c#=Customer.c#
3 and exists (select distinct cname from Customer,Account where Account.c#=Customer.c#);
CNAME BNAME
-------------------- --------------------
Adams England
Adams America
Adams Royal
Adams France
Blake England
Blake America
Clark America
Clark Royal
America
Royal
10 rows selected.
[3]Get the name of the customer who has an account in every bank except France Bank.
SQL> select distinct cname from Account,Bank,Customer
2 where Account.b#=Bank.b#
3 and Account.c#=Customer.c#
4 and bname !='France';
CNAME
--------------------
Adams
Blake
Clark
[4]Get the name of the customer who banks only in the banks that Clark banks.
7 rows selected.
SQL> select distinct cname from Account,Customer,Bank where Account.b#=Bank.b# and
2 Customer.c#=Account.c# and
3 bname in (select bname from Account,Bank,Customer where Account.b#=Bank.b#
4 and Customer.c#=Account.c# and cname='Clark');
CNAME
--------------------
Adams
Blake
Clark
[5]Get the name of the customer, the number of banks he/she banks, and total balance he/she has.
SQL> select distinct cname,count(bname),sum(balance)
2 from Account,Bank,Customer
3 where Account.b#=Bank.b#
4 and Account.c#=Customer.c#
5 group by cname;
CNAME COUNT(BNAME) SUM(BALANCE)
-------------------- ------------ ------------
Adams 4 10000
2 9000
Blake 2 5000
Clark 2 7000
[6]Get customer names and total balance in all banks. If the customer has no account, leave it null.
SQL> select distinct cname,sum(balance)
2 from Account,Customer
3 where Account.c#=Customer.c#
4 group by cname;
CNAME SUM(BALANCE)
-------------------- ------------
Adams 10000
9000
Blake 5000
Clark 7000