Write a query to display the name, department number, and department name for al

Question

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

1. SELECT e.first_name, e.last_name, e.department_id, d.department_name
FROM employees e, departments d WHERE e.department_id = d.department_id;

2. SELECT DISTINCT job_id, location_id FROM employees e, departments d
WHERE e.department_id = d.department_id AND e.department_id = 30;

3. SELECT e.first_name, e.last_name, d.department_name, d.location_id, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id AND e.commission_pct IS NOT NULL;

4. SELECT first_name, last_name, department_name FROM employees e, departments d
WHERE e.department_id = d.department_id AND last_name LIKE ’%A%’;

5. SELECT e.first_name, e.last_name, e.job_id, e.department_id,
d.department_name
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
JOIN locations l
ON (d.location_id = l.location_id)
WHERE UPPER(l.city) = ’DALLAS’;

6. SELECT e1.first_name, e1.last_name "Employee", e1.employee_id "EMP#",
e2.last_name "Manager", e2.employee_id "Mgr#"
FROM employees e1 join employees e2
ON (e1.manager_id = e2.employee_id);

7. SELECT e1.first_name, e1.last_name "Employee", e1.employee_id "EMP#",
e2.last_name "Manager", e2.employee_id "Mgr#"
FROM employees e1 LEFT OUTER JOIN employees e2
ON (e1.manager_id = e2.employee_id);

8. SELECT e1.department_id department, e1.first_name, e1.last_name employee,
e2.last_name colleague
FROM employees e1 JOIN employees e2
ON (e1.department_id = e2.department_id)
WHERE e1.employee_id <> e2.employee_id
ORDER BY e1.department_id, e1.first_name, e1.last_name, e2.last_name;

9. SELECT e1.last_name, e1.hire_date, e2.last_name, e2.hire_date

FROM employees e1 JOIN employees e2
ON (e1.manager_id = e2.employee_id)
WHERE e1.hire_date < e2.hire_date;

10. SHOW columns from SALGRADE;

(OR)

Describe SALGRADE;

11. SELECT e.first_name, e.last_name, e.job, d.dname, e.sal, j.grade

FROM employees e, department d, salgrade s

WHERE e.deptno=d.deptno and e.sal between s.losal and s.hisal;

12. SELECT e.first_name, e.last_name, e.hire_date
FROM employees e, employees e1
WHERE e1.last_name = ’Blake’
AND e1.hire_date < e.hire_date

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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