JOINS INTERVIEW QUESTIONS

What is joins? What are all the difference types of joins available?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Equi join,

outer join,

  left outer join 

  right outer join

  full outer join

Self Join

Cross Join

Natural Join

  

Explain outer join and its types with example.

outer join 

  left outer join -Matched Records from left side table 

  right outer join - Matched Records from right side table

  full outer join- Matched and Unmatched Records from both side. 

What is Self Join and why is it required?

With in the table join performed means its self join. 

if one column is referred by another column with in the table means its required. 

What is the difference between inner and outer join? Explain with example. 

Inner Join: Its not include the unmatched records.

outer join: Its show unmatched records also.

What is a Cartesian product.

Cartesian Product means it compares two or more table result m*n output. ex if the table contains 5 row and next table contains 6 rows means the output returns 30 rows.

 

If I try to Fetch data from 25 tables. How many number of join condition required?

If N number table means N-1 number of conditions needed 

so 24 join conditions needed to fetch in the table.

Write a query to display the last name, department number, and department name for all employees

select e.last_name, d.department_id,d.department_name
from employees e,departments d
where e.department_id= d.department_id;

Create a unique listing of all jobs that are in department 30. Include the location of department 90 in the output.

select distinct e.job_id,d.location_id 
from  employees e,  departments d
where e.department_id = d.department_id
and d.department_id=80; 

Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission. 

select e.commission_pct,e.last_name, d.department_name,d.location_id,l.city
from employees e, departments d,locations l
where e.department_id=d.department_id 
and d.location_id=l.location_id and commission_pct is not null;

Display the employee last name and department name for all employees who have an a (lowercase) in their last names. Place your SQL statement in a text file named lab4_4.sql.

select lower(e.last_name), d.department_name 
from employees e, departments d
where e.department_id=d.department_id 
and last_name like '%a';  

Write a query to display the last name, job, department number, and department name for all employees who work in Toronto. 

select e.last_name,e.job_id,e.department_id,d.department_name 
from employees e, departments d,locations l
where e.department_id = d.department_id and d.location_id=l.location_id and l.city= 'Toronto'; 

Display the employee last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Place your SQL statement in a text file named lab4_6.sql.

select e.last_name "Employee",e.employee_id "Emp#" ,m.last_name 
"Manager",m.employee_id"Mgr#"
from employees e , employees m
where e.manager_id=m.employee_id; 

Modify lab4_6.sql to display all employees including King, who has no manager. Place your SQL statement in a text file named lab4_7.sql. Run the query in lab4_7.sql 

select e.last_name "Employee",e.employee_id "Emp#" ,m.last_name 
"Manager",m.employee_id"Mgr#" from employees e, employees m
where e.manager_id=m.employee_id(+) order by e.employee_id;

Create a query that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label.

select e.department_id,e.last_name,m.last_name
from employees e full join employees m
on e.department_id=m.department_id 
where e.employee_id<>m.employee_id 
order by department_id;

JOINS IN SQL

JOINS

•  EQUI JOIN 
•  OUTER JOIN

1.     RIGHT OUTER JOIN
2.     LEFT OUTER JOIN
3.     FULL OUTER JOIN

• SELF JOIN 
• CROSS JOIN

EQUI joins

Matched Records from both table

select * from std;
select * from course;

select sname, cname  
from std, course
where std.cid=course.cid;

ALIAS NAME IN JOINS

select s.sname, c.cname  
from std s, course c
where s.cid=c.cid;

OUTER JOINS

LEFT OUTER JOIN

Its display unmatched records from left hand side.if mentionion left outer join using right 

hand side of the employees

select sname, cname  
from std, course
where std.cid=course.cid(+);

RIGHT OUTER JOIN

Its display unmatched records from right hand side.if mentionion left outer join using left 

hand side of the employees

select sname, cname  
from std, course
where std.cid(+)=course.cid;

FULL OUTER JOIN

Its display both matched and unmatched records from the table . In the full outer join the syntax is

Replacing where clause by on clause conditions are to be entered after the on clause Join is mentioned after the from clause 

Select col1,col2
from tab1 Full Join tab2 
on conditions 

select sname, cname  
from std full outer join course
on std.cid=course.cid;

Full Outer join with 3 tables

select e.first_name, e.department_id, d.location_id,l.city  
from employees e full outer join departments d  
on e.department_id=d.department_id 
full outer join locations l 
on d.location_id= l.location_id  

select sname,cname
from std, course;

SELF JOIN 

With in the table perform join conditions means its a self join

consider one example 

select employee_id, first_name,manager_id from employees;

select e1.first_name"Employee Name" ,e2.first_name "Manager Name"
from employees e1,employees e2
where e1.manager_id = e2.employee_id order by 1;

select * from std;
select * from course;
select * from faculty;

select s.sname,c.cname,f.fname
from std s, course c, faculty f
where s.cid=c.cid
and  c.cid=f.cid;

CROSS JOIN

Cross join is a cartesian product no of rows in the first table is joined no of rows in a second table.

Example :

With out mentining any join conditions in query is retrieve all the records with the 

combination of two or more tables

Select s.sname,c.cname 
from stud s,course c

NOTES: IF COMPARING N NUMBER OF TABLES AND PERFORMS JOINS MEANS IN WHERE CLAUSE N-1 

CONDITIONS.

n number of table 
n-1 of condition

GROUP FUNCTIONS INTERVIEW QUESTIONS

Group functions work across many rows to produce one result.

True

Group functions include nulls in calculations. 

False. Group functions ignore null values. If you want to include null values, use the NVL function. 

The WHERE clause restricts rows prior to inclusion in a group calculation.

True

Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. Place your SQL statement in a text file named lab5_6.sql.

SELECT   ROUND(MAX(salary),0) "Maximum", ROUND(MIN(salary),0) "Minimum", ROUND(SUM(salary),0) "Sum", ROUND(AVG(salary),0) "Average" FROM     employees;

Modify the query in lab5_4.sql to display the minimum, maximum, sum, and average salary for each job type. Resave lab5_4.sql to lab5_5.sql. Run the statement in lab5_5.sql.

SELECT   job_id, ROUND(MAX(salary),0) "Maximum", ROUND(MIN(salary),0) "Minimum", ROUND(SUM(salary),0) "Sum", ROUND(AVG(salary),0) "Average" FROM     employees GROUP BY job_id;

Write a query to display the number of people with the same job. 

Select job_id,count(job_id) from employees group by job_id;

Determine the number of managers without listing them. Label the column Number of Managers. Hint: Use the MANAGER_ID column to determine the number of managers

select count( distinct manager_id) " Number of Managers" from employees;

Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE.

select Max(salary)-Min(Salary) " Difference" from employees;

Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is less than $6,000. Sort the output in descending order of salary.

Select manager_id,min(salary)from employees where manager_id is not null group by manager_id having Min(salary)>6000 order by min(salary) desc;

Write a query to display each department’s name, location, number of employees, and the average salary for all employees in that department. Label the columns Name, Location, Number of People, and Salary, respectively. Round the average salary to two decimal places.

select d.department_name"NAME",d.location_id "LOCATIONS", count(*)"NUMBER OF PEOPLE" ,round(avg(e.salary),2) "SAL" from departments d, employees e  where e.department_id=d.department_id group by d.department_name,d.location_id;

Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings.

SELECT  COUNT(*) total, 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),2005,1,0))"1995", 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0))"1996", 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0))"1997", 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0))"1998" FROM    employees;

SINGLE ROW FUNCTIONS INTERVIEW QUESTIONS

What are the types of functions available in oracle.

-Single Row Function
  Case MAnipulation Function
  Character Manipulation 
  Genaral funtion 
  Number Function
  Date Function

-Multiple Row Function
  Group Functions

Difference between single row function and multiple row function.

Single Row Function 
Input Sinlge Row 
Output Single Row 

Multiple Row Function
Input Multiple Rows
Output Single Rows

List out all the case and character functions.

CASE MANIPULATION FUNCTION

UPPER()
LOWER()
INITCAP()

CHARACTER MANIPULATION FUNCTION

SUBSTR()  INSTR()
LTRIP()   RTRIM()
LPAD()    RPAD()
REPLACE() TRANSLATE()
LENGTH()
CONCAT()
REVERSE()

Display first three characters from first name.

SELECT substr(First_name,1,3)"First_Name" FROM employees;

Display last two character from last name.

SELECT substr(last_name,-2,2)"LAST_NAME" FROM Employees;

Display all the first name and position of a in that name (first occurrence of a).    

SELECT First_name, instr(first_name,'a',1) FROM employees;

Display all the first name and position of a in that name (second occurrence of a)

SELECT First_Name,instr(First_name,'a',1,2) FROM employees;  

Display all the name which contain two or more number of a 's in the first name.

SELECT First_Name,instr(First_name,'a',1,2) from employees where instr(First_name,'a',1,2)<>0 

Difference between SUBSTR and INSTR function.

SUBSTR :
It returns a specified portion of a string 

INSTR
It returns a character position(ie occurances of the character).

Difference between REPLACE and TRANSLATE function.

Replace:The Replace Function replaces single character with multiple characters. 

Translate: Translate Function replaces single character with single character only.

Difference between LPAD and RPAD.

LPAD : To adjust the left hand side padding of charecters,numbers.
RPAD : To adjust the right hand side padding of charecters, numbers.

Difference between LTRIM and RTRIM.

LTRIM: Trim the charecters and numbers in left hand side.
RTRIM: Trim the charecters and numbers in Right hand side.

Display all the first name and its length.

SELECT First_name, length(first_name) from employees;

List out all the number functions in oracle.

ROUND();
MOD();
TRUNC();
POWER(); 

List out all the Date functions in oracle?

ADD_MONTHS();
MONTHS_BETWEEN();
NEXT_DAY();
LAST_DAY();

Display all the first name and their total year of experience. rename first name column name as name and second column name as Year of Exep.

SELECT First_Name "Name" ,round(months_between(sysdate,hire_date)/12)"Year of Experiance" from employees;

How to display months between two given date.

select months_between('12-jan-2016','12-dec-2016')from dual;

Write a query to display today's date.

Select sysdate from dual;

Write a query to display the date after 3 months from today.

Select add_months(sysdate, 3) from dual;

Display last date of the current month.

Select last_day(sysdate)from dual; 

Display the up coming Wednesday date.

Select next_day(sysdate,'wednesday') from dual; 

Which date function return number as output.  

To_Number()

What are all the type conversion functions available.

To_Char()
To_Date()
To_number()


How to convert date into character.

TO_CHAR ( Value, [format])

Value  : A Number or Date that will be converted to a string
Format  : This is the format it will be used to convert value to a string.

How to convert character in to date.

TO_DATE( string, format)

String  : The string that will be converted to a date.
Format : The format that will be used to convert string to date.

What is the use of general function.

Its Mainly used to process the NULL Values.

Explain NVL, NVL2 , NULLIF and COALESCE function with example.

NVL() : Its accept two arguments if the first arguments is null and its display the second arguments else its display the first arguments.

consider one example
select first_name,commission_pct,nvl(commission_pct,1)from employees;

NVL2( ): Its accept three arguments if the first arguments is null and its display the third arguments else its display the second arguments.

select first_name,commission_pct,nvl2(commission_pct,1,3)from employees;

Null If (): It accepts two arguments return null  if both arguments are equal else it will display the first arguments.

COALESCE():  It accept N Number of arguments returns the first not null values from the expression list. and Instead of NVL() we can use the COALESCE() .

select first_name,commission_pct,coalesce(commission_pct,null,null,1,null)from employees;
SQL> select nullif(5,10) from dual;COALESCE

What are all the aggregate functions available in oracle.

Aggregate functions means group function

MIN()
MAX()
SUM()
AVG()
COUNT()

Write a query to select maximum salary from employees table.

SELECT MAX(SALARY) FROM EMPLOYEES;

Write a query to select second maximum salary from employees table.

SELECT max(salary) FROM Employees  WHERE salary NOT IN (SELECT max(salary) FROM Employees)  ;

Display average salary in the department 90.

select avg(salary) from employees where department_id=90;

Display number of employees working in department 90 and 60.

select count(*) from employees where department_id in( '90','60');

Display all the department id and its maximum salary.

Select department_id,max(salary) from employees group by department_id order by 1;

Display all the department id and number of employees working in that department.   

select department_id, count(*) from employees group by department_id;

Display all the department id and salary allocated for that department.

select department_id,sum(salary) from employees group by department_id order by 1; 

Display all the department id and number of employees working in that department. Total no employees working for the particular department must be greater than 30.

select department_id, count(*)from employees group by department_id having count(*)>30;  

Difference between WHERE clause and HAVING clause. 

Where 

Its followed by from clause

Having

Its followed by group by clause

Write a query to display the current date. Label the column Date.

Select sysdate "Date" from dual;

For each employee, display the employee number, last_name, salary, and salary increased by 15% and expressed as a whole number. Label the column New Salary. Place your SQL statement in a text file named lab3_2.sql.

Select Employee_id,Last_name, Salary,Round(( Salary*(15/100)+salary)) as "New Salary" From 

Employees;

Modify your query lab3_2.sql to add a column that subtracts the old salary from the new salary. Label the column Increase. Save the contents of the file as lab3_4.sql. Run the revised query. 

Select   Employee_id,Last_name, Salary,Round(( Salary*(15/100)+salary))  as "New  Salary",Round(( Salary*(15/100)+salary)) - Salary as "Increment" From Employees;

Write a query that displays the employee’s last names with the first letter capitalized and all other letters lowercase, and the length of the names, for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names.

Select Initcap(Last_name) Last_name ,Length(Last_name) Length From Employees Where Last_Name like 'J%' or Last_Name like 'A%' or Last_Name like 'M%'

order by Last_name desc;

For each employee, display the employee’s last name, and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.

Select Last_Name, Round (Months_between(sysdate ,Hire_date)) "Months Worked" from employees;

Write a query that produces the following for each employee: <employee last name> earns <salary> monthly but wants <3 times salary>. Label the column Dream Salaries.

Select Last_Name ||' Earns ' || salary ||'Monthly But Wants '|| Salary*3 "Dream Salaries" from employees;

Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with $. Label the column SALARY. 

select last_name, Lpad(salary,'15','$') from employees; 

Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”

select Last_name ,Hire_date , add_months(Hire_date,6) as saldate from employees;

SELECT last_name,hire_date,TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6), 'Monday'),'DAY,"THE" DDSP "OF" MONTH YYYY') "REVIEW"

FROM employees;

Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday. 

Select last_name, hire_date, to_char(To_date(Hire_date),'Day') "DAY" from employees order by to_char(hire_date-1,'d');

Select last_name, hire_date, to_char((Hire_date),'Day') from employees;

Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, put “No Commission.” Label the column COMM.

Select last_name,NVL(NULL,'No Commission') from employees;

Select last_name,commission_pct, NVL(Null,'No Commission') from employees;

Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, put “No Commission.” Label the column COMM.

SELECT Last_Name,NVL(TO_Char(Commission_pct),'No Commision') "COMM" FROM Employees;

Create a query that displays the employees’ last names and indicates the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES. 

SELECT last_name||' '|| rpad(' ', (salary*12)/1000, '*') EMPLOYEES_AND_THEIR_SALARIES FROM  employees ORDER BY salary DESC;

Using the DECODE function, write a query that displays the grade of all employees based on the value of the column JOB_ID, as per the following data:

Job Grade

AD_PRES A

ST_MAN B

IT_PROG C

SA_REP D

ST_CLERK E

None of the above 0

SELECT job_id, decode (job_id, 'ST_CLERK',  'E', 

                                'SA_REP',   'D', 

                               'IT_PROG',   'C', 

                               'ST_MAN',    'B', 

                               'AD_PRES',   'A', '0')GRADE FROM employees;

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;

RESTRICTING AND SORTING DATA

RESTRICTING AND SORTING DATA

SELECT
FROM
WHERE
ORDER BY

NOTE : Without SELECT and FROM Clause there is no query will be executed.

Where Clause: 

SELECT * FROM Employees;

SELECT Department_id,First_Name,Last_Name, Salary FROM Employees where department_id=90 ;

SELECT First_Name, Salary FROM Employees WHERE First_Name= 'lex';

No Data Found -- CASE SENSITIVE IN DATA IS MANDOTORY

SELECT First_Name, Salary FROM Employees WHERE First_Name= 'Lex';

SELECT First_Name,Hire_Date, Salary FROM Employees WHERE Hire_Date = '05/21/2007';

SELECT First_Name,Hire_Date, Salary FROM Employees WHERE Hire_Date = '05-21-2007';

LOGICAL OPERATORS:

Both AND/OR are used in where class only

AND -- TWO OR MORE CONDITIONS SATISFY
OR  -- EITHER ONE CONDITION IS SATISFIED 

SELECT First_name,Salary FROM Employees Where First_name='Lex' and salary>10000; 

SELECT First_name,Salary FROM Employees Where First_name='Lex' or salary>10000;

RELATIONAL OPERATOR

= < > >= =< |= 

IN        ,  NOT IN
LIKE      ,  NOT LIKE
BETWEEN   ,  NOT BETWEEN 
IS NULL   ,  IS NOT NULL 
ALL 
ANY 

NOTE 

=    Denotes to get only one value
in   Multiple values 

SELECT First_name, Salary from employees where first_name in ('Steven', 'Lex');  

SELECT First_name, Salary from employees where first_name = 'Steven', 'Lex'; 

--ORA-00933: SQL command not properly ended

SELECT First_name, Salary from employees where first_name = 'Steven' and 'Lex'; 

--ORA-00920: invalid relational operator

SELECT First_name, Salary from employees where first_name = 'Steven';

SELECT First_name, Salary from employees where first_name not in ('Steven', 'Lex');  

SELECT First_name, Salary FROM Employees where first_name not in ('Steven', 'Lex');  

SELECT First_name, Salary FROM Employees where first_name like ('Steven', 'Lex');  

ORA-00907: missing right parenthesis

SELECT First_name, Salary FROM Employees where first_name like ('Steven');  

LIKE is used to pattern matching search

% denotes to Something or Nothing

SELECT First_name, Salary FROM Employees where first_name like '%a';  

The Query Shows the ending letter of a;

SELECT First_name, Salary FROM Employees where first_name like '%a%';

The above Query shows the who are all the name having a;  

SELECT First_name, Salary FROM Employees where first_name like '%_a%'; -- 2nd letter of a

SELECT First_name, Salary FROM Employees where first_name like '%_a_i%';-- 2nd letter of a and 4th letter of i

SELECT First_name, Salary FROM Employees where first_name like '%A%r';-- Starts from A and Ends with r  

SELECT First_name, Salary FROM Employees where first_name like 'Ne%'; -- Starts with Ne 

Not Like:

Its opposite of the like operator. 

SELECT First_name, Salary FROM Employees where first_name not like '%_a%'; -- Except 2nd letter of a
SELECT First_name, Salary FROM Employees where first_name not like '%_a_i%';-- Except 2nd letter of a and 4th letter of i
SELECT First_name, Salary FROM Employees where first_name not like '%A%r';-- Except Starts from A and Ends with r  
SELECT First_name, Salary FROM Employees where first_name not like 'Ne%'; -- Except Starts with Ne 

Escape Concepts

Select first_name, email_id from t1 where email_id like '___%' escape'%';

In mail id having underscore.It is represented as  a character suppose we want to search the before the underscore value using escape keyword.

Between

Its belong to range of the two values.

Select first_name, salary from employees where salary between 11000 and 12000;

It will display the range of the salary between the 11000 and 12000.

Select first_name, salary from employees where salary not between 11000 and 12000;

It will display except the  range of the salary between the 11000 and 12000.

Note : the % is not working in the between, not between, in, not in operator

> all

Greater than the greatest value

Select first_name, salary from employees where Salary>15000;

Select first_name, salary from employees where Salary>15000,20000;

ORA-00933: SQL command not properly ended

The greater than compares only one values

Select first_name, salary from employees where Salary>all(15000,20000);

If we need to compare multiples of values use greater than all

>any 

Greater than the lowest value

Select first_name, salary from employees where Salary>any (15000,20000,10000);

It will display the above 10000 salary because Its the lowest value.


Is NULL

Select first_name, salary, commission_pct from employees where commission_pct is null;

ORDER BY 

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Select * from employees order by first_name; -- The query retrieves order by using the first_name column

Select * from employees order by 3;-- 3 denotes to last column 

Select * from employees order by first_name desc;

DESC - Descending Unknown values having high priority 
ASC  - Ascending

Select * from employees order by commission_pct desc; 

if not mention the DESC or ASC.  Ascending  will taken as  default.