More Complex Views: inner join


1>
2> create table employee(
3>     ID          int,
4>     name        nvarchar (10),
5>     salary      int )
6> 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> select * from employee;
4> 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>
3> --More Complex Views
4>
5> CREATE VIEW EmployeeJob
6> AS
7> SELECT   e.id,
8>          e.name,
9>          j.title
10> FROM     Employee AS e
11> INNER JOIN   Job AS j
12>       ON e.ID = j.ID
13> GO
1>
2> select * from EmployeeJob
3> GO
id          name       title
----------- ---------- ----------
          1 Jason      Developer
          2 Robert     Tester
          3 Celia      Designer
          4 Linda      Programmer

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

Related examples in the same category

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

More Complex Views: inner join : Create View « View « SQL Server / T-SQL

Related examples in the same category