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
Queries:
1. Show all records from EMP table ANSWER: 2. Show all records from DEPT table ANSWER: 3. Show records of all employees with Salary greater than 2000. Show the employee name and salary ANSWER: 4. Show records of all employees with the job title of Manager. Only show the name of the employee and hire date in the result. (Hint: make sure the value for the job title matches exactly as it appears in the table e.g. in all uppercase) Could you use the UPPER or LOWER function if you did not know how the value appears in the table
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help