Obtaining data from more than one table using various types of joins including E

Question

Obtaining data from more than one table using various types of joins including Equijoins, Non-Equijoins,

Outer Joins and Self Joins.

Write a query to display the name, department number, and department name for all employees.

Create a unique listing of all jobs that are in department 30. Include the location of the department 30 in the output.

Write a query to display the employee name, department name, and location of all employees who earn a commission.

Display the employee name and department name for all employees who have an 'A' in their name.

Write a query to display the name, job, department number, and department name for all employees who work in DALLAS.

Display the employee name and employee number along with their manager's name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.

Modify the previous query to display all employees including King, who has no manager. Note, the 3rd and 4th fields for King will display as empty.

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.

Show the structure of the SALGRADE table.

Create a query that will display the name, job, department name, salary, and grade for all employees.

Create a query to display the name and hire date of any employee hired after employee Blake.

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 Hire Date, Manager, and Mgr Hire Date, respectively.

Explanation / Answer

If you have any doubts, please give me comment...

-- 1)

SELECT E.last_name, E.department_id, D.department_name

FROM employees E INNER JOIN departments D ON E.department_id = D.department_id;

-- 2)

SELECT DISTINCT job_id, location_id

FROM employees E INNER JOIN departments D ON E.department_id = D.department_id

WHERE E.department_id = 80;

--3)

SELECT E.last_name, D.department_name, D.location_id, L.city

FROM employees E INNER JOIN departments D ON E.department_id = D.department_id INNER JOIN locations L ON D.location_id = L.location_id

WHERE E.commission_pct IS NOT NULL;

---4)

SELECT last_name, department_name

FROM employees E INNER JOIN departments D ON E.department_id = D.department_id

WHERE last_name LIKE '%A%';

-- 5)

SELECT E.last_name, E.job_id, E.department_id, D.department_name

FROM employees E INNER JOIN departments D ON (E.department_id = D.department_id) INNER JOIN locations L ON (D.location_id = L.location_id)

WHERE UPPER(l.city) = 'DALLAS';

-- 6)

SELECT W.last_name "Employee", W.employee_id "EMP#", M.last_name "Manager", M.employee_id "Mgr#" FROM employees W join employees M ON (W.manager_id = M.employee_id);

-- 7)

SELECT W.last_name "Employee", W.employee_id "EMP#", M.last_name "Manager", M.employee_id "Mgr#"

FROM employees W LEFT OUTER JOIN employees M ON W.manager_id = M.employee_id;

-- 8)

SELECT E.department_id department, E.last_name employee,

C.last_name colleague

FROM employees E INNER JOIN employees C ON e.department_id = c.department_id

WHERE E.employee_id <> C.employee_id

ORDER BY E.department_id, E.last_name, C.last_name;

-- 9)

DESC SALGRADE;

--10)

SELECT last_name, firstname, job, department_name, salary, grade

FROM employees E INNER JOIN departments D ON E.employee_id = D.employee_id;

--11)

SELECT E.last_name, E.hire_date

FROM employees E JOIN employees E1 ON E1.last_name = 'Blake'

WHERE E1.hire_date < E.hire_date;

-- 12)

SELECT W.last_name, W.hire_date, M.last_name, M.hire_date

FROM employees W INNER JOIN employees M ON W.manager_id = M.employee_id

WHERE W.hire_date < M.hire_date;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.