|
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>
|