create table sailors
(Sid integer primary key,
Sname varchar2(10) not null,
Rating integer,
Age number);
insert into sailors values (22,'Dustin',7,45);
insert into sailors values (29,'Brutus',1,33);
insert into sailors values (31,'Lubber',8,55.5);
insert into sailors values (32,'Andy',8,25.5);
insert into sailors values (58,'Rusty',10,35);
insert into sailors values (64,'Horatio',7,35);
insert into sailors values (71,'Zorba',10,16);
insert into sailors values (74,'Horatio',9,40);
insert into sailors values (85,'Art',3,25.5);
insert into sailors values (95,'Bob',3,63.5);
create table boats
(bid integer primary key,
bname varchar2(10),
color varchar2(10));
insert into boats values (101,'Interlake','blue');
insert into boats values (102,'Interlake','red');
insert into boats values (103,'Clipper','green');
insert into boats values (104,'Marine','red');
create table reserves
(sid integer references sailors(sid),
bid integer references boats(bid),
day varchar2(12));
insert into reserves values (22,101,'1998-10-10');
insert into reserves values (22,102,'1998-10-10');
insert into reserves values (22,103,'1998-10-8');
insert into reserves values (22,104,'1998-10-7');
insert into reserves values (31,102,'1998-11-10');
insert into reserves values (31,103,'1998-11-6');
insert into reserves values (31,104,'1998-11-12');
insert into reserves values (64,101,'1998-9-5');
insert into reserves values (64,102,'1998-9-8');
insert into reserves values (74,103,'1998-9-8');
select sqrt(64) from dual;
select length('merin') from dual;
select empname,length(empname) from employee;
select sysdate from dual;
select months_between (sysdate,'08-jan-2024') from dual;
create table dept
(deptno varchar2(6) primary key,
deptname varchar2(10));
insert into dept values('101','statistics');
insert into dept values('102','cs');
insert into dept values('103','psych');
insert into dept values('104','libsci');
insert into dept values('105','comm');
create table project
(projno varchar2(6) primary key,
projname varchar2(10),
projst date,
projend date);
insert into project values('101','aaa','01-jan-2023','01-mar-2024');
insert into project values('102','bbb','02-jun-2023','03-mar-2024');
insert into project values('103','ccc','01-jun-2023','03-mar-2024');
insert into project values('104','ddd','05-jun-2023','02-mar-2024');
insert into project values('105','eee','04-jun-2023','05-mar-2024');
many to one
one to many
many employees in one project
one project to an employee
create table employee
(empno varchar2(5) primary key,
empname varchar2(12),
dob date,
doj date,
salno number(10,2),
gender varchar2(5),
deptno varchar2(6) references dept(deptno),
projno varchar2(6) references project(projno));
insert into employee values('111','ppp','01-jan-2002','01-mar-2024',25000.00,'F','101','102');
insert into employee values('112','qqq','01-jun-2001','18-mar-2024',25000.00,'M','104','102');
insert into employee values('113','rrr','08-apr-2002','01-mar-2024',35000.00,'F','105','101');
insert into employee values('114','sss','01-dec-2002','05-mar-2024',25000.00,'F','104','104');
insert into employee values('115','ttt','26-nov-2002','11-mar-2024',25000.00,'M','101','105');
select projname,months_between (projend,projst) from project;
select projname,months_between (projend,projst) duration from project;
select projname,months_between (sysdate,projst) months from project;
select to_char(dob,'MON') from employee;
select to_char(dob,'day') from employee;
select to_char(dob,'year') from employee;
select to_char(dob,'yyyy') from employee;
select to_char(dob,'yy') from employee;
select to_date('12/01/2024','dd-mm-yyyy') from dual;
select to_date('2012/01/12','yyyy-mm-dd') from dual;
select deptno,count(*)
from employee
group by deptno;
select deptname,count(*)
from dept
group by deptname;
select deptno,sum(salno)
from employee
group by deptno;
select deptname,sum(salno)
from dept,employee
group by deptname;
select deptno,sum(salno)
from employee
group by deptno having sum(salno)>60000;
select empno,count(deptno)
from employee
group by deptno having count(deptno)>3;
select deptno,count(empno)
from employee
group by deptno having count(empno)>1;
select deptname,count(empno)
from dept,employee
group by deptname having count(empno)>1;
select gender, avg(salno)
from employee
group by gender;
select deptno, max(salno),min(salno)
from employee
group by deptno;
select empname,deptname
from employee emp,dept
where [Link]=[Link];
select empname,deptname
from employee natural join dept;
select empname,projname
from employee,project
where [Link]=[Link];
select empname,projno
from employee natural join project;
select empname,projname
from employee join project on [Link]=[Link];
select empname,deptname,projname
from employee,dept,project
where [Link]=[Link] and [Link]=[Link];
select empname,deptname,projname
from employee natural join dept natural join project;
select empname,deptname,projname
from employee join dept on [Link]=[Link] and join project on
[Link]=[Link];
select deptname,avg(salno)
from dept,employee
where [Link]=[Link]
group by deptname;
select deptname,count(*)
from dept,employee
where [Link]=[Link] and to_char(dob,'MON')='jun'
group by deptname;
select empname,projname
from employee,project
where [Link]=[Link] and projname='aaa';
select empname,deptno
from employee,dept,project
where [Link]=[Link] and [Link]=[Link] and (projst between
'01-jan-2022' and '31-jan-2024');
select empname
from employee
where salno>(select avg(salno) from employee);
select deptno,empname
from employee
where deptno in(select deptno from dept where deptname='comm');