Skip to content

Instantly share code, notes, and snippets.

@deep5050

deep5050/company_relation.sql

Created February 22, 2018 09:41
Show Gist options
  • Save deep5050/7ab2d51c37c3361c6029040c1c87e105 to your computer and use it in GitHub Desktop.
Save deep5050/7ab2d51c37c3361c6029040c1c87e105 to your computer and use it in GitHub Desktop.
Download ZIP
school / college project on company relation table (oracle 10g) (calcutta university)
Raw
company_relation.sql
-- create table employee(emp_id varchar2(5)primary key,
-- person_name varchar2(20),
-- dob date ,
-- street varchar2(20),city varchar2(20)
-- );
-- modify
create table employee(emp_id varchar2(5) primary key,
person_name varchar2(20) not null,
dob date check(dob<'1-jan-2000'),
street varchar2(20) default 'N/A',
city varchar2(20) default 'N/A'
);
SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID NOT NULL VARCHAR2(5)
PERSON_NAME VARCHAR2(20)
DOB DATE
STREET VARCHAR2(20)
CITY VARCHAR2(20)
SQL>
-- create table company(company_id varchar2(5)primary key,
-- company_name varchar2(20),
-- city varchar2(20)
-- );
-- modify
create table company(company_id varchar2(5)primary key,
company_name varchar2(20) unique not null,
city varchar2(20) not null
);
SQL> desc company;
Name Null? Type
----------------------------------------- -------- ----------------------------
COMPANY_ID NOT NULL VARCHAR2(5)
COMPANY_NAME VARCHAR2(20)
CITY VARCHAR2(20)
SQL>
-- create table works(emp_id varchar2(5)references employee(emp_id),
-- company_id varchar2(5)references company(company_id),
-- salary number(6)
-- );
-- modify
create table works(
emp_id varchar2(5),
company_id varchar2(5),
salary number(6) not null check(salary>1000),
foreign key (emp_id)references employee(emp_id) on delete cascade,
foreign key(company_id) references company(company_id) on delete set null
);
SQL> desc works;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID VARCHAR2(5)
COMPANY_ID VARCHAR2(5)
SALARY NUMBER(6)
SQL>
-- create table manages(emp_id varchar2(5)references employee(emp_id),
-- manager_id varchar2(5)
-- );
-- modify
create table manages(
emp_id varchar2(5)references employee(emp_id)
on delete cascade,
manager_id varchar2(5) references employee(emp_id)
on delete cascade
);
SQL> desc manages;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID VARCHAR2(5)
MANAGER_ID VARCHAR2(5)
SQL>
SQL> drop table manages;
Table dropped.
SQL>
SQL> drop table works;
Table dropped.
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
insert into employee (emp_id,person_name,dob)values('e-1','dipankar pal','15-jul-1997');
insert into employee values('e-2','subhadip roy','15-jan-1997','garia','kolkata');
insert into employee values('e-3','soumyajit samanta','11-aug-1996','patuli','kolkata');
insert into employee (emp_id,person_name,dob,city)values('e-4','akash samanta','11-jul-1996','kolkata');
insert into employee values('e-5','krinshna patel','22-aug-1996','block-1','pune');
insert into employee values('e-6','pintu das','18-aug-1998','jan','kolkata');
insert into employee values('e-7','natasha agarwal','11-aug-1997','block-B','noida');
insert into employee values('e-8','tina duttagupta','23-feb-1997','cannel side','contai');
insert into employee values('e-9','pratik haldar','11-jun-1999','gariahaat','kolkata');
-----result-----
SQL> select * from employee
2 ;
EMP_I PERSON_NAME DOB STREET CITY
----- -------------------- --------- -------------------- --------------------
e-1 dipankar pal 15-JUL-97 N/A N/A
e-2 subhadip roy 15-JAN-97 garia kolkata
e-3 soumyajit samanta 11-AUG-96 patuli kolkata
e-4 akash samanta 11-JUL-96 N/A kolkata
e-5 krinshna patel 22-AUG-96 block-1 pune
e-6 pintu das 18-AUG-98 jan kolkata
e-7 natasha agarwal 11-AUG-97 block-B noida
e-8 tina duttagupta 23-FEB-97 cannel side contai
e-9 pratik haldar 11-JUN-99 gariahaat kolkata
9 rows selected.
SQL>
-- insert into employee values('e-1','dipankar pal','15-jul-1997','h.m raod','kolkata');
-- insert into employee values('e-1','dipankar pal','15-jul-1997','h.m raod','kolkata');
insert into company valueS('c-1','tcs','kolkata');
insert into company valueS('c-2','infosys','kolkata');
insert into company valueS('c-3','wipro','bengaluru');
SQL> select * from company;
COMPA COMPANY_NAME CITY
----- -------------------- --------------------
c-1 tcs kolkata
c-2 infosys kolkata
c-3 wipro bengaluru
SQL>
insert into works values('e-1','c-1',9000);
insert into works values('e-2','c-2',2000);
insert into works values('e-3','c-2',22000);
insert into works values('e-4','c-1',92000);
insert into works values('e-5','c-2',2000);
insert into works values('e-6','c-3',21000);
insert into works values('e-7','c-3',3500);
insert into works values('e-8','c-2',3500);
insert into works values('e-9','c-1',3500);
SQL> select * from works;
EMP_I COMPA SALARY
----- ----- ----------
e-1 c-1 9000
e-2 c-2 2000
e-3 c-2 22000
e-4 c-1 92000
e-5 c-2 2000
e-6 c-3 21000
e-7 c-3 3500
e-8 c-2 3500
e-9 c-1 3500
9 rows selected.
SQL>
-- manager
insert into manages values('e-3','e-1');
insert into manages values('e-4','e-1');
insert into manages values('e-5','e-1');
insert into manages values('e-6','e-2');
insert into manages values('e-7','e-2');
insert into manages values('e-7','e-8');
insert into manages values('e-9','e-8');
SQL> select * from manages;
EMP_I MANAG
----- -----
e-3 e-1
e-4 e-1
e-5 e-1
e-6 e-2
e-7 e-2
e-7 e-8
e-9 e-8
7 rows selected.
SQL>
select * from employee where emp_id in(select emp_id from works where salary > 5000);
SQL> select * from employee where emp_id in(select emp_id from works where salary > 5000);
EMP_I PERSON_NAME DOB STREET CITY
----- -------------------- --------- -------------------- --------------------
e-1 dipankar pal 15-JUL-97 h.m raod kolkata
e-3 soumyajit samanta 11-AUG-96 patuli kolkata
e-4 akash samanta 11-JUL-96 dumdum kolkata
e-6 pintu das 18-AUG-98 jan kolkata
SQL>
--------------------------------------------------------
select * from employee where emp_id in(select emp_id from manages);
SQL> select * from employee where emp_id in(select emp_id from manages);
EMP_I PERSON_NAME DOB STREET CITY
----- -------------------- --------- -------------------- --------------------
e-3 soumyajit samanta 11-AUG-96 patuli kolkata
e-4 akash samanta 11-JUL-96 dumdum kolkata
e-5 krinshna patel 22-AUG-96 block-1 pune
e-6 pintu das 18-AUG-98 jan kolkata
e-7 natasha agarwal 11-AUG-97 block-B noida
e-9 pratik haldar 11-JUN-99 gariahaat kolkata
6 rows selected.
SQL>
------------------------------------
select distinct e.person_name , c.company_name from employee e, manages m, works w,company c where e.emp_id =m.manager_id and m.manager_id=w.emp_id and w.company_id =c.company_id;
SQL> select distinct e.person_name , c.company_name from employee e, manages m, works w,company c where e.emp_id =m.manager_id and m.manager_id=w.emp_id and w.company_id =c.company_id;
PERSON_NAME COMPANY_NAME
-------------------- --------------------
subhadip roy infosys
tina duttagupta infosys
dipankar pal tcs
SQL>
----------------------------------------------
select e.emp_id,e.person_name, c.company_name from employee e, company c, works w where c.company_id=w.company_id and w.emp_id=e.emp_id order by c.company_name;
SQL> select e.emp_id,e.person_name, c.company_name from employee e, company c, works w where c.company_id=w.company_id and w.emp_id=e.emp_id order by c.company_name;
EMP_I PERSON_NAME COMPANY_NAME
----- -------------------- --------------------
e-5 krinshna patel infosys
e-8 tina duttagupta infosys
e-3 soumyajit samanta infosys
e-2 subhadip roy infosys
e-9 pratik haldar tcs
e-4 akash samanta tcs
e-1 dipankar pal tcs
e-7 natasha agarwal wipro
e-6 pintu das wipro
9 rows selected.
SQL>
------------------------------------------
-- select e.emp_id,e.person_name from employee e, manages m
-- where m.manager_id='e-1' and m.manager_id=e.emp_id;
-- select e.emp_id,e.person_name from employee e , manages m
-- where m.manager_id in(select e.emp_id from employee) and m.manager_id='e-1';
-------------------------------------------------------------
-- select e.emp_id,e.person_name from manages m,employee e where
-- m.emp_id in(select m.emp_id from manages) and e.emp_id=m.emp_id and m.manager_id='e-1';
select e.emp_id,e.person_name from manages m,employee e where
m.emp_id in(select m.emp_id from manages) and e.emp_id=m.emp_id and m.manager_id = (select emp_id from employee where person_name like 'subhadip%');
------ result-----
SQL> select e.emp_id,e.person_name from manages m,employee e where
2 m.emp_id in(select m.emp_id from manages) and e.emp_id=m.emp_id and m.manager_id = (select emp_id from employee where person_name like 'subhadip%');
EMP_I PERSON_NAME
----- --------------------
e-6 pintu das
e-7 natasha agarwal
SQL>
-- SQL> select e.emp_id,e.person_name from manages m,employee e where
-- 2 m.emp_id in(select m.emp_id from manages) and e.emp_id=m.emp_id and m.manager_id='e-1';
-- EMP_I PERSON_NAME
-- ----- --------------------
-- e-3 soumyajit samanta
-- e-4 akash samanta
-- e-5 krinshna patel
-- SQL>
---------------------------------------------
-- select company_name from company where company_id in(select company_id from works group by company_id having count(*)=(select max(count(*))from works group by company_id));
-- SQL> select company_name from company where company_id in(select company_id from works group by company_id having count(*)=(select max(count(*))from works group by company_id));
-- COMPANY_NAME
-- --------------------
-- infosys
-- SQL>
----------------------------------------------------
-- company wise highest salaried person
-- select distinct e.person_name,w.emp_id,c.company_id from works w,company c,employee e where salary=(select max(salary)from works) and e.emp_id=w.emp_id and c.company_id=w.company_id;
-- company having max employee
select e.emp_id, c.company_name from employee e, company c, works w where c.company_id=w.company_id and e.emp_id=w.emp_id
and salary=(select max(salary) from works);
-- select e.emp_id ,c.company_name from company c, employee e, works w where e.emp_id in(select emp_id from works where salary=(select max(salary) from works where w.emp_id=e.emp_id) )and e.emp_id=w.emp_id;
-- select e.emp_id,c.company_name from employee e, company c,works w where c.company_id in(select company_id from works where salary=(select max(salary) from works where w.company_id=c.company_id and w.emp_id=e.emp_id));
select e.emp_id,e.person_name,c.company_name from company c, employee e, works w where e.emp_id in(select w.emp_id from works where w.salary in (select max(w.Salary) from works where w.company_id in (select c.company_id from company)));
-- focus here----
-- select e.emp_id,e.person_name,c.company_name from company c, employee e, works w where e.emp_id in(select w.emp_id from works where salary in (select max(Salary) from works where w.company_id in (select company_id from company)));
-- select e.emp_id,e.person_name,c.company_name from employee e, company c,works w
-- where e.emp_id in(select emp_id from works where salary=(select max(salary) from works where company_id in (select distinct company_id from company))) and e.emp_id=w.emp_id and w.company_id=c.company_id;
-- select e.emp_id,c.company_name from employee e,comapny c where e.emp_id in(select w.emp_id from works w, company c where (w.company_id=c.company_id and (w.salary = (select max(salary) from works where w.company_id in (select company_id from company)))));
-- select employee.person_name from employee where emp_id =(select emp_id from works where salary=(select max(salary) from works group by (company_id) having (select company_id from company)));
-- select e.person_name,w.company_id,w.salary
-- from works w
-- inner join employee e
-- on e.emp_id = w.emp_id
-- where (select count(*)
-- from works
-- where company_id=w.company_id
-- and salary>=w.salary
-- ) <=1
-- group by w.company_id
-- ;
-- select e.person_name,w.company_id,w.salary
-- from works w
-- inner join employee e
-- on e.emp_id = w.emp_id
-- where (select count(*)
-- from works
-- where company_id=w.company_id
-- and salary>=w.salary ) <=1 group by w.company_id;
-- select e.person_name
-- from
-- (
-- select w.emp_id,w.company_id,w.salary
-- from works w
-- where (select count(*)
-- from works
-- where company_id=w.company_id
-- and salary>=w.salary
-- ) <=1
-- group by w.company_id
-- ) tmp
-- inner join employee
-- on e.emp_id = tmp.emp_id
-- group by e.emp_id,e.person_name
-- ;
----------------------------------- query g--------------------- from hati
-- select person_name ,b.salary ,company_name from
-- (
-- select emp_id ,works.salary,works.company_id wcid from
-- (
-- select company_id ,max(salary) sal from works group by (company_id)
-- ) a, works where a.company_id =works.company_id and a.sal=works.salary
-- ) b, employee,company where b.emp_id=employee.emp_id and b.wcid=company.company_id;
------- result-----------------------
-- SQL> select person_name ,b.salary ,company_name from
-- 2 (
-- 3 select emp_id ,works.salary,works.company_id wcid from
-- 4 (
-- 5 select company_id ,max(salary) sal from works group by (company_id)
-- 6 ) a, works where a.company_id =works.company_id and a.sal=works.salary
-- 7 ) b, employee,company where b.emp_id=employee.emp_id and b.wcid=company.company_id;
PERSON_NAME SALARY COMPANY_NAME
-------------------- ---------- --------------------
akash samanta 92000 tcs
soumyajit samanta 22000 infosys
pintu das 21000 wipro
SQL>
--------- my query-------
-- select person_name, company.company_name ,salary from (select emp_id ,works.salary ,company_id from (select company_id, max(salary) from works group by (company_id)) q_1,works where works.salary=q_1.salary)q_2,company,employee where q_2.company_id=company.company_id and q_2.emp_id=employee.emp_id;
-------!yyayyyyyyyyy
-- company wise max salary
select company.company_name,employee.person_name as employee_name,y.salary as salary from employee,company,(select works.emp_id,works.company_id,works.salary from works,
(select company_id,max(salary) as high from works group by(company_id)
)x where high=works.salary) y
where employee.emp_id=y.emp_id and company.company_id=y.company_id order by company_name
;
-- -- company having maximun employee
-- select company_name from
-- (
-- select company_id from
-- ( select company_id,count(*) as result from works group by(company_id))t1
-- where t1.result>=(select max(count(*)) as result from works group by(company_id))
-- ) t2, company
-- where t2.company_id=company.company_id
-- ;
-- select company_name from
-- (select company_id,max(count(*)) as result from works group by(company_id)
-- )t1 ,company where t1.company_id=company.company_id
-- ;
select company_name, result as employee_count from
(
select company_id ,result from
( select company_id,count(*) as result from works group by(company_id))t1
where t1.result=(select max(count(*)) as result from works group by(company_id))
) t2, company
where t2.company_id=company.company_id
;
----------result=======
SQL> select company_name, result as employee_count from
2 (
3 select company_id ,result from
4 ( select company_id,count(*) as result from works group by(company_id))t1
5 where t1.result=(select max(count(*)) as result from works group by(company_id))
6 ) t2, company
7 where t2.company_id=company.company_id
8 ;
COMPANY_NAME EMPLOYEE_COUNT
-------------------- --------------
infosys 4
SQL>
-- select e.emp_id,e.person_name from manages m,employee e where
-- m.emp_id in(select m.emp_id from manages) and e.emp_id=m.emp_id and e.person_name like 'subhadip%';
-- select manages.emp_id,count(*) from (select emp_id from employee where person_name like 'subhadip%') t1,manages where t1.emp_id=manages.manager_id group by(manager_id);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment