Category: SQL Server Tutorial

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.

MULTI ROW FUNCTIONS   :–

{NULL VALUE ARE NOT CALCULATED}

1. These functions are also calld “Aggregate functions” (Or) Group functions.
2. All these multi row functions will process multiple records.
3. Applied on group of records and returns one value from the entire group.

MAX ( ) Function in SQL:-

• Returns maximum value of a given expression.

Syntax   :

Max(expr)

• Select max(sal) from emp
• Output → 5000

Display max salary of 20^th department  :-

• Select max(sal),from emp Where deptno=20
• Output → 3000

• Select max(sal +isnull(comm,0)) from emp
• Output → 5000

• Select max (ename) from emp
• Output → word   {based on Ascii}

• Select max(hiredate)from emp
• Output → 1983

Min ( ) Function in SQL:-

• Returns minimum value of a given expression
• Syntax :-min(expr).
• Select min(sal) from emp
• Output → 36025

Note  :– We can not apply this functions to varchar & data columns, Apply only on numeric columns.

display total salaries paid to manager

• Select sum(sal) from emp Where job ‘manager’

avg  ( ) Function in SQL:-

• It returns avg value of given expression
• Select avg (sal) from emp
• Output → 2250.725

• Select ceiling (avg(sal)) from emp
• Output → 2251  (named value)

Note  :- This function also we can’t apply on varchar & date column.

Count ( ) Function in SQL:-

• Count no of values present in a column
• Select count(empno) from emp
• Output → 16

count * ( ) Function in SQL:-

• It returns no of records in the table
• Select count (*) from emp
• Output → 16

count_big(*) Function in SQL:-

Count

• the return type of count is integer .

count_big

• The return type of count is big_integer.

Select count_big(*) from emp.

OLAP FUNCTIONS  :-

(I) It is also called as “Analytical function”
(II) This is two types

• Rank
• Dense_rank

(III) These two functions are used to calculate rank of a particular value from the set of values.

Syntax  :-

                Rank( ) over (set of values)

                Dense_rank() over [set of values]

Display enames,salary,with rank ranking should be based on salary

Select ename,sal,rank ( ) over (order by sal desc) as rank from emp.

• Ename         sal           rank
•     B               500             1
•     King         500             1
•     Scott        300             3      
•      Ford        300             3
•     Anum       200             5

rank functions generate “gaps”.

dense_rank functions won’t generate “Gaps”.

Select ename,sal dense rank ( ) over(order by sal desc) as rnk from emp

• Ename        sal          rnk
•   B               503            1
•   King          503          1
•   Scott          300         2
•   Ford           300         2
•   Anum         200         3

Based on salary first then rank should be on hiredate

Select ename,sal dense_rank ( ) over(order by sal desc,hiredate) as rnk from emp.

• Ename            sal         rnk
•   B                   500        1
•   King              500        2
•   Scott              300        3
•   Ford               300        4
•   Anum              200       5

Ford the ranks of the employee with in the department based on their salaries.

Select ename,sal,deptno, dense_rank ( ) over(particular by deptno order by sal desc)as rnk from emp.

• Ename       sal        deptno   rnk
•    B             500         10        1
•   King        500         10        1
•   Scot        300          20        1
•  Ford        200           20        2
• Anum      200            30        1

SPECIAL FUNCTIONS :-

db_name ( ) SQL Function:

• Returns current name of the current database.
• Select db_name ( )
• Output → mydb

user_name( ) SQL Function:

• Returns name of the user
• Select user_name ( )
•  Output → dbo {database owner}

host_name ( ) SQL Function:

• Returns name of the server
• Select host_name()
• Output → IEEE

ident-current ( ) SQL Function:

• This functions returns current value of the identity…………………..table name.
• Select identity current(‘cust’).
• Output →  103.

ident_seed ( ) SQL Function:

• It returns starting value of the identity
• Select ident_seed(‘cust’) 
• Output → 100

ident_incr ( ) SQL Function:

• It returns increment by value.
• Select ident_incr(cust)
• Output → 1

isnumeric ( ) SQL Function:

• Isnumeric is expression,if expression is number this function returns ‘1’ otherwise ‘0’.
• Select isnumeric(10)
• Output → 1

• Select isnumeric (‘abed’)
• Output → 0

Isnull ( ) SQL Function:

• This function is used to convert null values.

Syntax  :-   isnull(exp1,xp2)

• If exp 1 is null ,it returns exp2
• If exp1 is notnull ,it returns exp1 only

Example :-

• Select is null (null,100)
• Output → 100
• Select isnull(200,100)
• Output → 200

 

Example  :–

Select ename ,sal,comm,sal+isnull[comm,0]  Totsal from emp

Ename      sal          comm      total

 A           2000        NULL     2000

 B           5000        NULL     5000

 C           5000        1000        6000

Example  :-Select ename,sal,isnull(cast(comm as varchar),N/A) as comm from emp

A           2000       N/A

B            5000      N/A

C            5000     1000

Example  :-     Select sal,comm from emp  Order by comm.

(Note  :- By default sql server decide null value is low}

Display the commission in ascending order but null value is display to last only.

Select ename,sal,comm from emp Order by isnull(comm,99999999999)

            C         5000        1000

            A         2000        NULL

            B         5000        NULL