Hjgbjhhljk

Only available on StudyMode
  • Topic: SQL, Employment, Articles with example SQL code
  • Pages : 7 (2358 words )
  • Download(s) : 71
  • Published : December 1, 2012
Open Document
Text Preview
SQL Exercises (with answers) Give the SQL commands for the following and answer any associated questions: (A) “Simple” SELECT Command Questions 1. Display all information in the tables EMP and DEPT. SELECT * FROM emp; SELECT * FROM dept; 2. 3. 4. 5. 6. 7. Display only the hire date and employee name for each employee. SELECT hiredate, ename FROM emp; Display the hire date, name and department number for all clerks. SELECT hiredate, ename, deptno FROM emp WHERE job = ’CLERK’; Display the names and salaries of all employees with a salary greater than 2000. SELECT ename, sal FROM emp WHERE sal > 2000; Display the names of all employees with an ‘A’ in their name. SELECT ename FROM emp WHERE ename LIKE ’%A%’; Display the names of all employees with exactly 5 letters in their name. SELECT ename FROM emp WHERE ename LIKE ’ ’; Display the names and hire dates of all employees hired in 1981 or 1982 (Note in Visual Basic or Access SQL you need to refer to dates in a WHERE clause between #s, eg. #1 Jan 2000#). SELECT ename, hiredate FROM emp WHERE hiredate LIKE ’%1981’ OR hiredate LIKE ’%1982’; —OR— SELECT ename, hiredate FROM emp WHERE hiredate >= ’1/1/1981’ AND hiredate 0; 1

8.

3.

4. 5.

Display the department number and number of clerks in each department. SELECT deptno, count(job) FROM emp WHERE job = ’CLERK’ GROUP BY deptno; Display the department number and total salary of employees in each department that employs four or more people. SELECT deptno, sum(sal) FROM emp GROUP BY deptno HAVING count(empno) >= 4; Display the employee number of each employee who manages other employees with the number of people he or she manages. SELECT mgr, count(mgr) FROM emp WHERE mgr IS NOT NULL GROUP BY mgr;

6.

(C) Join SELECT Command Questions 1. 2. 3. Display the name of each employee with his department name. SELECT ename, dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno; Display a list of all departments with the employees in each department. SELECT dname, ename FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno; Display all the departments with the manager for that department. SELECT dname, ename FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE job = ’MANAGER’; 4. 5. Display the names of each employee with the name of his/her boss. SELECT s.ename, b.ename FROM emp s INNER JOIN emp b ON s.mgr = b.empno; Display the names of each employee with the name of his/her boss with a blank for the boss of the president. SELECT s.ename, b.ename FROM emp s LEFT OUTER JOIN emp b ON s.mgr = b.empno; Display the employee number and name of each employee who manages other employees with the number of people he or she manages. SELECT a.mgr, b.ename, count(a.mgr) FROM emp a INNER JOIN emp b ON a.mgr = b.empno WHERE a.mgr IS NOT NULL GROUP BY a.mgr, b.ename; 7. Repeat the display for the last question, but this time display the rows in descending order of the number of employees managed. SELECT a.mgr, b.ename, count(a.mgr) FROM emp a INNER JOIN emp b ON a.mgr = b.empno WHERE a.mgr IS NOT NULL GROUP BY a.mgr, b.ename ORDER BY count(a.mgr) DESC; (D) SELECT with Subqueries Questions 1. Display the names and job titles of all employees with the same job as Jones. SELECT ename, job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = ’JONES’); 2. Display the names and department name of all employees working in the same city as Jones. SELECT ename, dname FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno WHERE loc = (SELECT loc FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno WHERE ename = ’JONES’); Display the name of the employee whose salary is the lowest. SELECT ename FROM emp WHERE sal = (SELECT min(sal) FROM emp); 4. 5. Display the names of all employees except the lowest paid. SELECT ename FROM emp WHERE sal > (SELECT min(sal) FROM emp); Display the names of all employees whose job title is the same as anyone in the sales dept. SELECT ename FROM emp WHERE job IN (SELECT DISTINCT job FROM emp INNER JOIN...
tracking img