Giải chi tiết Lab 3 SQL Hệ CSDL Đại học Bách Khoa HCM 2021
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (21.06 KB, 7 trang )
--Q1a
select fname,minit,lname
from employee right outer join (
works_on left outer join project on works_on.pno=project.pnumber)
on employee.ssn=works_on.essn
where employee.dno=5 and project.pname='ProductX' and works_on.hours>10;
--Q1b
select fname,minit,lname
from employee right outer join dependent on ssn=essn
where employee.fname=dependent.dependent_name;
--Q1c
select sup.ssn,sup.fname
from employee emp right outer join employee sup on emp.ssn=sup.superssn
where (emp.fname || ' ' || emp.lname)='Franklin Wong';
--Q1d
select pname, sum(hours) hours
from project left outer join works_on on pnumber=pno
group by pnumber,pname;
--Q1e
select pname,fname
from employee right outer join (
project left outer join works_on on pnumber=pno)
on ssn=essn
order by pname;
--Q1f
select fname
from (
select *
from employee minus
(select employee.*
from employee right outer join works_on
on ssn=essn)
);
--Q1g
select dname, avg(salary) avg_sal
from department left outer join employee on dnumber=dno
group by dnumber,dname;
--Q1h
select avg(salary) avg_female_sal
from employee
where sex='F';
--Q1i
select distinct fname,address
from (employee left outer join dept_locations on dno=dnumber) left outer join (
works_on left outer join project on pno=pnumber)
on ssn=essn
where dlocation != 'Houston' and plocation ='Houston';
--Q1j
select *
from (
select *
from ( select department.* from department left outer join employee on mgrssn=ssn)
minus
(select department.* from department left outer join dependent on mgrssn=essn)
);
--a
update employee
set address='123 Lý Thường Kiệt F.14 Q.10'
where ssn=123456789;
--b
update dependent
set relationship='FRIEND'
where dependent_name='Joy' and essn=(select ssn from employee where fname='Franklin')
--c
update employee
set salary = salary*2
where dno=(select dnumber from department where dname='Research');
--d
update employee
set salary = salary*0.95
where ssn=(select ssn from (select ssn,nvl(sum(hours),0) h_sum from employee left outer join works_on
on ssn=essn where h_sum < 40 group by ssn));
--a
delete from works_on
where pno=(select pnumber from project where pname='ProductZ');
delete from project
where pname='ProductZ';
--b
delete from works_on
where essn='999887777';
delete from dependent
where essn='999887777';
delete from employee
where ssn='999887777';
--Q2a
select ename, deptno, sal
from emp
where sal >= 1000 and sal<=2000;
--Q2b
select deptno, dname
from dept
order by dname;
--Q2c
select empno, ename
from emp
where deptno=10 or deptno=20;
--Q2d
select ename, job
from emp
where job='CLERK' and deptno=20;
--Q2e
select empno,ename
from emp
where ename like '%TH%' or ename like '%LL%';
--Q2f
select ename,job
from emp
where mgr is not null;
--Q2j
select emp.*, salgrade.grade
from emp, salgrade
where sal between salgrade.losal and hisal;
--Q2g
select ename,deptno from emp where extract(year from hiredate) = 1983;
--Q2i
select avg(nvl(sal,0)) avg_sal
from emp;
select min(sal)
from emp;
select max(sal)
from emo;
--Q2k
select min(sal)
from emp
group by job;
select max(sal)
from emp
group by job;
--Q2l
select distinct mgr
from emp;
--Q2m
select dname, sub1.emp_count
from dept,
(select deptno, count(deptno) as emp_count
from emp
group by deptno) sub1
where dept.deptno = sub1.deptno
and sub1.emp_count > 3;
--Q2n
select ename, dept.loc, dept.dname
from emp
left join dept on dept.deptno = emp.deptno
where sal > 1500;
--Q2o
select ename, job, sal, dept.dname
from emp
left join dept on dept.deptno = emp.deptno
where job not in 'clerk'
order by sal desc;
