Delete duplicate values by using CTE

How to Delete Duplicate values using CTE ?
CREATE TABLE [dbo].[emp]
(
[id] [int]  NULL,
[name] [varchar](20) NULL,
[city] [varchar] (20) NULL,
[dept] [varchar](10) NULL,
[salary] [money] NULL

)

insert into emp values(100,'Kishore','bangalore','IT',48000)
insert into emp values(101,'pavan','bangalore','HR',32000)
insert into emp values(102,'mahesh','pune','Slaes',24000)
insert into emp values(103,'harish','mumbai','IT',42000)
insert into emp values(104,'ramya','pune','HR',28000)

insert into emp values(105,'swetha','bangalore','Slaes', 26000)
insert into emp values(102,'mahesh','pune','Slaes',24000)
insert into emp values(103,'harish','mumbai','IT',42000)
insert into emp values(104,'ramya','pune','HR',28000)

Actual Table:

Required Result:

Query:
with e(edup)
As
(
             select id,
             ROW_NUMBER() over(PARTITION by id,name,city                      order by id ) as edup 
             from emp
)

delete from e where edup>1

Result:(3 row(s) affected)

We can also delete duplicates by using temporary table

1. Selete distinct * into #emp123 from dbo.emp
2. Truncate table dbo.emp
3. Insert into Emp Select * from #emp123

Joins

                                                             JOINS

• It is a process of joining column from 2 or more tables that always produces a new table.
• joining condition is must 
• To join n tables 'N-1' joining conditions required.
• A joining condition can be followed by any no of filtering conditions.

There are 6 types are there....

1. Equi join
2. Non-Equi join
3. Left Outer join
4. Right Outer join
5. Cartesian join
6. Self join

Sample tables:

create table tab1
(
id int,
name varchar(20),
salary money
)

create table tab2
(
id int,
did int
)

insert into tab1 values(1,'A',2000),(2,'P',3000),(5,'J',2500),(8,'AK',5000)

insert into tab2 values(1,26),(2,50),(3,35),(4,56),(6,25)

1.Equi join:

                 At This join concept columns from 2 or more tables can be joined by making a joining condition using '=' operation.

                  To work with Equi join, Table should contain at least one column with respect to data and data type.

Ex:

Select * from tab1,tab2 where tab1.id = tab2.id

2.Non-Equi join:

                       At This join concept columns from 2 or more tables can be joined by making a joining condition without using '=' operation.

                  To work with Non-Equi join, Table should contain at least one column with respect to data and data type.

Ex:

Select * from tab1,tab2 where tab1.id > tab2.id

3.Outer join:(Right /Left Outer joins)

                     At this join concept columns from 2 or more tables can be joins with respect to rows that gets matched and unmatched with joining conditions.

                      Sql Server provides outer join operator as '*' which should be specified at left / right side of joining condition.

There are 2 types of joins.

1. Left Outer Join
2. Right Outer Join

• Left Outer Join:

                           At this joining concept outer join operator is placed on left side of joining condition,which will retrieve match and unmatched rows from left side table which is on left side of the joining condition and substitutes null values for the columns of right side table.

Ex:

Select * from tab1,tab2 where tab1.id * = tab2.id

• Right Outer Join:

                           At this joining concept outer join operator is placed on right side of joining condition,which will retrieve match and unmatched rows from right side table which is on right side of the joining condition and substitutes null values for the columns of left side table.

Ex:

Select * from tab1,tab2 where tab1.id  = * tab2.id

4.Cartesian join:

                          This join concept supports to join the columns from 2 or more tables with out any joining condition.

                          It provides multiplication of rows among the tables used for joins.

Ex:

Select * from tab1,tab2

we will get (4X5=) 20 rows.

5.Self Join:

                         At this join concept single table will be used for more than once, Differentiating them with alias names.

                          Data is retrieved, joining condition is made on single table .

Ex:

select t1.id,t2.id from tab1 t1 , tab t2

where t1.id=t2.id

Unmatched Records from Two tables by using CTE

Unmatched Records from Two tables by using CTE:
create table tab1
(
id int,
name varchar(20),
salary money
)

create table tab2
(
id int,
did int
)

insert into tab1 values(1,'A',2000),(2,'P',3000),(5,'J',2500),(8,'AK',5000)

insert into tab2 values(1,26),(2,50),(3,35),(4,56),(6,25)

Select * from tab1
Select * from tab2
table 1:                                                   table 2:

                                               

Required Result:

Query:
with e (id,id2,name,salary,did)
As
(
Select tab1.id,tab2.id,name,salary,did from tab1 
full outer join 
tab2 on tab1.id=tab2.id
)
Select * from e where
 id not in (select tab1.id from tab1 inner join tab2 on tab1.id=tab2.id) or
id2 not in (select tab1.id from tab1 inner join tab2 on tab1.id=tab2.id)

But if we want results like following:

Query:
with e (id,name,salary,did)
As
(
Select IsNull(tab1.id,tab2.id),name,salary,did from tab1 
full outer join 
tab2 on tab1.id=tab2.id
)

Select * from e where id not in (select tab1.id from tab1 inner join tab2 on tab1.id=tab2.id)

Indexes

                                              Indexes

• Indexes plays a major role when a data is to be searched in bulk records.
• In indexing a separate index structure is created that includes search key value and address of a record and index always stores the data in ascending order for performing.
• Indexing is classified into the following types

1. Clustered Index
2. Non-Clustered Index
3. Unique Index

Syntax:

Create [Unique]/[Clustered]/[Non-Clustered] Index

index_name ON table_name (column1, column2,........)

Composite Index: Index created on multiple columns is called as Composite index.

1. Clustered Index:

• Clustered index will alter the physical representation of rows in a table(In ascending order).
• A table can have only one clustered index.
• This index should be created for a column where more than one search value is available.

2. Non-Clustered Index:

• It will not allow the physical representation of rows
• It will not sort the data of a table.
• It is a default index created.

3.Unique Index:

• This contains only on those columns which contains unique data.
• This is Automatically created when Unique Constraint is specified on a column.(unique non-clustered)

Different Questions on Joins

Different Questions on Joins:

CREATE TABLE [dbo].[one]
(
[x] [varchar](10) NULL
)

CREATE TABLE [dbo].[two]
(
[y] [varchar](10) NULL

)

insert into one values ('A'),('B'),('C'),('C')

insert into two values ('A'),('B'),('C'),('D')

Table one                                                 Table Two

                                                                         

Select X,Y from one inner join two on x=y
Result:

Select X,Y from one left outer  join two on x=y
Result:

Select X,Y from one right outer  join two on x=y
Result:

Select X,Y from one full outer join two on x=y
Result:

Row_number(), Rank(), Dense_rank(), Ntile(int)

Row_number(), Rank(), Dense_rank(), Ntile(int):

CREATE TABLE [dbo].[emp321]
(
 [id] [int]  NULL,
 [name] [varchar](20) NULL,
 [city] [varchar] (20)NULL,
 [dept] [varchar](10) NULL,
 [salary] [money] NULL
)
insert into emp321 values(100,'Kishore','bangalore','IT',32000)
insert into emp321 values(101,'pavan','bangalore','HR',32000)
insert into emp321 values(102,'mahesh','pune','Slaes',24000)
insert into emp321 values(103,'harish','mumbai','IT',42000)
insert into emp321 values(104,'ramya','pune','HR',26000)
insert into emp321 values(105,'swetha','bangalore','Slaes', 26000)
insert into emp321 values(106,'sindhu','pune','IT', 34000)
insert into emp321 values(107,'naresh','mumbai','HR',30000)
insert into emp321 values(108,'kumar','mumbai','Slaes', 30000)
insert into emp321 values(109,'ashok','bangalore','IT', 42000)
insert into emp321 values(110,'siva','madanapalli','IT',36000)

select * from emp321

Query:

Select Salary,ROW_NUMBER() over(order by salary)[row number],RANK() over(order by salary)[rank],

DENSE_RANK() over (order by salary)[dense rank],ntile(5) over(order by salary) [ntile] from emp321 

Result:

Observer the result set .............

Unmatched Records from Two tables

Unmatched Records from Two tables:
create table tab1
(
id int,
name varchar(20),
salary money
)

create table tab2
(
id int,
did int
)

insert into tab1 values(1,'A',2000),(2,'P',3000),(5,'J',2500),(8,'AK',5000)

insert into tab2 values(1,26),(2,50),(3,35),(4,56),(6,25)

Select * from tab1
Select * from tab2
table 1:                                                   table 2:

                                               

Required Result:

Query:
select * from tab1 full outer join tab2 on tab1.id=tab2.id
where tab1.id not in(
                                 select a.id from tab1 as a join tab2 as b on                                           a.id=b.id
                                 ) or 

tab2.id not in (select b.id from tab1 as a join tab2 as b on a.id=b.id)

But if we want results like following:

Query:

Select IsNull(tab1.id,tab2.id),name,salary,did from tab1 
full outer join 
tab2 on tab1.id=tab2.id
where tab1.id not in(
                                  select a.id from tab1 as a join tab2 as b on                          a.id=b.id ) or 

tab2.id not in (select b.id from tab1 as a join tab2 as b on a.id=b.id)

How to find last month first date and last month last date ?

How to find last month first date and last month last date ?
Solution:
Select 
DateAdd(M,(DateDiff(M,0,GetDate()-1)),0)As [First Date],
DateAdd(M,(DateDiff(M,-1,Getdate()-1)),-1)as [last date]

DateAdd(-,-,-):
It will take 3 values and which is used to add no.of days or months or years to a given date.
First value- Date part
Second value- Numeric value
Third value -Date
DateDiff(-,-,-):
It will also take 3 values and which is used to find the difference between two dates, in terms of days,months and years.
First value- Date part
Second value - Start Date
Third value - End Date

How to Find Duplicate values in SQL?

How to Find Duplicate values in SQL?
CREATE TABLE [dbo].[emp]
(
[id] [int]  NULL,
[name] [varchar](20) NULL,
[city] [varchar] (20) NULL,
[dept] [varchar](10) NULL,
[salary] [money] NULL

)

insert into emp values(100,'Kishore','bangalore','IT',48000)
insert into emp values(101,'pavan','bangalore','HR',32000)
insert into emp values(102,'mahesh','pune','Slaes',24000)
insert into emp values(103,'harish','mumbai','IT',42000)
insert into emp values(104,'ramya','pune','HR',28000)

insert into emp values(105,'swetha','bangalore','Slaes', 26000)
insert into emp values(102,'mahesh','pune','Slaes',24000)
insert into emp values(103,'harish','mumbai','IT',42000)

insert into emp values(104,'ramya','pune','HR',28000)
 Actual Table:

Required table:

Query:

select id,name,city,dept,salary from
(
          select id,name,city,dept,salary,COUNT(id)as cnt 
          from emp
          group by salary,dept,city,name,id

          having COUNT(id)>1
)as kk

Department wise highest salary

Department wise highest salary

CREATE TABLE [dbo].[emp](
[id] [int]  NULL,
[name] [varchar](20) NULL,
[city] [varchar] (20) NULL,
[dept] [varchar](10) NULL,
[salary] [money] NULL
)

insert into emp values(100,'Kishore','bangalore','IT',48000)
insert into emp values(101,'pavan','bangalore','HR',32000)
insert into emp values(102,'mahesh','pune','Slaes',24000)
insert into emp values(103,'harish','mumbai','IT',42000)
insert into emp values(104,'ramya','pune','HR',28000)
insert into emp values(105,'swetha','bangalore','Slaes', 26000)
insert into emp values(106,'sindhu','pune','IT', 34000)
insert into emp values(107,'naresh','mumbai','HR',30000)
insert into emp values(108,'kumar','mumbai','Slaes', 30000)
insert into emp values(109,'ashok','bangalore','IT', 42000)
insert into emp values(110,'siva','madanapalli','IT',36000)

                                                                   Actual table                                                                          

Required Result

Query:

Select id,name,city,dept,salary from
 (
           select id,name,city,dept,salary,
           DENSE_RANK() over(partition by dept order by salary                    desc) as drank from emp
) as kk
where drank=1

Views

                                            Views

                       View is a virtual table. Which allows to view and modify related data. view is DB Object,which stores only select Query in compiled format. Hence it is also called as "Stored Query".

Types of views:

1. Simple View
2. Check option view
3. Complex view
4. Indexed view

Syntax:

            create view view_name

                        [with Encryption]/[with Schema binding]

              As

              Select Query [with check option]

1.Simple view:

                        A view which is created on a single table and does not contain group by clause, group by function , Distinct operator and Joins is called simple view. To perform insert operation through simple view all mandatory columns of base objects has to be enclosed.
Ex:
create view v1
As
select id,name,salary from employee
Select * from v1

2.Check option view:
                                  A view which is created on conditional basis. if data beyond the condition then it will reject the data. 
Ex:
create view v2
As 
Select id, name, salary from employee where salary>10000 with check option
Select * from v2

3.Complex view:
                            A view which is created on multiple tables and contain group by clause, group by function , Distinct operator and Joins is called Complex view. By default it is not update able view.
Ex:
create view v3
As
select dept,sum(salary) from employee 
group by dept
select * from v3

4.Indexed view:
                         This view is different from other views. here we create index for the view which will enhance the performance of a Query.
To create indexed view we have the following restrictions:

1. The view must be a schema binding view
2. The view should contains count_big aggregate function on it
3. If the aggregate functions are associated with any column and if that column allows null values in ti then we should associated it with "isnull()" function.

Ex:

create view v4 with schema binding

As

select dept,count_big(*) as cnt ,sum(isnull(comm,0)) as commission from DBO.employee

group by dept

                        The above view is logical to make it physical we need to create index for that view.

Create unique clustered index dept_index on v4(dept)

Note:

if you want to use order by clause in your view then it is must that select Query should be associated with TOP keyword.

Q.what is Encrypted view?

A.Which is used to hide the query stored in a view. These views are associated by an option called " with Encryption"

Ex:

create view v5 with encryption

As

select id, name, salary from employee

check this: sp_helptext v5

Q.what is Schema binding ?

A.This view is not allowed to drop  base object. These views will be associated by an option "with schema binding". These views when stored a query table should be preceded by schema name and these views will not allow to story select operator ("*").

Note:

if we required we can use both encryption and schema binding in single query

Ex:

create view v6 with encryption , Schema binding

As

select query

Structure of the Query

                     Structure of the Query

Select */List_of_Columns/functions/experssions
[from table1(tab2............)]
[where condition]
[Group by col1,col2,........]
[Having condition]
[Order by col1,col2,.........]
[For XML modes]


But Execution wise the order will be different........

1. It starts from FROM clause
2. then WHERE clause
3. then Group by clause
4. then Having clause
5. then Select 
6. then Order by clause

DQL

                                                                DQL
Data Query Language:
            This Language is used to retrieve the data from a DB server in different ways. This Language includes "SELECT" command.
There are 3 ways to retrieve data from DB 

1. Selection
2. Projection
3. Joins  

1.Selection:

                   when we required to select all the columns in the table then then we will go for selection. we can select all the columns of the table by using  selection operator ("*").

2.Projection:

                             when we required show only required columns then we will go for Projection just by specifying required columns names in the Query.

3.Joins:

                   when we required to show columns from different tables then we will go for joins

                             

           

SQL

                                                                      SQL

Structured Query Language:
                  
                  "SQL is a Language which is used to Communicate with DB server". SQL called Command based language. SQL can be understand by every RDBMS.

1. which is insensitive language.
2. which is also called non-procedural language.
3. which contains 5 sub languages( DQL, DML, TCL, DDL, DCL)

RDBMS

                                                       
                                                              RDBMS


RDBMS=R+DBMS

R->Relationships
              |- > Referential Integrity Constraints.


One to One           |
                               |-> Direct Implementation 
One to Many        |


Many to Many ->Indirect Implementation

1. RDBMS Always represents the data in 2 Dimensional format that is rows and columns.
2. Collection of characters is a column or Field or Identifier or Attribute.
3. Collection of rows and columns is a Table or Object or Relationship or Entity
4. RDBMS always represents data in normalized way.

Components of RDBMS:

1.Collection of Objects

2.Set Of Operators.

3.Data Accuracy

DBMS

                                                                                DBMS

         Which is a collection of software programs used to store and manage and retrieve data.

Data retrieval is based on 3 key points

1. Accuracy - Free from Errors
2. Timeliness - with in a stipulated time
3. Relevancy  - Related data

                                                                            DATABASE

          Database is a logical container which contains related objects includes tables, views ,indexes, synonyms, stored procedures, stored functions, stored triggers etc....

Models Of DBMS 

1. ER Model
2. Object o\Oriented Model
3. Record Based Logical Model
1. HDBMS
2. NDBMS
3. RDBMS
4. ORDBMS
4. Infological Model
5. Semantic Model

Thanks:

KishoreKumar.v