forked from disouzam/sql-tutorial
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathinsert_select.sql
More file actions
47 lines (43 loc) · 995 Bytes
/
insert_select.sql
File metadata and controls
47 lines (43 loc) · 995 Bytes
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
create table job (
ident integer primary key autoincrement,
name text not null,
billable real not null
);
insert into job values
(null, 'calibrate', 1.5),
(null, 'clean', 0.5);
create table person (
ident integer primary key autoincrement,
name text not null
);
insert into person values
(null, 'mik'),
(null, 'po'),
(null, 'tay');
create table work (
person text not null,
job text not null
);
insert into work values
('mik', 'calibrate'),
('mik', 'clean'),
('mik', 'complain'),
('po', 'clean'),
('po', 'complain'),
('tay', 'complain');
-- [keep]
create table new_work (
person_id integer not null,
job_id integer not null,
foreign key (person_id) references person (ident),
foreign key (job_id) references job (ident)
);
insert into new_work
select
person.ident as person_id,
job.ident as job_id
from
(person inner join work on person.name = work.person)
inner join job on job.name = work.job;
select * from new_work;
-- [/keep]