Bases De Datos

1. Write a query to display the name, department number, and department name for all Employees


Answer
SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

2.Create a unique listing of all jobs that are in department 30. Include the location of department 30 in the output.

Answer
SELECT DISTINCT (EMP.JOB), DEPT.LOC
FROM EMP, DEPT
WHERE DEPT.DEPTNO =30;

3. Write a query to display the employee name, department name, and location of all employees who earn a commission.

Answer
SELECT EMP.ENAME, DEPT.DNAME, DEPT.LOC
FROM EMP, DEPT
WHEREEMP.DEPTNO = DEPT.DEPTNO AND EMP.COMM IS NOT NULL;

4. Display the employee name and department name for all employees who have an A in their name. Save your SQL statement in a file called p4q4.sql.Answer
SELECT EMP.ENAME, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.ENAME LIKE ‘%A%’;

5. Write a query to display the name, job, department number, and department name forall employees who work in DALLAS.


Answer
SELECT EMP.ENAME, EMP.JOB, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND DEPT.LOC = ‘DALLAS’;

6. Display the employeename and employee number along with their manager’s name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Save your SQL statement in a file called p4q6.sql.Answer
SELECT E.ENAME “Employee”, E.MGR “Mgr#”, M.ENAME “Manager” , E.EMPNO “Emp#”
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO;

7. Modify p4q6.sql to display all employees including King, who hasno manager. Resave as p4q7.sql. Run p4q7.sql.


Answer
SELECT E.ENAME “Employee”, E.EMPNO “Emp#”, M.ENAME “Manager”, E.MGR “Mgr#”
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO(+);

8. Create aquery 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.


Answer
SELECT...