Hjgbjhhljk

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