<< PRACTICE PRACTICAL 3 >>
1. create table ACCOUNT(acc_no varchar2(5) primary key,Name varchar2(30) NOT NUL
L,City varchar2(20) NOT NULL,Balance number(10,2),loan_taken varchar2(3),check(a
cc_no like 'A%'),check(Balance>=500),check(loan_taken='YES' or loan_taken='NO'))
;
>> insert into ACCOUNT values('&acc_no','&Name','&City','&Balance','&loan_taken'
);
2. create table LOAN(loan_no varchar2(5) primary key,acc_no varchar2(5) referenc
es ACCOUNT(acc_no),loan_amt number(10,2) NOT NULL,interest_rate number(5,2) NOT
NULL,loan_date date,remaining_loan number(10,2),check(loan_no like'L%'),check(re
maining_loan < loan_amt));
>> insert into LOAN values('&loan_no','&acc_no','&loan_amt','&interest_rate','&l
oan_date','&remaining_loan');
3. create table INSTALLMENT (loan_no varchar2(5) references LOAN(loan_no),inst_n
o varchar2(5),inst_date date NOT NULL,Amount number(10,2) NOT NULL,check(inst_no
like'I%'));
>> insert into INSTALLMENT values('&loan_no','&inst_no','&inst_Date','&Amount');
4. create table TRANSACTION(acc_no varchar2(5) references ACCOUNT(acc_no),tr_Dat
e date NOT NULL,Amt number(10,2) NOT NULL,type_of_tr char(1),mode_of_pay varchar
2(10),check(mode_of_pay='Cash' or mode_of_pay='Cheque'),check(type_of_tr='D' or
type_of_tr='W'));
>> insert into TRANSACTION values('&acc_no','&tr_Date','&Amt','&type_of_tr','&mo
de_of_pay');
<<QUERIES>>
1. Retrieve specified information for the account holder who are not in
Ahmedabad .
>> select * from ACCOUNT where not City='Ahmedabad';
2. Retrieve specified information for the account holder who are not in Ahmedabad
or Vadodara .
>> select * from ACCOUNT where City not in ('Ahmedabad','Vadodara');
3. Retrieve those records of Account holder whose balance between is 50000 and 1
00000.
>> select * from ACCOUNT where Balance between 50000 and 100000;
4. Retrieve those records of Account holder whose balance not between is50000 an
d 100000.
>> select * from ACCOUNT where Balance not between 50000 and 100000;
5. Display only those records whose amount is 5000, 25000, 30000.
>> select * from INSTALLMENT where Amount in (5000,25000,30000);
6. Display only those records whose amount not in 5000, 25000, 30000.
>> select * from INSTALLMENT where Amount not in (5000,25000,30000);
7. Display System date.
>> select sysdate from dual;
8. Find the date,15 days after today s date.
>> select sysdate+15 from dual;
9. Perform following operation using DUAL table. 5*5,34+34,1000/300,length of
ce ,display only month of systemdate
>>
>>
>>
>>
>>
select
select
select
select
select
5*5 from dual;
34+34 from dual;
1000/300 from dual;
length('hardik') from dual;
to_char(sysdate,'mon') from dual;
10. Find the date,20 days before today s date.
>> select sysdate-20 from dual;
1. Find the total transaction amount of account holder from transaction table.
>> select sum(AMT) "total amount" from TRANSACTION;
2. Find minimum amount of transaction.
>> select min(AMT) "minimum amount" from TRANSACTION;
3. Find maximum amount of transaction.
>> select max(AMT) "maximumm amount" from TRANSACTION;
4. Count the total account holders.
>> select count(AMT) from TRANSACTION;
5. Count only those records whose made of payment is cash .
uvp
>> select count(AMT) from TRANSACTION where Mode_of_pay='cash';
6. Count only those records whose transaction made in the month of
MAY .
>> select count(AMT) from TRANSACTION where to_char(Trans_Date,'MON')='MAY';
7. Find the average value of transaction.
>> select avg(AMT) from TRANSACTION;
8. Display the result of 4 rest to 4.
>> select power(4,4) from dual;
9. Find the square root of 25.
>> select sqrt(AMT) from dual;
[Link] the query for the following Function.
LOWER,INITCAP,UPPER,SUBSTR,LENGTH,LTRIM,RTRIM,LPAD,RPAD.
>> select lower('HARDIK') "LOWERCASC" FROM DUAL;
>> select initcap('HARDIK') "mid range" FROM DUAL;
>> select upper('hardik') "upper case" FROM DUAL;
>> select upper('hardik') "upper case" FROM DUAL;
>> select length('hardik') "length" from dual;
>> select substr('hardik',2,3) "substr" from dual;
>> select ltrim('hardik','j') "hi" from dual;
>> select rtrim('hardik','n') "hi" from dual;
>> select lpad('hardik',50,'*') "hi" from dual;
>> select rpad('hardik',50,'*') "hi" from dual;
<<Create a table:STUDENT>>
>> create table STUDENT(Rollno Varchar2(6),Name Varchar2(20),Branch Varchar2(6),
Address Varchar2(20));
1. Add PRIMARY KEY (roll no) and provide constraint name PRIM_rollno.
>> alter table STUDENT add constraint PRIM_rollno primary key(Rollno);
2. Add NOT NULL constraint to name,branch for student table.
>> alter table STUDENT modify(Name constraint NOTNULL1 NOT NULL,branch constrai
nt NOTNULL2 NOT NULL);
3. Add check constraint and check name is in capital letter.
>> alter table STUDENT add constraint CK check(Name=upper(Name));
4. Drop the primary key.
>> alter TABLE STUDENT DROP primary key;
alter TABLE STUDENT DROP primary key;
>> alter table student drop constraint NOTENULL1;
alter table student drop constraint NOTENULL2;
<<Create a Table REGISTER.>>
create table REGISTER(Rollno Varchar2(6),Name Varchar2(20));
1. Provide foreign key references rollno of student table.
>> alter table REGISTER add foreign key(Rollno) references student;
2. Add check constraint to check name s first letter is always capital.
>> alter table REGISTER add constraint JK check(substr(Name,1)=upper(substr(name
,1,1)));
3. Add NOT NULL constraint to name of register table.
>> alter table REGISTER modify(Name constraint NOTNULL1 NOT NULL);
4. Drop foreign key of REGISTER table.
-> alter TABLE REGISTER DROP constraint foreignkey;
5. Drop NOT NULL constraint.
>> alter table REGISTER drop constraint NOTNULL1;