Seneca College
Applied Arts & Technology
School of computer studies
RIS-220
Lab
MULTI -TABLE SELECT
Write a query to display the last name, department number, and department name for all employees.
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
Create a unique listing of all jobs(employees table) that are in department 80. Include the location of the department (departments table) in the output.
SELECT DISTINCT job_id, location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND employees.department_id = 80;
Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission.
SELECT 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;
Display the employee last name and department name for all employees who have an a (lowercase) in their last names.
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND last_name LIKE ’%a%’;
Write a query to display the last name, job, department number, and department name for all employees who work in Toronto.
SELECT e.last_name, e.job_id, e.department_id,
d.department_name
FROM employees e, departments d, locations l
WHERE
e.department_id = d.department_id
AND
d.location_id = l.location_id
AND
LOWER(l.city) = 'toronto';
Display the employee name and employee number along with their manager’s name and manager employee number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. (You can not see King in this list)
Look closely to emp and dept tables. You will see that there is a department number 40 in department table data but if you trace this department in emp table you will see that nobody is working in this department. Now write a query show all employees with their empno, ename, dname along with the department 40 name (Even if nobody works there).
SELECT empno, ename, dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
Modify the same query and for empno and ename column for department 40 show ‘No Body Assigned’.
SELECT NVL(TO_CHAR(empno),'No Body is Assigned'), NVL(ename, 'No Body is Assigned'), dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
Look closely to departments and locations tables. There are more locations then departments as you can see. Now write a query and show department information with their postal code from location table. In your output show also all postal codes which are not assigned to any department.
SELECT DEPARTMENTS.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, LOCATIONS.POSTAL_CODE FROM DEPARTMENTS, LOCATIONS
WHERE LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID (+)
ORDER BY 1
Show the structure of the JOB table. Create a query that displays the name, job, department name, salary, and grade for all employees.
SELECT e.last_name, e.job_id, d.department_name,
e.salary, j.job_title
FROM employees e, departments d, job j
WHERE e.department_id = d.department_id
AND j.job_id = e.job_id;
