Friday, June 15, 2012

SQL Join, Cross Apply, Cross Join and Replace

--CREATE TABLE department
--(
--    DepartmentID INT,
--    DepartmentName VARCHAR(20)
--);

--CREATE TABLE employee
--(
--    LastName VARCHAR(20),
--    DepartmentID INT
--);

--INSERT INTO department(DepartmentID, DepartmentName) VALUES(31, 'Sales');
--INSERT INTO department(DepartmentID, DepartmentName) VALUES(33, 'Engineering');
--INSERT INTO department(DepartmentID, DepartmentName) VALUES(34, 'Clerical');
--INSERT INTO department(DepartmentID, DepartmentName) VALUES(35, 'Marketing');

--INSERT INTO employee(LastName, DepartmentID) VALUES('Rafferty', 31);
--INSERT INTO employee(LastName, DepartmentID) VALUES('Jones', 33);
--INSERT INTO employee(LastName, DepartmentID) VALUES('Steinberg', 33);
--INSERT INTO employee(LastName, DepartmentID) VALUES('Robinson', 34);
--INSERT INTO employee(LastName, DepartmentID) VALUES('Smith', 34);
--INSERT INTO employee(LastName, DepartmentID) VALUES('John', NULL);

SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID;

SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID;

SELECT * FROM employee NATURAL JOIN department ON employee.DepartmentID = department.DepartmentID;
SELECT * FROM employee CROSS JOIN department where employee.LastName=Jones;

SELECT * FROM employee

SELECT * FROM department

SELECT e.*,d.* FROM employee e full outer JOIN department d ON e.DepartmentID = d.DepartmentID
 where d.DepartmentID is null and e.DepartmentID is null;

SELECT * FROM employee RIGHT  JOIN department ON employee.DepartmentID = department.DepartmentID;


SELECT RIGHT(LTRIM(RTRIM(value1)),3) , value2, MAX(location), MAX(date), MAX(category),
 SUM(debitamount), SUM(creditamount) FROM table1 GROUP BY RIGHT(LTRIM(RTRIM(value1)),3), value2

 select RIGHT(LTRIM(RTRIM(LastName)),3) from employee where DepartmentID=31
  select RIGHT(RTRIM(LastName),2) from employee where DepartmentID=31
select Left(RTRIM(LTRIM(LastName)),3) from employee where DepartmentID=31

select * from employee group by Departmentid


create table testtable (id int identity(1,1), PersonID int,    Unit varchar(10))
insert into testtable values (1,'Che')
insert into testtable  values (2,'Mat')
insert into testtable  values (1,'Phy')
insert into testtable values (2,'Che2')
insert into testtable  values (2,'Mat2')
insert into testtable  values (2,'Phy2')
insert into testtable  values (3,'Phy3')


SELECT t1.PersonID,
       Units =REPLACE( (SELECT Unit AS [data()]
           FROM testtable t2
          WHERE t2.PersonID = t1.PersonID
          ORDER BY Unit
            FOR XML PATH('')
            ), ' ', ',,')
      FROM testtable t1
      GROUP BY PersonID ;
select * from employee


select LastName,DepartmentID from employee

SELECT e1.DepartmentID,
  DepartmentID=replace((SELECT LastName AS [data()]
    FROM employee e2 WHERE e2.DepartmentID=e1.DepartmentID  ORDER BY DepartmentID
    FOR XML PATH('')),' ',',')FROM employee e1 GROUP BY DepartmentID   



SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY
        (
        SELECT  TOP 3 *
        FROM    t2
        WHERE   t2.t1_id = t1.id
        ORDER BY
                t2.rank DESC
        ) t2o
   SELECT * from employee cross join department    
       
SELECT  employee.*, department.* FROM    employee
CROSS APPLY
        (
        SELECT  *
        FROM    department
        --WHERE   department.DepartmentID = employee.DepartmentID       
        where employee.LastName='Rafferty') department


SELECT * FROM    employee
CROSS APPLY
        (
        SELECT  *
        FROM    department
        --WHERE   department.DepartmentID = employee.DepartmentID       
        where department.departmentName='sales') department
       
        select * from Employee
        CROSS APPLY
        (select * from department where department.departmentname='sales')department
       
        create table sample
        (s_id int identity(1,1) primary key,
         s_Name varchar(10),
         s_Price int,
         s_product varchar(29),
          constraint candidate_price UNIQUE(s_Price))
         
         
             create table sample2
        (s_id int identity(1,1) primary key,
         s_Name varchar(10),
         s_Price int,
         s_product varchar(29),
          constraint chck_sample check(s_Price>10))
         
          insert into sample2(s_Name,s_Price,s_product)
      
        
         
          select  'Manu',5,'pen'
          union all
         
          select  'Raj',6,'pen'
         
select @@MAX_CONNECTIONS

select @@SERVERNAME

No comments:

Post a Comment