RDBMS

EMPLOYEE CODE GENERATION USING SEQUENCE 

AND TRIGGERS

SQL QUERIES :
SQL> create table emp(snonumber,empcodevarchar(10),empnamevarchar(12));
Table created.

SQL> create sequence seq1;
Sequence created.

SQL> create or replace trigger trig1 before insert on emp for each row
 when(new.empcode is null)
 begin
 select 'emp0'|| to_char(seq1.nextval) into :new.empcode from dual;
 end;
 /

Trigger created.

SQL> insert into emp(sno,empname)values(1,'ram');
1 row created.

SQL> insert into emp(sno,empname)values(2,'janu');
1 row created.

SQL> insert into emp(sno,empname)values(3,'stocks');
1 row created.

SQL> insert into emp(sno,empname)values(4,'butler');
1 row created.

SQL> select * from emp;
 SNO EMPCODE EMPNAME
---------- ---------- ------------
 1 emp01 ram
 2 emp02 janu
 3 emp03 stocks
 4 emp04 butlerbutler

Formatting table 

Create table

ttitle left 'pageno:' format99 sql.pno center 'product_report';

column dealar heading supplier;

column phoneno justify right;

btitle col6 'Date' tab6 '08-nov-2022' tab6 'DMart';

 select * from product;

Insert TABLE

SQL>ttitle off;

SQL>btitle off;

SQL> clear column

columns cleared

SQL> select * from product;

Date FUNCTIONS 

 select current_date from dual; 

select sysdate from dual; 

select extract(year from to_date('29-apr-2020 05:30:20','dd-mon-yyyy 

HH24:MI:SS'))year from dual;

select to_date('20 apr 2020','dd mon yyyy')converted_date from dual; 

 select to_char(sysdate,'dd-mm-yyyy')new_date from dual; 

select last_day(sysdate)last_day from dual; 

select months_between(sysdate,date '2011-04-02')month_diffrence from dual; 

 select add_months(sysdate,2)newdate from dual; 

select current_timestamp from dual; 

select dbtimezone from dual; 

select from_tz(timestamp '2020-05-01 19:35:10','07:00')newvalue from dual; 

select new_time(sysdate,'pst','ast')time_in_ast from dual; 

select to_char(round(to_date('01-may-2020 20:27:15','dd-mon-yyyy 

HH24:MI:SS')))rounded_date from dual;

select sessiontimezone from dual; 

select systimestamp from dual; 

select trunc(sysdate,'mm')month from dual;

SUBQUARIES

Create table sailors(sidint,sname varchar2(20),rating int,ageint,primary key(sid)); 

 insert into sailors values(„&sid‟,‟&sname‟,‟&rating‟,‟&age‟); 

select * from sailors;

create table boats(bid int,bname varchar2(20),color varchar2(20),primary key(bid)); 

insert into boats values(„&bid‟,‟&bname‟,‟&color‟,‟&age‟); 

create table reserves(sidint,bidint,date varchar2(20),primary key(sid,bid,date)

foreign key (sid) references sailors,fpreign key (bid) references boats);

 insert into boats reserves(„&sid‟,„&bid‟,‟&date‟);

select * from reserves;

select sname from sailors where exsists(select * from reserves where bid=103 and sid=sid);

SNAME

Sam

select sname from sailors where except(select * from reserves where bid=103 and sid=sid); 

SNAME

John Dean

select sid from sailors left join boats on sailors.bid=boat.bid except select sid

from sailors right join boats on sailors.bid=boat.bid;

SID

1

3

select sid from sailors except select sid from reserves;