SQL Examples

--1st table
create table community1234_832249(ID int IDENTITY (12,1) not null primary key,CName varchar(30) not null);
select * from community1234_832249
create proc comm1234_832249(@Name varchar(30),@ID int out)
as
begin
insert into community1234_832249(CName) values(@Name)
set @ID=@@identity
end
Declare @result int 
Execute comm1234_832249 'Microsoft',@ID=@result output
Execute comm1234_832249 'Java',@ID=@result output
Execute comm1234_832249 'BIPM',@ID=@result output
Execute comm1234_832249 'Learning & Development',@ID=@result output
print @result
select * from community1234_832249



--2nd tble
create table Employee123_832249(EmployeeID bigint primary key,Name varchar(30),Role varchar(30),Password varchar(30));
select * from Employee123_832249
create proc Emp_832249(@EmployeeID bigint,@Name varchar(30),@Role varchar(30),@Password varchar(30))
as
begin
insert into Employee123_832249 values(@EmployeeID,@Name,@Role,@Password)
end
Execute Emp_832249 402510,'Zacharia','HR','tcs@123'
Execute Emp_832249 520114,'Milow Neo J','LF','tcs@123'
Execute Emp_832249 320694,'Salli Ben','LF','tcs@123'
Execute Emp_832249 281354,'Mathew','HR','tcs@123'

select * from Employee123_832249



--3rd table
create table Employee_Community_Subject12_832249 (CommunityID int foreign key references  community123_832249(ID), 
EmployeeID bigint foreign key references Employee123_832249(EmployeeID) , Subject varchar(30), DateOfPosting datetime );
select * from Employee_Community_Subject12_832249 
create proc Emp_comm1_832249(@CommunityID int,@EmployeeID bigint,@Subject varchar(30),@DateOfPosting datetime)
as
begin
insert into Employee_Community_Subject12_832249  values(@CommunityID,@EmployeeID,@Subject,@DateOfPosting)
end
Execute Emp_comm1_832249 12,320694,'Microsoft & VSTO','08/12/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','09/13/13'
Execute Emp_comm1_832249 15,520114,'Self Discipline & Persistance','10/10/13'
Execute Emp_comm1_832249 13,320694,'Comapatibility with Java Swing','11/15/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','12/14/13'
select * from Employee_Community_Subject12_832249 



--Display the Employee & community details while validiting the login credentials using employeId & Passoword.
create proc create1234
(@Empid bigint,@password varchar(30))
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=@Empid and E.Password='tcs@123')as D
on D.CommunityID=C.ID
end
 
 Execute create1234 320694,'tcs@123'

----Display the Employee details & Subject details for the posts from 13 sept 2013 to tilldate considering
 --different communities & subject line starting with M & ending with any charecter.

create proc abc12345
 as
 begin
 select D.CommunityID,D.Subject,D.DateofPosting,D.EmployeeID,C.CName from community1234_832249 as C inner join 
 (select S.CommunityID,S.Subject,S.DateofPosting,S.EmployeeID from  Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.Subject like 'M%' and S.DateofPosting between 
'09/13/14' and getDate()) as D on C.ID=D.CommunityID where C.CName like 'M%'
 end
 Execute abc12345

-- Select the Subject line details & Employee details of those who posted more than once,

  --on behalf of the Learning & Development Community in the sorting order of Date. 
alter proc xyz12
  as
  begin
 select E.EmployeeID,E.Name,D.CommunityID from Employee123_832249 as E 
inner join (select C.ID,C.CName,S.CommunityID, 
 S.EmployeeID  from community1234_832249 as C inner join Employee_Community_Subject12_832249 as S
on C.ID=S.CommunityID  group by S.EmployeeID,S.CommunityID,
C.ID,C.CName having count(S.EmployeeID)>1 ) as D on E.EmployeeID=D.EmployeeID
 where D.CName='Learning_and_Development'
 end
 Execute xyz12

--Display the Community detail with the Employee details who haven't posted anything.
create proc create123456
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.DateofPosting IS NULL)as D
on D.CommunityID=C.ID
end
Execute create123456

--search
create proc search1
(@EmployeeID bigint)
as
begin
select D.EmployeeID,E.Name,E.Role,D.DateofPosting,D.Subject from Employee123_832249 as E inner join (select S.EmployeeID,C.CName,S.Subject,S.DateofPosting from  community1234_832249 as C inner
join Employee_Community_Subject12_832249 as S on C.ID=S.CommunityID) as D on D.EmployeeID=E.EmployeeID where D.EmployeeID=@EmployeeID
end
Execute search1 320694

--modify
create proc modify1
(@empid bigint, @Name varchar(30),@Role varchar(30),@password varchar(30))
as
begin
update Employee123_832249 set Name=@Name,Role=@Role,Password=@password where EmployeeID=@empid
end

Execute modify1 320694,Swati,Lecturer,swati
select * from Employee123_832249

GETDATE() Returns the current date and time
NOW() Returns the current date and time
ALTER TABLE Persons
DROP COLUMN DateOfBirth
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year
TRUNCATE TABLE table_name
DROP TABLE table_name
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;


SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;



SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

select distinct m.name from students s
inner join mentor_ralationships mr on mr.student_id=s.student_id
inner join mentors m on m.mentoir_id=mr.mentor_id
where s.teacher_id=1;


SELECT     s.StudentID
    , s.FName
    , s.LName
    , s.Gender
    , s.BirthDate
    , s.Email
    , r.HallPref1
    , h1.hallName as Pref1HallName
    , r.HallPref2 
    , h2.hallName as Pref2HallName
    , r.HallPref3
    , h3.hallName as Pref3HallName
FROM  dbo.StudentSignUp AS s 
INNER JOIN RoomSignUp.dbo.Incoming_Applications_Current AS r 
    ON s.StudentID = r.StudentID 
INNER JOIN HallData.dbo.Halls AS h1 
    ON r.HallPref1 = h1.HallID
INNER JOIN HallData.dbo.Halls AS h2
    ON r.HallPref2 = h2.HallID
INNER JOIN HallData.dbo.Halls AS h3
    ON r.HallPref3 = h3.HallID   


extra
--1st table
create table community12341_832249(ID int primary key,CName varchar(30) not null);
select * from community1234_832249
create proc comm1234_832249(@Name varchar(30),@ID int out)
as
begin
insert into community1234_832249(CName) values(@Name)
set @ID=@@identity
end
Declare @result int 
Execute comm1234_832249 'Microsoft',@ID=@result output
Execute comm1234_832249 'Java',@ID=@result output
Execute comm1234_832249 'BIPM',@ID=@result output
Execute comm1234_832249 'Learning & Development',@ID=@result output
print @result
select * from community1234_832249

--2nd tble
create table Employee123_832249(EmployeeID bigint primary key,Name varchar(30),Role varchar(30),Password varchar(30));
select * from Employee123_832249
create proc Emp_832249(@EmployeeID bigint,@Name varchar(30),@Role varchar(30),@Password varchar(30))
as
begin
insert into Employee123_832249 values(@EmployeeID,@Name,@Role,@Password)
end
Execute Emp_832249 402510,'Zacharia','HR','tcs@123'
Execute Emp_832249 520114,'Milow Neo J','LF','tcs@123'
Execute Emp_832249 320694,'Salli Ben','LF','tcs@123'
Execute Emp_832249 281354,'Mathew','HR','tcs@123'

select * from Employee123_832249

--3rd table
create table Employee_Community_Subject12_832249 (CommunityID int foreign key references  community123_832249(ID), 
EmployeeID bigint foreign key references Employee123_832249(EmployeeID) , Subject varchar(30), DateOfPosting datetime );
select * from Employee_Community_Subject12_832249 
create proc Emp_comm1_832249(@CommunityID int,@EmployeeID bigint,@Subject varchar(30),@DateOfPosting datetime)
as
begin
insert into Employee_Community_Subject12_832249  values(@CommunityID,@EmployeeID,@Subject,@DateOfPosting)
end
Execute Emp_comm1_832249 12,320694,'Microsoft & VSTO','08/12/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','09/13/13'
Execute Emp_comm1_832249 15,520114,'Self Discipline & Persistance','10/10/13'
Execute Emp_comm1_832249 13,320694,'Comapatibility with Java Swing','11/15/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','12/14/13'
select * from Employee_Community_Subject12_832249 

--Display the Employee & community details while validiting the login credentials using employeId & Passoword.
/*create proc create12_swati
as
begin
select E.EmployeeID,E.Name,E.role,E.Password,R.CommunityID from Employee123_832249 as E inner join 
Employee_Community_Subject12_832249 as R on E.EmployeeID=R.EmployeeID
end
Execute create12_swati*/


create proc create1234
(@Empid bigint,@password varchar(30))
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=@Empid and E.Password='tcs@123')as D
on D.CommunityID=C.ID
end
 
 Execute create1234 320694,'tcs@123'



/*select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 
as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=281354 and E.Password='tcs@123'

select * from Employee_Community_Subject12_832249;
select CName,ID from community1234_832249;


create proc create123
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join (select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=@Empid and E.Password=@password)as D
on D.CommunityID=C.ID
end
Execute create123

create proc login1
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Role,E.Name from  Employee123_832249 as E 
where EmployeeID=@EmployeeID and Password=@Password
end
Execute login1 402510,'tcs@123'
create proc login12
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Role,C.Subject,C.DateOfPosting from  Employee_Community_Subject12_832249  as C inner join Employee123_832249 as E on C.EmployeeID=E.EmployeeID 
where E.EmployeeID=@EmployeeID and Password=@Password
end
Execute login12 402510,'tcs@123'
Execute login12 520114,'tcs@123'
Execute login12 281354,'tcs@123'

create proc login123
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Name,E.Role,R.Subject,R.DateofPosting from Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID where in (select C.ID from community123_832249 
as C on C.ID = R.CommunityID)
end
create proc login1233
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Name,E.Role,R.Subject,R.DateofPosting from Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID inner join  community123_832249 
as C on C.ID = R.CommunityID where E.EmployeeID=@EmployeeID and Password=@Password 
end
Execute login1233 520114,'tcs@123'
Execute login1233 320694,'tcs@123'*/
--Display the Employee details & Subject details for the posts from 13 sept 2013 to tilldate considering
 --different communities & subject line starting with M & ending with any charecter.
 /*create proc Empp_1

 as
 begin
 select E.Name,E.Role,R.Subject from 
  Employee123_832249 as E inner join Employee_Community_Subject12_832249 as R on R.EmployeeID=E.EmployeeID 
  where R.Subject like 'M%' and R.DateofPosting>'09/13/13'
 end
 Execute Empp_1*/

 create proc abc12345
 as
 begin
 select D.CommunityID,D.Subject,D.DateofPosting,D.EmployeeID,C.CName from community1234_832249 as C inner join 
 (select S.CommunityID,S.Subject,S.DateofPosting,S.EmployeeID from  Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.Subject like 'M%' and S.DateofPosting between 
'09/13/14' and getDate()) as D on C.ID=D.CommunityID where C.CName like 'M%'
 end
 Execute abc12345


 -- Select the Subject line details & Employee details of those who posted more than once,
  --on behalf of the Learning & Development Community in the sorting order of Date. 
  
  create proc check12
  as 
  begin
  select count(R.EmployeeID),
  E.Name,E.Role,R.Subject from Employee123_832249 as E
  inner join Employee_Community_Subject12_832249 as R on R.EmployeeID=E.EmployeeID inner join 
  community123_832249 as C on C.ID=R.CommunityID
  where C.Name='Learning & Development'
  group by 
  having count(R.EmployeeID)>1
  end
 
  create proc check12
  as 
  begin
  select CommunityID,count(EmployeeID),
  from  Employee_Community_Subject12_832249 as R inner join community123_832249 as C on C.ID=R.CommunityID
  inner join 
  where C.CName='Learning & Development'
  group by CommunityID
  having count(EmployeeID) > 1
  end

  alter proc xyz12
  as
  begin
 select E.EmployeeID,E.Name,D.CommunityID from Employee123_832249 as E 
inner join (select C.ID,C.CName,S.CommunityID, 
 S.EmployeeID  from community1234_832249 as C inner join Employee_Community_Subject12_832249 as S
on C.ID=S.CommunityID  group by S.EmployeeID,S.CommunityID,
C.ID,C.CName having count(S.EmployeeID)>1 ) as D on E.EmployeeID=D.EmployeeID
 where D.CName='Learning_and_Development'
 end
 Execute xyz12

--Display the Community detail with the Employee details who haven't posted anything.

  create proc abc1
  as
  begin
  select C.Name,E.Name,E.Role,R.Subject from Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID inner join  community123_832249 
as C on C.ID = R.CommunityID where R.DateofPosting=null
  end
  Execute abc1

create proc create123456
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.DateofPosting IS NULL)as D
on D.CommunityID=C.ID
end
Execute create123456

 
 

   --Create a console application for the Modification & Search of the Employees given in the above Employee Table. Search the Employee with the EmployeeId & display the details. Then Modify the
--employee record for the Relevant fields. 
create proc search
(@EmployeeID bigint)
as
begin
select E.Name,E.Role,R.Subject,R.DateofPosting from  Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID where E.EmployeeID=@EmployeeID
end
Execute search 281354


create proc modify
(@Name varchar(30),@Role varchar(30),@Subject varchar(30),@DateofPosting DateTime)
as
begin
update Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID where E.Name=@NameE.Role=@Role, R.Subject=@Subject, R.DateofPosting=@DateofPosting
end
create proc search1
(@EmployeeID bigint)
as
begin
select D.EmployeeID,E.Name,E.Role,D.DateofPosting,D.Subject from Employee123_832249 as E inner join (select S.EmployeeID,C.CName,S.Subject,S.DateofPosting from  community1234_832249 as C inner
join Employee_Community_Subject12_832249 as S on C.ID=S.CommunityID) as D on D.EmployeeID=E.EmployeeID where D.EmployeeID=@EmployeeID
end
Execute search1 320694



create proc modify1
(@empid bigint, @Name varchar(30),@Role varchar(30),@password varchar(30))
as
begin
update Employee123_832249 set Name=@Name,Role=@Role,Password=@password where EmployeeID=@empid
end

Execute modify1 320694,Swati,Lecturer,swati
select * from Employee123_832249