SQL statements for Relations Account, Depositor, Branch

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';