forked from sandipmohapatra/MissionInfosys
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Employ Details.txt
53 lines (35 loc) · 1.74 KB
/
Employ Details.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
drop table bank;
create table Infy3(employno number,employname char(50),email varchar2(50),dob date,salary number(8,2))
insert into Infy3 values(3,'Prasad','[email protected]','30-Jun-1998',45000.00);
select employno,employname,email,dob,salary+1000 from Infy3;
select employno,employname,email,dob,round(salary/3,2) from Infy3;
DUPLICATE TABLE:
create table perdalsal as select employno,employname,email,dob,round(salary/30,2) as perdayamount from Infy3;
create table annualsal as select employno,employname,email,dob,salary*12 as annualamount from Infy3;
drop table annualsal;
select * from perdalsal;
select * from annualsal;
insert into Infy3(employno,employname,email,dob) values(4,'sai','[email protected]','1-jan-1997');
update Infy3 set salary=50000 where employno=4;
COMPARISON TABLE:
select * from Infy3 where salary > 30000;
select * from Infy3 where salary < 30000;
select * from Infy3 where salary <= 30000;
select * from Infy3 where salary between 30000 and 50000;
NAME SEARCHING:
select * from Infy3 where salary>=1000 and email like 'v%';
select * from Infy3 where salary>=1000 and email like '_r%';
select * from Infy3 where salary>=1000 and employname like '%y';
select * from Infy3 where employno in (1,2);
select * from Infy3 where salary >any(30000);
select * from Infy3 where salary >some(25000);
select * from Infy3 where salary >=all(30000);
RENAME COLUMN
Alter table Infy3 REnaME column employ_no to employno;
alter table Infy4 rename to Infy5;
alter table Infy5 rename to Infy3;
delete Infy3 where employno=4;
rollback;
savepoint a1;
delete Infy3 where employno=3;
commit;