· 9 years ago · Oct 08, 2016, 09:02 PM
1BEGIN TRANSACTION;
2
3CREATE TABLE IF NOT EXISTS 'Salespeople' (
4 'snum' INTEGER,
5 'sname' TEXT,
6 'city' TEXT,
7 'comm' REAL,
8 PRIMARY KEY('snum')
9) WITHOUT ROWID;
10
11INSERT INTO 'Salespeople'
12 (snum,sname,city,comm)
13VALUES
14 (1001 , 'Peel' , 'London' , 0.12),
15 (1002 , 'Serres' , 'San Jose' , 0.13),
16 (1003 , 'Axelrod' , 'New York' , 0.10),
17 (1004 , 'Motica' , 'London' , 0.11),
18 (1007 , 'Pifkin' , 'Barcelona' , 0.15);
19
20
21CREATE TABLE IF NOT EXISTS 'Customers' (
22 'cnum' INTEGER,
23 'cname' TEXT,
24 'city' TEXT,
25 'rating' INTEGER,
26 'snum' INTEGER,
27 PRIMARY KEY('cnum'),
28 FOREIGN KEY('snum') REFERENCES 'Salespeople'('snum')
29) WITHOUT ROWID;
30
31INSERT INTO 'Customers'
32 ('cnum', 'cname', 'city', 'rating', 'snum')
33VALUES
34 (2001 , 'Hoffman' , 'London' , 100 , 1001),
35 (2002 , 'Giovanni' , 'Rome' , 200 , 1003),
36 (2003 , 'Liu' , 'San Jose' , 200 , 1002),
37 (2004 , 'Grass' , 'Berlin' , 300 , 1002),
38 (2006 , 'Clemens' , 'London' , 100 , 1001),
39 (2007 , 'Pereira' , 'Rome' , 100 , 1004),
40 (2008 , 'Cisneros' , 'San Jose' , 300 , 1007);
41
42
43CREATE TABLE if NOT EXISTS 'Orders' (
44 'onum' INTEGER,
45 'amt' REAL,
46 'odate' TEXT,
47 'cnum' INTEGER,
48 'snum' INTEGER,
49 PRIMARY KEY('onum')
50 FOREIGN KEY('cnum') REFERENCES 'Customers'('cnum'),
51 FOREIGN KEY('snum') REFERENCES 'Salespeople'('snum')
52) WITHOUT ROWID;
53
54INSERT INTO 'Orders'
55 ('onum', 'amt', 'odate', 'cnum', 'snum')
56VALUES
57 (3001 , 18.69 , '10/03/1990' , 2008 , 1007),
58 (3003 , 767.19 , '10/03/1990' , 2001 , 1001),
59 (3002 , 1900.10 , '10/03/1990' , 2007 , 1004),
60 (3005 , 5160.45 , '10/03/1990' , 2003 , 1002),
61 (3006 , 1098.16 , '10/03/1990' , 2008 , 1007),
62 (3009 , 1713.23 , '10/04/1990' , 2002 , 1003),
63 (3007 , 75.75 , '10/04/1990' , 2004 , 1002),
64 (3008 , 4723.00 , '10/05/1990' , 2006 , 1001),
65 (3010 , 1309.95 , '10/06/1990' , 2004 , 1002),
66 (3011 , 9891.88 , '10/06/1990' , 2006 , 1001);
67
68COMMIT;