What are the different clauses in SQL
1) SELECT Clause: - SELECT clause is mandatory and it is used to specify
the list of columns that are to be retrieved from the table.
eg. SELECT employee_id,first_name FROM employees;
2) FROM
Clause :- FROM Clause
is also mandatory and it is used to specify the list of table(s) from
which data is to be retrieved.
eg. SELECT employee_id,first_name FROM employees;
3) WHERE
Clause :- WHERE clause is
optional (in SELECT,DELETE or UPDATE statements) and it is used to
restrict data based on some condition.
eg. SELECT employee_id,first_name FROM employees WHERE job_id='IT_PROG';
4) ORDER BY Clause :- ORDER BY clause used to order the result set (of a select query)
as per your requirements.
eg. SELECT employee_id,first_name FROM employees ORDER BY employee_id;
5) GROUP BY Clause :- is used in SELECT statement to groups a result into subsets that have matching values for one or more columns.It is mostly used with aggregate functions.
Below query will display the average salary of each department.
eg. SELECT avg(salary),department_id FROM employees GROUP BY department_id;
6) HAVING Clause :- This clause is used in conjunction with Group by clause to restrict the data of grouped data.
eg. SELECT avg(salary),department_id FROM employees GROUP BY department_id HAVING
avg(salary)>5000;
What are INLINE views?
When a Select statement is used in the FROM clause of another Select statement it is called as Inline view.
Look at the below example-
SELECT *
FROM (SELECT deptno, count(*) emp_count
FROM employees GROUP BY deptno) employees,department
WHERE
department.deptno = employees.deptno;
What is Correlated Sub query?
Correlated sub queries are sub queries ( a query nested inside another) that used values from outer query.
This means the outer is executed first and its
result is used to execute the sub query.
Example finding the list of all employees whose salary
is above average for their departments
SELECT employee_id,first_name
FROM
employees AS e
WHERE
salary > (
SELECT
AVG(salary)
FROM
employees
WHERE
department = e.department);
Find out the Nth highest salary?
We can use analytical function called DENSE_RANK() to find out the Nth highest salary from employee table.
SELECT e.first_name,
e.last_name,
e.salary
FROM
(SELECT first_name,
last_name,
DENSE_RANK () OVER ( order by salary DESC ) AS rnk
FROM employees
) e
WHERE rnk=N; -------------- Where N is the rank i.e 1 , 2 , 3 etc
