Restricting and Sorting Data

• Create a query to display the last name and salary of employees earning more than $12,000.

SELECT last_name, salary 

FROM employees

WHERE salary>12000;

•  Create a query to display the employee last name and job code for employee number 176.

SELECT last_name, job_id 

FROM employees 

WHERE employee_id=176;

• Create a query to display the last name and salary form all employees whose salary is not in the range of $5,000 and $12,000.

SELECT last_name, salary 

FROM employees 

WHERE salary NOT BETWEEN 5000 AND 12000;

•  Display the employee last_name, Job ID, and start date of employees hired between February 20, 1998, and May 1, 1998. Order the query in ascending order by start date.

SELECT last_name, job_code, hire_date 

FROM employees 

WHERE hire_date BETWEEN '20-Feb-98' AND '01-May-98' 

ORDER BY hire_date; 

•  Display the last name and department number of all employees in departments 20 and 50 in alphabetical order by name.

SELECT last_name, department_id 

FROM employees 

WHERE department_id IN (20, 50) 

ORDER BY last_name; 

•  Create a query to list the last name and salary of all employees who earn between $5,000 and $12,000, and are in department 20 or 50. Label the column Employee and Monthly Salary respectively. 

SELECT last_name "Employee", salary "Monthly Salary" 

FROM employees 

WHERE salary BETWEEN 5000 AND 12000 

AND department_id IN (20, 50);

• Display the last name and hire date of every employee who was hired in 1994.

SELECT last_name, hire_date 

FROM employees 

WHERE hire_date LIKE '%94'; 

•  Display the last name and job title of all employees who do not have a manager.

SELECT last_name, job_id 

FROM employees 

WHERE manager_id IS NULL;

•  Display the last name, salary and commission for all employees who earn commissions. Sort the date in descending order of salary and commissions.

SELECT last_name, salary, commission_pct 

FROM employees 

WHERE commission_pct IS NOT NULL 

ORDER BY salary DESC, commission_pct DESC;

• Display the last names of all employees where the third letter of the name is an a 

SELECT last_name 

FROM employees 

WHERE last_name LIKE '__a%' ;

•  Display the last name of all employees who have an a and an e in their last name.

SELECT last_name 

FROM employees 

WHERE last_name LIKE '%a%' 

AND last_name LIKE '%e%' ;

•  Display the last name, job and salary for all employees whoso job is sales representative or stock clerk and whose salary is not equal to $2,500, $3,500 or $7,000.

SELECT last_name, job_id, salary 

FROM employees 

WHERE job_id IN ('SA_REP', 'ST_CLERK') 

AND salary NOT IN (2500, 3500, 7000);

•  Display the last name, salary and commission for all employees hose commission amount is 20%.

SELECT last_name, salary, commission_pct 

FROM employees 

WHERE commission_pct = .20 ;