INTERVIEW QUESTIONS- RESTRICTING AND SORTING DATA

What are all the operators available in oracle?

AND , OR , NOT , < , <= , >, >=  

Difference between IN and EXISTS ? Which one is more Faster? Why?

IN OPERATOR

The IN ( ... ) is actually translated by Oracle server to a set of OR conditions: a = value1 OR a = value2 OR a = value3. So using IN ( ... ) has no performance benefits, and it is used for logical simplicity. 

EXISTS OPERATOR

The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns true if the subquery returns one or more records.

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

Which operator is used for pattern matching or to do wildcard search?

LIKE  operator is used to pattern matching or wildcard search

Write a query to display all the name which starts with S.

Select first_name from employees where first_name like 'S%';

Write a query to display all the name starts with S and ends with character n.

Select first_name from employees where first_name like 'S%n';

Write a query to display all the employees who are all working for department 90 and their name must starts with S.

Select first_name from employees where department_id=90 and first_name like 'S%';

Display all the job id which contain _ (underscore) as 3rd character.

Select first_name ,job_id from employees where job_id like '__/_%' escape '/';

Write a query to print all the first_name which contains five characters.

Select first_name from employees where length(first_name)=5;

Write a query to display all the employees who are all working in department 10,20,50 and 90.

Select * from employees where department_id in(10,20,50,90);

Write a query to display first name, salary and department id of the employees who are all not working for 10,20,50 and 90.

Select first_name,salary,department_id from employees where department_id not in(10,20,50,90);

Display all the employees who are all hired in year 1994.

Select * from employees where substr(hire_date,-2,2) = '94'

Write a query to display who are all getting salary between 5000 and 7000.

Select * from employees where salary between 5000 and 7000;
Select first_name, Salary from employees where salary between 5000 and 7000;

Display First_name, salary, department_id and manager_id of the employee who don't have manager.

Select first_name, salary, department_id,manager_id from employees where manager_id is null;

Display all the records in employees table and sort the first name in ascending order.

Select * from employees order by first_name asc;

Display first name, department id and salary from employees table and sort the records ( sort department id in ascending order and salary in descending order)    

Select First_name, department_id,salary from employees order by department_id asc, salary desc;

What is the default ordering of an ORDER BY clause in a SELECT statement .

FROM
WHERE
SELECT 
ORDER BY 

Create a query to display the last name and salary of employees earning more than $12,000. Place your SQL statement in a text file named lab2_1.sql. Run your query.

SELECT Last_name, Salary from employees where salary >12000;

Create a query to display the employee last name and department number for employee number 176.

Select last_name, department_id from employees where employee_id = 176;

Modify lab2_1.sql to display the last name and salary for all employees whose salary is not in the range of $5,000 and $12,000. Place your SQL statement in a text file named

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_id, hire_date from employees where hire_date between '02/20/1998' and '06/01/1998' order by hire_date asc;

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;

Modify lab2_3.sql to 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. Resave lab2_3.sql as lab2_6.sql. Run the statement in lab2_6.sql.

select Last_name " Employee", salary "Monthly Salary"  from employees where salary between 5000 and 12000 and (department_id=20 or department_id=50);

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

Select last_name,hire_date from employees where substr(hire_date,-2,2)=94;

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

select *from employees;
Select last_name,job_id,manager_id from employees where manager_id is null;

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

Select last_name,salary, commission_pct from employees where commission_pct<>0 order by salary 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%e%';

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.

Select last_name,job_id, salary from employees;
Select last_name,job_id, salary from employees where job_id in ('SA_REP','ST_CLERK');
Select last_name,job_id, salary from employees where job_id in ('SA_REP','ST_CLERK') and Salary not in (2500,3500,7000); 

Modify lab2_6.sql to display the last name, salary, and commission for all employees whose commission amount is 20%. Resave lab2_6.sql as lab2_13.sql. Rerun the statement in lab2_13.sql.

select last_name, salary,commission_pct from  employees where commission_pct = .2;