SQLsol - SQL Exercises (with answers) Give the SQL commands...

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: 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. Display only the hire date and employee name for each employee. SELECT hiredate, ename FROM emp; 3. Display the hire date, name and department number for all clerks. SELECT hiredate, ename, deptno FROM emp WHERE job = ’CLERK’; 4. Display the names and salaries of all employees with a salary greater than 2000. SELECT ename, sal FROM emp WHERE sal > 2000; 5. Display the names of all employees with an ‘A’ in their name. SELECT ename FROM emp WHERE ename LIKE ’%A%’; 6. Display the names of all employees with exactly 5 letters in their name. SELECT ename FROM emp WHERE ename LIKE ’ ’; 7. 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 <= ’31/12/1982’; 8. Display the names and dates of employees with the column headers “Name” and “Start Date” SELECT ename AS "Name", hiredate AS "Start Date" FROM emp; 9. Display the names and hire dates of all employees in the order they were hired. SELECT ename, hiredate FROM emp ORDER BY hiredate; 10. Display the names and salaries of all employees in reverse salary order. SELECT ename, sal FROM emp ORDER BY sal DESC; 11. Display ‘ ename of department deptno earned commission $’ for each salesman in reverse salary order. SELECT ename || ’ of department ’ || deptno || ’ earned commission $’ || comm FROM emp WHERE job = ’SALESMAN’ ORDER BY sal DESC; 12. Display the department numbers of all departments employing a clerk. SELECT DISTINCT deptno FROM emp WHERE emp.job = ’CLERK’; (B) Group SELECT Command Questions 1. Display the maximum, minimum and average salary and commission earned. SELECT max(sal), min(sal), avg(sal), max(comm), min(comm), avg(comm) FROM emp; 2. Display the department number, total salary payout and total commission payout for each depart- ment. SELECT deptno, sum(sal), sum(comm) FROM emp GROUP BY deptno; 3. Display the department number, total salary payout and total commission payout for each depart- ment that pays at least one employee commission. SELECT deptno, sum(sal), sum(comm) FROM emp GROUP BY deptno HAVING sum(comm) > 0; 1 4. Display the department number and number of clerks in each department. SELECT deptno, count(job) FROM emp WHERE job = ’CLERK’ GROUP BY deptno; 5. Display the department number and total salary of employees in each department that employs four or more people....
View Full Document

Page1 / 7

SQLsol - SQL Exercises (with answers) Give the SQL commands...

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