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;
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.
1 answer