Use Oracle-VM SQL Data Definition Language to create the following Bank-and-Cust

Question

Use Oracle-VM SQL Data Definition Language to create the following Bank-and-Customer database. You should properly define primary keys and foreign keys and then use SQL Query Language to express the following queries. In the document that you submit, you should have the question with its number, SQL query, and query result generated by Oracle-VM for each question. Each query is 3 marks and the result is C#B# | Balance Cl BI 1000 CI B2 2000 C1 B33000 BH Name Cit B1 England London B2 America New York B3 Royal Toronto B4 France Paris C2 BI 2000 C2 B2 3000 Customer 20||London | C3 B2 3000 CB31 4000 C3 B3 4000 CL Adams 2 Blake30 Paris 4000 C4 B35000 C4 Your Lastname 20 London 30 Toronto 1. Get the name of the customer who has an account in every Dank. 2. Get the name of the customer who has an account in every bank except France Bank. 3. Get the name of the customer who has an account in every bank that Clark banks without using NOT EXISTS (B MINUS A) 4. Get the name of the customer who has an account in every bank that Clark banks using NOT EXISTS (B MINUS A)

Explanation / Answer

Solution:

Queries for creating the tables:

CREATE TABLE Bank ( B# varchar2(10),

Name varchar2(50),

City varchar2(50),

constraint pk_Bank primary key (B#));

CREATE TABLE Customer ( C# varchar2(10),

Name varchar2(50),

Age number,

City varchar2(50),

constraint pk_Customer primary key (C#) );

CREATE TABLE Account ( B# varchar2(10),

C# varchar2(10),

Balance number,

constraint pk_Account primary key (B#, C#),

  constraint fk_Account_B# foreign key (B#) references BANK (B#),

  constraint fk_Account_C# foreign key (C#) references CUSTOMER (C#) );

1)

SELECT Customer.Name FROM Customer INNER JOIN Account ON= Customer.C#= Account. C# INNER JOIN Bank ON Bank.B#= Account.B# WHERE  EXIST (SELECT 1 FROM Account WHERE C#= Customer.C#)

2)

SELECT Customer.Name FROM Customer INNER JOIN Account ON= Customer.C#= Account. C# INNER JOIN Bank ON Bank.B#= Account.B# WHERE  EXIST (SELECT 1 FROM Account WHERE C#= Customer.C# AND Bank.Name!= 'France')

3)

Select c.name from customer c, account a where a.bid = ALL (

select aa.bid from account aa, customer cc

where cc.name='Clark');

4)

SELECT Customer.Name FROM Customer  INNER JOIN Account ON= Customer.C#= Account. C# INNER JOIN Bank ON Bank.B#= Account.B# WHERE NOT EXISTS (SELECT Customer.Name FROM Customer WHERE Customer.Name != 'Clark')

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.