Practice4 - Practice 4 SQL 1. Write a query to display the...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Practice 4 SQL 1. Write a query to display the name, department number, and department name for all employees SQL> select e.ename, 2 e.deptno, d.dname 3 from emp e, dept d 4 where e.deptno=d.deptno; ENAME DEPTNO DNAME---------- ---------- -------------- SMITH 20 RESEARCH ALLEN 30 SALES WARD 30 SALES JONES 20 RESEARCH MARTIN 30 SALES BLAKE 30 SALES CLARK 10 ACCOUNTING SCOTT 20 RESEARCH KING 10 ACCOUNTING TURNER 30 SALES ADAMS 20 RESEARCH ENAME DEPTNO DNAME---------- ---------- -------------- JAMES 30 SALES FORD 20 RESEARCH MILLER 10 ACCOUNTING 14 rows selected. 2. Create a unique listing of all jobs that are in department 30. SQL> select distinct(job) 2 from emp 3 where deptno=30; JOB--------- SALESMAN CLERK MANAGER 3. Write a query to display the employee name, department name, and location of all employees who earn a commission. SQL> select e.ename, d.dname, d.loc 2 from emp e , dept d 3 where 4 e.deptno=d.deptno 5 and 6 e.comm is NOT NULL; ENAME DNAME LOC---------- -------------- ----------- TURNER SALES CHICAGO MARTIN SALES CHICAGO WARD SALES CHICAGO ALLEN SALES CHICAGO 7. Modify p4q6.sql to display all employees including King, who has no manager. Resave as p4q7.sql. Run p4q7.sql SQL> ed/ SQL> edit Wrote file afiedt.buf 1 select w.ename "Employee" , w.empno "EmpNo", 2 m.ename "Manager" , m.empno "MgrNo" 3 from emp w left outer join emp m 4* on(w.mgr=m.empno) SQL> / Employee EmpNo Manager MgrNo---------- ---------- ---------- ---------- FORD 7902 JONES 7566 SCOTT 7788 JONES 7566 JAMES 7900 BLAKE 7698 TURNER 7844 BLAKE 7698 MARTIN 7654 BLAKE 7698 WARD 7521 BLAKE 7698 ALLEN 7499 BLAKE 7698 MILLER 7934 CLARK 7782 ADAMS 7876 SCOTT 7788 CLARK 7782 KING 7839 BLAKE 7698 KING 7839 Employee EmpNo Manager MgrNo---------- ---------- ---------- ----------...
View Full Document

Page1 / 7

Practice4 - Practice 4 SQL 1. Write a query to display the...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online