Write a query to create client table schema in such a way that if we delete row from parent, its corresponding matching row must be deleted in child table. Write sql query to list all clients who have applied for loan. Write sql query to display details of those clients whose loan status is ‘accepted’. Create a view of client to display client name, address, and bank and loan status. Create a view of client to display loan amount of those clients who belong to Karachi. Create a view so that client can see only HBL bank account details. Create a view to display max amount of loan gender wise. Write a query to give SELECT and DELETE rights on column loan status to Tahir. Write a query to give all rights on column loan status and loan to Tahir and Javeria and take back rights. Write a query to display all the data from both tables. I Need Queries Output of the following create table Accounts (account_no number primary key, client_name varchar2(200),Gender varchar2(20),address Varchar2(200)); create table loans (branch_id number, bank_name varchar2(200),Loan number, loan_Status varchar2(200),account_no number references Accounts(account_no) ON DELETE CASCADE); delete from accounts where account_no= 101; select * from accounts a where exists (select 1 from loans l where l.account_no= a.account_no) select * from accounts a where exists (select 1 from loans l where l.account_no= a.account_no and status = 'accepted') 4. create or replace view view1 as select client_name , address,l.bank_name,l.loan_Status from accounts a, loans l where a.account_no = l.account_no 5.create or replace view view2 as select client_name , address,l.bank_name,l.loan_Status from accounts a, loans l where a.account_no = l.account_no and address = 'Karachi' 6. create or replace view view3 as select client_name , address,l.bank_name,l.loan_Status from accounts a, loans l where a.account_no = l.account_no and bank_name= 'HBL' 7. select gender,max(Loan) from accounts a, loans l where a.account_no = l.account_no group by gender 8. select a.*, l.* from accounts a, loans l where a.account_no = l.account_no
Note: Attach query and screenshot of output.
Account_no |
Client name |
Gender |
address |
|
||||
101 |
Tahir |
M |
Karachi |
|
||||
102 |
Umar |
M |
Isb |
|
||||
103 |
Javeria |
F |
karachi |
|
||||
104 |
Asma |
F |
lahore |
|
||||
105 |
Sohail |
M |
multan |
|
||||
Branch_ID |
Bank_Name |
Loan |
Loan status |
Account_no |
||||
201 |
HBL |
80000 |
open |
101 |
||||
202 |
UBL |
90000 |
pending |
102 |
||||
203 |
JS |
77000 |
accepted |
103 |
||||
204 |
Metro |
68000 |
Accepted |
101 |
||||
205 |
Al Habib |
75000 |
close |
null |
||||
- Write a query to create client table schema in such a way that if we delete row from parent, its corresponding matching row must be deleted in child table.
- Write sql query to list all clients who have applied for loan.
- Write sql query to display details of those clients whose loan status is ‘accepted’.
- Create a view of client to display client name, address, and bank and loan status.
- Create a view of client to display loan amount of those clients who belong to Karachi.
- Create a view so that client can see only HBL bank account details.
- Create a view to display max amount of loan gender wise.
- Write a query to give SELECT and DELETE rights on column loan status to Tahir.
- Write a query to give all rights on column loan status and loan to Tahir and Javeria and take back rights.
- Write a query to display all the data from both tables.
I Need Queries Output of the following
create table Accounts (account_no number primary key, client_name varchar2(200),Gender varchar2(20),address Varchar2(200));
create table loans (branch_id number, bank_name varchar2(200),Loan number, loan_Status varchar2(200),account_no number references Accounts(account_no) ON DELETE CASCADE);
- delete from accounts where account_no= 101;
- select * from accounts a where exists (select 1 from loans l where l.account_no= a.account_no)
- select * from accounts a where exists (select 1 from loans l where l.account_no= a.account_no and status = 'accepted')
4.
create or replace view view1 as
select client_name , address,l.bank_name,l.loan_Status
from accounts a, loans l
where a.account_no = l.account_no
5.create or replace view view2
as
select client_name , address,l.bank_name,l.loan_Status
from accounts a, loans l
where a.account_no = l.account_no
and address = 'Karachi'
6.
create or replace view view3
as
select client_name , address,l.bank_name,l.loan_Status
from accounts a, loans l
where a.account_no = l.account_no
and bank_name= 'HBL'
7.
select gender,max(Loan)
from accounts a, loans l
where a.account_no = l.account_no
group by gender
8.
select a.*, l.*
from accounts a, loans l
where a.account_no = l.account_no
Step by step
Solved in 4 steps with 7 images