Pl/Sql queries

1. Create a stored procedure named Raise_pay that will increase an employee’s id no. and the percent increase to his salary.

Create or Replace procedure raise_pay
(
pEmpno. Emp.Empno%type
pPercent  Number
)
As
 incAmount Emp.Sal%Type;
 Sal  Emp.Sal%type;
Begin
Select SAL into sal  from Emp
Where Empno. = pEmpno.  ;
incAmount := pPercent * sal/100 ;
update Emp set sal = sal + incAmount
 where Empno. = pEmpno. ;
End;

Write procedure that adds Item name and Supplier name (both passed as parametre) in Item table along with a unique item code. The code should be calculated as existing last code (generally maximum code)+1

 Create or Replace procedure New_item
(It_name VARCHAR 2, Sup_name VARCHAR 2)
As
 Code Item.Code%type;
Begin
 Select Max(code)+1
 INTO It_code
 From Item ;
Insert into Item(code, Item name, Supplier name)
Values(108, pencil, Camlin);
End;

Normalization

Normalization: The objective of relational database is to generate a set of relations that allows the user to store information without unnecessary redundancy where permits to retrieve information easily. The right approach is to design schemes that are in appropriate normal form. The basic concept of normalization is that, same data should not be stored in multiple places or we can say through normalization conceptual schema (logical data structures) transformed in computer representable form. The normalization process helps to attain good database design.

create view in sql

Write sql command to create view consisting of all students in "Medical" stream and who have scored "A" grade.

CREATE View V1 As
Select * from student
where stream = 'Medical' and grade = 'A';

how to write the script increment in rate in sql

Q. Answer the questions based on the table Flight given below: [CBSE D 05 : 2marks]
Table: flight

Column Name	Data type	Size	Constraint	Description
Flight_no	NUMBER	4	PRIMARY KEY	Flight number
Origin	VARCHAR2	30	NOT NULL	Place of origin of flight
Destination	VARCHAR2	30	NOT NULL	Destination of the flight
Seats	NUMBER	3	--	No. of seats available
Flt_date	DATE	--	--	Date of flight
Rate	NUMBER	7,2	--	Rate of a ticket on the flight

Write a code/statements to increase the Rate of all the flights by 5%.

Pl/Sql statements and examples

Q. Write Pl/sql cursor declaration to store employee code(EMPNO), employee name(ENAME) of employees form Emp table who are located (LOC) in Delhi. [CBSE D 07 : 2marks]

Cursor emp_c1 is
Select empno, ename
From Emp
Where loc = ‘Delhi’;

SIMILAR QUESTIONS FOR PRACTICE (LIKE ABOVE Q.)

Q. Write Pl/sql cursor declaration to store emp code(EMPNO), employee name(ENAME) of employees from Emp table whose designation is salesman.
Cursor emp_c1 is
Select empno, ename
From Emp
Where design = ‘salesman’;
Q. Write Pl/sql cursor declaration to store student id(STU_ID), student name(STU_NAME), student class(STU_CL) of student from STUDENT table who score marks less than 40%.
TRY IT

Q. . Write a code/statements to declare variable grade Amt having datatype similar to tha of balance field of TAB1 table ?
Ans. Amt TAB1.balance% TYPE ;
Q. Write a code/statements to read two numbers and display their sum ?
Declare
Num1 Number(2);
Num2 Number(2);
Num3 Number(2);


Begin
Num1:=& Number1 ;
Num2 :=& Number2 ;
Num3:= Num1+Num2 ;
Dbms_output.put_line(‘The sum is ‘num3);
End;
Q. Write a code/statements to read two numbers and display their product ?
Declare
Num1 Number(2);
Num2 Number(2);
Num3 Number(2);
Begin
Num1:=& Number1 ;
Num2 :=& Number2 ;
Num3:= Num1*Num2 ;
Dbms_output.put_line(‘The product is ‘num3);
End;
Q. Write a code/statements to read two numbers and display their product ?
Declare
Num1 Number(2);
Num2 Number(2);
Num3 Number(2);
Sq1 Number(2);
Begin
Num1:=& Number1 ;
Num2 :=& Number2 ;
Sq1:= Num1*Num1 ;
Dbms_output.Enable ;
If num1>num2
Then
Dbms_output.Put_line(‘Square of bigger no. is’sq1);
Else
Dbms_output.Put_line(‘First no. is less than second no.’);
End If;
End;

Important Q /Ans for class XII of Information Practices

Q. Write a code/statements to declare variables rollno., name, class and marks. Decide their datatype on your own?
Ans. Declare
Rollno. Number(2);
Name Varchar2(30);
Class Number(2);
Marks Number(4,2);
Begin
..
End;