Modul 4
mysql> create database pethouse;
Query OK, 1 row affected (0.03 sec)
mysql> use pethouse;
Database changed
mysql> create table employee_join(id
int(11) primary key,first_name varchar(15),last_name varchar(15),start_date
date,end_date date,salary float(8,2),city varchar(10),job_id int(11),foreign
key(job_id) references job(job_id));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into employee_join
values('1','jason','martin','1996-07-25','2006-07-25','1235.56','toronto','1'),('2','alison','mathews','1976-03-21','1986-02-21','6662.78','vancouver','2');
Query OK, 2 rows affected (0.11 sec)
Records: 2
Duplicates: 0 Warnings: 0
mysql> insert into employee_join
values('3','james','smith','1978-12-12','1990-03-15','6545.78','vancoufer','2'),('4','celia','rice','1982-10-24','1999-04-21','2345.78','vancouver','3');
Query OK, 2 rows affected (0.37 sec)
Records: 2
Duplicates: 0 Warnings: 0
mysql> insert into employee_join
values('5','roberth','black','1984-01-15','1998-08-08','2335.78','vancouver','2'),('6','linda','green','1987-07-30','1996-01-04','4323.78','new
york','2');
Query OK, 2 rows affected (0.36 sec)
Records: 2
Duplicates: 0 Warnings: 0
mysql> insert into employee_join
values('7','david','larry','1990-12-31','1998-02-12','7898.78','new
york','3'),('8','james','cat','1996-09-17','2002-04-15','1233.78','vancouver','2');
Query OK, 2 rows affected (0.05 sec)
Records: 2
Duplicates: 0 Warnings: 0
mysql> insert into employee_join
values('9','hercule','poirod','1973-05-23','2001-08-09','4313.98','brussels','5'),('10','lincoln','rhyme','1999-05-25','2011-07-13','3213.98','new
york','6');
Query OK, 2 rows affected (0.36 sec)
Records: 2
Duplicates: 0 Warnings: 0
mysql> insert into employee_join
values('11','sherlock','holmes','1923-08-12','1945-07-21','4124.21','london','5');
Query OK, 1 row affected (0.05 sec)
mysql> create table job(job_id int(11)
primary key,title varchar(25));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into job values('1','programer'),('2','tester'),('3','manager'),('4','spy'),('5','detective'),('6','vorensics'),('7','developer');
Query OK, 7 rows affected (0.05 sec)
Records: 7
Duplicates: 0 Warnings: 0
mysql> create view view_2 as select
*from employee_join;
Query OK, 0 rows affected (0.12 sec)
mysql> select
full_name(first_name,last_name),salary,city from view_2;
