a. Find the record of the customer with balance in between Rs 10,000 and Rs 100,000.
To find: Record of customer. ie; Depositor.
Given condition: balance between 10000 and 100000.
The condition(balance) is present in another table Account. Thus, JOIN is required.
Option #1: Using NATURAL JOIN and >=, <=.
SELECT Depositor.cust_name,
Depositor.acc_number,
Account.balance
FROM Depositor
NATURAL JOIN Account
WHERE Account.balance >= 10000
AND Account.balance <= 100000;
Option #2: Using NATURAL JOIN and BETWEEN.
SELECT Depositor.cust_name,
Depositor.acc_number,
Account.balance
FROM Depositor
NATURAL JOIN Account
WHERE Account.balance BETWEEN 10000 AND 100000;
Option #3: Using INNER JOIN and >=, <=.
SELECT Depositor.cust_name,
Depositor.acc_number,
Account.balance
FROM Depositor
INNER JOIN Account
ON Depositor.acc_number = Account.acc_number
WHERE Account.balance >= 10000
AND Account.balance <= 100000;
Option #4: Using INNER JOIN and BETWEEN.
SELECT Depositor.cust_name,
Depositor.acc_number,
Account.balance
FROM Depositor
INNER JOIN Account
ON Depositor.acc_number = Account.acc_number
WHERE Account.balance BETWEEN 10000 AND 100000;
b. Find the name of depositor consisting of at most 6 characters.
Option #1: Using LEN OR DATALENGTH. For Microsoft SQL Server (MSSQL).
SELECT cust_name
FROM Depositor
WHERE LEN(cust_name) <= 6;
---------- OR ------------
SELECT cust_name
FROM Depositor
WHERE DATALENGTH(cust_name) <= 6;
Option #2: Using LENGTH OR CHAR_LENGTH. For MySQL.
SELECT cust_name
FROM Depositor
WHERE LENGTH(cust_name) <= 6;
---------- OR ------------
SELECT cust_name
FROM Depositor
WHERE CHAR_LENGTH(cust_name) <= 6;
c. Find all the account maintained at branch Bhaktapur.
SELECT *
FROM Account
WHERE branch_name = 'Bhaktapur';
d. Increase the interest by 10% to the account with balance over Rs 1,000,000.
UPDATE Account
SET balance = balance * 1.10
WHERE balance > 1000000;
e. Find the name of depositor whose Name starts with 'Z'.
Option #1: Using LIKE. Works in both MySQL and MSSQL.
SELECT cust_name
FROM Depositor
WHERE cust_name LIKE 'Z%';
Option #2: Using SUBSTR. Works in both MySQL and MSSQL.
SELECT cust_name FROM Depositor
WHERE SUBSTR(cust_name, 1, 1) = 'Z';
Option #3: Using SUBSTRING. Works in MySQL.
SELECT cust_name FROM Depositor
WHERE SUBSTRING(cust_name, 1, 1) = 'Z';