Doe anyone know how to write these SQL queries?

Question

How would one write the following queries?

1.Write a hierarchical query to display the organization chart of the employees table.
Ensure the query starts with the employee who has no manager. Make the query
return the employee first and last names along with the last name of his/her manager

2 Write a query that displays the department_id, job_id and the total salary for each
job_id within a department. Make the query return subtotals of salary per
department_id and a grand total of all salaries across the company. Use the
EMPLOYEES table for this query.

3. Write a query that will combine the following columns: employee first and
last_name, department_id, job_id, salary from the employees table, the employee_id,
start_date, end_date and job_id from job_history and the department_name and
location_id from the departments table. The queries should return one single output.

Answer 1

First off, this is not a question - it's merely a repeat of a homework assignment.

Secondly, if you want help regarding SQL, give us some info regarding your schema...we have no idea how your tables are defined.

Thirdly, the first problem would likely involve multistep scripting rather than simple SQL, so we'd need to know what DBMS you're using.

Answer 2

1. The question wants something specific to what you were taught, or there's some detail missing we need to know. There is no such thing as a generic "hierarchical query". Also, hierarchies are defined in relational databases using nested sets or adjacency lists, not a single table. Not matter how fancy your query is, what happens in this not so contrived example?



INSERT INTO employees (name, manager) values ('Bob', 'Sarah');

INSERT INTO employees (name, manager) values ('Sarah', 'Bob');

INSERT INTO employees (name, manager) values ('Sarah', 'Sarah');



2.

SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY (department_id, job_id);

SELECT department_id, SUM(salary) FROM employees GROUP BY (department_id);

SELECT SUM(salary) FROM employees;



3. This is simply a join on the three tables.

SELECT employee_id, first_name, last_name, salary, job_id, start_date, end_date, department_id, department_name, location_id FROM employees emp

LEFT JOIN job_history job ON emp.employee_id = job.employee_id

LEFT JOIN departments dep ON emp.department_id = dep.department_id;



We're working off a spurious design, btw. A job belongs to a department; an employee only temporarily belongs to a department. The job_history table will contain detached dependencies if an employee switches departments with that schema.

Answer 3

This is not a question, this is "do all my work for me because I'm too lazy to look anything up"

Answer 4

Teach yourself at http://www.sql-ex.ru/