Wednesday, 8 June 2011

Learn SQL Queries --Part One

CREATE TABLE [dbo].[employee]([empid] [varchar](10) NULL,[empname] [varchar](100) NULL,[salary] [int] NULL,[mgrid] [varchar]
)
 CREATE TABLE [dbo].[phone]([empid] [varchar](10) NULL,[phone] [varchar]
)

 Insert into employee select 'E0001','RAM',25000,'E0002' Union all
select
'E0002','SHYAM',55000,'E0003'Union all
select
'E0003','RAMSHYAM',55000,''Union all
select
'E0004','JOSHI',35000,'E0002'/*
Insert into employee
select 'E0001','RAM',25000,'E0002'
Insert into employee
select 'E0002','SHYAM',55000,'E0003'
Insert into employee
select 'E0003','RAMSHYAM',55000,''
Insert into employee
select 'E0004','JOSHI',35000,'E0002'
*/
Insert into phone select 'E0001',9800000000Insert into phone select 'E0001',9800000001Insert into phone select

 /* Select all employees who doesn't have phone */select empid,empname from employee where

 /* Select the employee names who is having more than one phone numbers. */Select
(
eM.empid,em.Empname from employee EM,Select empid,count(empid)as countx from phonegroup by empid having count(empid)>1)As PTwhere

 /* Select the details of 3 max salaried employees from employee table. */select top 3 salary ,empidfrom

 /*Display all managers from the table. (manager id is same as emp id)*/select * from employeeselect empid,empname from employee whereempid

 /* Write a Select statement to list the Employee Name, Manager Name under a particular manager */select e1.empid,e1.empname,e1.mgrid,e2.empname as MgrNamefrom employee e1 Inner Join employee e2ON e1.mgrid=e2.empid
in (Select mgrid from employee)
employee order by salary desc
em.empid=pt.empid
empid not in (select empid from phone)
'E0002',9800000002


(10) NULL ON [PRIMARY]

(10) NULL ON [PRIMARY]

No comments: