SQL Practise

SQL Practise content Start.:---------

--creation of a table start

CREATE TABLE [dbo].[department](

[department_id] [int] NOT NULL,

[name] [nvarchar](50) NOT NULL,

[location_id] [int] NOT NULL

) ON [PRIMARY]

=====================

CREATE TABLE [dbo].[job](

[job_id] [int] NOT NULL,

[Function] [varchar](50) NOT NULL

) ON [PRIMARY]

==========================

CREATE TABLE [dbo].[location](

[location_id] [int] NOT NULL,

[regional_group] [nvarchar](50) NULL

) ON [PRIMARY]

============================

CREATE TABLE [dbo].[Employee](

[employee_id] [int] NOT NULL,

[last_name] [nvarchar](50) NULL,

[first_name] [nvarchar](50) NOT NULL,

[middle_name] [nvarchar](50) NULL,

[job_id] [int] NOT NULL,

[manager_id] [int] NOT NULL,

[hiredate] [datetime] not null,

[salary] [int] NOT NULL,

[comm] [int] NULL,

[department_id] [int] NOT NULL

) ON [PRIMARY]

--creation of a table end

--===============================

--insertion in tables

--===============================

insert into location

select 122,'new york'

union

select 123,'dallas'

union

select 124,'chicago'

union

select 167,'boston'

================================

insert into department

select 10,'accounting',122

union

select 20,'research',124

union

select 30,'sales',123

union

select 40,'operations',167

====================

insert into job

select 667,'clerk'

union

select 668,'staff'

union

select 669,'analyst'

union

select 670,'salesperson'

union

select 671,'manager'

union

select 672,'president'

==============================

insert into employee

select 7369,'Smith','John','Q','667','7902','17-DEC-84','800',null,'20'

union

select 7499,'Allen','Kevin','J','670','7698','20-FEB-85','1600','300','30'

union

select 7505,'Doyle','Jean','K','671','7839','04-APR-85','2850',null,'30'

union

select 7506,'Dennis','Lynn','S','671','7839','15-MAY-85','2750',null,'30'

union

select 7507,'Baker','Leslie','D','671','7839','10-JUN-85','2200',null,'40'

union

select 7521,'Wark','Cynthia','D','670','7698','22-FEB-85','1250','500','30'

--insertion in tables end

NOW Practise Session start 

--list all the location detail

select * from location

--list all the dempartment detail

select * from department

--list all the job detail

select * from job

--list all the employee detail

select * from employee

--list out the firstname ,lastname salary commission of employees

select first_name, last_name,salary,comm from employee

select employee_id as [ID of the employee],last_name as [name of the employee],department_id as [department Id]

from employee

--list out the employee anual salary of all employee

select last_name ,(12*salary) as [anuual salary] from employee

--where conditions

--list the detail about smith

select * from employee where last_name='smith'

--list the department 20 detail

select * from employee where department_id=20

--list out the detail of the employee whose salary between 800 and 2500

select * from employee where salary between 800 and 2500

--------------

--list out the detail of the employee whose department_id is 10 and 20

select * from employee where department_id=10 or department_id=20

select * from employee where department_id in(10,20)

-------------

--list out the detail of the employee who not working is 10,30

select * from employee where department_id not in(10,30) 

--list out the employee detail whose name start form s

select * from employee where last_name like 's%'

--list out the empoloyee whose last_name start with s and end with h

select * from employee where last_name like 's%h'

----------

--list out the employee whose name is of five char and start with s

select * from employee where Len(last_name)=5 and last_name like 's%'

select * from employee where last_name like 's____'

-----------

--list out the employee  whose department is 30 and salary is grater then 1700

select * from employee where department_id=30 and salary>1700

--whose not geting commission

select * from employee where comm is NULL or comm=0

--ascending order based on employee id

select employee_id,last_name from employee order by employee_id asc

--descending order based on salary

select employee_id,last_name from employee order by salary desc

--base on name and salary

select * from employee order by last_name asc, salary desc

--based on lastname asc department desc

select * from employee order by last_name asc , department_id desc

--group by and having clause

--how many employee working in diffrent department wise

select count(employee_id) as [number of employee],department_id from employee group by department_id

--list out department wise min,max,avg salary

select count(*), max(salary) as [max sal],avg(salary) as [avg salary], min(salary) as [min sal], department_id

from employee group by department_id 

--list out num of employee joind in every month

select count(*) as [num of employee] ,datename(month,hiredate) as [month] from employee group by datename(month,hiredate) order by [month]

select count(*) as [num of employee] ,datename(month,hiredate) as [month] from employee group by datename(month,hiredate),datepart(month,hiredate) order by datepart(month,hiredate)

--list out num of employee joined in each month of the year

select count(*) as [num of employee], (datename(month,hiredate)+'/'+datename(year,hiredate))as [month-year] from employee group by (datename(month,hiredate)+'/'+datename(year,hiredate)) 

select count(*) as [num of employee], (datename(month,hiredate)+'/'+datename(year,hiredate))as [month-year] from employee group by (datename(month,hiredate)+'/'+datename(year,hiredate)),datename(year,hiredate) order by datename(year,hiredate)  

select count(*) as [num of employee], datename(month,hiredate)+'/'+datename(year,hiredate)as [month-year] from employee group by datename(month,hiredate),datename(year,hiredate)

--list out department id having more then 3 employee

select count(*)as [num of employee],department_id from employee group by department_id having count(*)>3

--------------------

--how many employee in february month

select count(*) as [num of employee],datename(month,hiredate) month_char, datepart(month,hiredate) month_num from employee group by datepart(month,hiredate),datename(month,hiredate) having datepart(month,hiredate)=2

select count(*) as [num of employee],datename(month,hiredate) month_char, datepart(month,hiredate) month_num from employee where datepart(month,hiredate)=2 group by datepart(month,hiredate),datename(month,hiredate) 

--------------------

--join in feb and jun

select count(*) as [num of employee],datename(month,hiredate),datepart(month,hiredate) from employee group by datepart(month,hiredate),datename(month,hiredate) having datepart(month,hiredate) in(2,6)

--joined in 1985

select count(*)as [num of employee],datename(year,hiredate)as [year] from employee group by datename(year,hiredate) having datename(year,hiredate)=1985

--each month in 1985

select count(*) as [num of employee], datename(month,hiredate) as month_char, datename(year,hiredate) from employee group by datename(month,hiredate),datename(year,hiredate) having datename(year,hiredate)=1985

--feb 1985

select count(*)as [num of employee], datename(month,hiredate)as month_char,datepart(month,hiredate) as month_num, datename(year,hiredate) as [year] from employee group by datename(year,hiredate),datepart(month,hiredate),datename(month,hiredate) having datepart(month,hiredate)=2 and datename(year,hiredate)=1985

--grater then 2 employee joined in feb 1985

select count(*)as [num of employee],department_id from employee group by datename(year,hiredate),datepart(month,hiredate),datename(month,hiredate),department_id having datepart(month,hiredate)=2 and datename(year,hiredate)=1985 and count(*)>=2

----------

--=============

--subquery

--display the employee who get the max salary

select * from employee where salary=(select max(salary) from employee)

--working in sales department

select * from employee where department_id=(select department_id from department where name='sales')

--working in clark department

select * from employee where job_id=(select job_id from job where  [function]='clerk')

--working in dallas

select * from employee where  department_id=( select department_id from department where location_id=(select location_id from location where regional_group='dallas'))

----------

--num of employee working in sales

select count(*) from employee group by department_id having department_id=(select department_id from department where name='sales')

select count(*) from employee  where department_id=(select department_id from department where name='sales')group by department_id

-------------

------

--update employee salary working as clerk with 10%

select * from employee where job_id=(select job_id from job where [function]='clerk')

update employee set salary=(salary*10/100)+salary where  job_id=(select job_id from job where [function]='clerk')

-------------

--delete the employee working in opertations department

select * from employee where department_id=(select department_id from department where name='operations')

delete  from employee where department_id=(select department_id from department where name='operations')

--------------

--==============================

--display second highest salary

select top 1  * from employee where salary in(select top 2 salary from employee order by salary desc) order by salary asc

select  * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))

--display the nth salary

WITH Salaries AS

(

select  row_number() over (order by salary desc)as row,salary from employee 

)

SELECT

  Salary

FROM

  Salaries

WHERE

   row = 6

  

   

   ----

   --list out the employee who earn more then every employee employee in 30

   select * from employee where salary>(select max(salary) from employee where department_id=30)

   select * from employee where salary> all(select salary from employee where department_id=30)

   ------

    --list out the employee who earn more then lowest every employee employee in 30

   select * from employee where salary>(select min(salary) from employee where department_id=30)

   select * from employee where salary> any(select salary from employee)

   -------------------------------

   -- select last_name from department,employee e where department.department_id=e.department_id

    ----------------

    --find out whose department has not employee

    select last_name,department_id from employee e where not exists(select department_id from department d where d.department_id=e.department_id )--???

    select name  from department d where  not exists(select d.name from employee e where d.department_id=e.department_id)--???

   

   --co related sub query\

   --find out the employee who earn grater then the avr salary of there department

   select * from employee e where  salary>(select avg(salary)from employee where department_id=e.department_id)

   ---------------

   

   --joins 

   --list out employee with there department name

   select e.employee_id,last_name, e.department_id, d.name from employee e, department d where e.department_id=d.department_id

   select e.employee_id,e.last_name,e.department_id,d.name from employee e inner join department d on e.department_id=d.department_id

   

   select * from employee e inner join department d on e.department_id=d.department_id

   

   --list out employee with designation

   select employee_id,last_name,job.job_id,[function]  from employee inner join job on employee.job_id=job.job_id

   

   

   --===============

   ---------------

   --display employee with there department and regional group

   select employee_id,last_name,name,regional_group from employee e

   inner join department d on e.department_id=d.department_id

   inner join location l on d.location_id=l.location_id

   ---------------

   

   select e.employee_id,e.last_name,e.name,l.regional_group from 

   (select e.employee_id,last_name,d.name,e.department_id,d.location_id from employee e inner join department d on e.department_id=d.department_id) e

   inner join location l on e.location_id=l.location_id

   select employee_id,last_name,name,regional_group from employee e,department d, location l where

   e.department_id=d.department_id and l.location_id=d.location_id

--============

--how many employee who working in diffrent department and display departmentname

select count(*)[num of emploee],employee.department_id,name from employee

inner join department on employee.department_id=department.department_id

group by employee.department_id,department.name

-----

select count(*)[num of employee],name from employee,department 

where employee.department_id=department.department_id

group by name

--how many employee working in sales department

select count(*)[num of employee],name from employee inner join department

 on employee.department_id=department.department_id

 where name='sales' group by name

--which department having more then 3 employee and display the department name

select  name,count(*) from employee e inner join department d 

on e.department_id=d.department_id

group by name

having count(*)>3 order by name

select * from location

select * from department

how many jobs in the orgnigation with designations

select [function],count(*) [num of employee] from employee e,job j 

where e.job_id=j.job_id

group by j.[function]

--how many employee working in chicago

select count(*),regional_group from employee e

inner join department d on e.department_id=d.department_id

inner join location l on l.location_id=d.location_id

where l.regional_group='chicago'

group by  l.regional_group

--non equi join

?

--self join

select * from employee

select * from employee

--display the employee detail with manager name

select e.employee_id,e.last_name,e.manager_id,m.last_name  from employee e,employee m

where e.manager_id=m.employee_id

select e.employee_id,e.last_name,e.manager_id ,f.last_name from employee e inner join

employee f on  e.manager_id =f.employee_id

--display employee salary who earn more salary then his mangager salary

select e.employee_id,e.last_name,e.manager_id ,m.last_name from employee e inner join

employee m on  e.manager_id =m.employee_id

where e.salary>m.salary

--show the number of employee working under every manager

select count (e.employee_id),m.last_name as [manager name] from employee e

inner join employee m

on e.manager_id=m.employee_id

group by m.last_name

--outer join

select * from employee

insert into employee values(

7371,'singh','anish','kumar',667,7370,'2/2/1990',15000,null,50

)

select last_name,d.department_id,d.name from employee e

right outer join department d

on e.department_id=d.department_id

select last_name,d.department_id,d.name from employee e

left outer join department d

on e.department_id=d.department_id

select last_name,d.department_id,d.name from employee e

right outer join department d

on e.department_id=d.department_id 

where d.name in('sales','operation')

select * from job

select distinct j.[function],d.department_id from employee e

inner join department d

on e.department_id=d.department_id

inner join job j

on j.job_id=e.job_id

select  j.[function],d.department_id from employee e

inner join department d

on e.department_id=d.department_id

inner join job j

on j.job_id=e.job_id

--=========================

NOW Practise Session End

SQL Practise content end.:---------

About Dot.Net Frame WOrk

The .NET Framework (pronounced dot net) is a software framework developed by Microsoft that runs primarily on Microsoft Windows. It includes a large library and provides language interoperability (each language can use code written in other languages) across several programming languages. Programs written for the .NET Framework execute in a software environment (as contrasted to hardware environment), known as the Common Language Runtime (CLR), an application virtual machine that provides services such as security, memory management, and exception handling. The class library and the CLR together constitute the .NET Framework.



Microsoft started development of the .NET Framework in the late 1990s, originally under the name of Next Generation Windows Services (NGWS). By late 2000 the first beta versions of .NET 1.0 were released.

Generation	Version number	Release date	Development tool	Distributed with
1.0	1.0.3705.0	2002-02-13	Visual Studio .NET	N/A
1.1	1.1.4322.573	2003-04-24	Visual Studio .NET 2003	Windows Server 2003
2.0	2.0.50727.42	2005-11-07	Visual Studio 2005	Windows Server 2003 R2
3.0	3.0.4506.30	2006-11-06	Expression Blend	Windows Vista, Windows Server 2008
3.5	3.5.21022.8	2007-11-19	Visual Studio 2008	Windows 7, Windows Server 2008 R2
4.0	4.0.30319.1	2010-04-12	Visual Studio 2010	N/A
4.5	4.5.50709.17929	2012-08-15	Visual Studio 2012	Windows 8, Windows Server 2012

Web Forms Code Model

When using Visual Studio .NET to create ASP.NET Web applications, you use a standard deployment model: your project is compiled and the resulting files are deployed. The Web Forms code-behind class file (.aspx.vb, or .aspx.cs), but not the .aspx file, is compiled into a project .dll file along with all other class files included in your project. This single project .dll file is then deployed to the server, without any source code. When a request for the page is received, the project .dll file is instantiated and executed.



In the ASP.NET Page class model, the entire Web Forms page is, in effect, an executable program that generates output to be sent back to the browser or client device. In this model, the page goes through a series of processing stages similar to those of other components — initialize, process, and dispose — with two differences:

• The page class performs these steps each time the page is called. The page is initialized, processed, and disposed of every time a round trip to the server occurs.
• The page class has a unique stage —render— that occurs toward the end of the page life cycle, during which output is generated.

Tell Me Something About Yourself" in a Job Interview

Hi,
My name is ABC. I am from Delhi.
I have an experience of 2.5 years of working in a IT which is the fastest growing industry and would like to continue with the same profession.
[I started my career with L&T at Mumbai and it’s been 3 years now.]

I did my education from xyz and am currently doing my graduation from ABC.
I come up with creative solutions to challenges in job.
I am always ready to face challenges and get along with people very well.
I believe in vision and action.
I learn from my mistakes.
I’m a hard worker and I’m pleased to be considered by an organization with such a solid business reputation.


My interests are to play chess, listen music, net surfing and reading.
and at last, I enjoy everything that I do.
This is all about me, (Your Name) from my side.

get month, year from date column in sql server

 get month, year  from date column in sql server

select
datepart(month,getdate()) -- integer (1,2,3...)
,datepart(year,getdate()) -- integer
,datename(month,getdate()) -- string ('September',...)

How to disable Firefox's default drag and drop on all images or anchor tag behavior with jQuery?

$(document).bind("dragstart", function() {
     return false;
});

////////////////////

$(document).bind("dragstart", function(e) {
     if (e.target.nodeName.toUpperCase() == "IMG") {
         return false;
     }
});

//////////////////  

Removing Duplicates from a Table in SQL Server

Checking for Duplicates

On any version of SQL Server, you can identify duplicates using a simple query, with GROUP BY and HAVING, as follows:
DECLARE @table TABLE (data VARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')

SELECT  data
      , COUNT(data) nr
FROM    @table
GROUP BY data
HAVING  COUNT(data) > 1

Removing Duplicate Rows in SQL Server

 

DECLARE @table TABLE (data VARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')

SET ROWCOUNT 1
DELETE FROM @table WHERE data = 'duplicate row'
SET ROWCOUNT 0


get full reference from given link

https://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/