Sql Marking question

Delete duplicate rows in sql


SQL Script to create Employees table
Create table Employees
(
     ID int,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
     Salary int
)
GO

Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)
Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)
Insert into Employees values (1, 'Mark', 'Hastings', 'Male', 60000)
Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000)
Insert into Employees values (2, 'Mary', 'Lambeth', 'Female', 30000)
Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values (3, 'Ben', 'Hoskins', 'Male', 70000)

Solution 

WITH EmployeesCTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) ASRowNumber
   FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber > 1


Finding Duplicate Records Using GROUP BY in SQL Server



CREATE TABLE CUSTOMER ( FirstName VARCHAR(50), LastName VARCHAR(50), MobileNo VARCHAR(15) ); INSERT INTO CUSTOMER VALUES ('Niraj','Yadav',989898); INSERT INTO CUSTOMER VALUES ('Chetan','Gadodia',959595); INSERT INTO CUSTOMER VALUES ('Chetan','Gadodia',959595); INSERT INTO CUSTOMER VALUES ('Atul','Kokam',42424242); INSERT INTO CUSTOMER VALUES ('Atul','Kokam',42424242); INSERT INTO CUSTOMER VALUES ('Vishal','Parte',9394453); INSERT INTO CUSTOMER VALUES ('Vishal','Parte',9394453); INSERT INTO CUSTOMER VALUES ('Vishal','Parte',9394453); INSERT INTO CUSTOMER VALUES ('Jinendra','Jain',121212); INSERT INTO CUSTOMER VALUES ('Jinendra','Jain',121212); SELECT * FROM CUSTOMER;


Solution = SELECT DISTINCT FirstName, LastName, MobileNo FROM CUSTOMER;

this above quesrry is depends upon the coloumns. If suppose

INSERT INTO CUSTOMER VALUES ('Vishal','Jain',9394453);

SELECT DISTINCT FirstName, LastName FROM CUSTOMER;

Use the above query then you will get 2 times vishal :) means you want two distinct coloumn first and last name :)
due to this disadvantages we can use group by clause :)


SELECT FirstName, LastName, MobileNo, COUNT(1) as CNT FROM CUSTOMER GROUP BY FirstName, LastName, MobileNo HAVING COUNT(1) > 1;



please preferred this link for more updates and knowledge
http://stackoverflow.com/questions/9849846/find-duplicate-records-in-a-table-using-sql-server


 Transform rows into columns in sql server:



Create Table tblProductsSale
(
   Id int primary key,
   SalesAgent nvarchar(50),
   SalesCountry nvarchar(50),
   SalesAmount int
)

Insert into tblProductsSale values(1, 'Tom', 'UK', 200)
Insert into tblProductsSale values(2, 'John', 'US', 180)
Insert into tblProductsSale values(3, 'John', 'UK', 260)
Insert into tblProductsSale values(4, 'David', 'India', 450)
Insert into tblProductsSale values(5, 'Tom', 'India', 350)
Insert into tblProductsSale values(6, 'David', 'US', 200)
Insert into tblProductsSale values(7, 'Tom', 'US', 130)
Insert into tblProductsSale values(8, 'John', 'India', 540)
Insert into tblProductsSale values(9, 'John', 'UK', 120)
Insert into tblProductsSale values(10, 'David', 'UK', 220)
Insert into tblProductsSale values(11, 'John', 'UK', 420)
Insert into tblProductsSale values(12, 'David', 'US', 320)
Insert into tblProductsSale values(13, 'Tom', 'US', 340)
Insert into tblProductsSale values(14, 'Tom', 'UK', 660)
Insert into tblProductsSale values(15, 'John', 'India', 430)
Insert into tblProductsSale values(16, 'David', 'India', 230)
Insert into tblProductsSale values(17, 'David', 'India', 280)
Insert into tblProductsSale values(18, 'Tom', 'UK', 480)
Insert into tblProductsSale values(19, 'John', 'US', 360)
Insert into tblProductsSale values(20, 'David', 'UK', 140)


Select SalesAgent, India, US, UK
from
(
   Select SalesAgent, SalesCountry, SalesAmount from tblProductsSale
) as SourceTable
Pivot
(
 Sum(SalesAmount) for SalesCountry in (India, US, UK)
) as PivotTable


SQL query to find rows that contain only numerical data





 Join 3 tables in sql server


Create Table Departments
(
     DepartmentID int primary key,
     DepartmentName nvarchar(50)
)
GO

Create Table Genders
(
     GenderID int primary key,
     Gender nvarchar(50)
)
GO

Create Table Employees
(
     EmployeeID int primary key,
     EmployeeName nvarchar(50),
     DepartmentID int foreign key references Departments(DepartmentID),
     GenderID int foreign key references Genders(GenderID)
)
GO

Insert into Departments values (1, 'IT')
Insert into Departments values (2, 'HR')
Insert into Departments values (3, 'Payroll')
GO

Insert into Genders values (1, 'Male')
Insert into Genders values (2, 'Female')
GO

Insert into Employees values (1, 'Mark', 1, 1)
Insert into Employees values (2, 'John', 1, 1)
Insert into Employees values (3, 'Mike', 2, 1)
Insert into Employees values (4, 'Mary', 2, 2)
Insert into Employees values (5, 'Stacy', 3, 2)
Insert into Employees values (6, 'Valarie', 3, 2)
GO


SELECT EmployeeName, DepartmentName, Gender
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
JOIN Genders ON Employees.GenderID = Genders.GenderID

we can use join = inner join, left outer join, cross join in a single query :)


Can we join two tables without primary foreign key relation 


yes why not :)

Create table Departments
(
     ID int not null,
     Name nvarchar(50),
     Location nvarchar(50)
)
GO
select * from Departments
Create table Employees
(
     ID int,
     Name nvarchar(50),
     Gender nvarchar(50),
     Salary int,
     DepartmentId int
)
GO

Insert into Departments values (1, 'IT', 'New York')
Insert into Departments values (2, 'HR', 'London')
Insert into Departments values (3, 'Payroll', 'Sydney')
GO

Insert into Employees values (1, 'Mark', 'Male', 60000, 1)
Insert into Employees values (2, 'Steve', 'Male', 45000, 3)
Insert into Employees values (3, 'Ben', 'Male', 70000, 1)
Insert into Employees values (4, 'Philip', 'Male', 45000, 2)
Insert into Employees values (5, 'Mary', 'Female', 30000, 2)
Insert into Employees values (6, 'Valarie', 'Female', 35000, 3)
Insert into Employees values (7, 'John', 'Male', 80000, 1)
GO
--querry 
select * from Departments as d
inner join Employees e 
on d.ID=e.ID
order by e.Name




Insert into Employees values (8, 'Mary', 'Female', 80000, 100)
select * from employees

we can use this querry and it has been sucessfull .

but we cant maintain the database integrity :)

data base is not unique without primary key and foreign key 

We cant use this methodlogy 








Comments

Post a Comment

Popular posts from this blog

Prime Number Program in C#

Prime number is a number that is greater than 1 and divided by 1 or itself. In other words, prime numbers can't be divided by other numbers than itself or 1. For example 2, 3, 5, 7, 11, 13, 17, 19, 23.... are the prime numbers. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 {     class Program     {         static void Main(string[] args)         {             int number, i, m = 0, flag = 0;             Console.WriteLine("PLEASE ENTER THE NUMBER TO CHECK PRIME NUMBER");             number = int.Parse(Console.ReadLine());             m = number / 2;             for(i=2;i<=m;i++)             {           ...
Read more

Fibonacci Series in C#

In the case of Fibonacci series, the next number is the sum of the previous two numbers for example 0, 1, 1, 2, 3, 5, 8, 13, 21 etc. The first two numbers of Fibonacci series are 0 and 1. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 {     class Program     {         static void Main(string[] args)         {             int val1 = 0, val2 = 1, val3, i, number;             Console.WriteLine("Please Enter any number");             number = int.Parse(Console.ReadLine());             Console.WriteLine(val1 + " " + val2);             for (i=2;i<=number;i++)             {                 val3 =...
Read more

view in sql server

Image
A view is nothing more than a  saved SQL query . A view can also be considered as a virtual table .   A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. SQL CREATE VIEW Syntax CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE coHEndition SQL Script to create tblEmployee table: CREATE TABLE  tblEmployee (  Id  int Primary Key ,  Name  nvarchar (30),  Salary  int ,  Gender  nvarchar (10),  DepartmentId  int ) When this query is executed , the database engine actually retrieves the data from the underlying base tables,  tblEmployees and tblDepartments . The View itself, doesnot store any data by default. When to Use a Vi...
Read more