--CREATE TABLE department
--(
-- DepartmentID INT,
-- DepartmentName VARCHAR(20)
--);
CodeProject
--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
