Blog

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
 

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)
 
 

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
  2. UNION ALL; It merges rows from 2 data set including duplicates
  3. EXCEPT:  It takes results set from 1st data set excluding duplicates
  4. 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

RELATED COURSES

Get Updates on Tech posts, Interview & Certification questions and training schedules