DBT_Lab Exam

create database exam;
use exam;

create table DEPT(
DEPTNO int(2),
DNAME varchar(15),
LOC varchar(10));


insert into DEPT values
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');
select * from DEPT;

create table EMP(
EMPNO int(4),
ENAME varchar(10),
JOB varchar(9),
HIREDATE date,
SAL float(7,2),
COMM float(7,2),
DEPTNO int(2));

insert into EMP values
(7839,'KING','MANAGER','1991-11-17',5000,NULL,10),
(7698,'BLAKE','CLERK','1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER','1981-06-09',2450,NULL,10),
(7566,'JONES','CLERK','1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN','1981-09-28',1250,1400,30),
(7499,'ALLEN','SALESMAN','1981-02-20',1600,300,30);
select * from EMP;

/* Write SELECT statements to achieve the following:- */

/* 3.	Display all the employees where SAL between 2500 and 5000 (inclusive of both).*/
select ENAME,Sal from emp where SAL between 2500 and 5000;

/* 4.	Display all the ENAMEs in descending order of ENAME.*/
select ENAME from EMP
order by ENAME desc;

/* 5.	Display all the JOBs in lowercase.*/
select lower(JOB) from emp;
/* 6.	Display the ENAMEs and the lengths of the ENAMEs. */
select ENAME ,length(ENAME) as Lengths from emp;

/*7.	Display the DEPTNO and the count of employees 
who belong to that DEPTNO .
*/
select DEPTNO,Count(*) as Count from EMP
group by DEPTNO;

/* 8.	Display the DNAMEs and the ENAMEs who 
belong to that DNAME. */
select DNAME,ENAME from EMP,DEPT where DEPT.DEPTNO=EMP.DEPTNO;

/*9.	Display the position at which the string ‘AR’ occurs 
in the ename.*/
select ENAME,POSITION('AR' in ENAME) as Position from EMP;

/* 10.	Display the HRA for each employee given that HRA 
is 20% of SAL. */

Select ENAME,SAL,SAL*0.2 as HRA from EMP;


/* 1.	Write a stored procedure by the name of PROC1 that accepts two varchar strings as parameters.
 Your procedure should then determine if the first varchar string exists inside the varchar string. 
 For example, if string1 = ‘DAC’ and string2 = ‘CDAC, then string1 exists inside string2.  
 The stored procedure should insert the appropriate message into a suitable TEMPP output table. 
 Calling program for the stored procedure need not be written.
*/

create table temp(
 A varchar(15),
 B varchar (15),
Result varchar(15));

delimiter //
create procedure PROC1(a varchar(15), b varchar(15))
begin
if instr(a,b)
then insert into temp values(a,b,'String Present');
else
insert into temp values(a,b,'String Absent');
end if ;
end; //
delimiter ;
call PROC1('CDAC','DAC');
call PROC1('Alo','bes');
select * from temp;
drop table temp;


/* 2.	Create a stored function by the name of FUNC1 to take three parameters, 
the sides of a triangle. The function should return a Boolean value:- TRUE if 
the triangle is valid, FALSE otherwise. A triangle is valid if the length 
of each side is less than the sum of the lengths of the other two sides. 
Check if the dimensions entered can form a valid triangle. Calling program for 
the stored function need not be written. */

delimiter //
create  function FUNC1( T1 int , T2 int, T3 int )
returns Boolean
deterministic
begin
declare Result boolean default false;
if ((T1+T2)<T3) then
	set result =false;
    return result;
else if ((T1+T3)<T2) then
	set result = false;
    return result;
else if ((T2+T3)<T1) then
	set result = false;
    return result;
else 
	set result = true;
	return result;
end if;
end if;
end if;
end ; //
delimiter ;
 ;
select Result(4,5,6) ;