Question 1:
Consider the following relational schema:
Account (AccountNo, Balance, DateOfStart, CustomerID) - AccountNO is a unique number given to every account with the Bank (Primary Key), Balance is the present balance of the account, DateofStart is the date of opening the account and CustomerID is a unique ID given to each customer (Foreign Key).
Customer (CustomerID, CustomerName) - CustomerID is the Primary Key and CustomerName is the name of the customer. A customer can open several accounts with the Bank.
(a) Create the tables using SQL from the schema as given above; you must include Primary key, foreign keys and constraints in your implementation. Enter few sets of meaningful data in each table.
(2 Marks)
(b) Write and run SQL statements for the following queries:
(3 Marks)
(i) List the account numbers of the customers whose name is "RAKESH". (ii) List the total balance of the customer whose customer ID is "C001"
(iii) Find the accounts that were opened after 01-01-2022.
(iv) List the Customer Names of the customers who have more than one account. (v) Assuming a customer is given an interest at the rate of 3% on the present balance,
compute the interest amount for each account. (vi) Find the list of customers who has zero balance in their account.
Note: Make suitable assumptions, if any.
a)
CREATE TABLE Account (
AccountNo INT PRIMARY KEY,
Balance DECIMAL(10,2),
DateOfStart DATE,
CustomerID INT,
CONSTRAINT FK_Customer FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50)
);
INSERT INTO Customer VALUES (1, 'RAKESH');
INSERT INTO Customer VALUES (2, 'SARA');
INSERT INTO Customer VALUES (3, 'JOHN');
INSERT INTO Account VALUES (1001, 500.00, '2022-01-15', 1);
INSERT INTO Account VALUES (1002, 750.50, '2021-05-27', 1);
INSERT INTO Account VALUES (1003, 1000.00, '2022-03-01', 2);
INSERT INTO Account VALUES (1004, 250.75, '2020-12-10', 3);
b)
(i)
SELECT AccountNo
FROM Account a
JOIN Customer c ON a.CustomerID = c.CustomerID
WHERE c.CustomerName = 'RAKESH';
(ii)
SELECT SUM(Balance) AS TotalBalance
FROM Account
WHERE CustomerID = 'C001';
(iii)
SELECT *
FROM Account
WHERE DateOfStart > '2022-01-01';
(iv)
SELECT CustomerName
FROM Customer
WHERE CustomerID IN
(SELECT CustomerID
FROM Account
GROUP BY CustomerID
HAVING COUNT(*) > 1);
(v)
SELECT AccountNo, Balance * 0.03 AS InterestAmount
FROM Account;
(vi)
SELECT CustomerName
FROM Customer c
JOIN Account a ON a.CustomerID = c.CustomerID
GROUP BY CustomerName
HAVING SUM(Balance) = 0;
(a) Here is the SQL script to create the tables and insert some sample data:
```sql
-- Create Account table
CREATE TABLE Account (
AccountNo INT PRIMARY KEY,
Balance DECIMAL(10, 2),
DateOfStart DATE,
CustomerID VARCHAR(10),
CONSTRAINT fk_Customer FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
-- Insert data into Account table
INSERT INTO Account (AccountNo, Balance, DateOfStart, CustomerID) VALUES
(1001, 5000.00, '2021-05-01', 'C001'),
(1002, 10000.00, '2021-08-15', 'C001'),
(2001, 7000.00, '2021-10-20', 'C002'),
(2002, 15000.00, '2022-01-10', 'C003');
-- Create Customer table
CREATE TABLE Customer (
CustomerID VARCHAR(10) PRIMARY KEY,
CustomerName VARCHAR(50)
);
-- Insert data into Customer table
INSERT INTO Customer (CustomerID, CustomerName) VALUES
('C001', 'RAKESH'),
('C002', 'ANITA'),
('C003', 'JOHN');
```
(b) Here are the SQL statements for the given queries:
(i) List the account numbers of the customers whose name is "RAKESH":
```sql
SELECT AccountNo
FROM Account
INNER JOIN Customer ON Account.CustomerID = Customer.CustomerID
WHERE Customer.CustomerName = 'RAKESH';
```
(ii) List the total balance of the customer whose customer ID is "C001":
```sql
SELECT SUM(Balance) AS TotalBalance
FROM Account
WHERE CustomerID = 'C001';
```
(iii) Find the accounts that were opened after 01-01-2022:
```sql
SELECT AccountNo
FROM Account
WHERE DateOfStart > '2022-01-01';
```
(iv) List the Customer Names of the customers who have more than one account:
```sql
SELECT CustomerName
FROM Customer
WHERE CustomerID IN (
SELECT CustomerID
FROM Account
GROUP BY CustomerID
HAVING COUNT(*) > 1
);
```
(v) Assuming a customer is given an interest at the rate of 3% on the present balance, compute the interest amount for each account:
```sql
SELECT AccountNo, Balance, (Balance * 0.03) AS InterestAmount
FROM Account;
```
(vi) Find the list of customers who have zero balance in their account:
```sql
SELECT CustomerName
FROM Customer
WHERE CustomerID IN (
SELECT CustomerID
FROM Account
WHERE Balance = 0
);
```
Note: You may need to adjust the SQL queries according to your database system and data types used.