Date :- 17/2/24
Lab Exercise-3
Initial Tables
Employee Table:
Department Table:
20) Displaying Employee names having Salary greater than 25000:-
SELECT firstName,lastName from Employee where salary > 25000;
21) Displaying Employee Names having salary in between 30000
and 70000:-
SELECT firstName,lastName from Employee where salary > 30000 and salary < 70000;
22)Displaying Employee names have No Supervisors:-
SELECT firstName,lastName from Employee where SupervisorSSN is null;
23)Displaying the DOB of all Employees in “DDthMonthYYYY”:-
SELECT CONCAT(DATE_FORMAT(Birthday, '%d'), 'th', DATE_FORMAT(Birthday, '%M
%Y')) AS DOB_formatted FROM Employee;
24)Displaying Employee names whose bdate is on or before 1978:-
SELECT firstName,lastName from Employee where Birthday <='1978-12-31';
25)Displaying Employee names having “salt lake” in there address:-
SELECT firstName,lastName from Employee where Address = "salt lake";
26)Displaying Department names that starts with letter “M”:-
SELECT DNAME from Department where DNAME like 'M%';
27) DIaplaying Department names that ends with “E”:-
SELECT DNAME from Department where DNAME like '%e';
28)Displaying names of employees having SSN as 554433221 and
333445555:-
SELECT firstName, lastName FROM Employee WHERE SSN = 554433221 OR SSN =
333445555;
Date :- 2/3/24
29) Displaying all the department names in upper case and lower
case:
Displaying department names in upper case:
SELECT UPPER(DNAME) AS DepartmentNameUpper FROM Department;
Displaying department names in lower case :
SELECT LOWER(DNAME) AS DepartmentNameLower FROM Department;
30) Displaying the first four characters and last four of the
department names using substring function:
SELECT SUBSTRING(DNAME, 1, 4) AS FirstFourCharacters, SUBSTRING(DNAME,
LENGTH(DNAME) - 3) AS LastFourCharacters FROM Department;
31) Displaying the substring of the Address (starting from 5th
position to 11 th position) of all employees:
SELECT SUBSTRING(Address, 5, 7) AS SubstringAddress FROM Employee;
32) Displaying the Mgrstartdate on adding three months to it:
SELECT DATE_ADD(MgrStartDate, INTERVAL 3 MONTH) AS
MgrStartDateAfterThreeMonths FROM Department;
33)Displaying the age of all the employees rounded to two digits:
SELECT ROUND(DATEDIFF(CURRENT_DATE, Birthday) / 365.25, 2) AS Age FROM
Employee;
34) Finding the last day and next day of the month in which each
manager has joined:
SELECT ManagerSSN, LAST_DAY(MgrStartDate) AS LastDayOfMonthJoined,
DATE_ADD(LAST_DAY(MgrStartDate), INTERVAL 1 DAY) AS NextDayOfMonthJoined
FROM Department;
35) Printing a substring from the string ‘Harini’:
SELECT SUBSTRING('Harini', 2, 3) AS SubstringFromHarini
36) Replacing the string ‘ni’ from ‘Harini’ by ‘sh’:
SELECT REPLACE('Harini', 'ni', 'sh') AS ModifiedString;
37)Printing the length of all the department names:
SELECT DNAME, LENGTH(DNAME) AS NameLength FROM Department;
38) Printing the system date in the format 25 th May 2007:
SELECT DATE_FORMAT(CURRENT_DATE, '%D %M %Y') AS FormattedDate;
39) Displaying the date after 10 months from current date:
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 10 MONTH) AS DateAfterTenMonths;
PI SQL PROGRAMS
51) Hello World Program in PL/SQL
Code:
set serveroutput on
declare
message varchar2(20):='Hello World!';
BEGIN
dbms_output.put_line(message);
END;
/
52) PL/SQL Program To Add Two Numbers
Code:
set serveroutput on
declare
var1 integer;
var2 integer;
var3 integer;
Begin
var1:=&var1;
var2:=&var2;
var3:=var1+var2;
dbms_output.put_line(var3);
End;
/
53) PL/SQL Program for Prime Number
Code:
set serveroutput on
declare
n number;
i number;
flag number;
begin 1:=2;
flag:=1;
n:=&n;
for i in 2..n/2
Loop
if mod(n, 1)=0
then
flag:=0;
exit;
end if;
end Loop;
if flag=1
then
dbms_output.put_line('prime');
else
dbms_output.put_line('not prime');
end if;
end;
/
54. PL/SQL Program to Find Factorial of a Number
Code:
set serveroutput on
declare
n number;
fac number:=1;
i number;
Begin
n:=&n;
for i in 1..n
loop fac:=fac*i;
end loop; dbms_output.put_line('factorial='||fac);
end;
/
55. PL/SQL Program to Print Table of a Number
Code:
set serveroutput on
declare
n number;
i number;
begin
n:=&n;
for i in 1..10
Loop
dbms_output.put_line(n||'x'||||''||nd);
end Loop;
end;
/
56)PI/SQL program for reverse of a number.
CODE:
SET SERVEROUTPUT ON;
DECLARE
num NUMBER := 12345;
reversed_num NUMBER := 0;
temp_num NUMBER;
BEGIN
temp_num := num;
WHILE temp_num > 0 LOOP
reversed_num := reversed_num * 10 + MOD(temp_num, 10);
temp_num := FLOOR(temp_num / 10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Original Number: ' || num);
DBMS_OUTPUT.PUT_LINE('Reversed Number: ' || reversed_num);
END;
/
OUTPUT:
57) PI/SQL program for Fibonacci Series.
CODE:
SET SERVEROUTPUT ON;
DECLARE
n NUMBER := 10; -- Number of terms in the Fibonacci series
a NUMBER := 0;
b NUMBER := 1;
next_term NUMBER;
i NUMBER := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE('Fibonacci Series:');
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
WHILE i <= n - 2 LOOP
next_term := a + b;
DBMS_OUTPUT.PUT_LINE(next_term);
a := b;
b := next_term;
i := i + 1;
END LOOP;
END;
/
OUTPUT:
58) PI/SQL Procedure to check number is Even or Odd.
CODE:
CREATE OR REPLACE PROCEDURE CheckEvenOdd (
num IN NUMBER,
result OUT VARCHAR2
)
IS
BEGIN
IF MOD(num, 2) = 0 THEN
result := 'Even';
ELSE
result := 'Odd';
END IF;
END;
/
DECLARE
num_to_check NUMBER := 5;
result_str VARCHAR2(10);
BEGIN
CheckEvenOdd(num_to_check, result_str);
DBMS_OUTPUT.PUT_LINE('Number ' || num_to_check || ' is ' || result_str);
END;
/
OUTPUT:
59) PL/SQL Function to Reverse a String.
CODE:
CREATE OR REPLACE FUNCTION ReverseString(input_string IN VARCHAR2) RETURN
VARCHAR2
IS
reversed_string VARCHAR2(1000);
BEGIN
FOR i IN REVERSE 1..LENGTH(input_string) LOOP
reversed_string := reversed_string || SUBSTR(input_string, i, 1);
END LOOP;
RETURN reversed_string;
END;
/
DECLARE
input_string VARCHAR2(100) := ‘hello’;
reversed_string VARCHAR2(100);
BEGIN
reversed_string := ReverseString(input_string);
DBMS_OUTPUT.PUT_LINE('Original String: ' || input_string);
DBMS_OUTPUT.PUT_LINE('Reversed String: ' || reversed_string);
END;
/
OUTPUT:
