EXP 4 Queries

**EXP[4]**

Table 1
create table Emp4(Emp_id varchar(3),Dept_id varchar(3),Job varchar(10),salary int);

insert into Emp4 values('1','1','Programmer','6000');
insert into Emp4 values('2','3','Pro_Leader','15000');
insert into Emp4 values('3','2','Sales_Mang','16000');
insert into Emp4 values('4','1','Programmer','10000');
insert into Emp4 values('5','4','Technician','5000');
insert into Emp4 values('6','3','Programmer','14000');


//update Emp4 set Emp_id='' where Salary='14000';
//select * from Emp4;

Table 2
create table Emp4_det(Emp_id int,F_name varchar(10),L_name varchar(10),City varchar(10),Hire_date date);

insert into Emp4_det values('1','Siva','Nanda','Poochakal','15-01-1996');
insert into Emp4_det values('2','Nanda','Pushpan','Thuravoor','16-09-1997');
insert into Emp4_det values('3','Sai','Lakshmi','Paanavalli','30-06-2004');
insert into Emp4_det values('4','Anandha','Krishnan','Paravoor','25-11-2025');
insert into Emp4_det values('5','Chithra','Lekha','Cherthala','13-09-2012');
insert into Emp4_det values('6','Rob','Man','Hawaii','28-02-2010');

Table 3
create table Emp4_dept(Dept_id int,Title varchar(12),Location varchar(10));

insert into Emp4_dept values('1','Programmer','Delhi');
insert into Emp4_dept values('2','Sales_Mang','Mumbai');
insert into Emp4_dept values('3','Pro_Leader','Kolkatta');
insert into Emp4_dept values('4','Technician','Kochi');
insert into Emp4_dept values('5','R and D','Tvm');

1)
select F_name,L_name from Emp4_det where length(F_name)=length(L_name);

2)
select initcap(city) as CITY from Emp4_det;

3)
select EMP_ID,F_NAME,L_NAME from EMP4_det where EMP_ID in('1','3','4','5') order by F_NAME asc;

//insert into Emp4_det values('7','Roy','Man','hawaii','28-02-2010');
//delete from Emp4_det where Emp_id='7';

4)
select count(*) from Emp4;

5)
select F_name,L_name,Dept_id from Emp4 Natural join Emp4_det where Dept_id='3' AND Job in('Programmer','Pro_Leader');

6)
select F_name,L_name,Dept_id,Salary from Emp4 Natural join Emp4_det where Salary>7000 AND Salary<15000;

7)
update Emp4 set Salary=Salary+(Salary*15)/100 where Dept_id in('4','2');

8)
select count(*) from Emp4 where Emp_id is not null;

//update Emp4 set Salary='4000' where Emp_id='1';
//EMP DEP JOB	       SALARY
  --- --- ---------- ----------
  1   1	Programmer	 6000
  2   3	Pro_Leader	15000
  3   2	Sales_Mang	18400
  4   1	Programmer	10000
  5   4	Technician	 5750
      3	Programmer	14000
9)
select sysdate,last_day(sysdate) from dual;

10)
select Emp_id,Emp_id*Emp_id as Square from Emp4;

11)
select 'Mr/Mrs'||F_name||' '||L_name||' '||'is working at'||' '||Title||'Dept of xyz company,Located at'||Location as message from Emp4_det Natural join Emp4_dept where Title='Sales_Mang';

12)
select * from Emp4_det where MONTHS_BETWEEN(sysdate,Hire_date)>12;

13)
select Next_day(sysdate,'Thursday') from dual;

14)
select Upper(Title) as TITLE from Emp4_dept;

15)
select TO_CHAR(Hire_date,'MM/DD/YYYY') as Hire_date from Emp4_det;

16)
select dept_id,avg(salary) from Emp4 group by (dept_id);

17)
select * from Emp4_det where mod(emp_id,2)=1;

18)
select dept_id,max(salary) as max_salary,min(salary) as min_salary from Emp4 group by (dept_id) having min(salary) <5000;

19)
select months_between(to_date('16-09-1997','dd-mm-yyyy'),to_date('15-01-1996','dd-mm-yyyy'))as month from dual;

20)
select distinct(job) from Emp4;

21)
select F_name||' '||L_name||' '||'was hired on'||' '||TO_CHAR(Hire_Date,'dd mon yyyy')as message from Emp4_det;

22)
savepoint sp;

update Emp4 set Salary='5000' where Emp_id='1';

rollback to savepoint sp;

select * from Emp4_det;

23)
alter table Emp4_det add Dob varchar(14);

update Emp4_det set Dob='19/09/2002' where Emp_id='1';
update Emp4_det set Dob='19/05/2003' where Emp_id='2';
update Emp4_det set Dob='25/06/2002' where Emp_id='3';
update Emp4_det set Dob='13/01/2001' where Emp_id='4';
update Emp4_det set Dob='15/05/2002' where Emp_id='5';

select F_name,L_name,round(Months_between(sysdate,dob)/12)as Age from Emp4_det;

24)

25)
rollback to savepoint sp;
select * from Emp4;

26)
truncate table Emp4_dept;

27)

28)
create view v1 as select Emp_id,job from Emp4;
rename v1 to v2;

29)
drop view v2;