Untitled
unknown
plain_text
5 months ago
7.4 kB
2
Indexable
SQL> create table branch_schema(branch_name varchar(10) primary key,branch_city
varchar(10) not null ,asset int not null);
Table created.
Table created.
SQL> create table customer2(customer_name varchar(10) primary key,customer_stree
t varchar(10) not null ,customer_city varchar(10) not null);
Table created.
SQL> create table loan2(loan_no int primary key,branch_name varchar(10) not null
,amount int not null,foreign key(branch_name) references branch_schema(branch_n
ame));
Table created.
SQL> create table borrower2(customer_name varchar(10) not null,loan_no int not n
ull,foreign key(customer_name) references customer2(customer_name),foreign key(l
oan_no) references loan2(loan_no));
Table created.
SQL> create table account2(account_no int primary key,branch_name varchar(10) no
t null,balance int not null, foreign key(branch_name) references branch_schema(b
ranch_name));
Table created.
SQL>
SQL> create table deposit2(customer_name varchar(10) not null,account_no int not
null,foreign key(customer_name) references customer2(customer_name),foreign key
(account_no) references account2(account_no));
SQL> select loan_no from loan2 where amount not between 90000 and 100000;
LOAN_NO
----------
101
102
103
104
SQL> select loan_no from loan2 where branch_name='paripally' and amount>1200;
LOAN_NO
----------
101
SQL> select avg(balance) from account2;
AVG(BALANCE)
------------
28000
SQL> select branch_name,avg(balance) from account2 group by branch_name;
BRANCH_NAM AVG(BALANCE)
---------- ------------
paripally 35000
alamcode 65000
oyoor 4000
anchal 8000
SQL> select count(customer_name) from customer2;
COUNT(CUSTOMER_NAME)
--------------------
4
SQL> select max(amount) from loan2 group by branch_name;
MAX(AMOUNT)
-----------
5000
75000
25000
50000
SQL> select branch_name,sum(balance) from account2 group by branch_name;
BRANCH_NAM SUM(BALANCE)
---------- ------------
paripally 35000
alamcode 65000
oyoor 4000
anchal 8000
SQL> insert into branch_schema values('oyoor','kollam',85000);
1 row created.
SQL> insert into branch_schema values('anchal','kollam',86000);
1 row created.
SQL> insert into branch_schema values('alamcode','tvm',105000);
1 row created.
SQL> select * from branch_schema;
BRANCH_NAM BRANCH_CIT ASSET
---------- ---------- ----------
paripally tvm 100000
oyoor kollam 85000
anchal kollam 86000
alamcode tvm 105000
SQL> insert into customer2 values('azhar','oyoor','kollam');
1 row created.
SQL> insert into customer2 values('bazil','oyoor','kollam');
1 row created.
SQL> insert into customer2 values('bhavya','alamcode','tvm');
1 row created.
SQL> insert into customer2 values('x','paripally','kollam');
1 row created.
SQL> select * from customer2;
CUSTOMER_N CUSTOMER_S CUSTOMER_C
---------- ---------- ----------
azhar oyoor kollam
bazil oyoor kollam
bhavya alamcode tvm
x paripally kollam
SQL> insert into loan2 values(101,'paripally','5000');
1 row created.
SQL> insert into loan2 values(102,'oyoor','25000');
1 row created.
SQL> insert into loan2 values(103,'anchal','50000');
1 row created.
SQL> insert into loan2 values(104,'alamcode','75000');
1 row created.
SQL> select * from loan2;
LOAN_NO BRANCH_NAM AMOUNT
---------- ---------- ----------
101 paripally 5000
102 oyoor 25000
103 anchal 50000
104 alamcode 75000
SQL> insert into borrower2 values('azhar',101);
1 row created.
SQL> insert into borrower2 values('bazil',102);
1 row created.
SQL> insert into borrower2 values('bhavua',103);
insert into borrower2 values('bhavua',103)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.SYS_C007084) violated - parent key not
found
SQL> insert into borrower2 values('bhavya',103);
1 row created.
SQL> insert into borrower2 values('x',104);
1 row created.
SQL> select * from borrower2;
CUSTOMER_N LOAN_NO
---------- ----------
azhar 101
bazil 102
bhavya 103
x 104
SQL> insert into account2 values(10,'paripally',35000);
1 row created.
SQL> insert into account2 values(11,'alamcode',65000);
1 row created.
SQL> insert into account2 values(12,'oyoor',4000);
1 row created.
SQL> insert into account2 values(12,'anchal',8000);
insert into account2 values(12,'anchal',8000)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C007088) violated
SQL> insert into account2 values(13,'anchal',8000);
1 row created.
SQL> select * from account2;
ACCOUNT_NO BRANCH_NAM BALANCE
---------- ---------- ----------
10 paripally 35000
11 alamcode 65000
12 oyoor 4000
13 anchal 8000
SQL> insert into deposit2 values('azhar',10);
1 row created.
SQL> insert into deposit2 values('bazil',11);
1 row created.
SQL> insert into deposit2 values('bhavya',12);
1 row created.
SQL> insert into deposit2 values('x',13);
1 row created.
SQL> select * from deposit2;
CUSTOMER_N ACCOUNT_NO
---------- ----------
azhar 10
bazil 11
bhavya 12
x 13
SQL> select loan_no from loan2 where amount not between 90000 and 100000;
LOAN_NO
----------
101
102
103
104
SQL> select loan_no from loan2 where branch_name='paripally' and amount>1200;
LOAN_NO
----------
101
SQL> select avg(balance) from account;
AVG(BALANCE)
------------
SQL> select avg(balance) from account2;
AVG(BALANCE)
------------
28000
SQL> select branch_name,avg(balance) from account2 group by branch_name;
BRANCH_NAM AVG(BALANCE)
---------- ------------
paripally 35000
alamcode 65000
oyoor 4000
anchal 8000
SQL> select branch_name,avg(balance) from account2 group by branch_name;
BRANCH_NAM AVG(BALANCE)
---------- ------------
paripally 35000
alamcode 65000
oyoor 4000
anchal 8000
SQL> select count(customer_name) from customer2;
COUNT(CUSTOMER_NAME)
--------------------
4
SQL> select max(amount) from loan2 group by branch_name;
MAX(AMOUNT)
-----------
5000
75000
25000
50000
SQL> select balance from account2 group by branch_name;
select balance from account2 group by branch_name
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> select balance from account2 order by branch_name;
BALANCE
----------
65000
8000
4000
35000
SQL> select branch_name,sum(balance) from account2 group by branch_name;
BRANCH_NAM SUM(BALANCE)
---------- ------------
paripally 35000
alamcode 65000
oyoor 4000
anchal 8000
Leave a Comment