Use Oracle-VM SQL Data Definition Language to create the following Bank-and-Cust
ID: 3730906 • Letter: U
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 mar Account C#B# | Balance B Name City B1 England London Cl BI 1000 B2 America New York C1B2 2000 B3 Royal Toronto B4 France Paris C1 B33000 C1 B4 4000 C2 BI 2000 C2 B2 3000 Customer Name 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 banks in more than two banks using grouping and aggregate function. 2. 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. 3. Get customer names and total balance in all banks. If the customer has no account, leave it null.Explanation / Answer
Below is the CREATE tavle statement to create all the given three tables.
CREATE TABLE Bank
(
B# CHAR(5),
Name VARCHAR(50),
City VARCHAR(20),
PRIMARY KEY (B#)
);
CREATE TABLE Customer
(
C# CHAR(5),
Name VARCHAR(50),
Age INT,
City VARCHAR(20),
PRIMARY KEY (C#)
);
CREATE TABLE Account
(
C# CHAR(5),
B# CHAR(5),
Balance DECIMAL(10,2),
PRIMARY KEY (C#, B#),
FOREIGN KEY (C#) REFERENCES Customer (C#),
FOREIGN KEY (B#) REFERENCES Bank (B#)
);
SQL queries to find the result
1) Aggregate function COUNT() is used to get the total account by each customer,
group by is used with aggregate function. Having clause is used to filter customer
with more than two accounts.
SELECT c.Name, COUNT(a.B#)
FROM Customer AS c
INNER JOIN Account AS a
ON c.C# = a.C#
GROUP BY c.Name
HAVING COUNT(a.B#) > 2;
2) Left join between Customer and Account table and Inner join between Account and bank
table is used.
SELECT c.C#, c.Name, c.Age, c.City, b.B#, b.Name, b.City, a.Balance
FROM FROM Customer AS c
LEFT JOIN Account AS a
ON c.C# = a.C#
INNER JOIN Bank AS b
ON b.B# = a.B#;
3) The Left join is used to join the Customer and Account table. All the information from
the Customer table is fetched and corresponding matching information from the Account table.
SELECT c.C#, c.Name, a.Balance
FROM FROM Customer AS c
LEFT JOIN Account AS a
ON c.C# = a.C#
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.