List the employee name, job, salary, grade and deptname for everyone in thecompany except clerks. Sort on salary, display the highest salaryfirst.
SELECT e.emp_name,e.job_name, e.salary,s.grade,d.dep_name FROM employees e,department d,salary_grade s
WHERE e.dep_id = d.dep_id
AND e.salary BETWEEN s.min_sal AND s.max_sal AND e.job_name NOT IN('CLERK')
ORDER BY e.salary DESC;

select emp.ename,emp.job,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno and job !='CLERK' order by sal desc;



Display all the employee details whose name consists two‘A’s.
SELECT * FROM employee WHERE emp_name LIKE '%A%A%';

Write a PL/SQL Program for Palindrome of a givennumber.

declare

n number; mnumber;
rev number:=0; rnumber;
begin n:=12321;
m:=n; while n>0 loop
r:=mod(n,10); rev:=(rev*10)+r; n:=trunc(n/10);
end loop;


if m=rev then
dbms_output.put_line('number is palindrome');

else

dbms_output.put_line('number is not palindrome'); end if;
end;

/


Write syntax DCL commands and example to rename a column of anytable.

Grant: grant insert,select on accounts to ram Revoke: revoke insert,select on accounts to ram
ALTER TABLE "table_name"
Change "column 1" "column 2" ["Data Type"];


Show the details of all employees hired on December 03, 98. SQL> SELECT * FROM EMP WHERE HIREDATE =’03-DEC-81’;

Find the names of sailors who have reserved a red boat, and list in the order of age.Write a PL/SQL.
PDF
Write a PL/SQL Program for factorial of a givennumber.
declare
n number(2); i number(2);
f number(5):=1; begin
n :=&n;
for i in 1..n loop f := f * i;
end loop;
dbms_output.put_line(' the factorial value = '||f); end;

Display employee number, name, salary prefix with the ‘$’ symbol and in descendingorder.
SQL> SELECT EMPNO, ENAME, ‘$’ || SALARY SALARY1 FROM EMP ORDER BY SALARY DESC;

List the no. of clerks, no. of managers department wise if both no. of clerks and no. of managers are>2.
select emp.ename,emp.job,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno and job !='CLERK' order by sal desc;

Write a PL/SQL Program for displaying the salary of a given employee number if not give a error message usingcursors.
declare

salary emp.sal%TYPE;

cursor c1 is select sal from emp where empno=7844; begin
open c1;

fetch c1 into salary; if c1%FOUND then
dbms_output.put_line(salary||' '); end if;
close c1; end;
/

OUTPUT:

@d:\cursor1.sql; 1500
PL/SQL procedure successfully completed.



Give necessary examples to differentiate DDL andDML. Ddl: create: create tabletablename(
Alter : ALTER TABLE table_name ADD column_name datatype; Drop : drop table table_name;
Truncate truncate table tablename;


Dml:

insert

Update: UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

Ex:
UPDATE students
SET FirstName = 'Jhon', LastName= 'Wick' WHERE StudID = 3;
Delete: DELETE FROM table_name [WHERE condition]; EX: DELETE FROM students
WHERE FirstName = 'Jhon';






Show the average salary for all departments employing more than 3 people.
SELECT AVG( sal ) FROM emp GROUP BY deptno HAVING COUNT(job)>3;

Display all the employee details who are completed their 30 years of service. SQL> SELECT * FROM EMP WHERE ABS(SYSDATE - HIREDATE)/365 >=30;


Write a PL/SQL Program for reverse of a givennumber.
declare
n number; n1 number; rev number; begin
n := &n;
n1 := n; rev := 0; while n>0 loop
rev := rev*10 + mod(n, 10); n := floor(n/10);
end loop;
dbms_output.put_line('reverse of ' || n1 || ' is: ' || rev); end;
Write syntax DCL commands and example to rename a column of anytable.

Grant: grant insert,select on accounts to ram Revoke: revoke insert,select on accounts to ram
ALTER TABLE "table_name"
Change "column 1" "column 2" ["Data Type"];




Display all the employee details whose age is between 50 to 60years.

Select ename from emp where age>50 and age<60;

select * from emp where age between '50' AND '60';


Write a PL/SQL Program for Implicitcursor.

DECLARE
total_rows number(2);
BEGIN
UPDATEcustomers
SET salary = salary + 5000;
IF sql%notfound THEN
dbms_output.put_line('no customers updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers updated ');
END IF;
END;
/

SQL> DECLARE
total_rows number(2); BEGIN
UPDATE emp SET salary = salary + 500;
IF SQL%NOTFOUND THEN
dbms_output.put_line('No employee salary updated'); ELSIF SQL%FOUND then
total_rows:=sql%rowcount;
dbms_output.put_line(total_rows||' of employee salaries updated'); END IF;
END;
/



Write a PL/SQL Program for armstrong of a givennumber.

declare

n number:=1634; s number:=0;
r number; len number; m number;



begin

m := n;


len := length(to_char(n));



while n>0 loop

r := mod(n , 10);
s := s + power(r , len); n := trunc(n / 10);
end loop;



if m = s then

else end if;



dbms_output.put_line('yes'); dbms_output.put_line('no');

end;


Give syntax and examples for insert command (3 ways should bethere).

INSERT INTO table_name VALUES (value1, value2, value3, ...); Eg.INSERT INTO Person
VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’);

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...);
Eg.INSERT INTO Person(Id, Name, DateOfBirth, Gender) VALUES (1, ‘John Lennon’, ‘1940-10- 09’, ‘M’);

INSERT INTO table_name values (&column1, &column2, &column3,...);


Eg.INSERT INTO Person values(&Id, '&Name', '&DateOfBirth', '&Gender') . Enter the value of I'd:1
Enter the value of name:x

Enter the value of DateOfBirth':11-11-11 Enter the value of gender:f

In which year most employees were joined in the company. Display the year and no. of employees.
Display the average, monthly salary bill for each job type within adept.
SQL> select job,deptno,avg(sal) from emp group by job,deptno;


Write a PL/SQL Program for reverse of a givennumber.
declare
n number; n1 number; rev number; begin
n := &n;
n1 := n; rev := 0; while n>0 loop
rev := rev*10 + mod(n, 10); n := floor(n/10);
end loop;
dbms_output.put_line('reverse of ' || n1 || ' is: ' || rev); end;


Write a PL/SQL Program for Implicitcursor.

DECLARE
total_rows number(2);
BEGIN
UPDATEcustomers
SET salary = salary + 5000;
IF sql%notfound THEN
dbms_output.put_line('no customers updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers updated ');
END IF;

END;
/


Show the average salary for all departments employing more than 3people.
SELECT AVG( sal ) FROM emp GROUP BY deptno HAVING COUNT(job)>3;
Construct a query, which finds the job with the highest averagesalary.SELECT MAX(AVG(salary)) FROM employee GROUP BY emp_id3.Write a PL/SQL Program for sum of a givennumbers.
declare


x number(5); y number(5); z number(7);

begin x:=10;
y:=20;

z:=x+y;


dbms_output.put_line('Sum is '||z);


end;

/
4.Give syntax and examples for insert command (3 ways should be there).

INSERT INTO table_name VALUES (value1, value2, value3, ...); Eg.INSERT INTO Person
VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’);

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...);
Eg.INSERT INTO Person(Id, Name, DateOfBirth, Gender) VALUES (1, ‘John Lennon’, ‘1940-10- 09’, ‘M’);
INSERT INTO table_name values (&column1, &column2, &column3,...);

Eg.INSERT INTO Person values(&Id, '&Name', '&DateOfBirth', '&Gender') . Enter the value of I'd:1
Enter the value of name:x

Enter the value of DateOfBirth':11-11-11 Enter the value of gender:f





Change the default display like this. For deptno 0 show financial department, for 20account department, for 30 management information system, 40 electronic dataprocessing.

SELECT DECODE(dept no,10,’Financial Department’,20,’Account department’,30,’Management information system’,40,’ electronic data processing’)FROM dept;
SQL>selectdeptno,decode(to_char(deptno),'10','financialmanagement','20','accountdepartment','30',' managementinformationsystem','40','electronicdataprocessing') from dept;
DEPTNO DECODE(TO_CHAR(DEPTNO),'10'



10 financialmanagement

20 accountdepartment

30 managementinformationsystem

40 electronicdataprocessing


Display the employee’s name, dept name, salary and hire date for deptno=20. Specify the alias ‘date-hired’ for HireDate.
SQL> select emp.ename,dept.dname,emp.sal,emp.hiredate as date_hired from emp,deptwhere emp.deptno=dept.deptno andemp.deptno=20;
ENAME	DNAME	SALDATE_HIRE



SMITH
RESEARCH
800 17-DEC-80

JONES
RESEARCH
2975 02-APR-81

SCOTT
RESEARCH
3000 09-DEC-82

ADAMS
RESEARCH
1100 12-JAN-83


FORD	RESEARCH	3000 03-DEC-81


Write a PL/SQL Program for displaying the salary of a given employee number if not give a errormessage.
declare

salary emp.sal%TYPE;

cursor c1 is select sal from emp where empno=7844; begin
open c1;

fetch c1 into salary; if c1%FOUND then
dbms_output.put_line(salary||' '); end if;
close c1; end;
/

OUTPUT:

@d:\cursor1.sql; 1500
PL/SQL procedure successfully completed.


List the dept details which doesn’t have any employees init.
Select dname from emp where ename=null;
SELECT DEPTNO, DNAME FROM DEPT WHERE DEPTNO NOT IN (SELECT DISTINCT DEPTNO FROM EMP);








List the employee name, job, salary, grade and deptname for everyone in the company except clerks. Sort on salary, display the highest salaryfirst.

SELECT e.emp_name,e.job_name, e.salary,s.grade,d.dep_name FROM employees e,department d,salary_grade s
WHERE e.dep_id = d.dep_id
AND e.salary BETWEEN s.min_sal AND s.max_sal AND e.job_name NOT IN('CLERK')
ORDER BY e.salary DESC;

List the dept details which doesn’t have any employees init.
Select dname from emp where enmae=null; 3.Write a PL/SQL program for Explicit cursors. declare
cursor a is select * from emp; b a%rowtype;
begin open a; loop
fetch a into b;

exit when a%notfound;

dbms_output.put_line(b.empno||' '||b.ename||' '||b.job||' '||b.mgr||' ' ||b.hiredate||' '||b.deptno ||' '); end loop;
close a; end; output:
SQL> @d:\explicit.sql 13 /
7369 SMITH CLERK 7902 17-DEC-80 20

7499 ALLEN SALESMAN 7698 20-FEB-81 30

7521 WARD SALESMAN 7698 22-FEB-8130

7566 JONES MANAGER 7839 02-APR-8120

7654 MARTIN SALESMAN 7698 28-SEP-81 30

7698 BLAKE MANAGER 7839 01-MAY-81 30

7782 CLARK MANAGER 7839 09-JUN-81 10

7788 SCOTT ANALYST 7566 09-DEC-82 20

7839 KING PRESIDENT 17-NOV-81 10

7844 TURNER SALESMAN 7698 08-SEP-81 30

7876 ADAMS CLERK 7788 12-JAN-8320

7900 JAMES CLERK 7698 03-DEC-8130

7902 FORD ANALYST 7566 03-DEC-81 20

7934 MILLER CLERK 7782 23-JAN-8210

PL/SQL procedure successfullycompleted.


4.Give syntax and examples for insert command (3 ways should be there).

INSERT INTO table_name VALUES (value1, value2, value3, ...); Eg.INSERT INTO Person
VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’);

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...);
Eg.INSERT INTO Person(Id, Name, DateOfBirth, Gender) VALUES (1, ‘John Lennon’, ‘1940-10- 09’, ‘M’);
INSERT INTO table_name values (&column1, &column2, &column3,...);


Eg.INSERT INTO Person values(&Id, '&Name', '&DateOfBirth', '&Gender') . Enter the value of I'd:1
Enter the value of name:x

Enter the value of DateOfBirth':11-11-11 Enter the value of gender:f





List the lowest paid employees working for each manager. Exclude any group wherethe minimum salary is less than 1000. Sort the output bysalary.
SQL> select * from (select mgr,empno,sal from emp ) where empno !=mgr and sal>1000 order by sal asc;
MGR	EMPNO	SAL



7788
7876
1100

7698
7521
1250

7698
7654
1250

7782
7934
1300

7698
7844
1500

7698
7499
1600

7839
7782
2450

7839
7698
2850

7839
7566
2975

7566
7902
3000

7566
7788
3000


11 rows selected.


List the details of those employees whose salaries greater than any salary of their department.
SELECT A.* FROM EMP A, (SELECT DEPTNO, MIN(SALARY) SALARY1 FROM EMP GROUP BY DEPTNO) B WHERE A.DEPTNO = B.DEPTNO AND A.SALARY > B.SALARY1;

Write a PL/SQL program to display employee details whose salary is greater than average salary of allemployees.
declare
cursor cur1 is select empno, upper(ename) ename, upper(job) job, deptno, salary from emp where salary >= (select avg(salary) from emp) order by salary;
curval cur1%ROWTYPE; avg_salaryemp.salary%TYPE; begin
selectavg(salary) into avg_salary from emp; dbms_output.put_line('AVERAGE SALARY OF ALL EMPLOYEES INEMP TABLE IS: Rs. ' ||avg_salary);
dbms_output.put_line('	'); dbms_output.put_line('LIST OF EMPLOYEES WHOSE SALARYGREATER THAN AVERAGE SALARY OFCOMPANY');
dbms_output.put_line('	'); open cur1;
fetch cur1 into curval; while cur1%FOUND loop
dbms_output.put_line(curval.empno || ' ' || curval.ename || ' ' || curval.job || ' ' || curval.deptno || ' ' || curval.salary);
fetch cur1 into curval; end loop;
close cur1; end;

OUTPUT:

AVERAGE SALARY OF ALL EMPLOYEES IN EMP TABLE IS: Rs. 19875

LIST OF EMPLOYEES WHOSE SALARY GREATER THAN AVERAGE SALARY OF COMPANY

1003 SREENIVAS CLERK 10 20000
1008 THIRUPATHI MANAGER 20 40000
2007 SAIRAM MANAGER 10 50000

PL/SQL procedure successfully completed.



declare
cursor b is select *from emp where sal >(select avg(sal) from emp); a b%rowtype;
begin open b; loop
fetch b into a;
exit when b%notfound;
dbms_output.put_line(a.empno||' '||a.ename||' '||a.job||' '||a.mgr||' ' ||a.hiredate||' '||a.deptno ||' '); end loop;
close b; end; output:
SQL> @d:\cursor2.sql 13 /
7566 JONES MANAGER 7839 02-APR-81 20
7698 BLAKE MANAGER 7839 01-MAY-81 30
7782 CLARK MANAGER 7839 09-JUN-81 10
7788 SCOTT ANALYST 7566 09-DEC-82 20
7839 KING PRESIDENT 17-NOV-81 10
7902 FORD ANALYST 7566 03-DEC-81 20
PL/SQL procedure successfully completed

Write a PL/SQL program to represent Armstrong numberdeclare
n number:=407; s number:=0;
r number; len number; m number;
begin
m:=n;

len:=length(to_char(n));

while n>0 loop
r:=mod(n,10); s:=s+power(r,len); n:=trunc(n/10);
end loop;

if m=s
then
dbms_output.put_line('armstrong number');
else
dbms_output.put_line('not armstrong number');
end if;

end;
/





Display the information of employees who earn more than their employees indeptno=30.

SELECT A.* FROM EMP A, (SELECT DEPTNO, MIN(SALARY) SALARY1 FROM EMP WHERE DEPTNO = 30 GROUP BY DEPTNO) B WHERE A.DEPTNO = B.DEPTNO AND A.SALARY > B.SALARY1;


Find the employees who earn a salary greater than the average salary in theirdept.
SELECT A.* FROM EMP A, (SELECT DEPTNO, AVG(SALARY) AVG_SALARY FROM EMP GROUP BY DEPTNO) B WHERE A.DEPTNO = B.DEPTNO AND A.SALARY > B.AVG_SALARY;

Write a PL/SQL Program for displaying the salary of a given employee number if not give a errormessage.
declare

salary emp.sal%TYPE;

cursor c1 is select sal from emp where empno=7844; begin
open c1;

fetch c1 into salary; if c1%FOUND then
dbms_output.put_line(salary||' ');

end if;

close c1; end;
/

OUTPUT:

@d:\cursor1.sql; 1500
PL/SQL procedure successfully completed.


Write syntax DCL commands and example to rename a column of anytable.
Grant: grant insert,select on accounts to ram Revoke: revoke insert,select on accounts to ram ALTER TABLE "table_name"
Change "column 1" "column 2" ["Data Type"];




Display the details of employees whose manager has earning highest salary amongall managers.
SELECT * FROM EMP WHERE MANAGER IN (SELECT EMPNO FROM EMP WHERE SALARY IN (SELECT MAX(SALARY) FROM EMP WHERE UPPER(JOB) = 'MANAGER'));


Find the names of sailors who have reserved allboats.
SELECT Sailors.name FROM Sailors INNER JOIN (SELECT Reserves.sid FROM Reserves
GROUP BY Reserves.sid

HAVING COUNT(DISTINCT CONCAT(Reserves.sid, Reserves.bid)) = (SELECT COUNT(DISTINCT Boats.bid)
FROM Boats)) sub ON Sailors.sid =sub.sid

Write a PL/SQL program to reverse a givenstring.
declare

str1 varchar2(50):='&str'; str2 varchar2(50);
len number; i number;

begin len:=length(str1); for i in reverse 1..len loop
str2:=str2 || substr(str1,i,1); end loop;
dbms_output.put_line('Reverse of String is:'||str2); end;
/


Give syntax and example each for table level integrityconstraints.

Table constraints include:

PRIMARY KEY

Specifies the column or columns that uniquely identify a row in the table. NULL values are not allowed.


UNIQUE

Specifies that values in the columns must be unique.


FOREIGN KEY

Specifies that the values in the columns must correspond to values in referenced primary key or unique columns or that they are NULL.


Note: If the foreign key consists of multiple columns, and any column is NULL, the whole key is considered NULL. The insert is permitted no matter what is on the non-null columns.
CHECK

Specifies a wide range of rules for values in the table.

Eg.CREATE TABLE classnew


(


stu_id varchar(20), Std_pno varchar2(20), class_id varchar2(20), Std_city varchar2(20),
Check std_city in('wrl','hnk'),


PRIMARY KEY (stu_id, class_id), Unique (std_pno),
FOREIGN KEY (stu_id) REFERENCES stud (stu_id)


)




Write any PL/SQL program on conditionalstatements.




Result:



WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 00 TO 100.
DECLARE

N NUMBER(5); BEGIN
FOR I IN 1 .. 100 LOOP
IF MOD(I,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(I||''); END IF;
END LOOP; END ;

Give an example to represent too_many_rowsexception.

DECLARE

temp varchar(20);


BEGIN


-- raises an exception as SELECT

-- into trying to return too many rows SELECT g_name into temp from geeks; dbms_output.put_line(temp);

EXCEPTION

WHEN too_many_rows THEN

dbms_output.put_line('error trying to SELECT too many rows');


end;


Write a PL/SQL program to reverse a givenstring

declare

str1 varchar2(50):='&str'; str2 varchar2(50);
len number; i number;


begin len:=length(str1); for i in reverse 1..len loop
str2:=str2 || substr(str1,i,1);

end loop;

dbms_output.put_line('Reverse of String is:'||str2); end;
/




Write a PL/SQL program that will accept an account number, amount to be debited from	ACCOUNT table. Ensure minimum balance of Rs. 500/- for any account
Note:
Create table ACCOUNT(account_id varchar2(5), Name varchar2(20), Balance number(5,2)).
Insert values into the table on which you want your PL/SQL code blockwill execute.
Display current values present in thetable.
Enter the account number and the amount to be debited from the account while executing your PL/SQL codeblock.
After any debit is completed, select the particular value from the table to show that proper updation of balance happened to account table.

declare acct_novarchar2(5); debit_amountnumber(6,2);
min_bal constant number(6,2) := 500.00; bal number(15,2);
money_present number(15,2); begin
acct_no := &acct_no; debit_amount := &debit_amount;
select balance into money_present from account where account_id = acct_no; if SQL%NOTFOUND then
dbms_output.put_line(acct_no || ' does not exist'); else
bal := money_present - debit_amount; ifbal>= min_bal then
update account set balance = bal where account_id = acct_no; commit;
else
dbms_output.put_line('Your account: ' || acct_no || ' has only Rs. ' || money_present || '


balance');
dbms_output.put_line('Cannot debit amount Rs. ' || debit_amount);

end if; end if; end;

OUTPUT:
SQL> select * from account;

ACCOUNT_ID
NAME
BALANCE

AC001
Snr
5000

AC002
Vinay
2000


Enter value for acct_no: 'AC002' old 8:  acct_no := &acct_no; new 8: acct_no :='AC002';
Enter value for debit_amount: 2000
old 9: debit_amount := &debit_amount; new 9: debit_amount := 2000;
Your account: AC002 has only Rs. 2000 balance Cannot debit amount Rs. 2000

PL/SQL procedure successfully completed. SQL> /
Enter value for acct_no: 'AC001' old 8:  acct_no := &acct_no; new 8: acct_no :='AC001';
Enter value for debit_amount: 2000
old 9: debit_amount := &debit_amount; new 9: debit_amount := 2000;

PL/SQL procedure successfully completed.






Give any 5 examples for Datefunctions.

Add_Months:Theadd_monthsdatafunctionreturnsadateafteraddingaspecifieddatawith the specified number of months. The format is add_months(d,n), where d is the date and n represents the number ofmonths.

Syntax:
Add_Months(Date,no.of Months)

Example:

SQL> select Add_Months(sysdate,2) from dual; This will add two months in system date.
ADD_MONTH

05-APR-19
Last_day: Returns the last date of month specified with thefunction.
Syntax:
Last_day(Date)


Example:

SQL> select sysdate, last_day(sysdate) from dual; SYSDATE LAST_DAY
———— ————- 05-FEB-19 28-FEB-19

Months_Between: Where Date1, Date2 are dates. The output will be a number. If Date1 is later than Date2, result is positive; if earlier, negative. If Date1 and Date2 are either the same days of the month or both last days of months, the result is always an integer; otherwise Oracle calculatesthefractionalportionoftheresultbasedona31-daymonthandconsidersthedifference in time components of Date1 andDate2.

Syntax:

Months_Between(Date1,Date2)
Example:

SQL>select months_between(sysdate,’02-AUG-01’) 溺 onths_ from dual;

MONTHS

4
Next_Day: Returns the date of the first weekday named by ‘char’ that is after the date named by‘Date’. ‘Day’ must be the day of theweek.
Syntax:
Next_Day(Date,Day)
Example:

SQL>select next_day(sydate, ‘sunday’) 哲 ext_ from dual;

This will return date of next sunday.
NEXT_DAY

09-SEP-00
Round: This function returns the date, which is rounded to the unit specified by the format.
Syntax:
Round (Date, [fmt])

If format is not specified by default date will be rounded to the nearest day. Example:

SQL>Select round(‘4-sep-01’,’day’) 迭 ounded_ from dual; Rounded
02-SEP-01

The date formats are ‘month’ and ‘year’.

If rounded with ‘month’ format it will round with nearest month. If rounded with ‘year’ format it will round with nearest year.
1)	Trunc (Truncate): This function returns the date, which is truncated to the unit specified by theformat.
Syntax:

Trunc(Date,[fmt])

If format is not specified by default date will be truncated. Example:
This will display first day of current week.


SQL>Select Trunc(‘4-sep-01’,’day’) 典 runcated_ from dual; Truncated
02-	SEP-01

The date formats are ‘month’ and ‘year ’.

If rounded with ‘month’ format it will display first day of the current month. If rounded with ‘year’ format it will display first day of the current year.

Create a referential integrity constraint between tables(on delete cascade)exist.


Show the difference between commit and rollback with necessaryexample








Give an example to represent cursor (open,fetch and close) withparameters

Syntax : FETCH NEXT/FIRST/LAST/PRIOR/ABSOLUTE n/RELATIVE nFROM
cursor_name

FETCH FIRST FROM S1 CLOSE s1

Give any 5 examples for characterfunctions

Initcap (Initial Capital): This String function is used to capitalize first character of the inputstring.

Syntax:

initcap(string)
Example:

SQL> select initcap(‘kits’) from dual; INITC
Kits
Lower: This String function will convert input string in to lowercase.
Syntax:
Lower(string)
Example:
SQL> select lower(‘KITS’) from dual;

LOWER

kits
Upper: This string function will convert input string in toupper case.
Syntax:

Upper(string)
Example:
SQL> select upper(‘kits’) from dual; UPPER
KITS

Ltrim (Left Trim): Ltrim function accepts two string parameters; it will fetch only those set of characters from the first string from the left side of the first string, and displays only those characters which are not present in second string. If same set of characters are not found in first string it will display wholestring

Syntax:

Ltrim(string,set)
Example:

SQL>select ltrim('kitswarangal','kits') from dual;

LTRIM('K

warangal

Rtrim (Right Trim): Rtrim function accepts two string parameters; it will fetch only those characters from the first string, which is present in set of characters in second string from the right side of the firststring.
Syntax:

Rtrim(string,set)
Example:
SQL>select rtrim('kitswarangal','kits') from dual;

RTRIM('KITSW

kitswarangal

Lpad(LeftPad):Thisfunctiontakesthreearguments.Thefirstargumentischaracterstring, whichhastobedisplayedwiththeleftpadding.Secondisanumber,whichindicatestotallength of return value and third is the string with which left padding has to be done whenrequired.

Syntax:
Lpad(String,length,pattern)
Example:

Sql > select lpad(‘Welcome’,15,’*’) from dual; LPAD(‘WELCOME’,
———————-
********Welcome
Rpad (Right Pad): Rpad does exact opposite then Lpadfunction.
Syntax:
Lpad(String,length,pattern)
Example:

SQL> select rpad(‘Welcome’,15,’*’) from dual; RPAD(‘WELCOME’,
———————-
Welcome********

create sequence for any table. CREATE SEQUENCE sequence_1 start with1
increment by 1

minvalue 0

maxvalue 100 cycle;
CREATE TABLE students (
ID number(10), NAME char(20)
);

INSERT into students VALUES(sequence_1.nextval,'Ramesh'); INSERT into students VALUES(sequence_1.nextval,'Suresh');

write a program to print even numbers from 1 to60.

DECLARE

N NUMBER(5); BEGIN
FOR I IN 1 .. 60 LOOP
IF MOD(I,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(I||''); END IF;
END LOOP; END ;



Write a PL/SQL program that will accept an account number, amount to be debited from ACCOUNT table. Ensure minimum balance of Rs. 500/- for anyaccount.
Note:
Create table ACCOUNT(account_id varchar2(5), Name varchar2(20), Balance number(5,2)).
Insert values into the table on which you want your PL/SQL code blockwill execute.
Display current values present in thetable.
Enter the account number and the amount to be debited from the account while executing your PL/SQL codeblock.
After any debit is completed, select the particular value from the tableto show that proper updation of balance happened to accounttable.


declare acct_novarchar2(5); debit_amountnumber(6,2);
min_bal constant number(6,2) := 500.00; bal number(15,2);
money_present number(15,2); begin
acct_no := &acct_no; debit_amount := &debit_amount;
select balance into money_present from account where account_id = acct_no;

if SQL%NOTFOUND then dbms_output.put_line(acct_no || ' does not exist'); else
bal := money_present - debit_amount; ifbal>= min_bal then
update account set balance = bal where account_id = acct_no; commit;
else
dbms_output.put_line('Your account: ' || acct_no || ' has only Rs. ' || money_present || '


balance');
dbms_output.put_line('Cannot debit amount Rs. ' || debit_amount); end if;
end if; end;

OUTPUT:
SQL> select * from account;

ACCOUNT_ID
NAME
BALANCE

AC001
Snr
5000

AC002
Vinay
2000


Enter value for acct_no: 'AC002' old 8:  acct_no := &acct_no; new 8: acct_no :='AC002';
Enter value for debit_amount: 2000
old 9: debit_amount := &debit_amount; new 9: debit_amount := 2000;
Your account: AC002 has only Rs. 2000 balance Cannot debit amount Rs. 2000

PL/SQL procedure successfully completed. SQL> /
Enter value for acct_no: 'AC001' old 8:  acct_no := &acct_no; new 8: acct_no :='AC001';
Enter value for debit_amount: 2000
old 9: debit_amount := &debit_amount; new 9: debit_amount := 2000;

PL/SQL procedure successfully completed.

Find the names of sailors who have reserved allboats.
pdf

Write a PL/SQL program to reverse a givenstring.
declare

str1 varchar2(50):='&str'; str2 varchar2(50);
len number; i number;


begin len:=length(str1); for i in reverse 1..len loop
str2:=str2 || substr(str1,i,1); end loop;
dbms_output.put_line('Reverse of String is:'||str2); end;
/


List the employee details whose names have exactly fivecharacters

Select * from emp where enamelike ‘	’;


Give an example to differentiate Truncate anddelete.

WriteaPL/SQLprogramtodisplaythename,deptno,jobandsalaryofthetopnhighestpaid employees from EMP table usingcursors.

Nvl(first_name, '...') manager

FROM departments d

left outer join employees e

ON ( d.manager_id = e.employee_id )

join locations l USING(location_id)

ORDER BY 2;

emp_name	employees.first_name%TYPE;

emp_max_salary employees.salary%TYPE;

BEGIN

FOR dept_all IN dpt_cur LOOP

SELECT Max(salary)

INTO emp_max_salary

FROMemployees

WHERE department_id = dept_all.id;



IF emp_max_salary IS NULL THEN

emp_name := '...';

ELSE

SELECT first_name

INTO emp_name

FROMemployees

WHERE department_id = dept_all.id

AND salary = emp_max_salary;

END IF;



dbms_output.Put_line(Rpad(dept_all.dptname, 20)

|| Rpad(dept_all.manager, 15)

|| Rpad(dept_all.city,20)

|| Rpad(emp_name,20));

END LOOP;

END;




Give an example to represent Selfjoin.
Id
FullName
Salary
ManagerId

1
John Smith
10000
3

2
Jane Anderson
12000
3

3
Tom Lanon
15000
4

4
Anne Connor
20000


5
Jeremy York
9000
1


SELECT
employee.Id, employee.FullName, employee.ManagerId,
manager.FullName as ManagerName FROM Employees employee
JOIN Employees manager
ON employee.ManagerId = manager.Id

I
d
FullNam e
ManagerI d
ManagerNam e


1
John Smith

3

Tom Lanon


2
Jane Anderson

3

Tom Lanon


3
Tom Lanon

4

Anne Connor


I
d
FullNam e
ManagerI d
ManagerNam e


5
Jeremy York

1

John Smith


create a function in which use of In-out parameter existing to a variable.\ CREATE OR REPLACE PROCEDURE emp_name (id IN NUMBER, emp_nameOUT NUMBER)IS
BEGIN
SELECT first_name INTO emp_name FROM emp_tbl WHERE empID =id; END;
/

We can call the procedure ‘emp_name’ in this way from a PL/SQL Block.

DECLARE
empName varchar(20);
CURSOR id_cur SELECT id FROM emp_ids; BEGIN
FOR emp_rec in id_cur LOOP
emp_name(emp_rec.id, empName);
dbms_output.putline('The employee ' || empName || ' has id ' ||
emp-
rec.id);
END LOOP;
END;
/







Create a single table in which primary key and more than one foreign keys(ondelete cascade)exist.
Give necessary example to differentiate table andview.

A table consists of rows and columns used to organize data to store and display records in a structured format. It is similar to worksheets in the spreadsheet application. It occupies space on our systems. We need three things to create a table:

Tablename
Columns/Fieldsname
Definitions for each field



CREATE TABLE [IF NOT EXISTS] table_name (
column_definition1,

column_definition2,
........,
table_constraints
);


Theviewisavirtual/logicaltableformedasaresultofaqueryandusedtoviewormanipulateparts of the table. We can create the columns of the view from one or more tables. Its content is based    on base tables.

The view is a database object with no values and contains rows and columns the same as real tables. It does not occupy space on our systems.

CREATE VIEW view_name AS SELECT columns
FROM tables [WHERE conditions];

Write a PL/SQL program to reverse a givennumber.
SET SERVEROUTPUT ON; DECLARE
num NUMBER; rev NUMBER;


BEGIN
num:=&num; rev:=0;
WHILE num>0 LOOP

rev:=(rev*10) + mod(num,10); num:=floor(num/10);

END LOOP;
DBMS_OUTPUT.PUT_LINE('Reverse of the number is: ' || rev); END;
/


Give an example to represent cursor (open,fetch and close) withparameters.

Syntax : FETCH NEXT/FIRST/LAST/PRIOR/ABSOLUTE n/RELATIVE n FROM
cursor_name

FETCH FIRST FROM S1 CLOSE s1


Show the difference between commit and rollback with necessaryexample.
Give syntax and examples for insert command (3 ways should bethere).
INSERT INTO table_name VALUES (value1, value2, value3, ...); Eg.INSERT INTO Person
VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’);
INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...);
Eg.INSERT INTO Person(Id, Name, DateOfBirth, Gender) VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’);
INSERT INTO table_name values (&column1, &column2, &column3,...);

Eg.INSERT INTO Person values(&Id, '&Name', '&DateOfBirth', '&Gender') . Enter the value of I'd:1
Enter the value of name:x
Enter the value of DateOfBirth':11-11-11 Enter the value of gender:f

Write a PL/SQL program to represent palindromenumber.declare
n number; mnumber;
rev number:=0; rnumber;
begin
n:=12321;
m:=n; while n>0 loop
r:=mod(n,10); rev:=(rev*10)+r;
n:=trunc(n/10);
end loop;

if m=rev
then
dbms_output.put_line('number is palindrome');
else
dbms_output.put_line('number is not palindrome');
end if; end;
/

Write a PL/SQL program to represent recordtype dynamicdeclaration.


Give syntax and example for column level and table level representation offoreign key.
Write a PL/SQL program to represent Fibonacciseries.

declare
first number:=0; second number:=1; third number;
n number:=&n; i number;

begin
dbms_output.put_line('Fibonacci series is:'); dbms_output.put_line(first); dbms_output.put_line(second);

for i in 2..n loop
third:=first+second; first:=second;  second:=third; dbms_output.put_line(third); endloop;
end;
/

Declare cursor that accepts empno and check if he is a manager then display name,job,deptno,sal of all employees working underhim.
Give any 5 examples for characterfunctions









Give syntax and example each for table level integrityconstraints.
List all analysts in department 20 whose names start with‘M’.
Select*from emp where deptno=20 and ename like ‘M%’;
Give an example to represent cursor (For loop) withoutparameters
Give an example to represent outerjoin.
Emp dname,dno Dept dno dname
Select * from table1 left outer join table2;
SQL> select * from emp left outer join dept on emp.deptno=dept.deptno; A:




List all managers whose names end with‘S’.
select * from emp where job='MANAGER' and ename like'%S';

List the employee details whose names have exactly fivecharacters.

select * from emp where length(ename)=5;


Declare cursor that accepts empno and check if he is a manager then display name,job,deptno,sal of all employees working underhim.
Write a program to print even numbers from 1 to60.
DECLARE
N NUMBER(5); BEGIN
FOR I IN 1 .. 60 LOOP
IF MOD(I,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(I||''); END IF;
END LOOP; END ; OUTPUT:



List all manager numbers without anyrepetition.
select distinct empno from emp where job='MANAGER';

List the number of employees who are working as managers. select count(empno) from emp wherejob='MANAGER';


Write a PL/SQL program that will accept an account number, amount to be debited from ACCOUNT table. Ensure minimum balance of Rs. 500/- for anyaccount.

Note:
Create table ACCOUNT(account_id varchar2(5), Name varchar2(20), Balancenumber(5,2)).
Insert values into the table on which you want your PL/SQL code blockwill execute.
Display current values present in thetable.
Enter the account number and the amount to be debited from the account while executing your PL/SQL codeblock.
After any debit is completed, select the particular value from the tableto show that proper updation of balance happened to accounttable.


declare acct_novarchar2(5); debit_amountnumber(6,2);
min_bal constant number(6,2) := 500.00; bal number(15,2);
money_present number(15,2); begin

acct_no := &acct_no; debit_amount := &debit_amount;
select balance into money_present from account where account_id = acct_no; if SQL%NOTFOUND then
dbms_output.put_line(acct_no || ' does not exist'); else
bal := money_present - debit_amount; ifbal>= min_bal then
update account set balance = bal where account_id = acct_no; commit;
else
dbms_output.put_line('Your account: ' || acct_no || ' has only Rs. ' || money_present || '


balance');
dbms_output.put_line('Cannot debit amount Rs. ' || debit_amount); end if;
end if; end;

OUTPUT:
SQL> select * from account;

ACCOUNT_ID
NAME
BALANCE

AC001
Snr
5000

AC002
Vinay
2000


Enter value for acct_no: 'AC002' old 8:  acct_no := &acct_no; new 8: acct_no :='AC002';
Enter value for debit_amount: 2000
old 9: debit_amount := &debit_amount; new 9: debit_amount := 2000;
Your account: AC002 has only Rs. 2000 balance Cannot debit amount Rs. 2000

PL/SQL procedure successfully completed. SQL> /
Enter value for acct_no: 'AC001' old 8:  acct_no := &acct_no; new 8: acct_no :='AC001';
Enter value for debit_amount: 2000
old 9: debit_amount := &debit_amount;

new 9: debit_amount := 2000;


PL/SQL procedure successfully completed.


List all analysts in department 20 whose names start with‘M’..
select * from emp where deptno=20 and job='ANALYST' and ename like'M%';



List the number of employees who do not avail commission in department0
select * from emp where comm =0 or comm is null;

List all employees who are either clerks or managers in ascending order of their names
select * from emp where job='CLERK'or job='MANAGER' order by ename asc;

Write a PL/SQL program to display the name, deptno, job and salary of the top n highest paid employees from EMP table usingcursors.
Declare cursor that accepts empno and check if he is a manager then display name,job,deptno,sal of all employees working underhim.



List the employees belonging to the department ofmiller
select * from emp where deptno=(select deptno from emp where ename='MILLER');
Write a PL/SQL program using stored procedure or function to debit an a/c and to find the balance of a particular account. Use the table BANK(ACCOUNT number(0), BNAME varchar2(5), BALANCE number(0,2)).
Write a program to represent %ISOPEN implicit cursor in anexample.
Declare a cursor that display the payslips ofemployees



List all the employees having salary equal to that of scott orford
select * from emp where sal in(select sal from emp where ename='SCOTT' or ename='FORD');

Count the number of salesman in salesdepartment
SQL> select count(*) from emp where job='SALESMAN' and deptno=(select deptno from dept where dname='SALES');
COUNT(*)


Write a program to represent %ISOPEN implicit cursor in anexample.
Write a program to represent explicit cursor with anexample.

declare

cursor a is select * from emp; b a%rowtype;

begin open a; loop
fetch a into b;

exit when a%notfound;

dbms_output.put_line(b.empno||' '||b.ename||' '||b.job||' '||b.mgr||' ' ||b.hiredate||' '||b.deptno ||' '); end loop;
close a; end; output:
SQL> @d:\explicit.sql 13 /
7369 SMITH CLERK 7902 17-DEC-80 20

7499 ALLEN SALESMAN 7698 20-FEB-81 30

7521 WARD SALESMAN 7698 22-FEB-81 30

7566 JONES MANAGER 7839 02-APR-81 20

7654 MARTIN SALESMAN 7698 28-SEP-81 30

7698 BLAKE MANAGER 7839 01-MAY-81 30

7782 CLARK MANAGER 7839 09-JUN-81 10

7788 SCOTT ANALYST 7566 09-DEC-82 20

7839 KING PRESIDENT 17-NOV-81 10

7844 TURNER SALESMAN 7698 08-SEP-81 30

7876 ADAMS CLERK 7788 12-JAN-83 20

7900 JAMES CLERK 7698 03-DEC-81 30

7902 FORD ANALYST 7566 03-DEC-81 20

7934 MILLER CLERK 7782 23-JAN-82 10

PL/SQL procedure successfully completed.





Give any 5 examples for Datefunctions

Add_Months:Theadd_monthsdatafunctionreturnsadateafteraddingaspecifieddatawiththe specified number of months. The format is add_months(d,n), where d is the date and n represents the number ofmonths.

Syntax:
Add_Months(Date,no.of Months)
Example:

SQL> select Add_Months(sysdate,2) from dual; This will add two months in system date.
ADD_MONTH

05-APR-19
Last_day: Returns the last date of month specified with thefunction.
Syntax:
Last_day(Date)


Example:

SQL> select sysdate, last_day(sysdate) from dual; SYSDATE LAST_DAY
———— ————- 05-FEB-19 28-FEB-19

Months_Between: Where Date1, Date2 are dates. The output will be a number. If Date1 is later than Date2, result is positive; if earlier, negative. If Date1 and Date2 are either the same days of the month or both last days of months, the result is always an integer; otherwise Oracle calculatesthefractionalportionoftheresultbasedona31-daymonthandconsidersthedifference in time components of Date1 andDate2.

Syntax:
Months_Between(Date1,Date2)
Example:

SQL>select months_between(sysdate,’02-AUG-01’) 溺 onths_ from dual;

MONTHS

4
Next_Day: Returns the date of the first weekday named by ‘char’ that is after thedate

named by‘Date’. ‘Day’ must be the day of the week.
Syntax:
Next_Day(Date,Day)
Example:

SQL>select next_day(sydate, ‘sunday’) 哲 ext_ from dual; This will return date of next sunday.
NEXT_DAY

09-SEP-00
Round: This function returns the date, which is rounded to the unit specified by the format.
Syntax:
Round (Date, [fmt])

If format is not specified by default date will be rounded to the nearest day. Example:

SQL>Select round(‘4-sep-01’,’day’) 迭 ounded_ from dual; Rounded
02-SEP-01

The date formats are ‘month’ and ‘year’.

If rounded with ‘month’ format it will round with nearest month. If rounded with ‘year’ format it will round with nearest year.
1)	Trunc (Truncate): This function returns the date, which is truncated to the unit specified by theformat.
Syntax:

Trunc(Date,[fmt])

If format is not specified by default date will be truncated. Example:
This will display first day of current week.


SQL>Select Trunc(‘4-sep-01’,’day’) 典 runcated_ from dual; Truncated
02-	SEP-01

The date formats are ‘month’ and ‘year ’.

If rounded with ‘month’ format it will display first day of the current month. If rounded with ‘year’ format it will display first day of the current year.



Write a PL/SQL program to reverse a givennumber.

DECLARE
N number ;
S NUMBER : = 0; R NUMBER;
K number; begin
N := &N;
K := N; LOOP
EXIT WHEN N = 0 ; S := S * 10;
R := MOD(N,10); S := S + R;
N := TRUNC(N/10);
end loop;
dbms_output.put_line( ' THE REVERSED DIGITS ' || ' OF ' || K || ' = ' || S); end;
Write a PL/SQL program to represent %Record type dynamicdeclaration
DECLARE
TYPE emp_det IS RECORD (
EMP_NO NUMBER,

EMP_NAME VARCHAR2(150),

MANAGER NUMBER, SALARY NUMBER
);
guru99_emp_rec emp_det; BEGIN
guru99_emp_rec.emp_no:= 1001; guru99_emp_rec.emp_name:=:'*****'; guru99_emp_rec.manager:= 1000;
guru99_emp_rec.salary:=10000; dbms_output.put.line('Employee Detail');
dbms_output.put_line ('Employee Number: '||guru99_emp_rec.emp_no); dbms_output.put_line ('Employee Name: '||guru99_emp_rec.emp_name); dbms_output.put_line ('Employee Salary: ' ||guru99_emp_rec.salary);
dbms_output.put_line ('Employee Manager Number: '||guru99_emp_rec.manager); END;

/




Declare cursor that accepts losal and hisal and display all employees in thatrange







Give syntax and example for view.CREATE VIEW view_name AS SELECT column1, column2, ...FROMtable_name
WHERE condition; EX:
CREATE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName FROM Customers
WHERE Country = 'Brazil';

Sequence
CREATE SEQUENCE sequence_name START WITH intial_value INCREMENT BY increment_value MINVALUE minimun value MAXVALUE maxvalue CYCLE|NOCYCYLE;
EX:
CREATE SEQUENCE sequence_1 Start with1
Increment by1 Minvalue0
Maxvalue 100 Cycle;




Give syntax and example forsequence.
Declare cursor that accepts losal and hisal and display all employees in thatrange.
Write a PL/SQL program to display all employee details belonging to a particular department. The department number has to be entered throughkeyboard.

declare
deptnov emp.deptno%type:=&deptnov;

cursor b is select *from emp where deptno=deptnov; a b%rowtype;
begin open b; loop
fetch b into a;
exit when b%notfound;
dbms_output.put_line(a.empno||' '||a.ename||' '||a.job||' '||a.mgr||' ' ||a.hiredate||' '||a.deptno ||' '); end loop;
close b;
end;

SQL> @d:\cursor2.sql 14 /
Enter value for deptnov: 10

old 2: deptnov emp.deptno%type:=&deptnov; new 2: deptnov emp.deptno%type:=10;
7782 CLARK MANAGER 7839 09-JUN-81 10

7839 KING PRESIDENT 17-NOV-81 10

7934 MILLER CLERK 7782 23-JAN-82 10

PL/SQL procedure successfully completed



Give syntax and example for user defined datatype
Give an example to use single sequence in 3tables
Write a PL/SQL program to reverse a givennumber(DONE)
DECLARE
N number;
S NUMBER : =0;
RNUMBER;
K number;
begin
9.	N :=&N;
10.	K :=N;
LOOP
EXIT WHEN N = 0 ; 13.	S := S *10;
14.		R := MOD(N,10); S := S +R;
N := TRUNC(N/10);
end loop;
dbms_output.put_line( ' THE REVERSED DIGITS ' || ' OF ' || K || ' = ' || S); end;


15. Declare a cursor that display the payslips of employees.




Give examples to represent equi join and non-equijoin
Write a PL/SQL program to reverse a givennumber.
DECLARE
N number ;
S NUMBER : = 0; R NUMBER;
K number; begin
N := &N;
K := N; LOOP
EXIT WHEN N = 0 ; S := S * 10;
R := MOD(N,10); S := S + R;
N := TRUNC(N/10);
end loop;
dbms_output.put_line( ' THE REVERSED DIGITS ' || ' OF ' || K || ' = ' || S); end;
Write a PL/SQL program to represent %Record type dynamicdeclaration

DECLARE

TYPE emp_det IS RECORD (
EMP_NO NUMBER, EMP_NAME VARCHAR2(150), MANAGER NUMBER, SALARY NUMBER
);

guru99_emp_rec emp_det; BEGIN
guru99_emp_rec.emp_no:= 1001; guru99_emp_rec.emp_name:=:'*****';

guru99_emp_rec.manager:= 1000; guru99_emp_rec.salary:=10000; dbms_output.put.line('Employee Detail');
dbms_output.put_line ('Employee Number: '||guru99_emp_rec.emp_no); dbms_output.put_line ('Employee Name: '||guru99_emp_rec.emp_name); dbms_output.put_line ('Employee Salary: ' ||guru99_emp_rec.salary); dbms_output.put_line ('Employee Manager Number: '||guru99_emp_rec.manager); END;
/


Declare cursor that accepts empno and check if he is a manager then display name,job,deptno,sal of all employees working underhim.


Write a PL/SQL program to display the name, deptno, job and salary of the top n highest paid employees from EMP table usingcursors.
Write a PL/SQL program to display all employee details belonging to a particular department. The department number has to be entered throughkeyboard.

declare
deptnov emp.deptno%type:=&deptnov;
cursor b is select *from emp where deptno=deptnov; a b%rowtype;
begin open b; loop
fetch b into a;
exit when b%notfound;
dbms_output.put_line(a.empno||' '||a.ename||' '||a.job||' '||a.mgr||' ' ||a.hiredate||' '||a.deptno ||' '); end loop;
close b;
end;

SQL> @d:\cursor2.sql 14 /
Enter value for deptnov: 10

old 2: deptnov emp.deptno%type:=&deptnov; new 2: deptnov emp.deptno%type:=10;
7782 CLARK MANAGER 7839 09-JUN-81 10

7839 KING PRESIDENT 17-NOV-81 10

7934 MILLER CLERK 7782 23-JAN-8210

PL/SQL procedure successfullycompleted.


List the integrity constraints with anexample.
Give an example to use single sequence in 3tables





Write a PL/SQL program to reverse a givennumber.

DECLARE
N number ;
S NUMBER : = 0; R NUMBER;
K number; begin
N := &N;
K := N; LOOP
EXIT WHEN N = 0 ; S := S * 10;
R := MOD(N,10); S := S + R;
N := TRUNC(N/10);
end loop;
dbms_output.put_line( ' THE REVERSED DIGITS ' || ' OF ' || K || ' = ' || S); end;
List the emps who joined on -MAY-8,3-DEC-8,7-DEC-8,9-JAN-80 in asc order of seniority.

Write a Pl/Sql program to generate multiplicationtable.


declare n number; i number;

begin n:=&n;
for i in 1..10 loop
dbms_output.put_line(n||' x '||i||' = '||n*i);

end loop; end;
/



Give examples for numeric functions and single row sub queries and multiple row sub queries.
a.Numeric:

SELECT ROUND (1372.472,1)

FROM dual;


ROUND(1372.472,1)



1372.5


SELECT TRUNC (72183,-2)

FROM dual;


TRUNC(72183,-2)



72100

b. Single row subqueries:

SELECT first_name, salary, department_id FROM employees
WHERE salary = (SELECT MIN (salary)

FROM employees); c.multiple row sub queries.
SELECT	first_name, department_id FROMemployees
WHERE department_id IN (SELECT department_id

FROM departments

WHERE LOCATION_ID = 100)




Write a PL/SQL function to count the number of employees present in each department. Show the three ways of executing a PL/SQLfunction.

DECLARE

TYPE emp_det IS RECORD (
EMP_NO NUMBER, EMP_NAME VARCHAR2(150), MANAGER NUMBER, SALARY NUMBER
);

guru99_emp_rec emp_det; BEGIN
guru99_emp_rec.emp_no:= 1001; guru99_emp_rec.emp_name:=:'*****'; guru99_emp_rec.manager:= 1000; guru99_emp_rec.salary:=10000; dbms_output.put.line('Employee Detail');
dbms_output.put_line ('Employee Number: '||guru99_emp_rec.emp_no); dbms_output.put_line ('Employee Name: '||guru99_emp_rec.emp_name); dbms_output.put_line ('Employee Salary: ' ||guru99_emp_rec.salary); dbms_output.put_line ('Employee Manager Number: '||guru99_emp_rec.manager); END;
/

Write a PL/SQL program using stored procedure or function to debit an a/c and to find the balance of a particular account. Use the table BANK(ACCOUNT number(0), AHNAME varchar2(5), BALANCE number(0,2)).
List the emps those who joined in80’s.
select * from emp where hiredate between '01-jan-1980' and '31-dec-1980';

List the emps whose Empno not starting withdigit78.
select * from emp where empno not in (select empno from emp where empno like '78%');




Write a Query to display the details of emps whose Sal is same as offord
select * from emp where sal =(select sal from emp where ename='FORD');

find the highest paid employee of salesdepartment.
> select * from emp where sal in(select max(sal) from emp where deptno=(select deptno from dept where dname='SALES'));

Declare cursor that accepts empno and check if he is a manager then display name,job,deptno,sal of all employees working underhim.
Write a PL/SQL program to represent %Record type dynamicdeclaration

DECLARE

TYPE emp_det IS RECORD (
EMP_NO NUMBER, EMP_NAME VARCHAR2(150), MANAGER NUMBER, SALARY NUMBER
);

guru99_emp_rec emp_det; BEGIN
guru99_emp_rec.emp_no:= 1001; guru99_emp_rec.emp_name:=:'*****'; guru99_emp_rec.manager:= 1000; guru99_emp_rec.salary:=10000; dbms_output.put.line('Employee Detail');

dbms_output.put_line ('Employee Number: '||guru99_emp_rec.emp_no); dbms_output.put_line ('Employee Name: '||guru99_emp_rec.emp_name); dbms_output.put_line ('Employee Salary: ' ||guru99_emp_rec.salary); dbms_output.put_line ('Employee Manager Number: '||guru99_emp_rec.manager); END;
/







List the employees who joined in 98 with the job same as the most senior person of the year98.
Find the total annual sal to distribute job wise in the year8.
Write a PL/SQL program using stored procedure or function to debit an a/c and to find the balance of a particular account. Use the table BANK(ACCOUNT number(0), AHNAME varchar2(5), BALANCEnumber(0,2)).
Write a PL/SQL program to represent %type dynamic declaration.




Write a PL/SQL program to represent %type dynamicdeclaration
Write a PL/SQL program to represent %Row type dynamic declaration.
Display the Grade, Number of emps, and max sal of eachgrade.

Display dname, grade, No. of emps where at least two emps areclerks.