Practicals of pl/sql

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

                                Prac tical-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 (5
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