Category: Tutorials

SCALAR QUERIES  :-

subqueries follows select clause are called “scalar queries”

Syntax  :-

Select (select statement) , (select statement) From<tabname>

Example  :-

Select (select max(sal) from emp where deptno =10) D10, (select max(sal) from emp where deptno=20) D20, (select max(sal) from emp where deptno =30) D30

• Output:-        D10          D20               D30
•                          6000       5000           4000

Display max salary of top3 & top3 of minimum salary

Select (select distinct sal from (select ename, sal, dense_rank ( ) over (order by sal desc) rnk from emp) where rnk<=3) max, (select distinct sal from (select ename, sal, dense_rank  ( ) over(order by sal) rnk from emp) where rnk<=3) min

Output →                                   Max                              Min        

                                                        3000                           9000

                                                         2500                          800

                                                         1500                          700

Derived tables   :-

• This is introduced from SQL Server 2005.
• Sub queries follows from clause are called “Derived tables”.

Syntax :

Select * from (select statement) as alias.

• Use derived tables to simplify the complex processing.
• To use result of one query in another query.

Example  :-

Select * from (select empno,ename,sal*12 annsal from emp) as e where annsal >2000.

Display top maximum 3 salaries in emp table :-

Select distinct sal from (select ename,sal, dense_rank ( ) over (order by sal desc) rnk from emp) as e where rnk <=3

{to remove this one, this is top min 3 sal}

Display department wise top maximum 3 salaries in emp table :-

Select distinct deptno,sal from (select ename,sal,deptno,dense_rnk ( ) over (partition by dept no order by sal desc rnk from emp) as e Where rnk c=3 order by deptno,sal desc

sales 1, sales 2, cust →

Select * from (select * from sales 1 union all select * from sales 2) as s, cust c where s.cusid=c.cusid

Co-related sub-queries  :-

• In co-related sub-queries execution starts from outer query for every record of outer query table Inner query is executed once.
• In co-related sub-queries inner query also takes value from outer query.

Example :- Display employee records earning morethan avg salary of their dept

Select * from emp a where a.sal>(select avg(sal) from emp where deptno=a.deptno)

•       Where   →   1          A      5000      10    √                                      avg
•                                 2          B       4000     20    x                       5000 > (4000)
•                                 3          C       3000     10     x                      4000 > (4500)
•                                 4          D       5000     20     √                     3000 > (4000)
•                                 5          E        6000     30     x                     5000 > (4500)

Display names of the employee earning max salaries in their department

Select  ename,sal,deptno from emp X where x.sal=(select max (sal) from emp where deptno = x.deptno)

 

•  In co-related sub-queries,we can use two operations

1) exists (sub query)
2) Non exists (sub query)
3) Exists non exists returns true/false.

• Exists returns true ,if sub query returns at-least one record.
• If sub query returns no records then exists returns false.
• Exists operation is used to check whether the record is present in the table(or) not.
• Display department list which are not empty.

Select * from dept d  where exists (select * from emp where deptno=d.deptno).

• Display departments which are empty.

Select * from dept d where not exists(select  * from emp where deptno=d.deptno)

SQL server recommands use exists, not exists instead of in & not in operator.

Display list of managers 

Select * from emp  x where exists (select * from emp where mgr=x.empno)

Example :–

Empno         ename          mgr

1                     A               —

2                     B                1

3                    C                 1

4                     D                2

Here  A is manager for C & B

B is manager  for   D

C is not  manager for any one similarly D

Difference  between join and subquery  :–

• Use sub queries we got data from one table & condition based on another table

• Use join operator to get data from multiple tables.

Nested Queries in SQL Server :-

A sub query embedded in another subquery is called “nested query”.

Select  → main query

            (select   → sub query

                   (select))  → Nested query                      

Get the name of employee whos earning 2^nd maximum salary.

Select ename from emp where   sal=(select max(sal)) from emp where sal<(select max (sal) from emp)

Display names of the department employees earning 2^nd maximum salary.

Select dname from dept where deptno in (select deptno from emp) where sal=(select max(sal) from emp where sal<(select max(sal) from emp)))

Sub-queries with update command  :–

Update the employee salary to max salary whose eno=1001.

Update emp set  al =(select max(sal), from emp)  where empno=1001.

Update the employee salary to max,salary of the sales department whose eno=1001.

Update emp set Sal=(select max(sal) from emp where depno=(select deptno from dept where dnsme=’sales’)) where empno=1001.

Sub-queries with DELETE Command   :–

• Delete all employee records whose job= job of smith.
• Delete from emp where job=(select job from emp where ename=’smith’).

Multi Row Sub-Queries :–

• If inner query returns more than one value,then the sub query is called “multirow sub query”

Syntax :- Select  <collist>from<tabname> where colname operation (select statement)

• Here operation must be in, Not in, Any, All, Exists, not exists

Example  :- Display employee records whos job=job of Smith(or) job of blake.

Select * from emp where job in (select job from emp where ename=’smith’ or Ename=’BLAKE’)

Display department which is empty

Select deptno from dept where deptno not in (select deptno from emp)

Operators :

Any Operator in SQL Server :–

Used to compare any of the values in given list.

• x>any(1000,2,000,3000)  =>min
• x<any(1000,2000,3000)   =<max

All Operator in SQL Server :–

Used to compare all the values of given list.

• x  > all (1000,2000,3000)     > max
• x  < all (1000,2000,3000)     = min

Display employee records who are earning morethan all clerks.

• Select * from emp where sal > all (select sal from emp where job=’clerk’)
• Select * from emp where sal >any(select sal from emp where job =’clerk’)

Single Row Subquery :-

• If inner query returns only one value, then the sub query is called “single row subquerry”
• Operations must be >, >=, <, <=, =, <>.

Example :- Display employee records who is job equal to job of smith.

Select * from emp where job  = (select job from emp where ename= ‘smith’)

Display employee name earning max salary

Select ename from emp where sal  = (select max(sal) from emp)

Ename

King

Display name of the employee having max experence

Select ename from emp where hiredate=(select min(hiredate) from emp)

Display employee records whos job=job of smith and sal must be>smith

Select * from emp  where job=(select job from emp where=’smith’) and sal >(select sal from emp where ename=’smith’)

SUB QUERIES   :–

• A query embeded in another query is called “sub querry”.
• In this ,there will be two queries,one queries called inner-query and another query is called outer query.
• When it is executed,first inner query is executed then outer query
• The result of inner query act as input to outer query.

Types of sub queries  :-

1. standard sub queries [follows where clause]
2. derived tables [follows from clause]
3. scalar subqueries [follows select clause]

 Standard sub queries  :–

• Subqueries follows where clause (or) standard subquries.
• Outer query can be  insert, Update, Delete, Select
• Inner query must be always select.

Syntax :–   Select   <collist> from <tabname> where colname operation[select statement]

• Use subqueries when where clause is based on unknown condition.

Types  of standard subqueries  :-

1. Single row subquery
2. multi row subquery
3. nested queries
4. co-related subqueries

Cube and Rollup :-

Both options are used to calculate sub_totals

Select deptno,job,sum(sal) sumsal from  emp group by deptno, job with rollup order  by deptno,job

Output :-

• Deptno              job                 sumsal
•  Null                  NULL                  31025                  → this is total salary of (hole) all dept
• 10                      NULL                   7450                    → this is total sal of 10^th dept
• 10                       HR                       5000 
• 10                       Manager            2450
• 20                       NULL                 9000                   → like wise
• 20                       Clerk                  4000
• 20                       Manager           5000 

Cube example  :-

Select deptno,job,sum(sal),sumsal from emp group by dept no,job with cube order by deptno,job

The difference between rollup & cube is:

• Rollup option calculate subtotals based on first dimension.
• Cube option calculates subtotals based on both dimensions
• In sql server 2005, both rollup & cube is non-ansi standard.
• In sql server 2008, both are ansi standard.

Select deptno,job sum(sal) sumsal from emp group by rollup(deptno,job) order by deptno,job —-> Ansi standard.

Having Clause :-

Select deptno,max(sal),maxsal from emp where deptno in (10,30) group by deptno having max(sal)>=3000

Where vs having clause :—

• To filter data be fine group by use where clause
• To filter data after group by use having clause.
• In condition, if there is no aggregate function their use where clause.
• In condition if there is aggregate function, then use having clause.

From emp                      where             group by dept no       having       select

Deptno (10,30)  maxsal >=(Graterthan and Equal to) 3000

Display number of employee joined in each year

When year=1981 (or) 82 and no of employee≥2

Select year (hiredate) year,count(*) no of emps from emp where year (hiredate) in(1981,1982) group by year(hiredate) having count (*)>2

Output :-

   Year                 no of emps

   1981                       10

Display department wise sum salaries &with in the department job wise

Select deptno,job,sum(sal) sumsal from emp group by deptno,job order by deptno,job

Output :→  deptno                  job                  sumsal        

                            10                          manager                5000

                            10                          president               2450

                            20                          analyst                   30000

Group by Clause :-

• group by clause is used to group records based on one (or) more columns to calculate Aggregates like max ,min,avg,sum etc.
• using group by we can produce summarized data from detailed data. 

Syntax  :-  Select <collist> from<tabname> [where<cond>] Group by <collist>[having<cond>] [order by <collist>]

Display max salaries for each department

Select deptno, max(sal) maxsal from emp group by deptno

Output  is below:

• Depno               max sal
•    10                    5000
•    20                    3000
•    30                    2850
•    40                    1300

•                         1            A       5000     10
•                         2            B       4000     20 
•                         3            C        3000     10
•                         4             D        3000     20
•                         5             E        6000      30  

• 10                 5000
•  20                4000
• 30                 6000

Display max sal,minsal,sum sal,no of employee working for each job.

Select job,max(sal) maxsal,min(sal),min sal sum (sal) sumsal,count(*) no of emps from emp group by job

Output  is below:

• Job             maxsal      minsal        sumsal         no of emps
• Analyst         3000         3000           6000              2
• Clerk             2000           800              6150               5

Display no of employees joined in each year

Select year (hiredate) year,count (*) no of emps from emp  group by year (hiredate)

•         Year            no of emps
•          1980             2
•          1981             10
•          1982              23

Number of employees joined in each day

Select date name (dw, hiredate) day, count(*) no of emps from emp group by datename(dw,hiredate)

Output  is below:

•        Day                number of emps
•      Friday                    5
•      Saturday               1

Display max salaries for each department whose department number is equal to 10 (or) 30

Select deptno,max(sal),maxsal from emp where deptno in(10-30) group by deptno

Output  is below:

•                 Dept no          maxsal
•                      10                5000
•                      30                 8250

Display max salaries for each department where department number is equal to 10 (or) 30 and max sal ≥ 3000.