Question

Oracle Assingment

Introduction to databases
Topics :Restricting and sorting data

Create a query to display the last name and salary of employees earning more than 1200

Display the last name and salary for all employees whose salary is not in the range of 5,000 and 10,000

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.

Display the last name and department number of all employees in departments 20 and 50 in alphabetical order by name. list the last name and salary of employees who earn between $5,000 and $12,000, and are in department 20 or 50. Label the columns Employee and Monthly Salary, respectively.

Display the last name and hire date of every employee who was hired in 1994 Display the last names of all employees where the third letter of the name is an a.

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

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

Answer 1

QUERY FOR DISPLAYING

TOOK THE REFERENCE OF THE ORACLE ASSIGNMENT FOR THE SCHEMA OF THE GIVEN RELATION.

1 . Last Name And Salary Of Employees earning more than 1200 .

SELECT Last_name, Salary

FROM employees

WHERE salary > 1200;

2. Last name and Salary of Employees whose salary is not in the range 5000 and 10000

SELECT Last_name, Salary

FROM employees

WHERE Salary NOT BETWEEN 5000 AND 10000;

3. 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_id, start_date

FROM employees

WHERE start_date BETWEEN ’20-Feb-1998’ AND ’01-May-1998’ ORDER BY start_date;

4. 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;

5.  last name and salary of employees who earn between $5,000 and $12,000, and are in department 20 or 50.

SELECT last_name "Employee", salary "Monthly Salary"

FROM employees

WHERE salary BETWEEN 5000 AND 12000 AND department_id IN (20, 50);

6 . 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’;

7. 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%’;

8. 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%’;