Normalization and T-SQL in SQL Server

  • (4.0)
  •   |   633 Ratings

Normalization & T-SQL

The process of applying normal forms one by one at the time of data designing is called normalization
  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

Checkout SSAS Interview Questions

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

            Create SYNONYM *(multiply) for EMP

             Select * from *(multiply)


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

                                       Where /.sub query

                                  Group by


                                 Order by
Note:  GROUP BY, ORDER BY we can use numeric’s
Enhance your IT skills and proficiency by taking up the SSAS Training.

Identify duplicate records:

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

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

Coordinating duplicates from table:

Delete from EMP where WMP . % %_Physloc %%
NOT in (select MIN (% % Physloc %%)) from EMP group by Eid


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
  5. Procedures
  6. Functions
  7. Triggers
  8. Cursors
  9. Extended procedures
→ 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
If displays top values from table
Select top (number) * (or) from
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.
    With < 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                                            
Explore SSAS Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

List of Related Microsoft Certification Courses:

 SSIS  Power BI
 SSRS  SharePoint
 SCCM  BizTalk Server
 Team Foundation Server  BizTalk Server Administrator


Popular Courses in 2018

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