SQL QUERY


                                Practical-2

1. Retrieve specified information for the account holder who are not in ‘Ahmedabad’.
Ans: select * from account where city not in ‘Ahemdabad’;

2. Retrieve specified information for the account holder who are not in ‘Ahmedabad or ‘Vadodara’.
Ans: select * from account where city not in('Ahemadabad', 'Vadodara');

3. Retrieve those records of Account holder whose balance between is 50000 and 100000.
Ans: select * from account where balance between 50000 and  1000000;

.4.Retrieve those records of Account holder whose balance not between is 50000 and 100000.
Ans: select * from account where balance not between 50000 and 100000;

5. Display only those records whose amount is 5000, 25000, 30000.
Ans: select * from Transaction where amount in (5000, 25000, 30000);

6. Display only those records whose amount not in 5000, 25000, 30000.
Ans:  select * from Transaction where Amt not in (5000, 25000, 30000);

7.Display System date.
Ans: select sysdate from dual;

8. Find the date,15 days after today’s date.
Ans:  select sysdate + 15 from dual;

9.. Perform the following operation using a DUAL table.
5*5,34+34,1000/300,length of ‘uvpce’,display only month of systemdate
              
               1:  select 5*5 "multiplication" from dual;
              
               2: select 34+34 "sum" from dual;
              
               3:select 1000/300 "Div" from dual;
              
               4:select length('uvpce') "Length" from dual;
              
               5:selectto_char(sysdate, ‘mon’) from dual;
  
10.Find the date,20 days before today’s date.
Ans:  select sysdate - 20 from dual;


Transaction:

1.create table Transaction(Acc_no varchar2(5), tr_Date Date, Amt Number(10,2),  type_of_tr Char(1), mode_of_pay varchar2(10));


insert into Transaction values('A001', '03-may-04', 10000, 'D', 'cash');
insert into Transaction values('A002', '05-july-04', 5000, 'W', 'Cheque');
insert into Transaction values('A003', '12-Aug-04', 25000, 'D', 'Cheque');
insert into Transaction values('A004', '15-may-04', 30000, 'D', 'Cheque        ');
insert into Transaction values('A005', '22-oct-04', 15000, 'W', 'cash');



1.Find the total transaction amount of account holder from transaction table.
Ans: select sum(amt)”sum”  from Transaction;

2.Find minimum amount of transaction.
Ans: select min(amt)”min” from Transaction;

3.Find maximum amount of transaction.
Ans: select max(amt)”max” from Transaction;

4.Count the total account holders.
Ans: select count(acc_no)”acc_hoder” from Transaction;

5.Count only those records whose made of payment is ‘cash’.
Ans: select count(acc_no)”cash”  from Transaction where mode_of_pay = 'cash';

6.Count only those records whose transaction made in the month of ‘MAY’.
Ans: select count(Acc_no)”may”  from Transaction where to_char(tr_date, 'mon') = 'may';

7.Find the average value of transaction.
Ans: select avg(amt)”avg”  from Transaction;

8.Display the result of 4 rest to 4.
Ans: select power(4, 4) from dual;

9.Find the square root of 25.
Ans: select sqrt(25) from dual;

10.Write the query for the following Function.
Ans:
               1:select lower('UVPCE') from dual;
              
               2:select upper('uvpce') from dual;
              
               3:selectinitcap('uvpce') from dual;
              
               4:selectsubstr('uvpce',2, 3) from dual;
              
               5:select length ('uvpce') from dual;
              
               6:selectLtrim('uvpce', 'u') from dual;
              
               7:selectrtrim('uvpce', 'e') from dual;
              
               8:selectlpad('uvpce', 10, '#') from dual;
              
               9:selectrpad('uvpce', 10 , '&') from dual;
              


Comments

Post a Comment

Popular posts from this blog

SQL QUERY FOR PRIMARY AND FOREIGN KEY

Practical-3 1. Create table Account_key Column name Data Type Size Attributes Acc_no Varchar2 5 Primary key / first letter must start with ‘A’ Name Varchar2 30 NOT NULL City Varchar2 20 NOT NULL Balance Number 10,2 Balance >=500 Loan_taken Varchar2 3 Values(‘NO’,’YES’) ANS . create table account_key(acc_no varchar2(5) primary key check(acc_no like 'A%'),name varchar2(30) not null,city varchar2(20) not null,balance number(10,2) check(balance>=500),loan_taken varchar2(3) check(loan_taken='yes' or loan_taken='no')); acc_no Name City Balance loan_taken A001 Patel Jigar Mehsana 50000 YES A002 Patel Ramesh Mehsana 50000 YES A003 Dave Hardik Ahmeda
Read more

SQL QUERY practical-5

                                     PRACTICAL-5 CREATE TABLE SALESMEN(SNUM VARCHAR2(6) PRIMARY KEY CHECK(SNUM LIKE 'S%'), SNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), COMM NUMBER(5,2)); INSERT INTO SALESMEN VALUES('S1001','PIYUSH','LONDON',0.12); INSERT INTO SALESMEN VALUES('S1002','NIRAJ','SAN JOSE',0.13); INSERT INTO SALESMEN VALUES('S1003','MITI','LONDON',0.11); INSERT INTO SALESMEN VALUES('S1004','RAJESH','BARCELONA',0.15); INSERT INTO SALESMEN VALUES('S1005','HARESH','NEW YORK',0.10); INSERT INTO SALESMEN VALUES('S1006','RAM','BOMBAY',0.10); INSERT INTO SALESMEN VALUES('S1007','NEHAL','DELHI',0.09); CREATE TABLE CUSTOMER(CNUM VARCHAR2(6) PRIMARY KEY CHECK(CNUM LIKE 'C%'), CNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), RATING NUMBER(5), SNUM VARCHAR2(6)); INSERT INTO CUSTOMER VALUES('
Read more

Practicals of RollBack, Commit and Cursor programs

Image
                                                                 Practical-10 AIM :- RollBack   , Commit and Cursor bases Programs. 1. Analyze output of following scripts: A.      Begin                               Insert into new_salary values ('6', '6000');                               Update new_salary set salary=4000 where id=4;                               Commit;                               Rollback;                 End;               / Ans:-                B.   Begin                                 Insert into new_salary values (8, 8000);                Update new_salary set salary=50000 where id=5;                Savepoint   first;                    Rollback to savepoint first;        End;        /               Ans:- 2. Write pl/sql blocks that first insert the new record into table salary. Update the salary of employee name ‘Riya’ and ‘Rima’ by 7000/- then check the total salary   which does
Read more

Programs Based on Exception Handling and Trigger.

                                                       PRACTICAL-11 Aim :-   Programs Based on Exception Handling and Trigger. 1. Table: STUDENT (sr_no, name, address). Write a pl/sql code to retrieve the data from                student table whose sr.no is 02ce33. if     data not found then show the message “data not                found “  (Use EXCEPTION in pl/sql code). Ans :- declare no int; nm varchar2(20); begin no:=&no; select NAME into nm from student where ROLLNO=no; dbms_output.put_line('Name of student is '||nm); exception when NO_DATA_FOUND then dbms_output.put_line('No data found!!!'); end; / 2. Give Example of “TOO_MANY_ROWS” Exception.     Give Example of Unnamed system Exception.(Give Example of Primary key constraint   violation) Ans:- declare nm varchar2(20); begin select name into nm from student; dbms_output.put_line('Name of student is '||nm);   exception when TOO_MANY_R
Read more