Skip to content

Instantly share code, notes, and snippets.

@deep5050

deep5050/ass2.sql

Created December 7, 2017 15:02
Show Gist options
  • Save deep5050/e1f5ee82c9f4d7fd23e53bbd08b59186 to your computer and use it in GitHub Desktop.
Save deep5050/e1f5ee82c9f4d7fd23e53bbd08b59186 to your computer and use it in GitHub Desktop.
Download ZIP
a simple employee- manager database
Raw
ass2.sql
create table employee(emp_id varchar2(5)primary key,
person_name varchar2(20),dob date ,
street varchar2(20),city varchar2(20)
);
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)
);
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)
);
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)
);
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 values('e-1','dipankar pal','15-jul-1997','h.m raod','kolkata');
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 values('e-4','akash samanta','11-jul-1996','dumdum','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');
SQL> select * from employee;
EMP_I PERSON_NAME DOB STREET CITY
----- -------------------- --------- -------------------- --------------------
e-1 dipankar pal 15-JUL-97 h.m raod kolkata
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 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-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';
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>
----------------------------------------------------
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;
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
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
;
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
;
-- 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