Given below are a few examples of a database and a few queries based on that.
(1). Suppose there is a banking database which comprises following tables :
Customer(Cust_name, Cust_street, Cust_city)
Branch(Branch_name, Branch_city, Assets)
Account (Branch_name, Account_number, Balance)
Loan(Branch_name, Loan_number, Amount)
Query : Find the names of all the customers who have taken a loan from the bank and also have an account at the bank.
Step 1 : Identify the relations that would be required to frame the resultant query.
First half of the query(i.e. names of customers who have taken loan) indicates “borrowers” information.
So Relation 1 —–> Borrrower.
Second half of the query needs Customer Name and Account number which can be obtained from Depositor relation.
Hence, Relation 2——> Depositor.
Step 2 : Identify the columns which you require from the relations obtained in Step 1.
Column 1 : Cust_name from Borrower
Column 2 : Cust_name from Depositor
Step 3 : Identify the operator to be used. We need to find out the names of customers who are present in both Borrower table and Depositor table.
Hence, perator to be used—-> Intersection.
Final Query will be