Question: Hello, I am taking an Intro to Database Management Course and there are a few queries that I need help on for an assignment. I
Hello,
I am taking an Intro to Database Management Course and there are a few queries that I need help on for an assignment. I have posted the SQL code that the instructor wants us to work with and the queries he wants us to do.
SQL Code:
create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
)
create table emp(
empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno)
)
insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK')
insert into dept
values(20, 'RESEARCH', 'DALLAS')
insert into dept
values(30, 'SALES', 'CHICAGO')
insert into dept
values(40, 'OPERATIONS', 'BOSTON')
insert into emp
values(
7839, 'KING', 'PRESIDENT', null,
to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10
)
insert into emp
values(
7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30
)
insert into emp
values(
7782, 'CLARK', 'MANAGER', 7839,
to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10
)
insert into emp
values(
7566, 'JONES', 'MANAGER', 7839,
to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20
)
insert into emp
values(
7788, 'SCOTT', 'ANALYST', 7566,
to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20
)
insert into emp
values(
7902, 'FORD', 'ANALYST', 7566,
to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20
)
insert into emp
values(
7369, 'SMITH', 'CLERK', 7902,
to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20
)
insert into emp
values(
7499, 'ALLEN', 'SALESMAN', 7698,
to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30
)
insert into emp
values(
7521, 'WARD', 'SALESMAN', 7698,
to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30
)
insert into emp
values(
7654, 'MARTIN', 'SALESMAN', 7698,
to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30
)
insert into emp
values(
7844, 'TURNER', 'SALESMAN', 7698,
to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30
)
insert into emp
values(
7876, 'ADAMS', 'CLERK', 7788,
to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20
)
insert into emp
values(
7900, 'JAMES', 'CLERK', 7698,
to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30
)
insert into emp
values(
7934, 'MILLER', 'CLERK', 7782,
to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10
)
select ename, dname, job, empno, hiredate, loc
from emp, dept
where emp.deptno = dept.deptno
order by ename
select dname, count(*) count_of_employees
from dept, emp
where dept.deptno = emp.deptno
group by DNAME
order by 2 desc
Questions (queries):
a.) Show all records from Employees and details of Department they belong using Natural Join operation.
b.) Show all records from Employees and details of Department they belong using Inner Join operation.
c.) Only show employee name and department name for all employees who belong to the Accounting Department.
d.) Show the average salary for each department. Using the Cast operator, restrict the decimal places to only two. Use the label Average Salary for the column showing the average salary. Sort the results on average salary in a descending manner.
e.) Show the average salary for each job. Using the Cast operator, restrict the decimal places to only two. Use the label Average Salary for the column showing the average salary. Only show the results for those jobs with the average salary less than 3000. (Hint: You will need to use the Having clause)
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help