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';
“OR”
SELECT hiredate, ename, deptno FROM emp WHERE UPPER( job) LIKE %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 length(ename)=5
NOTE: length function return the length of the string
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 TO_CHAR(hiredate,’YYYY’) IN (1981,1982)
“OR”
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';
NOTE: TO_CHAR(hiredate,’YYYY’) return the year
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;
NOTE: ORDER BY default order is ASC .if needed the data in descending order then use DESC along with column name.
ORDER BY COLUMN_NAME DESC/ASC
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;
NOTE: Instead of pipe(||) we can use nested CONCAT funcation .
12. Display the department numbers of all departments employing a clerk.
SELECT DISTINCT deptno FROM emp WHERE emp.job = 'CLERK';
I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebook, twitter
