SQL Practice Exercise: Single Entity Database(Employee)


SELECT statement:                    A few functions for column specifications:
   SELECT select_list                CONCAT (string1[, string2]...)
   [FROM table_source]               FORMAT(number, decimal_places)
   [WHERE search_condition]          ROUND(number[, decimal_places])
   [ORDER BY order_by_list]
   [LIMIT [offset,] row_count]
   
Project and Restrict operations:
 1. Select all the data from the EMPLOYEE table.

 2. List the first five rows in EMPLOYEE.
   
 3. Show the last five rows in EMPLOYEE.
   
 4. Project one column composed of employee name concatenated with job. 
    Use a column alias.
   
 5. Retrieve ENAME, SALARY, and a calculated value for updated salary that
    has a "COLA" increase equivalent to 2% of SALARY. Show two decimal places.
   
 6. List all EMPLOYEE columns from rows where the DEPTNO = 30.
   
 7. Access EMPLOYEE to Prepare a list of 'MANAGER's with ENAME, JOB and
    SALARY.  Restrict output to those rows with SALARY > $3,500.

 8. Query EMPLOYEE to project ENAME, JOB, and SALARY restricting rows to
    all managers plus all other employees earning over $3,500.

 9. Again prepare a list of managers with ENAME, JOB, and SALARY 
    but exclude those from DEPTNO 30.

10. List employee name and salary for those with Salary in the range $1600
    to $3990.

11. Find employees that have a DEPTNO in the "list" (10,30).

12. Make a list of employees having the letter 'R' in their name.

13. List SALARY, JOB, ENAME for employees in order by SALARY.

14. Repeat (13) in descending order by SALARY within JOB (ascending).  

15. Project JOB from EMPLOYEE with duplicates removed.

EMPLOYEE data(tab delimited):    
EMPNO   ENAME   JOB         MANAGER HIREDATE    SALARY  DEPTNO 
7369    SMITH   CLERK       7902    1995-12-17  1064.00 20 
7499    ALLEN   SALESMAN    7698    1996-02-20  2128.00 30 
7521    WARD    SALESMAN    7698    1996-02-22  1662.50 30 
7566    JONES   MANAGER     7839    1996-04-02  4000.00 20 
7654    MARTIN  SALESMAN    7698    1996-09-28  1662.50 30 
7698    BLAKE   MANAGER     7839    1996-05-01  4000.00 30 
7782    CLARK   MANAGER     7839    1996-06-09  4000.00 10 
7788    SCOTT   ANALYST     7566    2002-04-19  3990.00 20 
7839    KING    PRESIDENT   \N      1980-11-17  \N      10 
7844    TURNER  SALESMAN    7698    1996-09-08  1995.00 30 
7876    ADAMS   CLERK       7788    2002-05-23  1463.00 20 
7900    JAMES   CLERK       7698    1996-12-03  1263.50 30 
7902    FORD    ANALYST     7566    1996-12-03  3990.00 20 
7934    MILLER  CLERK       7782    1997-01-23  1729.00 10