Q1: Give the SQL statement that performs an INNER JOIN operation on tables EMPLO
ID: 3694176 • Letter: Q
Question
Q1: Give the SQL statement that performs an
INNER
JOIN
operation on tables
EMPLOYEES
and
DEPARTMENTS
in the
HR
account using the
MANAGER
ID
as the join condition. The output should
consist of employee’s first and last names and the department id.
•
Q2: Give the SQL statement that performs a
LEFT
OUTER
JOIN
operation on tables
EMPLOYEES
and
DEPARTMENTS
in the
HR
account using the
MANAGER
ID
as the join condition. The output should
consist of employee’s first and last names and the department id.
•
Q3: Give the SQL statement that performs a
RIGHT
OUTER
JOIN
operation on tables
EMPLOYEES
and
DEPARTMENTS
in the
HR
account using the
MANAGER
ID
as the join condition. The output should
consist of employee’s first and last names and the department id.
•
Q4: Give the SQL statement that performs a
FULL
OUTER
JOIN
operation on tables
EMPLOYEES
and
DEPARTMENTS
in the
HR
account using the
MANAGER
ID
as the join condition. The output should
consist of employee’s first and last names and the department id.
•
Q5: Give three SQL statements that perform the same join operation: a
LEFT
OUTER
JOIN
on tables
EMPLOYEES
and
DEPARTMENTS
in the
HR
account using the
MANAGER
ID
as the join condition. The
output should consist of employee’s first and last names and the manager id.
•
Q6: Give the SQL statement that counts how many rows are in the output of a
FULL
OUTER
JOIN
operation on tables
EMPLOYEES
and
DEPARTMENTS
in the
HR
account using the
MANAGER
ID
as the
join condition.
Explanation / Answer
Q1:
Select emp-fname,emp-lname,dept-id
From employees e1,departments d1
Where e1.manager-id=e1.emp-id
And
e1.emp-id=d1.emp-id;
Q2:
Select emp-fname,emp-lname,dept-id
From employees e1 natural left outer join departments d1
Where e1.manager-id=e1.emp-id
And
e1.emp-id=d1.emp-id;
Q3:
Select emp-fname,emp-lname,dept-id
From employees e1 natural right outer join departments d1
Where e1.manager-id=e1.emp-id
And
e1.emp-id=d1.emp-id;
Q4:
Select emp-fname,emp-lname,dept-id
From employees e1 full outer join departments d1
Where e1.manager-id=e1.emp-id
And
e1.emp-id=d1.emp-id;
Q5:a)
Select emp-fname,emp-lname,emp-manager-id
From employees e1,e2 natural left outer join departments d1
Where e1.manager-id=e1.emp-id
And
e1.emp-id=e2.emp-id;
b)
Select emp-fname,emp-lname,emp-manager-id
From employees e1,e2 natural right outer join departments d1
Where e1.manager-id=e1.emp-id
And
e1.emp-id=e2.emp-id;
c)
Select emp-fname,emp-lname,emp-manager-id
From employees e1,e2 full outer join departments d1
Where e1.manager-id=e1.emp-id
And
e1.emp-id=e2.emp-id;
Q6:
Select count(*)
From employees e1,e2 full outer join departments d1
Where e1.manager-id=e1.emp-id
And
e1.emp-id=e2.emp-id;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.