Wednesday, September 25

To display CONSTRAINT TYPE (Primary Key / Foreign Key) in a table, along with Table name, Column name and Constraint name

This Query can be used to find CONSTRAINT TYPE (Primary / Foreign) in a table, along with Table name, Column name and Constraint name.

SELECT 
   A.TABLE_NAME,
   A.CONSTRAINT_NAME,
   B.COLUMN_NAME
FROM 
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS A,
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
    CONSTRAINT_TYPE = 'PRIMARY KEY
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY 
   A.TABLE_NAME


For example i will use query on  AdventureWorksDW2008R2

Result:

 CONSTRAINT_TYPE = 'PRIMARY KEY'   can be replaced with  CONSTRAINT_TYPE = 'FOREIGN KEY'  to display Foreign Key results.

Deleting Duplicate Records from a Table using CTE

For this purpose we will create a table called  tblPopulation, 

------------Create Query-----------------------------------

Create table tblPopulation
(  In_Id int,
   Country varchar(255),
   [State]  varchar(255),
   City  varchar(255),
   [Population (in Millions)] int
);


-------------Insert Values into table--------------------------
Insert into tblPopulation  values
 (0,'India','Delhi','East Delhi',9),
(1,'India','Delhi','South Delhi',8),
(2,'India','Delhi','North Delhi',5),
(3,'India','Delhi','West Delhi',7),
(4,'India','Karnataka','Bangalore',9),
(5,'India','Karnataka','Belur',2),
(6,'India','Karnataka','Mysore',1),
(7,'India','Karnataka','Hubli',30),
(8,'India','Karnataka','Shimoga',20),
(9,'India','Karnataka','Bidar',11),
(10,'India','Karnataka','Mangalore',6);

--------------------------------------------------------------
Note: Insert same values two times so that duplicate records are formed in the table. Table will contain a total of 22 records.

To delete the duplicate records using CTE, Use the below query:

WITH Dup_Population (In_Id, Dup_Count)                           
AS                                                               
(                                                                
SELECT In_Id,                                                    
ROW_NUMBER() OVER(PARTITION BY In_Id ORDER BY In_Id) AS Dup_Count
FROM tblPopulation                                               
)                                                                
Delete FROM Dup_Population WHERE Dup_Count > 1 ;                 


Select * FROM tblPopulation;                                     

Output will produce Distinct 11 Records.


Monday, September 23

Select Minimum and Maximum from a table without using Group by clause

Select Minimum and Maximum Salary from a table without using Group by clause.

Use Northwind database, Table Employee

SELECT *
FROM EMPLOYEE
WHERE EMP_GROSSSAL = (SELECT MAX(EMP_GROSSSAL) FROM EMPLOYEE)
 OR EMP_GROSSSAL = (SELECT MIN(EMP_GROSSSAL) FROM EMPLOYEE);

Output:

Inner Join & Equi Join-SQL

Inner Join 

An Inner Join is a join in which the values in the columns are joined by using a comparison operator.
comparison operator such as 

  • = (equal to)
  • < (less than)
  • > (greater than)
  • <= (less than or equal to)
  • >= (greater than or equal to)
  • <> (not equal to)
  • != (not equal to)
  • !< (not less than)
  • !> (not greater than)
Example of Inner join;
Use AdventureWorksDW2008R2


select e.EmployeeKey,
c.CustomerKey,
e.FirstName,
c.LastName,
c.Gender,
e.Title,
e.BirthDate,
c.EmailAddress,
c.YearlyIncome from DimEmployee e inner join DimCustomer c 
on e.FirstName=c.FirstName and e.LastName=c.LastName ;

This query will display 24 rows of specified columns from both DimEmployee and DimCustomer based on firstname and lastname matching on a conditional operator.
As shown below:


Equi Join

An Equi Join is same as Inner Join, but the basic difference is that it selects all columns from both tables. so the query looks like this;

select * from DimEmployee e inner join DimCustomer c 
on e.FirstName=c.FirstName and e.LastName=c.LastName ;






                     

SQL- Joins

SQL JOIN  is used to combine rows from two or more tables, based on a common column between them.

The different kinds of SQL joins are:



Pictorial representation of Inner and Outer Joins are shown below.

Friday, September 20

Self Join-SQL

A table can be joined to itself in a self-join. Use a self-join when you want to create a result 
set that joins records in a table with other records in the same table. To list a table two times 
in the same query, you must provide a table alias for at least one of instance of the table 
name.

For explaining this concept we will use AdventureWorksDW2008R2.
Table used is DimEmployee, we will select few columns needed for


     Select EmployeeKey, FirstName, LastName, ParentEmployeeKey, FirstName, LastName from DimEmployee "

O/P:

The employees called Rob Walters, Gail Erickson, Jossef Goldberg has a primary key of
4,511,13 and a foreign key of 3. This means he works for the employee with a primary key 
of 3. That person is Roberto Tamburello, who in turn reports to Terri Duffy-14.

select E.EmployeeKey, E.FirstName, E.LastName, E.ParentEmployeeKey,
M.FirstName, M.LastName from DimEmployee as E join DimEmployee as M
on E.ParentEmployeeKey = M.EmployeeKey "

For understanding well write query as shown below, here we use inner join.

select E.EmployeeKey, E.FirstName, E.LastName, E.ParentEmployeeKey,
M.FirstName as Manager_FirstName,M.LastName as Manager_LastName 
from DimEmployee as E join DimEmployee as 
on E.ParentEmployeeKey = M.EmployeeKey "

The output will display 295 rows out of 296, i.e. one employee does not have a manager.
In order to display all the 296 records, i.e. employee without manager also; we can use Left 
outer join instead of Inner Join.
so the query can be re-written as:

select E.EmployeeKey, E.FirstName, E.LastName, E.ParentEmployeeKey,
M.FirstName as Manager_FirstName, M.LastName as Manager_LastName
from DimEmployee as E  left outer join  DimEmployee as 
on E.ParentEmployeeKey = M.EmployeeKey "

O/P:


Presentation on Subqueries


Friday, September 13

Presentation on Aggregate Function





Tables used in this presentation are 

  • FactResellerSales(AdventureWorksDW)
  •  tblPopulation, l, m, n, p.


Query for creating tblPopulation

Create table tblPopulation 
(
   Country varchar(255),
   [State]  varchar(255), 
   City  varchar(255),
   [Population (in Millions)] int
);

----Inserting values into tblPopulation -------

 Insert into tblPopulation  values('India','Delhi','East Delhi',9),
('India','Delhi','South Delhi',8),
('India','Delhi','North Delhi',5),
('India','Delhi','West Delhi',7),
('India','Karnataka','Bangalore',9),
('India','Karnataka','Belur',2),
('India','Karnataka','Manipal',1),
('India','Karnataka','Mumbai',30),
('India','Karnataka','Pune',20),
('India','Karnataka','Nagpur',11),
('India','Karnataka','Nashik',6);


Query for creating table l

Create table l
 (
 region varchar(255),
 name varchar(255),
 amount decimal

 );

----Inserting values into l -------

 Insert into l  values
 ('region1', 'name1', 2000.00),
('region1', 'name2', 100.00),
('region1', 'name3', 500.00),
('region1', 'name3', 3400.00),
('region2', 'name1', 3233.00),
('region2', 'name2', 5000.00),
('region2', 'name2', 5344.00),
('region3', 'name1', 1200.00),
('region2', 'name2', 900.00),
('region4', 'name1', 2540.00);


Table p, m, n are shown below: