Mindmajix

Normalization and T-SQL in SQL Server

Normalization & T-SQL

The process of applying normal forms one by one at the time of data designing is called normalization

Advantages:

  1. Reducing unnecessary memory
  2. Removing duplicates/redundancy
  3. Having proper relationships.

Hint:  helps the optimizer to provide an optimized execution plan

Hints applied for three levels:

  1. At join operation: it gives an efficient join plan
  2. At table plan: locking mechanism on table control
  3. Query level: select, insert, delete, update operations will get an efficient execution plan

SYNONYMS:  it is stored alias name for any object. We can use this instead of calling the main object name.

Ex:

            Create SYNONYM *(multiply) for EMP

             Select * from *(multiply)

Variables

a) Global variables  → Defined by system

                                       → Read only

EX: select @@ server name, @@ error

Local variables:

Generated manually for read, write …….. etc operations

Syntax:  Declare variable data type

 EX: Declare @ var varchar (30)

       Set @ var = ‘MSBI CLASS’

     Point @ var.

 → Select colnames/ * from <table name>

                                       Where <condition>/.sub query

                                  Group by <columns>

                                    Having <condition>

                                 Order by <columns>

Note:  GROUP BY, ORDER BY we can use numeric’s

EMP Select * from EMP order by Eid cor order by 1
Eid Ename Eid Ename
12312

4

ABCDE

F

 

11223

4

 

ABCDE

F

 

Identify duplicate records:

Select Eid from EMP group by Eid having count (*)

                                                                                Eid

                                                                                 1

                                                                                 2

Sub Query

Query insure another query

3 types:

  1. Simple sub query
  2. Nested sub query (sub query inserted another sub query)
  3. Co related sub query

The inner query reference by using scalar values machine operators like (<,<=>,>,>=,is,<>,————)

List of values multiple operators (IN, NOT, IN, ——)

List of rows multiple operators (EXISTS, NOT EXISTS)

s.no Simple sub query Co related sub query
1 Inner query is executed first Outer query is executed first 
2 Outer query is executed next  Inner query is executed next 
3 Inner query is executed only one time Inner query is executed many times

Screenshot_80

Simple sub query EX: 

Select max (partysal) from parity

                        Where partysal not in

                     (Select max (partysal) from parity)

Co related sub query EX:

 Select p.parity income from parity P

       Where 1= (select count (distinct (Q.parity income)) from

                  Parity Q where P. parity income <Q.parity income).

Coordinating duplicates from table:

Delete from EMP where WMP . % %_Physloc %%

NOT in (select MIN (% % Physloc %%)) from EMP group by Eid

Physloc

Physloc location of a row

    There are three more ways to delete the duplicates

  1. By using row number
  2. By using rank/Dens rank
  3. By using intermediate tables

Duplicate records:

Select Eid, Ename into x from EMP group by Eid, Ename having count (*)>1

Delete EMP data for deleting the records available in X àdelete from EMP, X hwere EMP. Eid

                                                                                                                                                = X.EID and

                                                                                                                                EMP. Name =X. Ename

Loading unique records = insert into Emp select 8 from X

Working with multiple tables:

To work with multiple tables the below objects required

  1. Sub queries
  2. Set operations
  3. Joins
  4. Views
  5. Procedures
  6. Functions
  7. Triggers
  8. Cursors
  9. Extended procedures

Note:

→ For 5th max salary take n-1 = 5-1 = 4

→ For top 5 salary take 5(5>)

There are many ways to find out top salary & max salaries

a)      By using rank () over (……)

             Row _ number over (……)

                                Dense _ rank () over (……)

                                Top key word

Top

If displays top values from table

Syntax:

Select top (number) * (or) <column> from <table name >

Ex:     1) display top 2 rows in the table

                   Select top (2) * from Emp

        2) Display top 3 employee’s salary

                    Select top (3) * from parity ORDER BY party income DESC

CTE (common table expressions)

It is also called temporary named result set, within the scope of an executing statement that can be used with in a select / insert / update / creates view / merges statement.

Syntax:

    With <CTE NAME > < columns> as (query)

EX: with XX (EID, ENAME)

        AS (select Eid , Ename from Emp ) select * from XX

Note:  insert; select statement use many times CTES

Set Operators

→ It performs operations row wise b/w result set.

→ It follows set theory and set operators S

  1. UNION:  It merges rows from results set excluding duplicates
  1. UNION ALL; It merges rows from 2 data set including duplicates
  1. EXCEPT:  It takes results set from 1st data set excluding duplicates
  1. INTERSECT: Takes common rows from both the data sets number of columns and order of columns should be same

Enroll for Live Instructor Led MSBI Training


 

0 Responses on Normalization and T-SQL in SQL Server"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.