Rename column when creating a view : Create View « View « SQL Server / T-SQL






Rename column when creating a view


1> create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int )
5> GO
1>
2> create table job(
3>     ID              int,
4>     title nvarchar  (10),
5>     averageSalary   int)
6> GO
1>
2>
3> insert into employee (ID, name, salary) values (1,  'Jason', 1234)
4> GO

(1 rows affected)
1> insert into employee (ID, name, salary) values (2,  'Robert', 4321)
2> GO

(1 rows affected)
1> insert into employee (ID, name, salary) values (3,  'Celia', 5432)
2> GO

(1 rows affected)
1> insert into employee (ID, name, salary) values (4,  'Linda', 3456)
2> GO

(1 rows affected)
1> insert into employee (ID, name, salary) values (5,  'David', 7654)
2> GO

(1 rows affected)
1> insert into employee (ID, name, salary) values (6,  'James', 4567)
2> GO

(1 rows affected)
1> insert into employee (ID, name, salary) values (7,  'Alison', 8744)
2> GO

(1 rows affected)
1> insert into employee (ID, name, salary) values (8,  'Chris', 9875)
2> GO

(1 rows affected)
1> insert into employee (ID, name, salary) values (9,  'Mary', 2345)
2> GO

(1 rows affected)
1>
2> insert into job(ID, title, averageSalary) values(1,'Developer',3000)
3> GO

(1 rows affected)
1> insert into job(ID, title, averageSalary) values(2,'Tester', 4000)
2> GO

(1 rows affected)
1> insert into job(ID, title, averageSalary) values(3,'Designer', 5000)
2> GO

(1 rows affected)
1> insert into job(ID, title, averageSalary) values(4,'Programmer', 6000)
2> GO

(1 rows affected)
1>
2>
3>
4> select * from employee;
5> GO
ID          name       salary
----------- ---------- -----------
          1 Jason             1234
          2 Robert            4321
          3 Celia             5432
          4 Linda             3456
          5 David             7654
          6 James             4567
          7 Alison            8744
          8 Chris             9875
          9 Mary              2345

(9 rows affected)
1> select * from job;
2> GO
ID          title      averageSalary
----------- ---------- -------------
          1 Developer           3000
          2 Tester              4000
          3 Designer            5000
          4 Programmer          6000

(4 rows affected)
1>
2> SELECT e.ID 'EmployeeID',e.name 'EmployeeName', j.title 'EmployeeTitle'
3> FROM Employee e INNER JOIN job j
4>         ON e.ID = j.ID
5> GO
EmployeeID  EmployeeName EmployeeTitle
----------- ------------ -------------
          1 Jason        Developer
          2 Robert       Tester
          3 Celia        Designer
          4 Linda        Programmer

(4 rows affected)
1>
2>
3> CREATE VIEW ViewOnEmployee
4> AS
5> SELECT e.ID 'EmployeeID',e.name 'EmployeeName', j.title 'EmployeeTitle'
6> FROM Employee e INNER JOIN job j
7>         ON e.ID = j.ID
8> GO
1>
2> select * from ViewOnEmployee
3> GO
EmployeeID  EmployeeName EmployeeTitle
----------- ------------ -------------
          1 Jason        Developer
          2 Robert       Tester
          3 Celia        Designer
          4 Linda        Programmer

(4 rows affected)
1>
2> drop table employee;
3> drop table job;
4> GO
1>
           
       








Related examples in the same category

1.More Complex Views: inner join
2.Create a simple view and retrieve data from it
3.Simple Views
4.Use view to filter out the NULL data
5.When you create a view, you're really just naming a SQL SELECT statement
6.Stored Procedures as Parameterized Views
7.CREATE VIEW statement called SCHEMA BINDING tells SQL Server to check for dependencies and disallow any modifications that would violate them
8.CREATE VIEW myView WITH SCHEMABINDING
9.WITH CHECK OPTION keeps the results synchronized with the table(s)
10.View cannot be sorted with ORDER BY unless the TOP command is also used
11.Column names in a view can be defined in the CREATE statement
12.Create view based on table-join query