project schema

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 );s

step-1: Copy and paste the schema in SQL Developer studio and execute all the sql commands (as a script or one by one) and it will create all the required tables and add data to the tables. It’s a must to have the script file/schema executed to answer the questions below

Project: Please provide the SQL’s for all the statements provided below: 

1. Display the dept information from department table.
2. Display the details of all employees.
3. Display the name and job for all employees.
4. Display name and salary for all employees.
5. Display employee number and total salary for each employee.
6. Display employee name and annual salary for all employees.
7. Display the names of all employees who are working in department number 30
8. Display the names of all employees working as clerks and drawing a salary more than 3000.
9. Display employee number and names for employees who earn commission.
10. Display names of employees who do not earn any commission.
11. Display the names of employees who are working as clerk, salesman or analyst and drawing a salary more than 3000.
12. Display the names of employees who are working in the company for the past 5 years.
13. Display the list of employees who have joined the company before 30th June 90 or after 31st dec 90.
14. Display current date.
15. Display the list of users in your database (using log table).
16. Display the names of all tables from the current user.
17. Display the name of the current user.
18. Display the names of employees working in department number 10 or 20 or 40 or employees working as clerks, salesman or analyst.
19. Display the names of employees whose name starts with letter ‘S’.
20. Display employee names for employees whose name ends with the letter ‘A’
21. Display the names of employees whose names have second letter as an ‘A’
22. Display the names of employees whose name is exactly five characters in length.
23. Display the names of employees who are not working as managers.
24. Display the names of employees who are not working as SALESMAN or CLERK or ANALYST.
25. Skipped for this database, no SQL required- Bonus point awarded
26. Display the total number of employees working in the company.
27. Display the total salary being paid to all employees.
28. Display the maximum salary from emp table.
29. Display the minimum salary from emp table.
30. Display the average salary from emp table.
31. Display the maximum salary being paid to CLERK.
32. Display the maximum salary being paid in dept no 20.
33. Display the min Sal being paid to any SALESMAN.
34. Display the average salary drawn by managers.
35. Display the total salary drawn by analyst working in dept no 40.
36. Display the names of employees in order of salary i.e. the name of the employee earning lowest salary should appear first.
37. Display the names of employees in descending order of salary.
38. Skipped for this database, no SQL required- Bonus point awarded
39. Display empno, ename, deptno, and sal. Sort the output first based on name and within name by deptno and within deptno by Sal;
40. Skipped for this database, no SQL required- Bonus point awarded
41. Skipped for this database, no SQL required- Bonus point awarded
42. Display the name of emp who earns highest sal.
43. Display the employee number and name of employee working as CLERK and earning highest salary among CLERKS.
44. Display the names of the salesman who earns a salary more than the highest salary of any clerk.
45. Display the names of clerks, who have a higher salary than James but less than Scott
46. Display the names of employees, who earn a higher salary than both James and Scott

Please put each statement, the SQL and the SQL output in a word document and have it uploaded.