The database schema is as follows:
DEPT(deptno,dname,loc) -- (deptno is the primary key)
EMP(empno,ename,job,mgr,hiredate, sal,comm,deptno)
-- EMPNO is primary key and deptno is the foriegn key references dept)
BONUS(ename,job, sal, comm)
SALGRADE(grade, losal, hisal)
Create a query to display the employee name and department number of all employees in departments 10 and 30 in alphabetical order by name.
Create a query that will display the employee name and commission amount. If the employee does not earn commission, put "No Commission." Label the column COMM.
Create a query to display the name, job, and salary for all employees whose job is Clerk or Analyst and
their salary is not equal to $1000, $3000, or $5000.
4. Write a query to display the name, department number, and department name for all employees.
5. Create a query that will display the employee name, department number, and all the employees that work in the same department as a given employee. Give each column an appropriate label.
6. Create a query to display all employees' names and hire dates along with their manager's name and hire date for all employees who were hired before their managers. Label the columns Employee, Emp Hiredate, Manager, and Mgr Hiredate, respectively.
7. Create a query to display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is less than $1000. Sort the output in descending order of salary.
8. Write a query to display the employee name and hire date for all employees in the same department as Blake. Exclude Blake.
Write a query to display the employee number, name, and salary for all employees who earn more than the average salary and who work in a department with any employee with a T in their name.
Write a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively.