Learn Sql server in hindi/urdu part-18(Updateable Views IN SQL SERVER)

CREATE TABLE tblEmployee

(

  Id int Primary Key,

  Name nvarchar(30),

  Salary int,

  Gender nvarchar(10),

  DepartmentId int

)





Insert into tblEmployee values (1,'John', 5000, 'Male', 3)

Insert into tblEmployee values (2,'Mike', 3400, 'Male', 2)

Insert into tblEmployee values (3,'Pam', 6000, 'Female', 1)

Insert into tblEmployee values (4,'Todd', 4800, 'Male', 4)

Insert into tblEmployee values (5,'Sara', 3200, 'Female', 1)

Insert into tblEmployee values (6,'Ben', 4800, 'Male', 3)









Create view vWEmployeesDataExceptSalary

as

Select Id, Name, Gender, DepartmentId

from tblEmployee





Select * from vWEmployeesDataExceptSalary

select * from tblEmployee





Update vWEmployeesDataExceptSalary



Set Name = 'salman' Where Id = 2





Update vWEmployeesDataExceptSalary

set salary=20000 where id=2

Learn Sql server in hindi/urdu part-17(Views/virtual table IN SQL SERVER)

--What is a View?
--A view is nothing more than a saved SQL query. A view can also be considered as a virtual table.


CREATE TABLE tblEmployee
(
  Id int Primary Key,
  Name nvarchar(30),
  Salary int,
  Gender nvarchar(10),
  DepartmentId int
)
CREATE TABLE tblDepartment
(
 DeptId int Primary Key,
 DeptName nvarchar(20)
)

Insert into tblDepartment values (1,'IT')
Insert into tblDepartment values (2,'Payroll')
Insert into tblDepartment values (3,'HR')
Insert into tblDepartment values (4,'Admin')


Insert into tblEmployee values (1,'John', 5000, 'Male', 3)
Insert into tblEmployee values (2,'Mike', 3400, 'Male', 2)
Insert into tblEmployee values (3,'Pam', 6000, 'Female', 1)
Insert into tblEmployee values (4,'Todd', 4800, 'Male', 4)
Insert into tblEmployee values (5,'Sara', 3200, 'Female', 1)
Insert into tblEmployee values (6,'Ben', 4800, 'Male', 3)
Insert into tblEmployee values (7,'salman',51800, 'Male', 4)



select * from tblDepartment
select * from tblEmployee


create view viewforall
as
select * from tblEmployee


select * from viewforall

create view viewforreception
as
select id,Name,Gender,DepartmentId from tblEmployee

select * from viewforreception



create view viewforrecpetionwithdeptname
as

select e.Id,e.Name,e.Gender,d.DeptName from tblEmployee e inner join tblDepartment d on d.DeptId=e.DepartmentId


select * from viewforrecpetionwithdeptname



Create View vWEmployeesCountByDepartment
as
Select DeptName, COUNT(Id) as TotalEmployees
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
Group By DeptName


select * from tblDepartment
select * from tblEmployee


select * from vWEmployeesCountByDepartment

Learn Sql server in hindi/urdu part-16(String fucntions-2 IN SQL SERVER)

Select LTRIM('      Hello')





Select RTRIM (LTRIM('  Hello   '))



Select LOWER('CONVERT This String Into Lower Case')

Select UPPER('CONVERT This String Into Lower Case')

Select REVERSE('ABCDEFGHIJKLMNOPQRSTUVWXYZ')

Select LEN('ABCDEFGHIJKLMNOPQRSTUVWXYZ')

Learn Sql server in hindi/urdu part-15(String fucntions IN SQL SERVER)

use stringfunction





print ASCII('A')

print CHAR(51)









declare @number int

set @number=65

while(@number<=90)

begin



print CHAR(@number)



set @number=@number+1

end

Learn Sql server in hindi/urdu part-15(String fucntions IN SQL SERVER)

use stringfunction





print ASCII('A')

print CHAR(51)









declare @number int

set @number=65

while(@number<=90)

begin



print CHAR(@number)



set @number=@number+1

end

Learn Sql server in hindi/urdu part-14(Union and Union All)

--The UNION operator is used to combine the result-set of two or more SELECT statements.



--Each SELECT statement within UNION must have the same number of columns

--The columns must also have similar data types

--The columns in each SELECT statement must also be in the same order





create table tblIndiaCustomers

(

id int identity,

Name nvarchar(20),

email nvarchar(20)

)

create table tblUKCustomers

(

id int identity,

Name nvarchar(20),

email nvarchar(20)

)





insert into tblIndiaCustomers

values('Aditi','Aditi@yahoo.com')



insert into tblIndiaCustomers

values('Rahul','Rahul@yahoo.com')



insert into tblIndiaCustomers

values('Sundar','Sundar@yahoo.com')



insert into tblIndiaCustomers

values('Ganesh','Ganesh@yahoo.com')





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



insert into tblUKCustomers

values('Mark','Mark@hotmail.com')



insert into tblUKCustomers

values('Ben','Ben@hotmail.com')



insert into tblUKCustomers

values('Linda','Linda@hotmail.com')



insert into tblUKCustomers

values('Suzan','Suzan@hotmail.com')



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





select * from tblIndiaCustomers

union

select * from tblUKCustomers





select * from tblIndiaCustomers

union all

select * from tblUKCustomers

Learn Sql server in hindi/urdu part-13 (Delete and Update using Store Pr...

create proc delete_employee

(

@emp_id int



)



as

begin



delete from employee where emp_id=@emp_id





end









execute delete_employee 3011













create proc  update_employee

(

@emp_id int,

@emp_salary int,

@emp_address nvarchar(100),

@emp_dept_id int



)

as

begin



update employee

set emp_salary=@emp_salary , emp_address=@emp_address ,dem_deptid=@emp_dept_id where emp_id=@emp_id





end



select * from employee





execute update_employee 2003,40000,'R-32 sector sec-11',2