Obtaining data from more than one table using various types of joins including E
ID: 3742749 • Letter: O
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
1.
Select First_name,Last_name,Departments.department_ID,Department_Name from employees inner join departments on employees.Department_ID = departments.Department_ID;
2.
Select distinct jobs.Job_ID,locations.Location_ID from jobs inner join inner join job_history on jobs.Job_ID = job_history.job_Id inner join Departments on Departments.Department_ID = job_history.Department_ID inner join locations on locations.Location_ID = departments.Location_ID where departments.Department_ID = 30;
3.
Select First_name,Last_name,Department_Name,locations.Location_ID from employees inner join departments on employees.Department_ID = departments.Department_ID inner join locations on locations.Location_ID = departments.Location_ID where Commission_Pct IS NOT NULL;
4.
Select First_name,Last_name,Department_Name from employees inner join departments on employees.Department_ID = departments.Department_ID where First_name LIKE '%A%' or First_name LIKE '%a%';
5.
Select First_name,Last_Name,jobs.Job_ID,departments.Department_ID ,Department_Name from jobs inner join inner join job_history on jobs.Job_ID = job_history.job_Id inner join Departments on Departments.Department_ID = job_history.Department_ID inner join locations on locations.Location_ID = departments.Location_ID where city = 'DALLAS';
6.
Select (Emp1.First_Name||Emp1.Last_Name) as Employee ,Emp1.Employee_ID as Emp# , (Emp2.First_Name|| Emp2.Last_Name) as Manager, Emp2.Employee_ID as Mgr# from employees Emp1 inner join employees Emp2 on Emp1.Manager_ID = Emp2.Employee_ID;
7.
Select (Emp1.First_Name||Emp1.Last_Name) as Employee ,Emp1.Employee_ID as Emp# , (Emp2.First_Name|| Emp2.Last_Name) as Manager, Emp2.Employee_ID as Mgr# from employees Emp1 left join employees Emp2 on Emp1.Manager_ID = Emp2.Employee_ID;
8.
SELECT e1.department_id as Department ,e1.First_Name,e1.Last_Name 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.last_name, e1.last_name;
9.
describe SALGRADE;
10.
Select First_name,Last_Name,jobs.Job_ID,Department_Name,salary,grade from jobs,job_history , Departments ,SalGrade WHERE employees.sal BETWEEN SalGrade.losal AND SalGrade.hisal;
11.
Select First_Name,Last_Name,Hire_Date from employees where Hire_Date >(Select Hire_Date from employees where Last_Name = 'Blake');
12.
Select (Emp1.First_Name||Emp1.Last_Name) as Employee ,Emp1.Hire_Date as EmpHireDate , (Emp2.First_Name|| Emp2.Last_Name) as Manager, Emp2.Hire_Date as MgrHireDate from employees Emp1 inner join employees Emp2 on Emp1.Manager_ID = Emp2.Employee_ID;
Do ask if any doubt. Please upvote.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.